How to configure Azure Database for MySQL Data-in Replication
In this article, you will learn how to set up Data-in Replication in the Azure Database for MySQL service by configuring the primary and replica servers. Data-in Replication allows you to synchronize data from a primary MySQL server running on-premises, in virtual machines, or database services hosted by other cloud providers into a replica in the Azure Database for MySQL service.
This article assumes that you have at least some prior experience with MySQL servers and databases.
Create a MySQL server to be used as replica
Create a new Azure Database for MySQL server
Create a new MySQL server (ex. "replica.mysql.database.azure.com"). Refer to Create an Azure Database for MySQL server by using the Azure portal for server creation. This server is the "replica" server in Data-in Replication.
The Azure Database for MySQL server must be created in the General Purpose or Memory Optimized pricing tiers.
Create same user accounts and corresponding privileges
User accounts are not replicated from the primary server to the replica server. If you plan on providing users with access to the replica server, you need to manually create all accounts and corresponding privileges on this newly created Azure Database for MySQL server.
Configure the primary server
The following steps prepare and configure the MySQL server hosted on-premises, in a virtual machine, or database service hosted by other cloud providers for Data-in Replication. This server is the "primary" in Data-in replication.
Turn on binary logging
Check to see if binary logging has been enabled on the primary by running the following command:
SHOW VARIABLES LIKE 'log_bin';
If the variable
log_binis returned with the value “ON", binary logging is enabled on your server.
log_binis returned with the value “OFF”, turn on binary logging by editing your my.cnf file so that
log_bin=ONand restart your server for the change to take effect.
Primary server settings
Data-in Replication requires parameter
lower_case_table_namesto be consistent between the primary and replica servers. This parameter is 1 by default in Azure Database for MySQL.
SET GLOBAL lower_case_table_names = 1;
Create a new replication role and set up permission
Create a user account on the primary server that is configured with replication privileges. This can be done through SQL commands or a tool like MySQL Workbench. Consider whether you plan on replicating with SSL as this will need to be specified when creating the user. Refer to the MySQL documentation to understand how to add user accounts on your primary server.
In the commands below, the new replication role created is able to access the primary from any machine, not just the machine that hosts the primary itself. This is done by specifying "syncuser@'%'" in the create user command. See the MySQL documentation to learn more about specifying account names.
Replication with SSL
To require SSL for all user connections, use the following command to create a user:
CREATE USER 'syncuser'@'%' IDENTIFIED BY 'yourpassword'; GRANT REPLICATION SLAVE ON *.* TO ' syncuser'@'%' REQUIRE SSL;
Replication without SSL
If SSL is not required for all connections, use the following command to create a user:
CREATE USER 'syncuser'@'%' IDENTIFIED BY 'yourpassword'; GRANT REPLICATION SLAVE ON *.* TO ' syncuser'@'%';
To create the replication role in MySQL Workbench, open the Users and Privileges panel from the Management panel. Then click on Add Account.
Type in the username into the Login Name field.
Click on the Administrative Roles panel and then select Replication Slave from the list of Global Privileges. Then click on Apply to create the replication role.
Set the primary server to read-only mode
Before starting to dump out the database, the server needs to be placed in read-only mode. While in read-only mode, the primary will be unable to process any write transactions. Evaluate the impact to your business and schedule the read-only window in an off-peak time if necessary.
FLUSH TABLES WITH READ LOCK; SET GLOBAL read_only = ON;
Get binary log file name and offset
show master statuscommand to determine the current binary log file name and offset.
show master status;
The results should be like following. Make sure to note the binary file name as it will be used in later steps.
Dump and restore primary server
Dump all databases from primary server
You can use mysqldump to dump databases from your primary. For details, refer to Dump & Restore. It is unnecessary to dump MySQL library and test library.
Set primary server to read/write mode
Once the database has been dumped, change the primary MySQL server back to read/write mode.
SET GLOBAL read_only = OFF; UNLOCK TABLES;
Restore dump file to new server
Restore the dump file to the server created in the Azure Database for MySQL service. Refer to Dump & Restore for how to restore a dump file to a MySQL server. If the dump file is large, upload it to a virtual machine in Azure within the same region as your replica server. Restore it to the Azure Database for MySQL server from the virtual machine.
Link primary and replica servers to start Data-in Replication
Set primary server
All Data-in Replication functions are done by stored procedures. You can find all procedures at Data-in Replication Stored Procedures. The stored procedures can be run in the MySQL shell or MySQL Workbench.
To link two servers and start replication, login to the target replica server in the Azure DB for MySQL service and set the external instance as the primary server. This is done by using the
mysql.az_replication_change_primarystored procedure on the Azure DB for MySQL server.
CALL mysql.az_replication_change_primary('<master_host>', '<master_user>', '<master_password>', 3306, '<master_log_file>', <master_log_pos>, '<master_ssl_ca>');
- master_host: hostname of the primary server
- master_user: username for the primary server
- master_password: password for the primary server
- master_log_file: binary log file name from running
show master status
- master_log_pos: binary log position from running
show master status
- master_ssl_ca: CA certificate’s context. If not using SSL, pass in empty string.
- It is recommended to pass this parameter in as a variable. See the following examples for more information.
Replication with SSL
@certis created by running the following MySQL commands:
SET @cert = '-----BEGIN CERTIFICATE----- PLACE YOUR PUBLIC KEY CERTIFICATE’S CONTEXT HERE -----END CERTIFICATE-----'
Replication with SSL is set up between a primary server hosted in the domain “companya.com” and a replica server hosted in Azure Database for MySQL. This stored procedure is run on the replica.
CALL mysql.az_replication_change_primary('primary.companya.com', 'syncuser', 'P@ssword!', 3306, 'mysql-bin.000002', 120, @cert);
Replication without SSL
Replication without SSL is set up between a primary server hosted in the domain “companya.com” and a replica server hosted in Azure Database for MySQL. This stored procedure is run on the replica.
CALL mysql.az_replication_change_primary('primary.companya.com', 'syncuser', 'P@ssword!', 3306, 'mysql-bin.000002', 120, '');
mysql.az_replication_startstored procedure to initiate replication.
Check replication status
show slave statuscommand on the replica server to view the replication status.
show slave status;
If the state of
Slave_SQL_Runningare "yes" and the value of
Seconds_Behind_Masteris “0”, replication is working well.
Seconds_Behind_Masterindicates how late the replica is. If the value is not "0", it means that the replica is processing updates.
Other stored procedures
To stop replication between the primary and replica server, use the following stored procedure:
Remove replication relationship
To remove the relationship between primary and replica server, use the following stored procedure:
Skip replication error
To skip a replication error and allow replication to proceed, use the following stored procedure:
- Learn more about Data-in Replication for Azure Database for MySQL.