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.
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.
Before starting this process, ensure that that the following prerequisites are in place:
The process of migrating from Azure Database for MariaDB to Azure Database for MySQL involves:
Note: For reference, the name and size of the initial database is shown in the following graphic.
To back up your Azure Database for MariaDB instance using MySQLSh, perform the following steps.
mysqlsh --uri <user_name>%40<mariadb_user_name>@<mariadb_endpoint>:<Port>
mysqlsh --uri azureuser%40rahulsaha-mariadb@rahulsaha-mariadb.mariadb.database.azure.com:3306
util.dumpInstance("<backup_directory>", {threads: 16, showProgress: true, users:false})
util.dumpInstance("/backup/mysqlsh/mariadb_backup", {threads: 16, showProgress: true, users:false})
Example 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#
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#
util.loadDump("<backup_directory>", {threads: 16, showProgress: true})
util.loadDump("/backup/mysqlsh/mariadb_backup", {threads: 16, showProgress: true})
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.
CALL mysql.az_replication_change_master('<source-servername>.mariadb.database.azure.com', '<replica-user>@<source-servername>', '<StrongPass>', 3306, '<binlog-file>', <binlog position>, '');
CALL mysql.az_replication_change_master('rahulsaha-mariadb.mariadb.database.azure.com', 'replica_user@rahulsaha-mariadb', 'StrongPass', 3306, 'mysql-bin.000003', 385, '');
Example output
call mysql.az_replication_start;
mysql> show slave status\G
Example output
MySQL rahulsaha-mariadb.mariadb.database.azure.com:3306 ssl JS > util.dumpInstance("/backup/mysqlsh/mariadb_backup", {threads: 16, showProgress: true, users:false})
MySQL Error 1205 (HY000): Lock wait timeout exceeded; try restarting transaction:…
SHOW VARIABLES LIKE ‘innodb_lock_wait_timeout’;
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!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.