Wednesday, September 1, 2021

Options for migrating Azure Database for MySQL - Single Server to Flexible Server

There are many ways to migrate an Azure Database for MySQL - Single Server database to Azure Database for MySQL - Flexible Server. In general, migrations can be categorized as either: 

  • Online, with minimal downtime 
  • Offline 

This blog post explains various methods that you can use to perform online and and offline migrations of a Single Server instance to Flexible Server. 

 

Online, minimal downtime migrations

You can migrate a Single Server instance to Flexible Server with minimum downtime to your applications by using a combination of open-source tools such as mydumper/myloader together with Data-in replication. For step-by-step detail on setting up Data-in replication between a Single Server instance and a Flexible Server instance, see Tutorial: Migrate Azure Database for MySQL - Single Server to Azure Database for MySQL - Flexible Server with minimal downtime 

 

Offline migrations

To perform offline migrations, you can use one of several options, including: 

  • Azure Data Migration Service 
  • MySQL Workbench 
    • MySQL Workbench Export/Import 
    • MySQL Workbench Migration Wizard  
  • Dump and restore using the: 
    • mysqldump utility 
    • MySQLDumper\MySQLLoader utility 
  • The shell script available in GitHub 

 

Using Azure Data Migration Service

You can use Azure Data Migration Service to perform an offline migration. The basic requirements for connecting the source and target databases are mentioned below. 

 

Connecting the source server to Azure Database Migration Service

In your Azure Database for MySQL - Single Server instance, update your firewall rules to ensure that the server can connect to Azure Database Migration Service. 

 

Note: You can also use "Allow access to Azure services" in the firewall rule. To learn more, see Firewall rules - Azure Database for MySQL. In addition, if the VNet is in same region, you can add the VNet and enable the service endpoint. 

 

If the source server is using private link, then you need to ensure that the private link VNet and the VNet hosting Azure Database Migration Service have connectivity. You can use peering for network connectivity or other connectivity methods, such as a VNet-to-VNet Connection, to ensure communication between VNets. For more details, see Azure Virtual Network peering and VNet-to-VNet VPN gateway.

 

Connecting to the target server 

The target server running Azure Database for MySQL -Flexible Server can use either public or private access. 

 

For servers using public access, be sure that the Allow public access from any Azure service within Azure to this server setting is selected.

 

For servers using private access, ensure that the VNet hosting the instance of Azure Database for MySQL - Flexible Server can communicate with the VNet that is hosting Azure Database Migration Service. Note that VNet peering is supported. You can also use other connectivity methods, such as a VNet-to-VNet Connection, to provide for communication between VNets. For more details, see VNet-to-VNet VPN gateway 

 

Refer to Tutorial Migrate MySQL to Azure Database for MySQL offline using DMS for step-by-step documentation on how to migrate.   

 

Using MySQL Workbench 

To use MySQL Workbench to perform the migration, the base requirement is that the utility be able to successfully connect to the source and target databases. So, if you’re using private link with the source Azure Database for MySQL - Single Server or private access in Azure Database for MySQL - Flexible Server, be sure that you install the tool on a virtual machine that can access to both the source and target databases.

 

MySQL Workbench Export/Import 

For more information, see Import and export - Azure Database for MySQL.

 

MySQL Workbench Migration Wizard

For more information, see Migrate Amazon RDS for MySQL to Azure Database for MySQL using MySQL WorkbenchThe primary difference is that you use Azure Database for MySQL as the source rather than Amazon RDS. 

 

Performing a dump and restore

 

Dump and restore using the mysqldump utility 

The base requirement when using dump and restore is having a successful connection to the source and target database from the location at which mysqldump is installed. Also consider the location to which you're dumping the data to ensure that the upload happens more quickly. Consider using a VM in same region with accelerated networking enabled. For more information, see Migrate using dump and restore - Azure Database for MySQL.

 

Dump and restore using mydumper/myloader utility 

If you are looking to migrate large databases with database sizes more than 1 TBs, you may want to consider using community tools such as mydumper/myloader , which support parallel export and import. For more information, see  How to migrate large MySQL databases. You need to install the utility on a VM, preferably in the same VNET (or region). For step-by-step documentation on how to migrate, see Tutorial: Migrate large databases to Azure Database for MySQL using mydumper/myloader.   

 

Using the shell script available in GitHub 

Migration isn’t just about moving your schema and data. The process also involves some post-migration tasks, such as data validation and migration of security settings such as firewall rules and server parameter configurations. This is important to ensure that your application actually functions properly. This task can at times prove tediousespecially when you’re dealing with a large of fleet of servers that have multiple databases. We have a shell script provided in GitHub that can help to migrate databases from one instance of Azure Database for MySQL to another. The script can migrate the: 

  • Database and schema, with an option of choosing all user databases in the server, or only specific databases. 
  • Firewall rules from source server to target server (optional)  
  • Server parameters that are changed from default value from source server to target server (optional) 
  • Users and grants with the user password setting to default (optional) 

The script can also create a basic database migration validation report after the migration. (optional)

 

For more details, see the post Migrate from Azure Database for MySQL - Single Server to Flexible Server in 5 easy steps!

 

Conclusion 

Based on the number and size of your databases and the downtime that each can accommodate, choose the best option for your specific scenario. If you have any feedback or questions about using the information above, please leave a comment below or email us at AskAzureDBforMySQL@service.microsoft.com.

Posted at https://sl.advdat.com/3gT7QcV