Migrate from Azure Database for MySQL - Single Server to Flexible Server in 5 easy steps!

Azure Database for MySQL - Flexible Server is a fully managed open-source community database service that offers multiple options for high-availability, maintenance control, and a simplified, out-of-the-box experience. With exciting new features released each month, be sure to consider migrating to Flexible Server so that you can quickly leverage its benefits!

To migrate from Azure Database for MySQL – Single Server to Flexible Server, you can use any of a variety of methods. Generally, migrations are classified either as offline, or online (minimal downtime). For offline migrations, you can use utilities such as Azure Database Migration Service, MySQL Workbench, mydumper/myloader, or mysqldump to backup and restore the database. For online migrations with minimal downtime, you can use the same utilities in combination with Data-in replication.  

However, 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 ensuring that your application actually functions properly. This task can at times prove tedious, especially when you’re dealing with a large of fleet of servers with multiple databases.

In this post, you learn how to use a sample shell script to automate migration tasks with the mysqldump utility. The script enables you to easily automate migration of single servers containing multiple databases in 5 easy steps, as shared below. The sample script migrates: 

• The database and schema, with an option to migrate all databases (ALL) on the server or only specific databases. 
• Firewall rules from the source server to the target server.
• Server parameters that vary from default values from source server to target server. 
• Users and grants with the user password setting to default. 

You can also use the script to create a basic database migration validation report after the migration completes.

Note:  

• All functionalities are enabled by default. You can edit the parameter file to enable or disable (YES/NO) specific functionalities beyond migrating the database and schema, which always occurs. 
• You can also use the script to migrate from Azure Database for MySQL – Single Server to Single Server as well as Flexible Server to Flexible Server using the same steps provided below.

Important: We recommend that you test the script thoroughly before using it in a production environment. 

Prerequisites 

Before you begin, be sure that you have: 

• A source server running Azure Database for MySQL - Single Server and a target server running Azure Database for MySQL - Flexible Server. 
• Admin connection credentials for the source and target server. 
• An environment in which to run the script. 
  • If your source and target servers use public access, then you can use the Azure cloud shell, which has all the necessary prerequisites to run the script. Note that the Azure cloud shell timeout and the storage size can be a limitation. In addition, the script creates a firewall rule to allow all private access for all Azure IP addresses in the source and target Azure Database for MySQL instances.
  • If your target server uses private access, then you need to use Azure VM running Linux (preferably Ubuntu) with the following installed: 
    • Azure CLI 
    • mysql ,mysqldump client tool 
    • Jq and cmp  

Also be sure to consider the following points.

• You can also use the script to verify that the prerequisites are in place by running it with dummy parameters. 
• Because MySQL utilities such as mysql/mysqldump aren’t necessarily forwardly compatible, be sure that the mysql/mysqldump version you're using is the same as the MySQL version running on the target server. 
• Verify that you can connect to the target server and source server from the VM. The script also performs a connectivity test before migration. 
• The firewall rule migration will be skipped if the target has private access.

Using the script 

1. Log in to the environment, the Azure cloud shell, or an Ubuntu Azure VM with the Bash shell. 
2. Download the latest version of the script and parameter files by running the following commands: 

wget -L https://raw.githubusercontent.com/Azure/azure-mysql/master/azuremysqltomysqlmigrate/azuremysqlmigfinal.sh  

wget -L https://raw.githubusercontent.com/Azure/azure-mysql/master/azuremysqltomysqlmigrate/migrateparameter.json

3. Edit the parameter file migrateparameter.json or create a parameter file (using the contents of master parameter file as a template) to provide details of the migration. 

Tip: If you're working with the cloud shell and want to edit the file, use the in-house editor by selecting the { } icon in the shell.

4. To make the script executable, run the following command:

chmod +x azuremysqlmigfinal.sh  

5. To use the script, run the following command:

./azuremysqlmigfinal.sh Parameterfilename 

Note:  

• If you don't specify a parameter file, the script looks in the same folder for the migrateparameter.json file. 
• View the sample file for details on the parameter values to specify. 
• Some parameters are case sensitive, so be sure to use the same case. (See the comment in parameter file for more details) 
• You can customize the script per your requirements. It is recommended to test the script thoroughly before using it in a production environment.  

 For more details and for the latest version of the script, please visit our GitHub repo.

Execution experience 

After you’ve updated the parameter file, run the script to: 

• Verify that the prerequisites are installed in the environment (Cloud Shell or VM) in which the script is executed. The process exits if any prerequisites are missing, and details of the issue(s) are displayed. 
• Read the specified parameter file, display the values, and request your confirmation:  

• After you confirm the values in the parameter file, the script checks connectivity to source and target servers and displays the result.

  If connectivity fails, the script exits. 

• Migrate users, databases (data and schema), firewall rules, server parameters, etc. based on the selections you specified in the parameter files. The script displays basic details on screen, with a more verbose description provided in the file migrationmmddyyHHMMSS.log. 
• If the database migration validation option is enabled, the validation report will be logged in the file validation_reportmmddyyHHMMSS.txt. 

  The log file and the validation report are included in the same folder:

The validation report helps by:

• Checking the count of Tables, Views, Routines, Constraints, and Triggers per database. 
• Checking the rows for each table per database. 
• Reporting the validation results and the summary.

If there's an error, the report provides a query that you can use to determine the cause of the validation failure.

Tip:  You can run the command cat validation_reportmmddyyHHMMSS.txt | grep "Error:" to filter the error from the report.

Conclusion

With the information above, you should easily be able to migrate your databases from Azure Database for MySQL - Single Server to Flexible Server. 

Supportability of the solution 

The script shared as part of this post is not a managed solution and is therefore unsupported by Microsoft Customer Support and Services (CSS). The solution is open-source and maintained by the community of experts in GitHub. For any issues, we recommend that you open an issue in GitHub. Also, feel free to contribute any updates or bug fixes by creating a pull request.