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.




Enabling Organizations with IT Transformation & Cloud Migrations | Principal CSM Architect at IBM, Ex-Microsoft, Ex-AWS. My opinions are my own.

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

A closer look on failed payments and refunds at redBus

Coping with the application milky way

<Bootcamp Day #2>

Custom Naming in vRealize Automation 8.x (Pt. 2)

Limited-time asset exchange event 1

Cross-chain indexing

Testing 1..2..3…

Clouds Will Fail, How To Make Resilient Apps

Red clouds on the sunset

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
kapil rajyaguru

kapil rajyaguru

Enabling Organizations with IT Transformation & Cloud Migrations | Principal CSM Architect at IBM, Ex-Microsoft, Ex-AWS. My opinions are my own.

More from Medium

Distributed SQL Tips and Tricks — January 14th, 2021

Advantages of YAML Pipeline over Classic Pipeline

OAuth 2.0 Client Credential Flow with Certificate-based Authentication on Microsoft Identity…

Database and Information Systems Development: Data Dictionaries