Migrate a database to MySQL Database on Azure

This article summarizes the methods for migrating user databases to MySQL Database on Azure. It also discusses issues to be considered.

Evaluate whether an application database can be migrated

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

Because MySQL Database on Azure is compatible with MySQL 5.6 and MySQL 5.7, the majority of applications can run without any modifications. To ensure a good level of fault tolerance and to avoid failure as a result of temporary inability to connect to the database, applications need a database reconnection mechanism. Even highly available cloud databases inevitably encounter situations where failover or server maintenance cause a 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 Service consulting FAQ. In most situations, you can use the database normally by replacing the MyISAM database engine with InnoDB in the table creation code.

Solution 1: Migrate 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 instance on MySQL Database on Azure. Complete the necessary configuration steps, such as the daily backup time. For more information about the procedure, see MySQL Database on Azure database basics: Step 1.

  2. Use the Azure portal to create the target database where you want to migrate 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 datacenter. 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 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 datacenter) 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 you upload it.

  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.

    a. 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<密码> 
    

    b. 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: Migrate based on data synchronization

Database import and export is relatively simple, but it 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 want to complete a smooth migration in stages, try the data synchronization method instead. MySQL Database on Azure provides this function, which allows 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.

    Note

    You must open up external access for the current database server. We strongly recommend that you configure SSL and only allow external access using SSL.

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

    Warning

    Because the database on Azure is running in read-only mode at this time, the functionality of the application might 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.

    Migrate

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

  5. Stop the database synchronization replication. To do so, select Stop Replication in the Overview window.

    Migrate

    Note

    This operation restarts the database server.

  6. Enable the new application.

Common issues with database migration

During the TRIGGER, PROCEDURE, VIEW, FUNCTION, or EVENT import process, you might receieve the following error message: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

Check to see whether the statement reporting the error uses DEFINER and uses users other than the current user (for example DEFINER=useruser@host). If so, MySQL requires SUPER privileges to execute this statement. MySQL Database on Azure does not provide user SUPER privileges (see Service limitations), which causes an error. To resolve the 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.