Comparison of MySQL database migration techniques:

MySQL databases are popular on-premises as well as in the public clouds with various development teams. Routinely, they find themselves in situations where they need to migrate their databases across servers.

There are two techniques for doing so for the MySQL databases hosted in the public cloud. The first involves the native support from MySQL server instances in the form of mysqldump utility and the second involves the public cloud capabilities to migrate the data.

Between these options, the choices are usually based on habits rather than leveraging their strengths and avoiding their weaknesses. This article provides a place for both as shown.

The azure data migration service instance comes with the following.  

Pros: 

1. One instance works across all subscriptions. 

2. Can transfer between on-premises and cloud and cloud to cloud. 

3. Pay-per-use billing. 

4. Provides a wizard to create data transfer activity. 

Cons: 

1. Limited features via IaC as compared to the portal but enough to get by. 

2. Not recommended for developer instances or tiny databases that can be exported and imported via mysqldump. 

3. binlog_expire_logs_seconds must be set to non-zero value on source server. 
4. Supports only sql login

The steps to perform the data transfer activity between the source and destination MySQL servers involves: 

1. Create a source mysql instance mysql-src-1 in rg-mysql-1 

2. Create a database and add a table to mysql-src-1 

3. Create a destination mysql instance mysql-dest-1 in rg-mysql-1 

4. Deploy the DMS service instance and project. 

5. Create and run an activity to transfer data. 

6. Verify the data. 

The mysqldump utilities, on the other hand, prepare the SQL for replay against the destination server. All the tables of the source database can be exported.

Pros:

1.       The SQL statements fully describe the source database.

2.       They can be edited before replaying

3.       There are many options natively supported by the database server.

Cons:

1.       It acquires a global read lock on all the tables at the beginning of the dump.

2.       If long updating statements are running when the Flush statement is issued, the MySQL server may get stalled until those statements finish

When using mysqldump, it might be better to leverage the MySQL Shell Dump Utilities which provide parallel dumping with multiple threads, file compression and progress information display.

It is best to determine the size of the database before choosing the options:

SELECT table_schema "DB Name",

ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB"

FROM information_schema.tables

GROUP BY table_schema;