Migrating from MariaDB to MySQL using MySQL Shell

We are getting many requests for migration from MariaDB to MySQL. Here is a quick guide and steps to follow:

On the MariaDB server:

• Create a logical dump of MariaDB (using MySQL Shell)
• Create a dedicated user for replication [optional]

On the MySQL Server:

• Import/Load the logical dump into MySQL 8.0 (using MySQL Shell)
  • You can load the schema and tables definition without the data to modify the character set
  • Load the data
• Setup replication between MariaDB and MySQL [optional]

MySQL Shell dump & load utility is the best method to perform logical dumps. Those dumps can be used for logical backups but also to migrate from versions where physical in-place upgrades are not possible.

This is the case with migration to the cloud for example.

In-place upgrades are also not possible when we want to migrate from an older version (or perform a downgrade) of MySQL. This is exactly what I’m covering in this article: migrating from MariaDB 10.6 to MySQL 8.0.

MySQL Shell Upgrade Checker

If we want to upgrade from MySQL 5.7 to 8.0, it is recommended to use MySQL Shell Upgrade Checker.

However, MySQL Shell Upgrade Checker utility doesn’t support any version below 5.7, neither MariaDB.

I would highlight that MySQL Shell Upgrade Checker:

• is mandatory for In-place Upgrade
• is much less necessary during a migration using a logical dump

The database I used for this migration is the employees database and sbtest created by sysbench.

During the full migration process, sysbench generates transactions on the system (connected to the MariaDB instance).

Logical Dump of MariaDB

Now we will dump the data (logical dump) using MySQL Shell Dump & Load. The dump is done in parallel and stored locally on disk:

The dump is fast and we can already load it to MySQL 8.0.

We will also take the opportunity to migrate to the new character set: UTF8mb4.

Load the dump into MySQL 8.0

It’s recommended to load first the users (if we plan to keep them). Users are required if definers are specified with views. In case we don’t want to have the definers specified, you need to take the logical dump using the strip_definers compatibility option like this:

MariaDB JS> util.dumpInstance('/home/fred/dump/maria-10.6.7',
            {'compatibility': ['strip_definers']})

We first load all the schemas and tables definitions using the option loadData set false. We also need to ignore the dump’s version:

Once all the schemas and tables are loaded, we modify the default character set like this:

When all modifications are done, we can load the data in parallel:

Live Migration

Usually when we want to perform migration with minimal downtime, we must use replication. The traffic to the application doesn’t need to be stopped during the dump process, neither while restoring the data to the new server.

So when the new server is ready, it needs to catch up all the transactions that were made during that period.

Asynchronous Replication is then used to catch up and only once the old and the new server are in sync, we can decide to move the traffic (switch) to the new server.

Does replication work between MariaDB and MySQL 8.0 ?

MySQL allows replication from old version to new version (not the reverse). So if your application is not using features that are specific to MariaDB, this should not be a problem at all.

Preparing for Replication

To replicate from MariaDB to MySQL 8.0, we need first a dedicated user:

MariaDB> CREATE USER repl@'%' IDENTIFIED by 'repl_passwd';
MariaDB> GRANT REPLICATION SLAVE ON *.* TO repl@'%';

In the folder where we stored the dump, there is a file called @.json containing all the metadata of the dump.

We will use that file to get the binary log file’s name and position from which we need to start the replication process:

Start Replica

On MySQL 8.0, if we want to use GTIDs (always recommended) we need to change the GTID mode to allow replication from the MariaDB server:

MySQL> SET PERSIST gtid_mode=on_permissive;

Then we can setup replication:

MySQL> CHANGE REPLICATION SOURCE TO source_host='localhost',
              source_port=10607, source_user='repl',
              source_password='repl_passwd', 
              source_log_file='mariab-bin.000004', source_log_pos=20972403;

And we can start the replica and wait for the lag to decrease:

MySQL> START REPLICA;
MySQL> SHOW REPLICA STATUS\G

Conclusion

We are now ready to move all our traffic (sysbench) to the new MySQL 8.0 server and we can retire the old MariaDB server.

We are constantly improving MySQL Shell Dump & Load utility. If you encounter an issue, please submit a bug or add a comment on this article.

Enjoy migrating to MySQL 8.0 !

FAQ

How can I migrate from older versions of MariaDB (10.0, 10.1, 10.2, 10.3, ..) to MySQL 8.0 ?

Using the exact same technique of creating logical dumps. And import them.

I already covered such topic in these previous articles: [1], [2].

Is replication from MariaDB to MySQL 8.0 supported ?

If you set the GTID mode to permissive on MySQL side, it should mostly work without any problem. Avoid using MariaDB specific features as it will break replication.

Can I load the users using MySQL Shell Dump & Load ?

No, as the syntax to create the users is not compatible anymore between both databases. However, you can use a MySQL Shell plugin to copy the users, see this article.

Example:

Please note that some MariaDB specific keywords like VIA are not supported.

What if I use specific features in MariaDB like System-Versioned Tables ?

Those tables will be ignored from the dump as their table type is SYSTEM VERSIONED, MySQL Shell Dump & Load is only using BASE TABLE and VIEW. Don’t forget that if such features are activated after the dump & load, while replication is running, this will break replication.

Extra