Migrating from Azure Database for MariaDB to Azure Database for MySQL
Published Jun 02 2023 03:08 PM 13.6K Views
Microsoft

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.

 

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.

 

1_Dump_Restore.png

2_MariaDB_Replication_MySQL.png

3_Cutover.png

 

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

 

4 - Initial DB size.png

 

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​


 

  1. 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
    5 - Backup.png



  2. 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#


  3. 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
    6 - Restore.png

    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.

 

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

    7 - MariaDB connectivity.png

 

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

    8 - Flex Networking.png

 

  1. 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
    9 - Replication1.png

 

  1. To start the replication, use the stored procedure mysql.az_replication_start, as shown below.

    Syntax
    call mysql.az_replication_start;

    Example output
    10 - Replication2.png

 

  1. 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
    11 - Replication.png

  2. 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
    12 - Error 1 backup.png

  • 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
    13 - Error 2 Restore.png

    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.

    14 - Error Fix.png

    Then, verify the change from the MySQL client by running the following command:

    SHOW VARIABLES LIKE ‘innodb_lock_wait_timeout’;​


    Example output
    15.png

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!

10 Comments
Version history
Last update:
‎Jun 08 2023 01:07 AM
Updated by: