Migrate from MariaDB to MySQL HeatWave: easier with MySQL Shell 8.2.1

I won’t cover reasons why you should migrate from MariaDB to MySQL, I already covered the topic in previous blogs and webinars:

• How to Migrate from MariaDB to MySQL HeatWave
• How to Migrate from MariaDB to MySQL 8.0
• Migrating from MariaDB to MySQL using MySQL Shell
• Migrating from MariaDB to MySQL (slides)
• Migration from MariaDB to MySQL (webinar)

In this post, I will cover a new easier way to migrate using the latest MySQL Shell 8.2.1 and the copyInstance() utility.

Previously, if you wanted to migrate to MySQL HeatWave (the name of the MySQL Database Service in OCI, DBaaS) you needed to use an intermediate storage (local disk or Object Storage). With the new copyInstance(), this is not required anymore.

Once you have created your MySQL HeatWave instance, on a compute instance or any local machine that has access to your local MariaDB instance and the MySQL HeatWave one, you can copy all the data directly from one instance to the other.

Please don’t forget that you also have preliminary checks to perform, like checking that you are not using some special storage engines and more (see Migrating from MariaDB to MySQL).

Let’s illustrate the procedure.

MySQL Shell 8.2.1

We need first to install MySQL Shell 8.2.1. Pay attention that you need the latest version (at least 8.2.1) that is compatible with MariaDB:

$ sudo yum install 
 https://dev.mysql.com/get/Downloads/MySQL-Shell/mysql-shell-8.2.1-1.el8.x86_64.rpm

Then you connect to your MariaDB instance:

copyInstance()

Once connected, you can start the verification before the copy using the dryRun mode (in Python or Javascript mode):

MySQL JS> \js
MySQL JS> util.copyInstance('mysql://admin@10.0.1.105', 
    {"compatibility": ["force_innodb", "skip_invalid_accounts",
                       "strip_definers", "strip_restricted_grants",
                       "strip_tablespaces", "ignore_wildcard_grants",
                       "strip_invalid_grants","create_invisible_pks"], 
      users: "false", threads: 4, ignoreVersion: "true", dryRun:"true"})

Mind the required options:

• compatibility: applying some specific requirements for MySQL HeatWave
  • force_innodb
  • skip_invalid_accounts
  • strip_definers
  • strip_restricted_grants
  • strip_tablespaces
  • ignore_wildcard_grants (not really required as we will skip the users)
  • strip_invalid_grants (not really required as we will skip the users)
  • create_invisible_pks (required if we plan to enable HA in the future)
• users: MariaDB users are not supported, we need to handle them separately
• threads
• ignoreVersion
• dryRun: we just perform the checks, we don’t actually copy the data

If the command succeeds, we can start the copy using the same command but setting dryRun to false:

… [output truncated] …

And voilà !

No intermediate storage has been required and the copy went smoothly.

Users

MariaDB manages the users differently than MySQL 8. If you need to also transfer the users, you can use the user MySQL Shell plugin (https://github.com/lefred/mysqlshell-plugins) :

Conclusion

With the latest MySQL Shell copyInstance() utility, migrating from MariaDB to MySQL HeatWave has become exceptionally straightforward. In fact, this tool is not limited to just that; it can also be utilized for seamless migration to MySQL on-premise.

Whether you’re working with a MariaDB or MySQL instance, the copyInstance() utility empowers you to effortlessly duplicate entire instances, schemas, and tables to MySQL 8.

This noticeable addition simplifies the migration process even further, eliminating the need for any intermediate storage.

You can download the full guide here.

Happy migrations !