Migrating from Azure Database for MariaDB to Azure Database for MySQL

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​

   
   
   

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

   

   
   
   

4. When the backup is completed, verify the contents of the backup inside the backup directory.
   
   The backup files that were created as a result are shown in the following output:
   

   root@rahulsaha-vm1:/backup/mysqlsh/mariadb_backup# ls -lrth
   total 19M
   -rw-r----- 1 azureuser azureuser  574 May 16 10:46 db1@t1.json
   -rw-r----- 1 azureuser azureuser  267 May 16 10:46 db1.json
   -rw-r----- 1 azureuser azureuser  234 May 16 10:46 aria_log_control.json
   -rw-r----- 1 azureuser azureuser  275 May 16 10:46 @.sql
   -rw-r----- 1 azureuser azureuser  275 May 16 10:46 @.post.sql
   -rw-r----- 1 azureuser azureuser  805 May 16 10:46 @.json
   -rw-r----- 1 azureuser azureuser  517 May 16 10:46 db1.sql
   -rw-r----- 1 azureuser azureuser  595 May 16 10:46 aria_log_control.sql
   -rw-r----- 1 azureuser azureuser  672 May 16 10:46 db1@t1.sql
   -rw-r----- 1 azureuser azureuser   80 May 16 10:46 db1@t1@0.tsv.zst.idx
   -rw-r----- 1 azureuser azureuser 2.9M May 16 10:46 db1@t1@0.tsv.zst
   -rw-r----- 1 azureuser azureuser   88 May 16 10:46 db1@t1@1.tsv.zst.idx
   -rw-r----- 1 azureuser azureuser 379K May 16 10:46 db1@t1@1.tsv.zst
   -rw-r----- 1 azureuser azureuser   88 May 16 10:46 db1@t1@3.tsv.zst.idx
   -rw-r----- 1 azureuser azureuser 376K May 16 10:46 db1@t1@3.tsv.zst
   -rw-r----- 1 azureuser azureuser   88 May 16 10:46 db1@t1@2.tsv.zst.idx
   -rw-r----- 1 azureuser azureuser 372K May 16 10:46 db1@t1@2.tsv.zst
   -rw-r----- 1 azureuser azureuser   80 May 16 10:46 db1@t1@4.tsv.zst.idx
   -rw-r----- 1 azureuser azureuser 346K May 16 10:46 db1@t1@4.tsv.zst
   -rw-r----- 1 azureuser azureuser   88 May 16 10:46 db1@t1@5.tsv.zst.idx
   -rw-r----- 1 azureuser azureuser 367K May 16 10:46 db1@t1@5.tsv.zst
   -rw-r----- 1 azureuser azureuser   88 May 16 10:46 db1@t1@9.tsv.zst.idx
   -rw-r----- 1 azureuser azureuser 415K May 16 10:46 db1@t1@9.tsv.zst
   -rw-r----- 1 azureuser azureuser   88 May 16 10:46 db1@t1@6.tsv.zst.idx
   -rw-r----- 1 azureuser azureuser 384K May 16 10:46 db1@t1@6.tsv.zst
   -rw-r----- 1 azureuser azureuser   96 May 16 10:46 db1@t1@7.tsv.zst.idx
   -rw-r----- 1 azureuser azureuser   96 May 16 10:46 db1@t1@8.tsv.zst.idx
   -rw-r----- 1 azureuser azureuser 355K May 16 10:46 db1@t1@7.tsv.zst
   -rw-r----- 1 azureuser azureuser 596K May 16 10:46 db1@t1@8.tsv.zst
   -rw-r----- 1 azureuser azureuser 1.7K May 16 10:46 @.done.json
   -rw-r----- 1 azureuser azureuser 9.7K May 16 12:15 load-progress.21d62496-a3ce-11ed-99d4-6045bda8cc51.json
   root@rahulsaha-vm1:/backup/mysqlsh/mariadb_backup#​

   
   The content of the ‘@.json’ file contains the Binlog file number and position, which can be used later in step 6 to set up replication. Example output is shown below:
   

   root@rahulsaha-vm1:/backup/mysqlsh/mariadb_backup# cat @.json
   {
       "dumper": "mysqlsh Ver 8.0.31 for Linux on x86_64 - for MySQL 8.0.31 (MySQL Community Server (GPL))",
       "version": "2.0.1",
       "origin": "dumpInstance",
       "schemas": [
           "db1",
           "aria_log_control"
       ],
       "basenames": {
           "db1": "db1",
           "aria_log_control": "aria_log_control"
       },
       "defaultCharacterSet": "utf8mb4",
       "tzUtc": true,
       "bytesPerChunk": 64000000,
       "user": "azureuser@rahulsaha-mariadb",
       "hostname": "rahulsaha-vm1",
       "server": "CLIENT",
       "serverVersion": "5.6.0-10.3.23-MariaDB-log",
       "binlogFile": "mysql-bin.000003",
       "binlogPosition": 385,
       "gtidExecuted": "",
       "gtidExecutedInconsistent": false,
       "consistent": true,
       "compatibilityOptions": [],
       "capabilities": [],
       "begin": "2023-05-16 10:46:09"
   }root@rahulsaha-vm1:/backup/mysqlsh/mariadb_backup#

   
   
   
5. To restore the backup to Azure Database for MySQL using MySQLSh, run the following command.
   
   Syntax

   util.loadDump("<backup_directory>", {threads: 16, showProgress: true})

   
   Example 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 is 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.
   
   Syntax

   CALL mysql.az_replication_change_master('<source-servername>.mariadb.database.azure.com', '<replica-user>@<source-servername>', '<StrongPass>', 3306, '<binlog-file>', <binlog position>, '');

   
   Example command
   

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

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

   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!