Migrate a database to MySQL Database on Azure

Evaluate whether an application database can migrate

Before you migrate to MySQL Database on Azure, evaluate whether your application database can do so successfully.

As MySQL Database on Azure is compatible with MySQL 5.6 and MySQL 5.7, the vast majority of applications will run without any modifications. Applications need a database reconnection mechanism to ensure a good level of fault tolerance and avoid the application failing as a result of temporary inability to connect to the database. Even highly available cloud databases inevitably encounter situations where failover or server maintenance cause temporary inability to connect to the database. You should also use connection pooling and persistent connections to access the database whenever possible, particularly for applications with higher performance requirements. For more information, see How to efficiently connect to MySQL Database on Azure.

MySQL Database on Azure does not support MyISAM format. (For more information, see “Why doesn’t MySQL Database on Azure support databases in MyISAM format?” in the FAQs.) In most situations, you can use the database normally by replacing the MyISAM database engine with InnoDB in the table creation code.

Solution 1: Migration based on database import/export

If your system can accept a long period of downtime (for example, one to two hours) caused by migration, use the following import/export method for database migration. Specific procedure

1. Sign in to the Azure portal. Create a new MySQL server on MySQL Database on Azure. Complete the necessary configuration steps, such as the daily backup time. For more information on the specific steps, see MySQL Database on Azure database basics: Step 1.

2. Use the Azure portal to create the target database where you want to migrate to on the newly created MySQL server. For more information, see MySQL Database on Azure database basics: Step 4.

3. If you have multiple database accounts that need to access the original database, you must create the corresponding accounts on the new database server by using the Azure portal.

4. If the database is relatively large (for example, more than 1 GB), prepare a virtual machine (VM) in the same Azure data center. Then transfer the data to the VM first, and then import it into the database.

5. On Azure, complete the deployment of the application’s components other than the database, such as websites.

6. After the preparatory work is finished, start the migration. First close the application or run it in read-only mode (if supported) to avoid creating of new data during the migration process.

7. Export the application database from the current database server to a file. You can use a tool that you’re familiar with, such as mysqldump or Workbench. The following example uses mysqldump to export a database:

mysqldump --databases <数据库名> --single-transaction --order-by-primary -r <备份文件名> --routines -h<服务器地址> -P<端口号> –u<用户名> -p<密码> 

8. If the database file is relatively large, transfer the database file to a VM (which should be in the same data center) on Azure. Use a data transfer tool that you are familiar with, such as FTP or AzCopy. This method prevents the entire database transfer process from failing if the internet connection drops out. If the backup file is very large, you can compress it before uploading.

9. Import the database data into the target database. You can use a tool that you’re familiar with, such as mysql.exe or Workbench. The following example uses mysql.exe to import the database.

9.1 Connect to the newly created MySQL server on your client by using mysql.exe. (Note: If you aren’t importing the data from a VM on Azure, you need to add the client to the IP safe list.)

mysql -h<服务器地址> -P<端口号> –u<用户名> -p<密码> 

9.2 Import the data from the SQL command line:

source <备份文件名>; 

10. Direct the newly deployed application to the migrated database and complete the remaining application migration steps.

Solution 2: Migration based on data synchronization

Database import and export is relatively simple, but involves a relatively long period of downtime. If you cannot accept downtime during the migration process, such as for a company’s web applications, or you would like to complete a smooth migration in stages, try the data synchronization method instead. MySQL Database on Azure provides this function, which will allow your application and current database to continue working unaffected during synchronization.

Recommended migration process:

1. Synchronize the database to MySQL Database on Azure. You can configure the database server running on MySQL Database on Azure as a subordinate server. For the specific steps involved in configuring and synchronizing databases, see Configure SQL Data Sync to replicate to MySQL Database on Azure.

2. Deploy the new application on Azure and direct it to the newly created database on Azure.

[AZURE.WARNING] As the database on Azure is running in read-only mode at this time, the functionality of the application may be limited.

3. Confirm that the database on Azure has reached a synchronized state. You can determine the sync status based on the subordinate instance latency on the Overview page.

4. Close the old application or make the application run in read-only mode (if it supports read-only mode).

5. Stop the database synchronization replication. You just need to press Stop Replication on the Overview page.

6. Enable the new application.

Common issues with database migration:

An error message saying “Access denied; you need (at least one of) the SUPER privilege(s) for this operation” is reported during the TRIGGER, PROCEDURE, VIEW, FUNCTION, or EVENT import process.

Check whether the statement reporting the error uses DEFINER and uses users other than the current user (for example DEFINER=useruser@host). If this is the case, MySQL requires SUPER privileges to execute this statement. MySQL Database on Azure does not provide user SUPER privileges (see Service limitations), causing an error. To resolve this error, delete DEFINER from the statement and use the default current user.

The MySQL Database on Azure Management Portal only supports read-write permissions for the entire database configured for the user. Can I still migrate the database if my database has more detailed user permissions settings?

Yes. Although the Azure portal and Windows PowerShell/REST API supports only setting read/write privileges for the entire database when you create users or databases, you can use the grant command to fine-tune user privilege settings.