You can use replication to copy data from a source MySQL database server to one or more replica MySQL database servers. By default, replication is asynchronous, such that replicas need not be permanently connected to receive updates from the source. For scenarios in which your application needs to write to multiple databases while maintaining the same data set across all databases, you might want to replicate data between two MySQL servers. This is generally referred to as “bidirectional”, or “master-master”, replication.
With Azure Database for MySQL – Flexible Server, you can use GTID-based replication to enable bidirectional replication. However, data consistency should be handled on the application side to address data conflicts. You should also keep in mind the following points:
This post walks you through the process of setting up bidirectional replication between two instances of Azure Database for MySQL - Flexible Server. Specifically, I’ll explain how to:
Note: This post applies only to bidirectional replication between two instances of Azure Database for MySQL - Flexible Server.
Before you begin to work through the process outlined in this post, be sure to:
Setting |
Value |
Comments |
enforce_gtid_consistency |
ON |
|
gtid_mode |
ON |
You can change the GTID values, one step at a time in ascending order of modes. For example, if gtid_mode is currently set to OFF_PERMISSIVE, you can change the value to ON_PERMISSIVE, but not to ON. For more details, see Read replicas - Azure Database for MySQL - Flexible Server. |
binlog_expire_logs_seconds |
86400 |
This will help to ensure that binlogs aren’t purged for a period of 24 hours. For more details, see Server parameters - Azure Database for MySQL - Flexible Server. |
2. In MySQL Workbench, add a connection.
3. To create a demo database and insert test data, run the following script:
CREATE DATABASE `bidirrepldemo` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE bidirrepldemo ;
CREATE TABLE `tasks` (
`task_id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) NOT NULL,
`start_date` date DEFAULT NULL,
`due_date` date DEFAULT NULL,
`status` tinyint(4) NOT NULL,
`priority` tinyint(4) NOT NULL,
`description` text,
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`task_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
CREATE TABLE `tutorials_tbl` (
`tutorial_id` int(11) NOT NULL AUTO_INCREMENT,
`tutorial_title` varchar(100) NOT NULL,
`tutorial_author` varchar(40) NOT NULL,
`submission_date` date DEFAULT NULL,
PRIMARY KEY (`tutorial_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
INSERT INTO tasks(title,start_date,due_date,status,priority,description,created_at)
VALUES('Task1',CURRENT_DATE(),CURRENT_DATE(),0, 10,'My First task 1' ,CURRENT_DATE());
INSERT INTO tutorials_tbl (tutorial_title,tutorial_author,submission_date)
VALUES ('Tutorial1','John' ,CURRENT_DATE());
To configure the networking requirements, you need to ensure that the servers participating in the replication can connect to each other over port 3306. Based on the type of endpoint set up on the server, perform the appropriate following steps.
CREATE USER 'syncuser'@'%' IDENTIFIED BY 'MyPassword';
GRANT REPLICATION SLAVE ON *.* TO ' syncuser'@'%' REQUIRE SSL;
3. In the Azure portal, on the Networking pane, select Download SSL Certificate.
4. Open the certificate in Notepad, and then copy the text.
5. In MySQL Workbench, in the session connected to the source instance of Azure Database for MySQL – Flexible Server, copy and run the following query:
SET @cert = '-----BEGIN CERTIFICATE-----
<insert certificate text copied in step 4 above>
-----END CERTIFICATE-----'
call mysql.az_replication_change_master_with_gtid('msql-instance2.mysql.database.azure.com', 'syncuser', 'MyPassword', 3306, @cert);
CALL mysql.az_replication_start;
6. In MySQL Workbench, in the session connected to the replica instance, copy and run the following query:
SET @cert = '-----BEGIN CERTIFICATE-----
<insert certificate text copied in step 4 above>
-----END CERTIFICATE-----'
call mysql.az_replication_change_master_with_gtid('msql-instance1.mysql.database.azure.com', 'syncuser', 'MyPassword', 3306, @cert);
CALL mysql.az_replication_start;
7. If you run the command show slave status in either of the instances, the status Waiting for master to send event should appear. If the message does not appear, troubleshoot connectivity issues by using the guidance in Troubleshoot replication latency in Azure Database for MySQL.
INSERT INTO tutorials_tbl (tutorial_title,tutorial_author,submission_date)
VALUES ('Tutorial2','Peter',CURRENT_DATE());
2. In MySQL Workbench, in the session connected to the replica instance, copy and run the following query:
INSERT INTO tasks(title,start_date,due_date,status,priority,description,created_at)
VALUES('Task2',CURRENT_DATE(),CURRENT_DATE(),0, 10,'My second task ' ,CURRENT_DATE());
3. To confirm that the data has been updated, in MySQL Workbench, in either the source or replica instance, run the following two queries:
select * from tasks;
select * from tutorials_tbl;
You’ve now set up bidirectional replication on Azure Database for MySQL – Flexible Server. Any changes to either instance will be replicated using the native replication technique. However, remember that you need to manage data conflicts and consistency on the application side.
If you have any feedback or questions, please leave a comment below or email our team at AskAzureDBforMySQL@service.microsoft.com. Thank you!
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.