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

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

Configuration steps

  1. Check that the system variable lower_case_table_names on the MySQL server is set to 1. If not, then you must set it to 1. This is 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;
  1. Set the master server to read-only mode.
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SET GLOBAL read_only = ON;
  1. 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:

Return to results

  1. Export all databases belonging 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’s built-in libraries, which include the mysql library and the test library.

  1. After you have finished exporting the databases, set the MySQL server back to read-only mode.
mysql> SET GLOBAL read_only = OFF;
mysql> UNLOCK TABLES;
  1. Create an account on the master MySQL server for data replication use, and set up the permissions.
CREATE USER '<your user>'@'%' IDENTIFIED BY '<your password\>';
GRANT REPLICATION SLAVE ON \*.\* TO '<your user\>'@'%';
  1. Sign in to the Azure portal, and then create a new MySQL server instance on MySQL Database on Azure.

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

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

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

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

  2. 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.

  3. Log in to the virtual machine, then connect to the new MySQL server using mysql.exe.

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

  1. Configure the new MySQL server as a subordinate server.
  1. Select the new MySQL server, then press Connect locally.

  2. Enable local replication, then enter the subordinate instance parameters.

i. For the master server binary log filename and offset, please enter the details that you obtained in Step 2.

ii. 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.

  1. After you have configured all the information, press Save.

Note

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

Configuration process

  1. 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 is set to subordinate server, the server will be in read-only mode. After the replication role of the MySQL server 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 slave server parameters. We recommend that you set the binlog_format parameter for the master server to Mixed or Row, to avoid data replication errors that could occur as a result of using unsafe statements such as sysdate().

Data replication restrictions

  1. 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 slave server, then you will need to create the same account yourself on MySQL Database on Azure.

  2. The master and slave server versions must be the same. For example, they must all be MySQL 5.6 of 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 MySQL servers. If the value of the parameter is smaller on the slave server than on the master server, some DMLs may run successfully on the master side but fail to run on the slave server, causing an error. Ensure that the max_allowed_packet values are consistent between the master and slave servers.

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

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

    1. 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 slave server, faulty write operations were performed on it.

    • There are input errors for the binary log file name or offset the replication role was changed to the slave server.

If data replication errors do occur, solve them by using the following process:

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

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

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

    1. 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 do not recommend making any changes.

    2. 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 will continue to use the previous password and CA certificate.

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