Advanced Data Solutions : Bidirectional replication with Azure Database for MySQL

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:

Primary keys that are created on one MySQL instance can’t clash with primary keys created on other MySQL instances.
Fields that auto increment can create problems unless you take steps to address potential issues.
Though triggers will be active on both servers, you’ll want to turn off events on one of the servers. Select one server to serve as your main master, and let the events run there. If you aren’t using events in your applications, then turn off events on both servers.

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:

Configure the server parameter that’s required for replication.
Create a demo database (optional).
Create a replica server to act as a second instance of Azure Database for MySQL - Flexible Server.
Configure networking requirements.
Configure bidirectional replication.
Test replication (optional).

Note: This post applies only to bidirectional replication between two instances of Azure Database for MySQL - Flexible Server.

Prerequisites

Before you begin to work through the process outlined in this post, be sure to:

Create an instance of Azure Database for MySQL - Flexible Server.
Download and install MySQL Workbench or another third-party MySQL client tool.

Configure the server parameters required for replication

In the Azure portal, navigate to the Server parameters pane, and then configure the following settings:

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.

Create a demo database for bidirectional replication

In the Azure portal, on the Overview pane of your Azure Database for MySQL- Flexible Server instance, look for the connection information.

Azure Portal.png

2. In MySQL Workbench, add a connection.

mysql workbench.png

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());

Create a replica server to act as a second instance

Create a replica server for the existing instance of Azure Database for MySQL – Flexible Server. This will act as the second instance of Azure Database for MySQL – Flexible Server. For more information, see Create a read replica.

To check the replication lag, connect to the second instance, run the command show slave status, and then confirm that the value of ‘seconds-behind master’ is zero. You can also check from the replication lag by using the Azure portal. For more information, see Monitor replication. After this, run the command show master status and confirm that the value of Executed_Gtid_Set is same in the source and replica servers.

Stop the replication. After you stop the replication, the second instance becomes read-write. For more information, see Stop replication to a replica server.

Configure networking requirements

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.

If the source server is configured with a public endpoint, then, in the firewall rule, ensure that “Allow access to Azure services” is enabled. For more information, in the article Azure Database for MySQL server firewall rules, see Connecting from Azure.
If the source server is configured with private access, then ensure that the network security group security rules on the VNet are configured to allow communication between the two instances of Azure Database for MySQL.

Configure bidirectional replication

In MySQL Workbench, connect to both instances of Azure Database for MySQL.
In each instance of Azure Database for MySQL, create a user by running the following command:

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.

Test the replication

In MySQL Workbench, in the session connected to the source instance of Azure Database for MySQL – Flexible Server, copy and run the following query:

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;

Conclusion

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!

Posted at https://sl.advdat.com/3r0589J

Advanced Data Solutions

Friday, January 7, 2022

Bidirectional replication with Azure Database for MySQL - Flexible Server