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:

dumpInstance() sees MariaDB as MySQL 5.5 – pay attention that GTIDs are not compatible between both platforms

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:

more threads can be specified

Compatibility Issue

It’s also possible that MySQL Shell Dump doesn’t work with MariaDB as it’s not supported. For example, depending of your data definition, it might be necessary to disable the dump using chunks. This will reduce the speed of the dump & load operations.

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.

I discussed with a friend during Percona Live and he explained to me that he used this technique to migrate all his databases (production and development) from MariaDB to MySQL 8.0. That represented terabytes of data !

Preparing for Replication

Disclaimer

Some offensive words may appear in some commands. In MySQL we pay a lot of attention to that and we are removing those offensive words everywhere, commands, comments, results, code…

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:

@.json

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

Can we perform an in-place upgrade from MariaDB to MySQL 8.0 ?

No, MariaDB and MySQL 8.0 are too different now. Those products are using the same protocol but are different. Even InnoDB is now different between those 2 databases.

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:

loading MariaDB users with MySQL Shell
MySQL Shell copy user plugin 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

Just because I am curious, I patched MySQL Shell to check what would be the result of MySQL Shell Upgrade Checker on MariaDB 10.6.7.

Let’s have a look at the output generated with this patched version of MySQL Shell:

We can see that some of the errors are related to impossible checks because some performance_schema tables are missing.

The main errors are also related to 2 tables in mysql schema, nothing to worry about as those tables are not specific to my application.

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.