How to implement two-way replication in MySQL

How to implement two-way replication in MySQL

Introduction

I know there are many articles and guides to implement master-slave replication on the Internet. In master-slave replication, the host affects the slave. However, any changes from the database will not affect the primary database. This article will help you implement bidirectional replication. (That is, changes to both the host and slave will affect the two servers ).

Background

You can refer to Aadhar Joshi's article to implement master-slave replication, or follow the simple steps below:
Refer:
Configure host (192.168.1.30) on machine)
Configure slave machine (192.168.1.29) on machine B)
We can use the following steps to achieve this
Step 1: machine A sets the host
Open the configuration file on the host. The default file location is C: \ Program Files \ MySQL Server 5.0 \ my. ini.
Replace [mysqld] with [mysqld] on the server

Copy codeThe Code is as follows:
Server-id = 1
Log-bin = mysql-bin
Innodb_flush_log_at_trx_commit = 1
Sync_binlog = 1
Binlog_do_db = demo
Port = 3306

Save the configuration file and close it.
Restart the mysql service to make it take effect.
Step 2: Set slave machine for machine B:
Open the mysql configuration file on the slave machine. The default location is C: \ Program Files \ MySQL Server 5.0 \ my. ini.
Replace [mysqld] with the following code on the server side.

Copy codeThe Code is as follows:
[Mysqld]
Server-id = 2
Log-bin = mysql-bin
Innodb_flush_log_at_trx_commit = 1
Sync_binlog = 1

Save the configuration file and disable

Restart the mysql service to make it take effect.
Where:
Server-id = 1 → unique identifier of the service. It must be different between the master and slave nodes.
Log-bin = mysql-bin → use transactions in InnoDB replication settings to ensure the best persistence and consistency. You should use innodb_flush_log_at_trx_commit = 1 and sync_binlog = 1.
Binlog_do_db = demo → database to be copied
Port = 3306 → default database port

Step 3: create a user on the host
Open mysql Command Line

Copy codeThe Code is as follows:
Mysql> grant replication slave on *. * TO 'replicator' @ '%' identified by 'replicase ';

Now we use this command 'show master status' to determine the file name and location of the current binary log file. Remember this detail! In our case, the following output is obtained:

Copy codeThe Code is as follows:
Mysql> show master status;

Copy codeThe Code is as follows:
+ ------------------ + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + -------------- + ------------------ +
| Mysql-bin.000153 | 106 |
+ ------------------ + ---------- + -------------- + ------------------ +
1 row in set (0.00 sec)

Continue: mysql> flush privileges;
Select Database: mysql> USE newdatabase;
LOCK the database to prevent any new changes: flush tables with read lock;
Step 4: Connection Master/Slave:
Open mysql Command Line
Stop slave: Stop slave;
Execute Command

Copy codeThe Code is as follows:
Mysql> CHANGE MASTER
-> MASTER_HOST = '192. 168.1.30 ',
-> MASTER_USER = 'replicator ',
-> MASTER_PASSWORD = 'replicase ',
-> MASTER_LOG_FILE = 'mysql-bin.000153 ',
-> MASTER_LOG_POS = 106;

4. Restart and Start replication from the machine: Start Slave;
You can also click Master-Slave Replication.

Bidirectional Replication

Step 1: Create a host user on the slave
Open the mysql Command Line (192.168.1.29) of the slave machine)
Mysql> grant replication slave on *. * TO 'master _ replicator '@' % 'identified by 'master ';
Now we use this command 'show master status' to determine the file name and location of the current binary log file. Remember this detail! In our case, the following output is obtained:

Copy codeThe Code is as follows:
Show master status;

Copy codeThe Code is as follows:
+ ------------------ + ---------- + -------------- + ------------------ +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+ ------------------ + ---------- + -------------- + ------------------ +
| Mysql-bin.000153 | 106 |
+ ------------------ + ---------- + -------------- + ------------------ +
1 row in set (0.00 sec)

Continue: mysql> flush privileges;
Select Database: mysql> USE newdatabase;
LOCK the database to prevent any new changes: flush tables with read lock;
Step 2: Use the host user to connect to the slave machine (192.168.1.30 ):
Open mysql command line on the host
Stop slave: Stop slave;
Execute Command

Copy codeThe Code is as follows:
Mysql> CHANGE MASTER
-> MASTER_HOST = '192. 168.1.29 ',
-> MASTER_USER = 'master _ replicator ',
-> MASTER_PASSWORD = 'master ',
-> MASTER_LOG_FILE = 'mysql-bin.000153 ',
-> MASTER_LOG_POS = 106;

4. Restart and Start replication from the machine: Start Slave;
Run the following command to check the system status:

Copy codeThe Code is as follows:
Show slave status \ G;

You can see 192.168.1.30:

Copy codeThe Code is as follows:
Mysql> show slave status \ G;
* *************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.29
Master_User: slave_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000013
Read_Master_Log_Pos: 98
Relay_Log_File: PC-relay-bin.000074
Relay_Log_Pos: 235
Relay_Master_Log_File: mysql-bin.000013
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
Replicate_Do_DB: demo
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 98
Relay_Log_Space: 235
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
1 row in set (0.01 sec)

Copy codeThe Code is as follows:
ERROR:
No query specified

Areas of interest

Now you can perform database operations, such as inserting, deleting, updating, and deleting tables, and check whether the database works properly.
Important:
1. The server_Id must be different between the host and slave.
2. Verify that the log file is set correctly.
3. Use these commands to check

Copy codeThe Code is as follows:
Show slave status \ G;

Copy codeThe Code is as follows:
Slave_IO_Running: Yes
Slave_ SQL _Running: Yes
Slave_IO_State: Waiting for master to send event

The above is all the content of this article. I hope you will like it.

Please take a moment to share your article with your friends or leave a comment. Thank you for your support!