MariaDB, the open-source relational database management system created by MySQL’s original developers, is designed to be highly compatible with MySQL. It supports the same SQL syntax, data types, and connectors as MySQL, allowing for a seamless transition of applications or databases that previously used MySQL.

With the pending retirement of Azure Database for MariaDB, we have received numerous requests from customers seeking guidance about moving from MariaDB to MySQL. This blog post provides guidance on migrating from Azure Database for MariaDB 10.3 to Azure Database for MySQL 5.7 using MyDumper/MyLoader utilities for logical backup and restore.

Note: With minor adjustments, you can use the same process to help migrate other compatible MariaDB-MySQL version pairs. For more details, see the article What's happening to Azure Database for MariaDB? (https://aka.ms/WhatsHappeningToMariaDB).

Before you begin

Before initiating any migration, it’s important that you assess any potential application changes or workarounds that might be required to ensure a smooth migration. In real-world scenarios, you can often encounter challenges, so testing your application(s) with the target MySQL 5.7 server is an important step before you begin the migration process.

Note: For a comprehensive understanding of potential incompatibilities between MariaDB and MySQL, please refer to the following articles in the MariaDB product documentation:

• Incompatibilities and Feature Differences Between MariaDB 10.3 and MySQL 5.7
• Function Differences Between MariaDB 10.3 and MySQL 5.7
• System Variable Differences Between MariaDB 10.3 and MySQL 5.7

Prerequisites

Before you start this migration process, be sure that you have the following prerequisites in place:

• A source instance of Azure Database for MariaDB running version 10.3.
• A target instance of Azure Database for MySQL running version 5.7.
• Network connectivity established (private using Private Endpoint or public via Azure Virtual Network integration) between the source and target.
• An Azure VM running Linux – MyDumper/MyLoader is currently only available on Linux platform. This server will act as an intermediate store for the database backups from the source server (Azure MariaDB Server) and loading platform for the target Azure Database for MySQL – Flexible Server, so the source and target both need to be accessible from this server.
• MyDumper/MyLoader- Installed on the above Azure VM running Linux.
• MariaDB/MySQL Client – Installed on the above Azure VM running Linux.

Migration process

The process for migrating your Azure Database for MariaDB server to an Azure Database for MySQL flexible server involves several key phases, including:

  I. Backing up Azure Database for MariaDB instance (using MyDumper).

 II. Restoring the backup to Azure Database for MySQL instance (using MyLoader).

III. Configuring and initiating replication from Azure Database for MariaDB to Azure Database for MySQL.

IV. Performing cutover from Azure Database for MariaDB to Azure Database for MySQL.

Each of these phases is described in greater detail in the following sections.

 

I. Backing up Azure Database for MariaDB instance (using MyDumper)

To perform database migration with minimal downtime, prepare the Azure Database for MariaDB server by configuring the following parameters:

• Binary Logging Enabled (log_bin): This allows for point-in-time recovery and ensures data integrity and consistency.

• Binlog Expiry Seconds (expire_log_seconds): Set this value to around 604800 (7 days) to retain the binary logs for at least 7 days.

Log in to your Azure VM running Linux and check the user / application databases, by executing the ‘show databases;’ command at the MariaDB / MySQL client. The result should be a near match to the following figure.

Figure 1. Screenshot demonstrating connecting to the database server and viewing databases.

Running the following command, for example, connects to the source the Azure Database for MariaDB server using the mydumper utility and creates a backup of the user / application databases.

mydumper --host=<servername> --user=<username> --password=<Password> --outputdir=./backup --rows=100000 --compress --build-empty-files --threads=16 --compress-protocol --trx-consistency-only --ssl  --regex '^(<Db_name>\.)' -L mydumper-logs.txt

 

If you run this command, you should receive the following output, showing the successful backup of the desired user database (in this case db1) existing / hosted on the source Azure Database for MariaDB instance at the desired location, as well as the files generated in the backup folder.

Figure 2. Screenshot of MyDumper command and output during backup phase.

II. Restoring the backup to Azure Database for MySQL instance (using MyLoader)

To restore the database backup on the target Azure Database for MySQL flexible server using the MyLoader utility, follow these steps:

• Secure a connection to the target server.
• Restore the database backups from the source Azure MariaDB server.

You can connect to the target Azure Database for MySQL server by executing the below command.

mysql -h  <hostname> -u <username> -p --ssl-mode=REQUIRED --ssl-ca=<DigiCertGlobalRootCA.crt.pem>

This step is to demonstrate that only the system databases will be present on the target MySQL database server, before the migration illustrated in the figure below.

Figure 3. Screenshot showing target server databases.

To restore the databases on the target Azure Database for MySQL flexible server, run the following command:

myloader --host=<servername> --user=<username> --password=<Password> --directory=./backup --queries-per-transaction=500 --threads=16 --compress-protocol --ssl --verbose=3 -e 2>myloader-logs.txt

After the above statement / command runs successfully, one can list the log file and verify to see if there were any errors recorded during the restoring process of the databases.

Figure 4a. Screenshot of the MyLoader command execution output showing restore process start and progress.

 As seen from the figure above, the database restoration process was completed without recording any errors. A continuation of the myloader log file is shown in the figure below.

Figure 4b. Screenshot of the MyLoader command execution output showing last lines restore process completion.

The final screen shows that the newly restored database is accessible on the target Azure Database for MySQL – Flexible Server. End users or application users can perform further data sanity checks to verify the data integrity of the restored or migrated database.

Figure 5. Screenshot demonstrating connecting to the target database post restore to view the restored database.

This concludes the offline migration phase of the user / application databases from Azure Database for MariaDB to Azure Database for MySQL – Flexible Server using the MyDumper/MyLoader utility. 

III. Configuring and initiating replication from Azure Database for MariaDB to Azure Database for MySQL

Given the global reach of end users facing web applications, many systems require minimum downtime over their active shelf life. The following sections illustrate the process to set up data-in replication from Azure MariaDB Server to Azure Database for MySQL – Flexible Server. As is the process with data-in replication, all the commands are run on the target flexible server.

1. Pre-requisites for Data-In Replication via MariaDB client

Refer to the Important Notes section in 'Phase I' wherein the contents of the metadata file were listed, especially the binlog file name and position. This information is crucial to set up the data-in replication on the target flexible server.

Before setting up replication, it’s important to ensure that the firewall of both Azure Database for MariaDB and Azure Database for MySQL servers is configured such that they are allowed to communicate with each other. To execute the stored procedure mysql.az_replication_change_master successfully, you need to make sure that your source MariaDB server is configured to accept any communication initiated from the target Azure database for MySQL Flexible server.

2. Data-In Replication via MariaDB client utility

Continuing from the earlier section, we need to execute the following command at the MariaDB / MySQL client prompt. If the connection was severed, refer to the figure below to re-connect to the Azure Database for MySQL – Flexible Server.

Figure 6. Screenshot demonstrating connecting to the database.

3. Set up the SSL Certificate variable

The SSL Certificate variable can be defined and populated by executing the following command at the MariaDB / MySQL client prompt as depicted in the figure below.

Figure 7. Screenshot showing the variable definition set to the SSL certificate.

4. Start the Data-In Replication

After the SSL Certificate variable is configured, the next step would be to change the master for the Target Server or the Azure Database for MySQL – Flexible Server as it will be acting as a slave to the source Azure MariaDB Server.

call mysql.az_replication_change_master('cmf-demoserver-mariadb-10-3.mariadb.database.azure.com', 'myadmin@cmf-demoserver-mariadb-10-3', '<password>', 3306, 'mysql-bin.000013', 517, @cert);

The following are the 2 important components of the above command:

• mysql.az_replication_change_master is the stored procedure to be called with the necessary parameters like source server name, username, etc.
• The @cert is the variable described / configured in the earlier section as the example uses a secure connection to connect to the Azure MariaDB Server

The output of the command should resemble the following figure.

Figure 8. Screenshot of the command to configure data-in replication between source and target server.

And running a ‘show slave status \G’ (in the figure above) should reveal that the command has been executed successfully with the binlog file and the position properly configured.

Next the actual replication process / task is started initiated by executing the following stored procedure.

call mysql.az_replication_start;

You will see the output similar to the following screenshot.

Figure 9. Screenshot showing the output of the command to start replication and 'show slave status\G'

Likewise executing ‘show slave status \G’ reveals that the replication from Azure MariaDB Server to Azure Database for MySQL – Flexible Server has started successfully.

This can be verified by performing some data inserts / data manipulation on the source server (MariaDB) and checking the results on the target server (Azure MySQL)

The following figure shows the insertion of 2 records in the emp table in the db1 database hosted on the MariaDB server.

Figure 10. Screenshot showing inserting records to a table in the source database.

Checking the results on the Target Server i.e. Azure Database for MySQL – Flexible Server should produce the desired results as depicted in the figure below.

Figure 11. Screenshot showing records inserted in source database replicated and present in the target database.

IV. Performing cutover from Azure Database for MariaDB to Azure Database for MySQL

After both the database servers i.e. the Source (Azure MariaDB) Server and the Target (Azure Database for MySQL – Flexible Server) are in sync with each other, a cutover to the Target Server can be performed provided some conditions are fulfilled.

• The application should be shut down and subsequently
• The Source Server should be put in ‘read-only’ mode

This is to avoid any scrupulous application / end user performing any data manipulation activity during the cutover activity. After the above conditions are satisfied, the cutover activity can be accomplished by executing the following two stored procedures in succession on the Target Server either via MySQL Client or MySQL Shell connection.

1. call mysql.az_replication_stop;
2. call mysql.az_replication_remove_master;

The first stored procedure stops the replication activity from the Source Server to the Target Server and the second stored procedure makes the Target Server an independent server with read-write enabled.

 

The entire cutover activity can be summarized in the figure below which shows that the Target Server is now an independent server and doesn’t act as a slave server to the Source Server.

Figure 12. Screenshot showing the target database server after cutover no longer replicating from the source.

Conclusion

In summary, transitioning from Azure Database for MariaDB to Azure Database for MySQL using logical dump and restore processes, supported by replication strategies is simplified whether you want to migrate in offline mode for smaller databases or minimize complexity for non-critical databases or take the online mode approach with minimal downtime, both can be accomplished with relative ease. It is vital to conduct comprehensive validation and performance benchmarking tailored to your environment. Each database workload and application topology has its own unique aspects, making planning, testing and tuning is recommended to mitigate risks during and after migration.

If you have any feedback or questions about the information supplied above, please leave a comment below or email us at AskAzureDBforMariaDB@service.microsoft.com. Thank you!