Migrating from Azure Database for MariaDB to Azure Database for MySQL

This post has been republished via RSS; it originally appeared at: Microsoft Tech Community - Latest Blogs - .

MariaDB is the open-source relational database management system created by MySQL’s original developers. MariaDB 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 for applications or databases that previously used MySQL.

Recently we’ve had many customers asking for guidance on moving from MariaDB to MySQL. To address this request, this blog post focuses on moving from Azure Database for MariaDB version 10.3 to Azure Database for MySQL version 5.7 using the MySQL Shell (MySQLSh) client. However, the same process, with minor changes, can help migrate other compatible MariaDB-MySQL version pairs.

Preparation

Before beginning, it’s important to assess any application changes or workarounds that are required to ensure a smoother migration. In real-world scenarios there are often challenges, so testing an application with the target MySQL 5.7 becomes crucial before starting the migration. To understand the potential incompatibilities between MariaDB and MySQL, refer to the following documents.

• 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 starting this process, ensure that that the following prerequisites are 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 (either via private or public network) between the source and target so that they can communicate with each other.

Migration overview

The process of migrating from Azure Database for MariaDB to Azure Database for MySQL involves:

1. Backing up your Azure Database for MariaDB instance using MySQLSh.
2. Restoring the backup to your Azure Database for MySQL instances using MySQLSh.
3. Configuring and starting replication on your Azure Database for MariaDB instance, sending data to your Azure Database for MySQL instance.
4. Performing cutover when the replication lag reaches zero.

Note: For reference, the name and size of the initial database is shown in the following graphic.

Procedure

To back up your Azure Database for MariaDB instance using MySQLSh, perform the following steps.

1. Connect to mysqlsh prompt by running the following command:
   
   Syntax
   
   mysqlsh --uri <user_name>%40<mariadb_user_name>@<mariadb_endpoint>:<Port>
   
   Example command
   
   mysqlsh --uri azureuser%40rahulsaha-mariadb@rahulsaha-mariadb.mariadb.database.azure.com:3306

2. To take a full backup (which will also include the master binlog file number and position) of the source server, at the mysqlsh prompt, run the following command:
   
   Syntax
   
   util.dumpInstance("<backup_directory>, {threads: 16, showProgress: true, users:false})
   
   Example command
   
   

   util.dumpInstance("/backup/mysqlsh/mariadb_backup", {threads: 16, showProgress: true, users:false})
   
   Example output
   
   

    

3. To restore the backup to Azure Database for MySQL using MySQLSh, run the following command.
   
   util.loadDump("/backup/mysqlsh/mariadb_backup", {threads: 16, showProgress: true})
   
   Example output
   
   
   
   

   Next, I need to set up replication from Azure Database for MariaDB to Azure Database for MySQL. However, before setting up replication, it’s important to ensure that the firewall of both Azure Database for MariaDB and Azure Database for MySQL servers are configured such that they are allowed to communicate with each other.

4. To configure network settings on the source Azure Database for MariaDB server, ensure that the IP addresses of the VM used to perform the migration and the target Azure Database for MySQL server are whitelisted in the firewall on the source Azure Database for MariaDB server, as shown below.
   
   

5. To configure network settings on the target Azure Database for MySQL server, ensure that the IP addresses of the VM used to perform the migration and the source Azure Database for MariaDB server are whitelisted in the firewall on the target Azure Database for MySQL server, as shown below.
   
   

6. To configure the replication, use the stored procedure mysql.az_replication_change_master, as shown below.
   
   

   CALL mysql.az_replication_change_master('rahulsaha-mariadb.mariadb.database.azure.com', 'replica_user@rahulsaha-mariadb', 'StrongPass', 3306, 'mysql-bin.000003', 385, '');

   
   Example output
   
   

7. To start the replication, use the stored procedure mysql.az_replication_start, as shown below.
   
   call mysql.az_replication_start;
   
   Example output
   
   

8. Monitor the replication by running the command below until the Second behind master value displays as 0.
   
   mysql> show slave status\G
   
   

   Example output
   
   
   
   

9. Finally, point the application to the target Azure Database for MySQL server by updating the connections string(s) as necessary.
   
   

Limitations

• MySQLShell doesn’t support backing up users. While creating the backup, be sure not to include users by using the following command:
  
  MySQL  rahulsaha-mariadb.mariadb.database.azure.com:3306 ssl  JS > util.dumpInstance("/backup/mysqlsh/mariadb_backup", {threads: 16, showProgress: true, users:false})
  
  Example output
  
  
  
  
• Restore error – Lock wait timeout exceeded. Locking can happen between transactions when loading data to MySQL, which may result in throwing the following error:
  
  MySQL Error 1205 (HY000): Lock wait timeout exceeded; try restarting transaction:…
  
  Example output
  
  
  
  If you get this error, you can work around it by increasing the value of the “innodb_lock_wait_timeout” parameter in the Azure portal (as shown below), and then continuing the migration.
  
  
  
  Then, verify the change from the MySQL client by running the following command:
  
  SHOW VARIABLES LIKE ‘innodb_lock_wait_timeout’;
  
  Example output
  
  
  
  

Conclusion

Migrating from Azure Database for MariaDB to Azure Database for MySQL using dump and restore tools, together with replication to minimize the downtime needed for cutover, is not a difficult process. However, be sure to thoroughly test and benchmark things to help minimize the potential for encountering issues during and post migration, as every database, application architecture can be incredibly unique.

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!