How to Migrate MySQL to Azure Database - Cambay Solutions | Cloud Infrastructure

Business Scenario:

Consider a scenario where a client wants to migrate\modernize their legacy business-critical large size database server to Azure, which is stored on MySQL 5.6 Community Edition and Linux 4.4 in an on-prem data center. The technology is outdated and has no further support from vendors regarding backup and recovery tools, patching, bug fixes, etc. The database migration process could foresee the following challenges because of outdated software specifications:

Business Challenges:

MySQL 5.6 and Linux 4.4 were both obsoleted long ago.
The MySQL community edition is open-source and has no support from MySQL to use the Enterprise backup tool.
Native MySQL dump and restore options don’t work because of the large database size.
No database backup and recovery solutions were implemented because of the database size.
Azure Migrate doesn’t support lift and Shift because Linux Image starts from 5+ later versions on Azure.
There will be many dependency issues while installing any third party\open-source backup tools (Ex: PerconaXtrabackup, MyDumper-MyLoader) due OS version on the on-premises (source) server.
Many other challenges like the end of the data center agreement, the Existing server being non-compliant, etc.

Solution Strategy:

Ensure the server is ready on Azure with available Linux images and MySQL 5.6 community edition.
We should use other third-party or open-source tools to accomplish this migration since there are no native tools from MySQL\Azure to support this migration process because of obsolete versions of the database and OS versions.
There are many open-source tools available in the market, but for these types of migrations with large-size databases and no native tools support, the best fit is My Dumper \My Loader.
My Dumper \My Loader is an open-source backup tool that helps to take backup of MySQL databases with TBs in size by using the multi-thread process to speed up the backup and restore process.
This tool has a lot of additional parameters that help to optimize backup and restore the performance of the large-size tables in small chunks like weekly, monthly, quarterly, and yearly and append it later in one go to the database to have the required data.
Install My Dumper\My Loader on different Linux servers (the middle layer between the source and Azure) where the source server is located datacenter.
From the middle layer, the server connects to the source server remotely to dump the backups on Azure file share and download the files from file share to the Azure server(destination) using Azcopy.
Once files are available on the azure server, restore those files as databases.
We can split the backup into one-month chunks with multiple files and later append all the data in one database from all files. This tool’s beauty is that we can create smaller backup files and merge them later as needed.
We must match the data on both servers with database size and row count to avoid data loss.

Outcome & Benefits:

Implementing the above solution allows us to migrate large MySQL databases to obsolete versions without significant issues.
This will save the cost of migration as we are not using any licensed software and hosting costs on Azure.
We can implement a strong database backup and restore strategy post-migration using PerconaXtrabackup.
We can improve the database performance by implementing the best disk configuration methods suggested by Microsoft.