How to: Prevent Data loss while migrating MySQL server to Azure Database for MySQL

Photo by Etienne Girardet on Unsplash

Azure Database for MySQL is a relational database service in the Microsoft cloud based on the MySQL Community Edition (available under the GPLv2 license) database engine, versions 5.6, 5.7, and 8.0. Azure Database for MySQL Server is a PaaS service that delivers built-in high availability, automated backups, and point-in-time restore. Additionally, automated maintenance for underlying hardware, OS, and DB engine, elastic scaling within seconds with predictable performance, ability to stop/start the server, and enterprise-grade security. These capabilities require almost no administration, and all are provided at no additional cost. Because of these benefits, enterprises are migrating their on-premises MySQL servers to Azure Database for MySQL server.

There are several various ways to migrate your premises’ MySQL servers to Azure MySQL. However, if you plan to migrate hundreds of MySQL instances and thousands of Databases with DB sizes of more than 1TBs, then the preferred way to migrate using community tools like mydumper/myloader, which supports parallel export and import. mydumper/myloader is a straightforward tool to perform the migration. However, if you do not set specific parameters on Azure MySQL server, Azure VM where you are running myloader, then there are chances that migration may result in some data loss. So, to avoid data loss during the migration, consider applying the following parameters setting on Azure MySQL Server and the Azure VM where you are running the myloader tool.

Azure VM with myloader

• Any General-Purpose family Linux VM would serve the purpose. Ubuntu should benefit customers since it is the VM with the lowest cost. The VM should have at least 8 vCore and 16GB of memory, supporting and enabling Accelerated Networking to provide a stable network.
• A Premium Disk should be added to the VM to store the exported data. Prefer to be sized at least 1TB or above (P30 tier or higher) to leverage the 5000+ IOPS and 200+MB/second throughput per disk.

Azure MySQL Server Parameters

• Scale up the server to 32 vCore Memory Optimized SKU from the Pricing tier of the portal during migration.
• Scale-up storage tier — The IOPs for Azure Database for MySQL server increases progressively with the increase in storage tier. For faster loads, you may want to provide at least 4TB to get 12K IOPs.

• max_allowed_packet — set to 1073741824 (i.e. 1GB) to prevent any overflow issue due to long rows
• slow_query_log — set to OFF to turn off the slow query log. This will eliminate the overhead causing by slow query logging
• query_store_capture_mode — set both to NONE to turn off the Query Store. This will eliminate the overhead causing by sampling activities by Query Store.
• innodb_buffer_pool_size — Calculate size by using this formula (DBInstanceClassMemory*4/5). The size would be in bytes. So you need to convert memory in GB to Bytes by multiplying twice with 1024 and then with 8. You can find Azure MySQL server Memory info here. In our case, Server memory is 320 GB, so the innodb buffer pool size would be 2684354560.
• innodb_write_io_threads & innodb_write_io_threads — Change to 16 from the Server parameters in the Azure portal to improve migration speed.
• innodb_io_capacity = Sets 9000 on I/O activity performed by InnoDB background tasks, such as flushing pages from the buffer pool and merging data from the change buffer.
• innodb_io_capacity_max = If flushing activity falls behind, InnoDB can flush more aggressively than the limit imposed by innodb_io_capacity. innodb_io_capacity_max defines an upper limit the number of I/O operations performed per second by InnoDB background tasks in such situations. Set this value to 9000.

After all the parameters are set on the Azure MySQL server, it’s time to execute myloader on Azure VM. However, before you execute myloader, consider setting the following parameter while executing the myloader command.

• queries-per-transaction — recommend setting to value not more than 500
• compress-protocol — enable client compression protocol to compress the data sent between VM and MySQL server
• threads — use a value equal to 2x of the vCore of the temporary VM

I hope this article will help you and your customer perform large-scale MySQL migration to the Azure MySQL server without any data loss and having optimal performance.

Disclaimer: I work for @Microsoft Azure Cloud & my opinions are my own.