Azure Database for MySQL is a relational database service in the Microsoft cloud based on the MySQL Community Edition database engine. MySQL is an advanced, feature-rich, open-source database system that’s popular with developers, used for a wide range of applications, and compatible with most popular application platforms.
Azure Database for MySQL – Flexible Server is the service deployment option that provides the most granular control and flexibility over database management functions and configuration settings. Many customers choose Azure Database for MySQL – Flexible Server to run their production workloads in Azure, and we continue to actively modernize our service to support different workloads at scale.
In this blog post, I’ll go over the various tools available for performing custom migrations from MySQL running on-premises, in Virtual Machines or a third-party cloud, or from Azure Database for MySQL – Single Server to Azure Database for MySQL – Flexible Server. I’ll also discuss complex scenarios to help in planning the migration and performing it with minimal issues.
Importance of benchmarking
There are many tools to choose from, and each has its own pros and cons – some work best for one scenario but perhaps not well in another. As a result, to recommend the best tool when migrating MySQL databases between platforms / clouds, it’s important to do some benchmark tests to better compare the tools. This information provides a solid baseline for selecting the best tool to address your specific needs while planning migrations.
Tools and their best practices (every tool and scenario). Parameter/tuning used for the tool.
MyDumper/MyLoader
MyDumper/MyLoader are MySQL Logical Backup/Restore Tools which are two tools in one package. MyDumper is responsible for exporting a consistent backup of MySQL databases. MyLoader reads the backup from MyDumper, connects to the destination database, and imports the backup. Both tools use multi-threading/parallel processing capabilities.
URL - https://github.com/mydumper/mydumper
MySQLSh
MySQLSh is a powerful tool that provides scripting capabilities for JavaScript and Python and includes APIs for working with MySQL. It also provides parallel dumping with multiple threads, file compression, and progress information display and MySQL Database Service compatibility checks and modifications. Dumps can be easily imported into a MySQL Server instance or a MySQL Database Service DB System using the MySQL Shell load dump utilities.
URL - https://dev.mysql.com/doc/mysql-shell/8.0/en/
MySQLPump
The mysqlpump client utility performs logical backups, producing a set of SQL statements that can be executed to reproduce the original database object definitions and table data. It dumps one or more MySQL databases for backup or transfer to another MySQL Server. It can dump DB in parallel and it is an excellent tool for backup.
URL - https://dev.mysql.com/doc/refman/8.0/en/mysqlpump.html
MySQLDump
The mysqldump client utility performs logical backups, producing a set of SQL statements that can be executed to reproduce the original database object definitions and table data. It dumps one or more MySQL databases for backup or transfer to another MySQL server. The mysqldump command can also generate output in CSV, other delimited text, or XML format.
URL - https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html
Benchmarking commands
The benchmarking we performed with variety of scenarios covering small to large databases in size such as 90 GB to 1Tb and simple to complex databases for example high number of database objects ranging from 400 to 40000. We chose the server SKU to be 16 vCore with 20000 IOPS since this provides good performance/cost for migrations and the servers only need to be scaled up during migration and scaled down post completion to minimize cost. Azure VM with 16 vCore with 7500 IOPS was used for dump and restore.
The following sections provide the commands for dump and load operations associated with each tool.
MyDumper/MyLoader commands
Backup
Command syntax:
mydumper --regex='^(?!(mysql|sys|information_schema|performance_schema))' --host=<DB_hostname> --user=<DB_username> --password=<DB_password> --outputdir=<backup_directory>--rows=500000 --compress --build-empty-files --threads=16 --compress-protocol
Sample output:
>root@vm1:/backup/mydumper/test2# time mydumper --regex='^(?!(mysql|sys|information_schema|performance_schema))' --host=single2-70gb.mysql.database.azure.com --user=azureuser@single2-70gb --password=************ --outputdir=/backup/mydumper/test2 --rows=500000 --compress --build-empty-files --threads=16 --compress-protocol
real 8m44.974s
user 131m30.487s
sys 3m14.229s
Restore
Command syntax:
mydumper --regex='^(?!(mysql|sys|information_schema|performance_schema))' --host=<DB_hostname> --user=<DB_username> --password=<DB_password> --outputdir=<backup_directory>--rows=500000 --compress --build-empty-files --threads=16 --compress-protocol
Sample output:
root@vm1:/backup/mydumper/test2# time myloader --host=flex5-restore.mysql.database.azure.com --user=azureuser --password==************ --directory=/datadrive/mydumper/LargeDBSimpleSchema --queries-per-transaction=500 --threads=16 --compress-protocol --verbose=3 -e
** Message: 06:46:22.018: Thread 4 shutting down
** Message: 06:46:23.706: Thread 3 shutting down
** Message: 06:46:23.706: Thread 12 shutting down
** Message: 06:46:25.670: Thread 6 shutting down
real 287m21.490s
user 1508m48.761s
sys 23m0.890s
MySQLSH commands
Backup
Command syntax:
mysqlsh --uri <DB_username>@<DB_Hostname>:<DB_Port>
util.dumpInstance("<backup_directory>", {threads: 16, showProgress: true})
Sample output:
root@#vm1:/backup/mysqlsh# mysqlsh --uri azureuser%40single90gb@single90gb.mysql.database.azure.com:3306
Please provide the password for 'azureuser%40single90gb@single90gb.mysql.database.azure.com:3306': ***************
Save password for 'azureuser%40single90gb@single90gb.mysql.database.azure.com:3306'? [Y]es/[N]o/Ne[v]er (default No): Y
MySQL Shell 8.0.31
Copyright (c) 2016, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.
Type '\help' or '\?' for help; '\quit' to exit.
Creating a session to 'azureuser%40single90gb@single90gb.mysql.database.azure.com:3306'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 62629
Server version: 5.7.32-log MySQL Community Server (GPL)
No default schema selected; type \use <schema> to set one.
MySQL _single90gb.mysql.database.azure.com:3306 ssl__JS_> util.dumpInstance("/backup/mysqlsh/SmallDBSmallSchema", {threads: 16, showProgress: true})
NOTE: Backup lock is not supported in MySQL 5.7 and DDL changes will not be blocked. The dump may fail with an error if schema changes are made while dumping.
Acquiring global read lock
Global read lock acquired
Initializing – done
2 out of 6 schemas will be dumped and within them 400 tables, 0 views.
3 out of 5 users will be dumped.
Gathering information – done
All transactions have been started
Global read lock has been released
Writing global DDL files
Writing users DDL
Running data dump using 16 threads.
NOTE: Progress information uses estimated values and may not be accurate.
Writing schema metadata – done
Writing DDL – done
Writing table metadata – done
Starting data dump
101% (400.00M rows / ~394.56M rows), 1.28M rows/s, 253.04 MB/s uncompressed, 115.09 MB/s compressed
Dump duration: 00:04:35s
Total duration: 00:04:52s
Schemas dumped: 2
Tables dumped: 400
Uncompressed data size: 77.56 GB
Compressed data size: 35.26 GB
Compression ratio: 2.2
Rows written: 400000000
Bytes written: 35.26 GB
Average uncompressed throughput: 281.23 MB/s
Average compressed throughput: 127.86 MB/s
Restore
Command syntax:
mysqlsh --uri <DB_username>@<DB_Hostname>:<DB_Port>
util.loadDump(" <backup_directory> ", {threads: 16, showProgress: true})
Sample output:
root@vm1:/backup/mysqlsh/test1# mysqlsh --uri azureuser@flex5-restore.mysql.database.azure.com:3306
Please provide the password for 'azureuser@flex5-restore.mysql.database.azure.com:3306': ***************
Save password for 'azureuser@flex5-restore.mysql.database.azure.com:3306'? [Y]es/[N]o/Ne[v]er (default No): Y
MySQL Shell 8.0.31
Copyright (c) 2016, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates.
Other names may be trademarks of their respective owners.
Type '\help' or '\?' for help; '\quit' to exit.
Creating a session to 'azureuser@flex5-restore.mysql.database.azure.com:3306'
Fetching schema names for auto-completion... Press ^C to stop.
Your MySQL connection id is 48
Server version: 5.7.39-log MySQL Community Server (GPL)
No default schema selected; type \use <schema> to set one.
MySQL flex5-restore.mysql.database.azure.com:3306 ssl JS > util.loadDump("/backup/mysqlsh/test1", {threads: 16, showProgress: true})
Loading DDL and Data from '/backup/mysqlsh/test1' using 16 threads.
Opening dump...
Target is MySQL 5.7.39-log. Dump was produced from MySQL 5.7.39-log
Scanning metadata – done
Checking for pre-existing objects...
Executing common preamble SQL
Executing DDL – done
Executing view DDL – done
Starting data load
2 thds loading | 100% (77.94 GB / 77.94 GB), 21.11 MB/s, 402 / 402 tables done
Executing common postamble SQL
Recreating indexes – done
1608 chunks (402.00M rows, 77.94 GB) for 402 tables in 3 schemas were loaded in 22 min 20 sec (avg throughput 61.39 MB/s)
0 warnings were reported during the load.
MySQLPump commands
Backup
Command syntax:
mysqlpump -h<DB_Hostname> -u<DB_User> -p<DB_Password> --include-databases=<database_name1,database_name2> --default-parallelism=16 > backup_file_name.sql
Sample output:
root@vm1:~# time mysqlpump -hsingle2-70gb.mysql.database.azure.com -uazureuser@single2-70gb -p********** --include-databases=db1,db2 --default-parallelism=16 > /backup/mysqlpump/test1/mysqlpump3.dmp
mysqlpump: [Warning] Using a password on the command line interface can be insecure.
Dump progress: 0/4 tables, 250/3945600 rows
Dump progress: 0/65 tables, 1371500/64115975 rows
Dump progress: 0/130 tables, 2969000/128232159 rows
Dump progress: 1/193 tables, 4082750/190375359 rows
Dump progress: 1/247 tables, 5064500/243641509 rows
Dump progress: 1/305 tables, 6059000/300852709 rows
Dump progress: 392/400 tables, 398936000/394561821 rows
Dump progress: 395/400 tables, 399410500/394561821 rows
Dump progress: 397/400 tables, 399666250/394561821 rows
Dump progress: 399/400 tables, 399839250/394561821 rows
Dump completed in 404085
real 6m47.945s
user 17m28.244s
sys 3m30.992s
Restore
Command syntax:
mysql -h<DB_Hostname> -u<DB_User> -p<DB_Password> < backup_file_name.sql
Sample output:
root@vm1:/backup/mysqlsh/test1# time mysql -hflex5-restore.mysql.database.azure.com -uazureuser -p*********** < /backup/mysqlpump/test1/mysqlpump1.dmp
mysql: [Warning] Using a password on the command line interface can be insecure.
real 415m44.683s
user 16m10.914s
sys 2m20.370s
MySQLDump commands
Backup
Command syntax:
mysqldump --column-statistics=0 -h<DB_Hostname> -u<DB_User> -p<DB_Password> --databases <database_name1 database name2> > /backup/mysqldump/SmallDBLargeSchema/mysqldump.sql
Sample output:
root@vm1:~# time mysql -hflex5-restore.mysql.database.azure.com -uazureuser -p********** < /backup/mysqlpump/test1/mysqlpump1.dmp
mysql: [Warning] Using a password on the command line interface can be insecure.
real 421m46.576s
user 16m4.305s
sys 2m31.939s
Restore
Command syntax:
mysql -h<DB_Hostname> -u<DB_User> -p<DB_Password> < backup_file_name.sql
Sample output:
root@vm1:/backup/mysqldump# time mysqldump --column-statistics=0 -hsingle2-70gb.mysql.database.azure.com -uazureuser@single2-70gb -p********* --databases db1 db2 > /backup/mysqldump/test1/mysqldump.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
real 91m54.313s
user 15m45.947s
sys 3m4.154s
Benchmarking results
The benchmarking results for the scenarios with varying database conditions appear in the tables and charts in the following sections. For all scenarios:
- The source is Azure Database for MySQL – Single Server
- The target is Azure Database for MySQL – Flexible Server
NOTE: MySQLPump and MySQLDump can only create the dump, so MySQL is used to restore.
Scenario 1: DB Size = 90 GB
| Tool |
Object Count |
Backup Time (sec) |
Restore Time (sec) |
|
MyDumper |
400 |
524 |
1261 |
|
MySQLSh |
400 |
292 |
1336 |
|
MySQLPump |
400 |
396 |
24944 |
|
MySQLDump |
400 |
5514 |
25059 |
|
MyDumper |
4000 |
581 |
2071 |
|
MySQLSh |
4000 |
396 |
1831 |
|
MySQLPump |
4000 |
358 |
29280 |
|
MySQLDump |
4000 |
5311 |
30451 |
Scenario 2: DB Size = 1 TB
|
Tool |
Object Count |
Backup Time (sec) |
Restore Time (sec) |
|
MyDumper |
5200 |
6491 |
17241 |
|
MySQLSh |
5200 |
5862 |
16980 |
|
MySQLPump |
5200 |
5481 |
25231 |
|
MySQLDump |
5200 |
14016 |
27045 |
|
MyDumper |
40000 |
9345 |
26833 |
|
MySQLSh |
40000 |
22260 |
14254 |
|
MySQLPump |
40000 |
9213 |
> 24 hr |
|
MySQLDump |
40000 |
34980 |
> 24 hr |
Conclusion
Based on benchmarking and testing these popular tools for backing up and restoring a MySQL database, it’s apparent that some options, such as MySQLDump, are legacy tools, lacking in features such as parallelism. As a result, these are only suitable for use with simple migrations of smaller databases, or if a schema dump is all that’s required. With support for parallelism, MyDumper/Myloader has proved to be the most popular tool for dumping/restoring large databases. Today, we also have native MySQL tools such as MySQLPump and MySQLShl, which also offer parallelism with moderately better results, providing additional options for performing a dump/restore.
If you have any feedback or questions about the information provided above, please leave a comment below or email us at AskAzureDBforMySQL@service.microsoft.com. Thank you!