Migrating a MySQL 5 database to MySQL 8

NOTE: You can use the Database Converter tab on the MySQL Databases page of the Control Panel to perform MySQL 5 to MySQL 8 conversions automatically.

MySQL 8 is the latest stable version of the popular database engine. It comes with security and performance enhancements over MySQL 5. Currently, both MySQL 5.7 and MySQL 8.0 are available on our servers.

If your application supports MySQL 8, you can migrate your database from MySQL 5 to MySQL 8 to benefit from the improvements in the MySQL 8 engine. The latest stable versions of WordPress, Joomla, Drupal, Magento, PrestaShop, and Nextcloud should work without issues with MySQL 8.

In order to run on the same machine, the MySQL 5 and MySQL 8 database processes have to use different ports and sockets for incoming connections. This means that you will have to update the configuration of your application in order to switch to using your new MySQL 8 database after migrating.

Before you begin

You don't need to create a backup of your MySQL 5 database before migrating it to MySQL 8. The existing MySQL 5 database will not be modified in any way, so you don't have to worry about breaking it.

Of course, you can easily create a database backup from the Backup page of the Control Panel of your hosting account, just to be on the safe side.

If your application has built-in maintenance mode, we recommend that you enable it here. While this step is not required, it will prevent any desynchronization issues during the migration.

In this article, we will use a stock WordPress installation made using the WordPress Manager in the Control Panel as an example.

Creating a database dump from MySQL 5

First, you have to create a database dump of your MySQL 5 database. This can be done on the MySQL Databases page of the Control Panel:

There, you have to click on the "Export" button next to the database in the list. The database dump will be created in the ~/private/MySQL_Dumps/ directory of your account.

Creating the new MySQL 8 database

After that, you can start configuring your new database by switching to "MySQL 8" on the "MySQL Version" dropdown menu.

At this point, you have to create the new MySQL 8 database. You can use the same name as the MySQL 5 one.

Also, make sure that the collation is set to the default utf8mb4_0900_ai_ci value:

This is the most up-to-date collation available with MySQL 8, and it will ensure that the full range of Unicode characters can be saved in the new database without issues.

Importing the data from the MySQL dump

Choose the database in the "Database name" dropdown menu under the "MySQL import wizard" section, and click on the "select an uploaded file" link. A popup window will appear that will let you browse the ~/private/MySQL_Dumps/ directory of your account. You should select the database dump file created earlier:

Once this is done, you should change the "Encoding" used for the import to utf8mb4:

This is the most reliable encoding with the best Unicode support that you can choose with MySQL 8.

Clicking on the "Import" button will migrate your MySQL 5 data into your new MySQL 8 database. After the import is completed, you will have a MySQL 8 database with the same data in it as the MySQL 5 one, and you can proceed to configuring your application to use the new database.

Creating the new MySQL 8 username for your database

Most applications have their own dedicated MySQL username. The easiest option to migrate to MySQL 8 is to create the same username for it as well.

For this, we recommend that you open the configuration file of your application in the File Manager of the Control Panel in a new browser tab and scroll to the database connection settings.

Here is a table showing the location of the configuration files of the most popular applications:

In this case, we edit the wp-config.php file in the WordPress installation directory:

Using the MySQL connection configuration in this file, you will be able to create the same MySQL 8 username on the "Usernames" tab of the "MySQL Databases" page of the Control Panel.

Make sure that you select the correct database in the "Database name" dropdown when creating the username. This will automatically attach all required permissions to the username:

This way, the configuration changes needed to make the application work with MySQL 8 will be minimal.

Configuration updates

At this point, you can update the configuration file of your application and update the MySQL connection details so that MySQL 8 is used.

Since we made the rest of the MySQL connection details the same, only the hostname for the connection needs to be updated. In our example, the value of the DB_HOST constant should be changed from localhost to localhost:/tmp/mysql8.sock.

The value of that setting varies depending on your application. For the most popular applications, the required changes are described in the following table:

After you save the file, the migration to MySQL 8 will be completed.

Done

Your application now works using the new MySQL 8 database. Disable maintenance mode (if applicable) and check your website for any issues.

We recommend that you delete the obsolete MySQL 5 database and users after verifying that your website is working correctly with the new MySQL 8 database.