How to upgrade from MySQL 5 to MySQL 8

MySQL 8.0 became the version available for download by default (the previous current version was MySQL 5.7). Therefore, now it is considered that it is ready for use in production. I took several hours to solve the problems that occurred during the update process, I hope very much that this note will save your time.

I upgraded MySQL to version 8.0 on Windows. But I suppose the similar errors will occur in Linux.

My web server and MySQL are installed according to this guide. If you installed in another way, then you may have other paths to folders, consider this.

Backup of all MySQL databases

Make sure you make backup before you perform the update! The MySQL 5 and MySQL 8 database files are not fully compatible, and when MySQL 8 starts, the database file structure is automatically changed. This means that if something goes wrong and you want to roll back to MySQL 5, it might just be impossible.

I make backups in two ways:

• export all databases to a file;
• backup the files in which the databases are stored.

Export all databases can be done directly in phpMyAdmin, for this go to the main page and select the ‘Export’ tab.

I recommend NOT ticking ‘Add DROP DATABASE IF EXISTS’, because if the file is large, then it will have to be imported in several stages, and if there is used the DROP DATABASE IF EXISTS query, then at the beginning of the import, the previously received records will be deleted, therefore it will be impossible to complete the operation.

You can make backup copies using the mysqldump utility. Features of using mysqldump in Windows:

• do not use PowerShell, use the command line (in PowerShell, redirection of the output, denoted by the > and < symbols) does not work, for this, in PowerShell execute the command

cmd

• go to the directory with the program, it is located in the C:\Server\bin\mysql-8.0\bin\ folder, to do this,

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

• You can run mysqldump without specifying the file extension, but specify the current folder, ie:

./mysqldump

• you do not need to back up the following databases:
• information_schema
• mysql
• performance_schema
• sys

The second option is to copy the folder C:\Server\data\DB\data\ to a safe place. To do this, first stop MySQL with the following command:

net stop mysql

Of course, you need to make regular backups, not only before updating the DBMS.

Error ‘The server requested authentication method unknown to the client [caching_sha2_password]’ (SOLVED)

This error will not only appear when updating MySQL, but also during fresh installation. It looks something like this:

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

The error appears in phpMyAdmin and in any script that query the database. The problem is that at the present time PHP does not implement an extension to work with the authentication method caching_sha2.

To avoid this error, add a directive to the my.ini configuration file:

default_authentication_plugin=mysql_native_password

An example of a workable file, in which there are all the necessary settings for the successful launch of the DBMS:

 [mysqld]

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

Updating the structure of database files when migrating to MySQL 8

Immediately after starting the DBMS, processing of the database files begins, for this reason the first start needs much time and even a message may appear that MySQL could not be started. But after a while, the service will be started.

Even after this, mistakes begin to appear in the logs:

[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.

And also:

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' 

To fix all these warnings and errors, you must run mysql_upgrade. The MySQL service must be started, and with the -u and -p options you must specify the login and password (if any), respectively:

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

MySQL 8 hanging when accessing some databases

Hangings occurred before and after mysql_upgrade. It was enough to access to some databases, for example, try to open in phpMyAdmin, or visit the website that uses it.

Nothing helped, MySQL 8 was hanging tightly: the service did not restart, only the restart of the computer helped, but not for long time, only before the first access to the problem database.

If you have a similar problem and you have not made a backup in the form of .sql for import, then downgrade to MySQL 5. In the C:\Server\data\DB\data\ folder, restore the old format files from the backup.

After that, export all the databases to a .sql file. When this file is ready, delete MySQL 5, delete the contents of C:\Server\data\DB\data\, install MySQL 8 and initialize it, as it does when first installing:

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

After that, restore all the databases from the backup (.sql file).

With this method of database migration, the problem with the hang has completely disappeared.

Restoring databases from a backup without phpMyAdmin in Windows

To export databases, phpMyAdmin works well. But with the import there were a number of problems. Below I will show how to resolve all these errors, but it is much easier and many times faster to make imports without phpMyAdmin and other helpers in PHP scripts.

To do this, open the Windows command prompt. Press WIN + x combination, then select Windows PowerShell. To exit PowerShell and go to the command prompt, run:

cmd

Then change the current folder:

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

And run the command like this:

mysql -uroot < C:\path\to\backup_file.sql

For example, my backup is contained in the localhost.sql file, which is located in the folder C:\Users\Alex\Downloads\, then the command looks like this:

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

Error phpMyAdmin ‘Core :: fatalError ('Incorrect format parameter');’

When I tried to import databases using phpMyAdmin, I always had an error:

phpMyAdmin - Error
Incorrect format parameter

To fix it, open the file C:\Server\data\htdocs\phpmyadmin\​import.php, find the line there

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

And delete it.

Save and close the file.

If I understand the code correctly, the format of the file is checked and if the format does not match the one specified in the list, then the program stops working. I had this problem for a sql file compressed into a zip archive. Perhaps it was enough to add zip to the list of formats.

Solving the problem with the error ‘import.php: Missing parameter: import_type’

Then immediately there was a problem:

phpMyAdmin - Error
import.php: Missing parameter: import_type

Despite ‘super verbose’ of the error, I figured out its reason: the script simply did not receive the file with a backup copy of the database. The reason is that by default PHP sets very small values for the maximum size of the file uploaded to the server, and the file transferred by the POST method. On a local server, this is very easy to fix. Open the c:\Server\bin\PHP\php.ini file, and edit the following directives:

upload_max_filesize = 2M

The maximum size of the file uploaded to the server. Initially installed a very small size - only two megabytes. For example, if you load a database into phpMyAdmin, it will not be possible to load a file larger than 2 megabytes until this directive is changed. Set this value more than the size of the imported file.

post_max_size = 8M

sets the maximum amount of data that will be received when sent using the POST method. Similarly, set the size larger than the imported file.

Other directives do not need be changed, but if you want, you can provide PHP scripts with additional resources:

memory_limit = 128M

sets the maximum amount of memory that the script can use

max_file_uploads = 20

maximum number of files to upload at a time

max_execution_time = 30

maximum execution time of one script

;default_charset = "UTF-8"

sets the encoding (by default, the string is commented out)

For these changes to take effect, you must restart you web server.

Conclusion

These are not all phpMyAdmin errors: after solving the above-mentioned problems, phpMyAdmin began to complain that there was an incorrect command format in my backup. In general, you should import databases directly into MySQL. If there are a lot of databases and they are large, compared to phpMyAdmin, the import speed will be many times faster.

So, we summarize, for updating from MySQL 5.* to MySQL 8.*:

• make a backup of the databases
• after installing MySQL 8, run the program to update the file format
• if there are freezes or other problems in the work, stop the database, delete the database files, initialize MySQL to create the default files and import the database from the backup

If similar problems occur on Linux, these recipes should also help.

If you are just going to install a web server on Windows, then the detailed guide are here.

Looking for really cheap hosting? You will not believe that this price is possible! Check it out here. VPS or shared-hosting for you.