Как обновиться с MySQL 5 до MySQL 8

Сегодня MySQL 8.0 стала версией доступной для скачивания по умолчанию (предыдущей актуальной версией была MySQL 5.7). Т.е. теперь считается, что это готовая для использования в продакшене версия. Решение проблем, возникавших в процессе обновления, заняло у меня несколько часов – очень надеюсь, что эта заметка поможет вам сэкономить время.

Я обновлял MySQL до версии 8.0 в Windows. Но я вполне допускаю, что аналогичные ошибки будут возникать и в Linux.

Мой веб-сервер и СУБД MySQL установлены по этой инструкции. Если вы устанавливали другим способом, то у вам могут быть другие пути к папкам – учитывайте это.

Бэкап всех баз данных MySQL

Перед тем, как выполнить обновление, обязательно сделайте резервные копии! Файлы баз данных MySQL 5 и MySQL 8 не являются совместимыми в полной мере, и при запуске MySQL 8 происходит автоматическое изменение структуры файлов баз данных. Это означает, что если что-то пойдёт не так и вы захотите просто откатиться до MySQL 5, это может оказаться просто невозможным.

Я делаю бэкапы двумя способами:

• экспорт всех баз данных в файл;
• резервная копия файлов, в которых хранятся базы данных.

Экспорт всех баз данных можно сделать прямо в phpMyAdmin, для этого перейдите на главную страницу и выберите вкладку «Экспорт».

Рекомендую НЕ ставить галочку «Добавить выражение DROP DATABASE IF EXISTS», поскольку если файл получится большим, то его придётся импортировать в несколько этапов, а если там будет использоваться конструкция DROP DATABASE IF EXISTS, то при каждом начале импорта будут удаляться ранее принятые записи – т.е. будет невозможно довести операцию до конца.

Можно делать резервные копии и с помощью утилиты mysqldump. Особенности использования mysqldump в Windows:

• не используйте PowerShell, используйте командную строку (в PowerShell не работает перенаправление вывода, обозначаемого символами > и <), для этого в PowerShell выполните команду

cmd

• перейдите в каталог с программой, например, если она размещена в папке C:\Server\bin\mysql5.7\bin\, для этого выполните

cd C:\Server\bin\mysql5.7\bin\

• файл mysqldump можно запускать без указания расширения файла, но указывайте текущую папку, т.е.:

./mysqldump

• не нужно создавать резервные копии следующих баз данных:
• information_schema
• mysql
• performance_schema
• sys

Второй вариант – это скопировать в безопасное место папку C:\Server\data\DB\data\. Чтобы это сделать, предварительно остановите MySQL следующей командой:

net stop mysql

Само собой, нужно регулярно делать резервные копии, не только перед обновлением СУБД,

Решение ошибки «The server requested authentication method unknown to the client [caching_sha2_password]»

Эта ошибка появится не только при обновлении MySQL, но и при установке. Выглядит она примерно так:

Warning: mysqli::__construct(): The server requested authentication method unknown to the client [caching_sha2_password] in C:\Server\data\htdocs\Test\MiAlFlexableSSL\mysqliConnector.dll.php on line 31
Warning: mysqli::__construct(): (HY000/2054): The server requested authentication method unknown to the client in C:\Server\data\htdocs\Test\MiAlFlexableSSL\mysqliConnector.dll.php on line 31

Появляется в phpMyAdmin и в любом скрипте, который обращается к базе данных. Проблема заключается в том, что в настоящее время в PHP не реализовано расширение для работы с методом аутентификации caching_sha2.

Чтобы избежать эту ошибку, в конфигурационный файл my.ini добавьте директиву:

default_authentication_plugin=mysql_native_password

Пример рабочего файла, в котором есть все необходимый настройки для успешного запуска СУБД:

 [mysqld]

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
datadir="c:/Server/data/DB/data/"
default_authentication_plugin=mysql_native_password

Обновление структуры файлов баз данных при переходе на MySQL 8

Сразу после запуска СУБД, начинается обработка файлов баз данных, по этой причине первый запуск занимает определённое время и даже может появится сообщение, что не удалось запустить MySQL. Но через некоторое время, служба всё-таки будет запущена.

Даже после этого, в логах начинают появляться ошибки:

[Warning] [MY-010952] [Server] The privilege system failed to initialize correctly. If you have upgraded your server, make sure you're executing mysql_upgrade to correct the issue.

Видимо, с этим же связаны ошибки и предупреждения вроде таких:

2018-04-20T06:29:41.106198Z 0 [ERROR] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-001146 - Table 'mysql.component' doesn't exist
2018-04-20T06:29:41.107205Z 0 [Warning] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-003543 - The mysql.component table is missing or has an incorrect definition.
2018-04-20T06:29:41.118728Z 0 [Warning] [MY-010929] [Server] Storage engine 'MyISAM' does not support system tables. [mysql.user].
2018-04-20T06:29:41.119216Z 0 [Warning] [MY-010929] [Server] Storage engine 'MyISAM' does not support system tables. [mysql.db].
2018-04-20T06:29:41.119672Z 0 [Warning] [MY-010929] [Server] Storage engine 'MyISAM' does not support system tables. [mysql.tables_priv].
2018-04-20T06:29:41.120157Z 0 [Warning] [MY-010929] [Server] Storage engine 'MyISAM' does not support system tables. [mysql.columns_priv].
2018-04-20T06:29:41.120644Z 0 [Warning] [MY-010929] [Server] Storage engine 'MyISAM' does not support system tables. [mysql.procs_priv].
2018-04-20T06:29:41.121125Z 0 [Warning] [MY-010929] [Server] Storage engine 'MyISAM' does not support system tables. [mysql.proxies_priv].
2018-04-20T06:29:41.122237Z 0 [ERROR] [MY-013143] [Server] Column count of mysql.user is wrong. Expected 49, found 45. The table is probably corrupted
2018-04-20T06:29:41.122803Z 0 [Warning] [MY-010966] [Server] ACL table mysql.role_edges missing. Some operations may fail.
2018-04-20T06:29:41.123551Z 0 [Warning] [MY-010966] [Server] ACL table mysql.default_roles missing. Some operations may fail.
2018-04-20T06:29:41.123995Z 0 [Warning] [MY-010966] [Server] ACL table mysql.global_grants missing. Some operations may fail.
2018-04-20T06:29:41.124435Z 0 [Warning] [MY-010966] [Server] ACL table mysql.password_history missing. Some operations may fail.
2018-04-20T06:29:41.127627Z 0 [ERROR] [MY-010965] [Server] Missing system table mysql.global_grants; please run mysql_upgrade to create it.
2018-04-20T06:29:41.134299Z 0 [Warning] [MY-010727] [Server] System table 'func' is expected to be transactional.
2018-04-20T06:29:41.145000Z 0 [Warning] [MY-010405] [Repl] Info table is not ready to be used. Table 'mysql.slave_master_info' cannot be opened.
2018-04-20T06:29:41.145561Z 0 [ERROR] [MY-010422] [Repl] Error in checking mysql.slave_master_info repository info type of TABLE.
2018-04-20T06:29:41.146026Z 0 [ERROR] [MY-010415] [Repl] Error creating master info: Error checking repositories.
2018-04-20T06:29:41.146810Z 0 [ERROR] [MY-010426] [Repl] Slave: Failed to initialize the master info structure for channel ''; its record may still be present in 'mysql.slave_master_info' table, consider deleting it.
2018-04-20T06:29:41.147569Z 0 [ERROR] [MY-010529] [Repl] Failed to create or recover replication info repositories.
2018-04-20T06:29:41.150828Z 0 [System] [MY-010931] [Server] c:\Server\bin\mysql-8.0\bin\mysqld: ready for connections. Version: '8.0.11'  socket: ''  port: 3306  MySQL Community Server - GPL.
2018-04-20T06:29:41.366807Z 0 [ERROR] [MY-011294] [Server] Plugin mysqlx reported: 'Unable to use user mysql.session account when connecting the server for internal plugin requests.'
2018-04-20T06:29:41.368045Z 0 [ERROR] [MY-011301] [Server] Plugin mysqlx reported: 'Unable to switch context to user mysql.session' 

Чтобы исправить все эти предупреждения и ошибки, необходимо запустить mysql_upgrade. Служба MySQL должна быть запущена, также с опциями -u и -p нужно указать логин и пароль (если есть) соответственно:

cd C:\Server\bin\mysql-8.0\bin\
./mysql_upgrade.exe -uroot

Зависание MySQL 8 при обращении к некоторым базам данных

Это оказалось главным вызовом, после обновления с MySQL 5.7, в которой всё работало отлично. Зависания происходили и до, и после запуска mysql_upgrade. Достаточно было обратиться к некоторым базам данных, например, попробовать открыть в phpMyAdmin, либо посетить веб-сайт, который её использует.

Не помогало ничего, MySQL 8 зависала наглухо – служба не перезапускалась, помогала только перезагрузка компьютера, причём до первого обращения к проблемной БД.

Если у вас похожая проблема, и вы ещё не сделали резервную копию в виде .sql для импорта, то откатитесь до MySQL 5. В папку C:\Server\data\DB\data\ восстановите из резервной копии файлы старого формата.

После этого экспортируйте все базы данных в файл .sql. Когда этот файл готов, удалите MySQL 5, удалите содержимое C:\Server\data\DB\data\, установите MySQL 8 и инициализируйте её, как это происходит при первой установке:

C:\Server\bin\mysql-8.0\bin\mysqld --initialize-insecure --user=root
C:\Server\bin\mysql-8.0\bin\mysqld --install
net start mysql

После этого восстановите из резервной копии (файл .sql) все базы данных.

При таком способе переноса баз данных, проблема с зависанием полностью исчезла.

Восстановление баз данных из резервной копии без phpMyAdmin в Windows

Для экспорта баз данных, phpMyAdmin работает хорошо. Но с импортом возникло ряд проблем. Ниже я покажу, как разрешить все эти ошибки, но намного проще и в разы быстрее сделать импорт без phpMyAdmin и других помощников на PHP скриптах.

Для этого откройте командную строку Windows. Нажмите комбинацию WIN + x, затем выберите Windows PowerShell. Чтобы выйти из PowerShell и перейти в командную строку, выполните:

cmd

Затем смените текущую папку:

cd C:\Server\bin\mysql-8.0\bin\

И запустите команду вида:

mysql -uroot < C:\путь\до\файла\резервной_копии.sql

Например, моя резервная копия содержится в файле localhost.sql, который размещён в папке C:\Users\Alex\Downloads\, тогда команда выглядит так:

mysql -uroot < C:\Users\Alex\Downloads\localhost.sql

Ошибка phpMyAdmin «Core::fatalError('Incorrect format parameter');»

При попытке импортировать базы данных с помощью phpMyAdmin, у меня всё время возникала ошибка:

phpMyAdmin - Ошибка
Incorrect format parameter

Чтобы её исправить, откройте файл C:\Server\data\htdocs\phpmyadmin\​import.php, найдите там строку

Core::fatalError('Incorrect format parameter');

И удалите её.

Сохраните и закройте файл.

Если я правильно понимаю код, идёт проверка формата файла и если формат не соответствует указанному в списке, то программа останавливает работу. У меня эта проблема была для sql файла, сжатого в zip архив. Возможно, достаточно было добавить в список форматов zip.

Решение проблемы с ошибкой «import.php: Отсутствующий параметр: import_type»

Затем сразу возникла проблема:

phpMyAdmin - Ошибка
import.php: Отсутствующий параметр: import_type

Несмотря на суперинформативность ошибки, я разобрался в её причине – до скрипта просто не доходил файл с резервной копией БД. Причина в том, что по умолчанию в PHP установлены очень маленькие значения на максимальный размер выгружаемого на сервер файла, и файла, передаваемого методом POST. На локальном, своём сервере это очень легко исправить. Откройте файл c:\Server\bin\PHP\php.ini, и отредактируйте там следующие директивы:

upload_max_filesize = 2M

максимальный размер загружаемого на сервер файла. Изначально установлен очень маленький размер – только два мегабайта. Например, при загрузке базы данных в phpMyAdmin, не получится загрузить файл больше 2 мегабайт до тех пор, пока не будет изменён этот пункт настройки. Мой бэкап был в сотни мегабайт – установите это значение больше, чем размер импортируемого файла.

post_max_size = 8M

устанавливает максимальное количество данных, которые будут приняты при отправке методом POST. Аналогично – установите размер больше, чем импортируемый файл.

Другие директивы можно не менять, но при желании вы можете предоставить PHP скриптам дополнительные ресурсы:  

memory_limit = 128M

устанавливает максимальное количество памяти, которое может использовать скрипт

max_file_uploads = 20

максимальное количество файлов для загрузки за один раз

max_execution_time = 30

максимальное время выполнения одного скрипта

;default_charset = "UTF-8"

устанавливает кодировку (по умолчанию, строка закомментирована)

Чтобы эти изменения вступили в силу, нужно перезапустить веб-сервер.

Заключение

Это не все ошибки phpMyAdmin: после решения указанных выше проблем, phpMyAdmin стал жаловаться, что в моём бэкапе неверный формат команд. В общем, импортируйте базы данных напрямую в MySQL. Если баз данных много и они большие, по сравнению с phpMyAdmin, скорость импорта будет в разы быстрее.

Итак, резюмируем, для обновления с MySQL 5.* до MySQL 8.*:

• делаем резервную копию баз данных
• после установки MySQL 8 запускаем программу для обновления формата файлов
• если появились зависания или другие проблемы в работе, останавливаем СУБД, удаляем файлы баз данных, инициализируем MySQL, чтобы были созданы дефолтные файлы и импортируем базы данных из резервной копии

Если аналогичные проблемы возникнут на Linux, указанные рецепты также должны помочь.

Если вы только собираетесь установить веб-сервер на Windows, то подробная инструкция здесь.

Если вам нужен отличный хостинг, то он здесь. Я хостю там сайты с 2009 года и с каждый годом люблю этого хостера больше и больше! Объективно лучший хостинг по показателям аптайма и скорости работы. Если вы хотите зарегистрировать домен в любой существующей доменной зоне – на выбор сотни тематических доменных зон.

Потрясающая отзывчивая и профессиональная служба поддержки и бесплатный перенос сайтов – вливайтесь в клуб любителей Hostland.