Configure Azure SQL Data Sync to replicate to MySQL Database on Azure

MySQL Database on Azure supports subordinate server mode and standard MySQL data replication. You can use this function to automatically sync the database data from a MySQL server instance that's running at your own location or somewhere else to a subordinate server that's running on MySQL Database on Azure.

Configuration steps

  1. Check to ensure that the system variable lower_case_table_names on the MySQL server instance is set to 1. If it is not, then you must set it to 1. You run this check because MySQL data replication requires that the parameter values are the same on the master and subordinate servers, but this parameter is set to 1 on MySQL Database on Azure.

    mysql> SET GLOBAL lower_case_table_names = 1;
    
  2. Set the master server to read-only mode.

    mysql> FLUSH TABLES WITH READ LOCK;
    mysql> SET GLOBAL read_only = ON;
    
  3. Run the SQL command show master status on the master server to obtain the current binary log filename and offset. The results returned should be similar to the following:

    Return to results

  4. Export all databases that belong to all users on the master server. You can use the mysqldump tool.

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

    Note

    You do not need to export the MySQL server built-in libraries, which include the mysql library and the test library.

  5. After you have finished exporting the databases, set the MySQL server instance back to read-only mode.

    mysql> SET GLOBAL read_only = OFF;
    mysql> UNLOCK TABLES;
    
  6. Create an account on the master MySQL server instance for data replication use, and set up the permissions.

    CREATE USER '<your user>'@'%' IDENTIFIED BY '<your password\>';
    GRANT REPLICATION SLAVE ON \*.\* TO '<your user\>'@'%';
    
  7. Sign in to the Azure portal, and then create a new MySQL server instance on MySQL Database on Azure.

  8. Create individual databases for all users on the master server on the newly created MySQL server instance.

  9. Create the required user accounts on the newly created MySQL server instance. This action is necessary because user account information cannot be replicated.

  10. Import the user database data exported from the master server to the newly created MySQL server instance. If the database file is very large, we recommend that you upload the file to a virtual machine on Azure. You can then import it into the MySQL server instance from the virtual machine. The virtual machine should be in the same data center as the newly created MySQL server instance. The specific steps are as follows:

    a. Upload the mysql.exe tool to the virtual machine.

    b. Upload the file that was exported from the database onto the virtual machine. If the backup file is very large, you can compress it before uploading.

    c. Log in to the virtual machine, and then connect to the new MySQL server instance by using mysql.exe.

    d. Run the source command to import the data from the backup file.

  11. Configure the new MySQL server instance as a subordinate server by doing the following:

    a. Select the new MySQL server instance, and then select Connect locally.

    b. Enable local replication, and then enter the subordinate instance parameters.

    • For the master server binary log filename and offset, enter the details that you obtained in step 2.

    • If you are using SSL links, select the Enable option in the locations by using the SSL links. Next, open the master server certification authority (CA) certificate and copy the entire contents into the input box of the master server CA certificate.

    c. After you have configured all the information, select Save.

    Note

    We strongly recommend that you use SSL to ensure that your data is secure.

    Configuration process

  12. After the configuration is successful, the Replication Status at the bottom should say replicating.

    Configuration process

    Note

    • After the replication role of the MySQL server instance is set to subordinate server, the server will be in read-only mode.
    • After the replication role of the MySQL server instance is set to subordinate server, none of the master server parameters on the replication page will be editable, except for the role. If there is an input error, you must set the replication role to Disabled and then reconfigure the subordinate server parameters.
    • To avoid data replication errors that could occur as a result of using unsafe statements, such as sysdate(), we recommend that you set the binlog_format parameter for the master server to Mixed or Row.

Data replication restrictions

Changes on the master server to accounts and permissions are not replicated. If you created an account on the master server and this account needs to access the subordinate server, you must create the same account yourself on MySQL Database on Azure.

The master and subordinate server versions must be the same. For example, they must all be MySQL 5.6 or MySQL 5.7.

Solve data replication errors

If replication stops because it encounters a problem of any kind, the replication status will change to Replication Error. You can find details of the error by looking at the Replication Error field.

Common causes of replication errors include:

  • The value of the max_allowed_packet parameter on the subordinate server is less than the value of the same parameter on the master server. This parameter determines the maximum permitted Data Manipulation Language (DML) size for the MySQL server instances. If the value of the parameter is smaller on the subordinate server than on the master server, some DMLs might run successfully on the master side but fail to run on the subordinate server, causing an error. Ensure that the max_allowed_packet values are consistent between the master and subordinate servers.

  • When the replication role is changed to subordinate server, there is a parameter input error on the master server. The error makes it impossible for the subordinate server to connect to the master server.

  • Data is consistent between the master and subordinate servers. For example, replication attempts to insert a record into the subordinate server that already exists. There are several possible causes for this error:

    • Some DMLs on the master server were not recorded in the binary log file. For example, before the DML was run on the master server, SET sql_log_bin=0 was run.

    • Before the replication role was changed to the subordinate server, faulty write operations were performed on it.

    • There are input errors for the binary log filename or offset the replication role was changed to the subordinate server.

If you receive data replication errors, resolve them by doing the following:

  1. Use the Azure portal to change the replication role of MySQL to Disabled. This change puts MySQL in read-only mode.

  2. Determine the cause of the error by looking at the replication error field. For example, you can set a max_allowed_packet value consistent with that on the master server and change the record on the subordinate server that is causing the replication failure.

  3. Use the Azure portal to change the replication role of MySQL back to the subordinate server.

    • The master server binary log file name and offset are the master server binary file name and offset that were previously replicated and executed. If there were previously no input errors with the binary log file name or offset, we recommend that you not make any changes.

    • For security reasons, the master server password and master server CA certificate that were previously entered will not be displayed at this time. If you do not make any changes, MySQL continues to use the previous password and CA certificate.

    • The other master server parameter fields display the corresponding parameter values that were previously entered. If there are no errors, you do not need to make any changes.