The method Guide of MySQL Implementing bidirectional replication _mysql

Brief introduction

I know there are a lot of articles and guidelines for implementing master-from replication on the Internet. In master-from replication, the host affects the machine. But any changes from the database will not affect the primary database, and this article will help you achieve bidirectional replication. (That is, both the host and the machine changes will affect both servers).

Background

You can refer to this article in Aadhar Joshi for master-slave replication, or you can follow these simple steps:
Please refer to:
Configuring the host in Machine A (192.168.1.30)
Configure the machine B from the machine (192.168.1.29)
We can use the following steps to achieve this
Step 1: Machine A sets up the host
Open the configuration file in the host, the default file location is C:\Program files\mysql\mysql Server 5.0\my.ini
Replace [mysqld] with [mysqld] in the service-side section

Copy Code code 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
Restart the MySQL service for it to take effect.
Step 2: Machine B set from machine:
Open the MySQL configuration file from the machine, the default location is C:\Program files\mysql\mysql Server 5.0\my.ini
Replace [mysqld] with the following code in the service-side section

Copy Code code as follows:

[Mysqld]
server-id=2
Log-bin=mysql-bin
Innodb_flush_log_at_trx_commit=1
Sync_binlog=1

Save configuration file, close

Restart the MySQL service to take effect.
Where:
Unique identification of the server-id=1→ service. Must be different from the Lord.
log-bin=mysql-bin→ to 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→ the database to be replicated
port=3306→ default Database Port

Step 3: Create user in Host
Open the MySQL command line

Copy Code code as follows:

Mysql> GRANT REPLICATION SLAVE on *.* to ' replicator ' @ '% ' identified by ' REPLICATION ';

Now we use this command ' Show Master status ' to determine the filename and location of the current binary log file. Remember this detail! In our case, we get the following output:

Copy Code code as follows:

Mysql> Show master status;

Copy Code code 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: Connect Master and subordinate:
Open the MySQL command line
Stop from machine: stop slave;
Execute command

Copy Code code as follows:

Mysql> Change MASTER to
-> master_host= ' 192.168.1.30 ',
-> master_user= ' Replicator ',
-> master_password= ' Replication ',
-> master_log_file= ' mysql-bin.000153 ',
-> master_log_pos=106;

4. Restart from the machine start copying: Start Slave;
Also can point Master-slave Replication.

Ways to achieve bidirectional replication

First step: Create a host user from the machine
Open the MySQL command line from the machine (192.168.1.29)
Mysql> GRANT REPLICATION SLAVE on *.* to ' master_replicator ' @ '% ' identified by ' master ';
Now we use this command ' Show Master status ' to determine the filename and location of the current binary log file. Remember this detail! In our case, we get the following output:

Copy Code code as follows:

Show master status;

Copy Code code 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: Connect with the host user from the Machine (192.168.1.30):
Open the MySQL command line on the host
Stop from machine: stop slave;
Execute command

Copy Code code as follows:

Mysql> Change MASTER to
-> 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 from the machine start copying: Start Slave;
The following command checks the system state:

Copy Code code as follows:

Show SLAVE Status\g;

You can see 192.168.1.30:

Copy Code code 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 Code code as follows:

ERROR:
No query specified

Areas of Interest

Now you can do some database operations, such as Insert deletion update delete table truncation, you can also check whether the database is working correctly.
Focus on Remembering:
1. The server_id must be different in the host and from the machine
2. Verify that the log file is the correct setting
3. Check with these commands

Copy Code code as follows:

Show SLAVE Status\g;

Copy Code code as follows:

Slave_io_running:yes
Slave_sql_running:yes
Slave_io_state:waiting for Master to send event

The above is the entire content of this article, I hope you can enjoy.

Please take a moment to share the article with your friends or leave a comment. We will sincerely thank you for your support!