Практический опыт обновления MySQL 5.7 до версии 8.0

    image

    Недавно мы обновили свои сервера с MySQL 5.7 на 8.0.

    Оставим за рамками этой статьи зачем и какие новые плюшки появились в MySQL 8.0, а вместо этого расскажем о том, с какими сложностями мы столкнулись в процессе обновления.

    Во-первых, перед обновлением стоит посмотреть на список изменений и поправить свой конфиг-файл.

    Как минимум, удалены следующие параметры:
    innodb_file_format, innodb_file_format_check, innodb_file_format_max,innodb_large_prefix
    query_cache_limit, query_cache_min_res_unit, query_cache_size, query_cache_type, query_cache_wlock_invalidate.

    В параметре sql_mode удалён в частности NO_AUTO_CREATE_USER — что особенно важно, т.к. в MySQL 5.7 он был включен по-умолчанию.

    Инструкция по in-place upgrade есть у Percona. И в общем случае можно следовать ей, однако нам удалось так обновить только один кластер, у остальных попытка такого обновления заканчивалась неудачно с подобной ошибкой:

    2019-06-22T05:04:18.510888Z 1 [ERROR] [MY-011014] [Server] Found partially upgraded DD. Aborting upgrade and deleting all DD tables. Start the upgrade process again.
    2019-06-22T05:04:23.115018Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
    2019-06-22T05:04:23.115655Z 0 [ERROR] [MY-010119] [Server] Aborting
    

    Поэтому остальные кластеры мы обновляли путем поднятия нового пустого инстанса и восстановления дампа БД с предыдущей версии.

    Чтобы сделать такое, во-первых, потребуется дамп БД. И тут поджидает опасность #1 — сделанный стандартным образом дамп:

    mysqldump -u root -p --hex-blob --default-character-set=utf8mb4 --all-databases --triggers --routines --events > dump.sql

    не восстанавливается, выдавая ошибку:

    ERROR 3554 (HY000) at line 15915: Access to system table 'mysql.innodb_index_stats' is rejected.
    

    Описание есть в багтрекере MySQL (со статусом Not a bug :), там же есть и совет как делать дамп, чтобы его таки можно было восстановить:

    mysqldump -u root -p --hex-blob --default-character-set=utf8mb4 --all-databases --triggers --routines --events --ignore-table=mysql.innodb_index_stats --ignore-table=mysql.innodb_table_stats > dump.sql

    Но при попытке использовать такой дамп, если в нём присутствовали триггеры (а у нас они были), может поджидать опасность #2, в виде вот такой ошибки:

    ERROR 1231 (42000) at line 54: Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER'
    

    Причина этого в том, как MySQL использует sql_mode для триггеров, а именно: MySQL сохраняет значение sql_mode для триггера в момент его создания и выполняет его потом всегда с этим значением. И соответственно сохраняет это значение в дамп.

    Описание этого в справке:
    dev.mysql.com/doc/refman/8.0/en/create-trigger.html
    MySQL stores the sql_mode system variable setting in effect when a trigger is created, and always executes the trigger body with this setting in force, regardless of the current server SQL mode when the trigger begins executing.

    Что же нам делать? Мы просто с помощью sed вырезали NO_AUTO_CREATE_USER из готового дампа. Подобной командой:

    sed "s/50003 SET sql_mode              = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER/50003 SET sql_mode              = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO/g" dump.sql > dump2.sql
    

    После этого дамп успешно восстанавливается, но нас поджидает опасность #3 (вполне ожидаемая правда) — системные таблицы восстановлены в состоянии от версии 5.7 и в логах у нас следующие ошибки:

    [ERROR] [MY-013143] [Server] Column count of mysql.user is wrong. Expected 51, found 45. The table is probably corrupted

    По опыту работы с прошлыми версиями это лечится запуском mysql_upgrade — но начиная с версии 8.0.16 — это не работает, т.к. mysql_upgrade объявлен deprecated и ничего не делает.

    Теперь, чтобы вызвать обновление системных таблиц, необходимо запустить MySQL с опцией upgrade=FORCE.

    На свежей ubuntu это можно сделать следующим образом:

    systemctl set-environment MYSQLD_OPTS="--upgrade=FORCE"

    После чего перезапустить MySQL. Ну и после успешного обновления удалить её:

    systemctl unset-environment MYSQLD_OPTS
    Поделиться публикацией
    AdBlock похитил этот баннер, но баннеры не зубы — отрастут

    Подробнее
    Реклама

    Комментарии 12

      0
      А зачем базу mysql надо было дампить, проще было выдернуть привилегии пользователей.
        0
        Не понял, как бы это помогло? Есть БД на MySQL 5.7, in-place upgrade падает с ошибкой, как апгрейднуться без дампа? Если есть какой-то способ — расскажите, нам уже не надо, но кому-то ещё поможет :)
          0
          Ну как минимум избавило бы от «Column count of mysql.user is wrong» и «Access to system table 'mysql.innodb_index_stats' is rejected.». Из базы mysql нужны только пользователи и их права на базы, а их можно выдернуть так:

          > mysql -uroot --skip-column-names -A -e "SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') AS query FROM mysql.user WHERE user NOT IN ('root')" | mysql  -uroot --skip-column-names -A | sed 's/$/;/g'


          на выходе получим привлегии всех юзеров (кроме рута) в виде sql запросов:

            GRANT USAGE ON *.* TO 'bitrix'@'%' IDENTIFIED BY PASSWORD '*6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4';
            GRANT ALL PRIVILEGES ON `bitrix`.* TO 'bitrix'@'%';


          которые надо будет засунуть в новый mysql.

          Да, если используются более сложные привилегии на таблицы, или еще что-то типа proxies_priv, этого не будет достаточно. Но я за 10 лет работы с клиентскими mysql ни разу не видел, чтобы такой функционал использовался.
            +1
            Т.е. сделать дамп еще и без mysql.user и потом привилегии отдельно восстановить? Мне кажется общий дамп и upgrade=FORCE меньше телодвижений требует и безопаснее — точно ничего не теряется. Но может кому-то Ваш вариант понравится больше.
        +1

        как раз на днях пытался перекатиться с 5.7 на 8.0.
        вы ещё (кажется) не упомянули про то, что там новый способ авторизации с SHA2.
        короче, мигрировал я в итоге на MariaDB 10.4

          0
          Новый способ отключается, если надо, Percona Server при установке вообще спрашивает использовать новый или оставить старый.
          +1
          Еще они синтаксис немного покрутили в сторону ужесточения, особенно в датах, так что всякие там битриксы на 8 мускуле падают в рандомных местах.
            0
            А еще можно через xtrabackup поднимать реплику сразу на 8-м и потом переключаться на нее, это на порядок быстрее чем mysqldump, особенно если у вас данных прилично (больше 1 ТБ). Мы только в процессе обновления, на текущий момент апнуто 3 из 20 бд, но пока проблем не обнаружено.
              +1
              Расскажите подробнее, как вы делали? В результате xtrabackup получится же копия на 5.7 или я чего-то не знаю? И если так, то чем оно от inplace-upgrade отличаться будет, который у нас зафейлился :(
              +1

              Раньше была возможность обновления через слейв. Сделали так 5.5 -> слейв 5.6 -> мастер 5.6 -> слейв 5.7 -> мастер/слейв 5.7. Прошло достаточно гладко, разве что всякие sql_mode:) Не знаете как сейчас с этим?

                +1
                Так и обновлялись, сначала слейвы на 8.0, потом переключение и мастер.
                +1
                В логах должна быть причина фейла in-place upgrade.
                В моих текстах это было например из-за
                1. In-place upgrade to MySQL 8.0 is not supported if tables contain old temporal columns in pre-5.6.4 format (TIME, DATETIME, and TIMESTAMP columns without support for fractional seconds precision).
                2. There must be no partitioned tables that use a storage engine that does not have native partitioning support.

                причин там уйма
                dev.mysql.com/doc/refman/8.0/en/upgrade-prerequisites.html

                после устранения in-place влёт прошёл.

                Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                Самое читаемое