MySQL bidirectional sync hot backup settings

1. Description of the environment.
Host: 103.241.49.137 (A)
Host: 103.240.182.191 (B)
MYSQL version is 5.1.11
2, authorized users. (I am lazy, directly with the root skip this step)
A:
Mysql> Grant Replication Slave,file on * * to ' backup_251 ' @ ' 103.240.182.191 ' identified
By ' 123456 ';
Query OK, 0 rows Affected (0.00 sec)

mysql> flush Privileges;
Query OK, 0 rows Affected (0.00 sec)

B:
Mysql> Grant Replication Slave,file on * * to ' backup_167 ' @ ' 103.241.49.137 ' identified
By ' 123456 ';
Query OK, 0 rows Affected (0.00 sec)

mysql> flush Privileges;
Query OK, 0 rows Affected (0.00 sec)
Then both stop the MySQL server.

3, configuration files.
MY.CNF on two machines, under Windows is the My.ini file, which opens the binary log.

Find the [MYSQLD] node and add the following below

Vi/etc/my.cnf
A:
user = MySQL
Log-bin=mysql-bin
Server-id = 1
Binlog-do-db=test (need to synchronize the library, if you want to synchronize the entire database, this sentence is removed)
Binlog-ignore-db=mysql (libraries that do not need to be synchronized)
Replicate-do-db=test
Log-slave-updates in order to let slave also can act as master, write Relay-log will also write to Bin-log
Replicate-ignore-db=mysql
Slave-skip-errors=all #出现错误后忽略, if you do not add this, any errors occur, the synchronization process terminates
Master-connect-retry=60 Breakpoint Reconnect Time
Sync_binlog=1
Auto_increment_increment=2 (because it is bidirectional, automatically added ID will conflict, change the step size to 2 initial set to 1)
Auto_increment_offset=1

///////////////////////////////////////////
Server-id = 1
Log-bin=/tmp/logbin/mysql-bin
Binlog-do-db =SIXFJ
Binlog-ignore-db=mysql
Replicate-do-db=sixfj
Replicate-ignore-db=mysql
Slave-skip-errors=all
Sync_binlog=1
auto_increment_increment=2
Auto_increment_offset=1

Linux shutdown mysql Command
/usr/local/mysql/bin/mysqladmin-uroot-p shutdown
Linux commands to start MySQL
/usr/local/mysql/bin/mysqld_safe &

/usr/local/mysql/bin/mysql-uroot-pgdczxw2008

Grant Replication Slave on * * to [e-mail protected] ' 103.240.182.191 ' identified by ' aa12345q ';

B:
user = MySQL
Log-bin=mysql-bin
Server-id = 2
Binlog-do-db=test (need to synchronize the library, if you want to synchronize the entire database, this sentence is removed)
Binlog-ignore-db=mysql (libraries that do not need to be synchronized)
Replicate-do-db=test
Replicate-ignore-db=mysql

Slave-skip-errors=all
Sync_binlog=1
Auto_increment_increment=2 (because it is bidirectional, automatically added ID will conflict, change the step size to 2 initial set to 2)
auto_increment_offset=2

///////////////////////////////////

Server-id = 3
Log-bin=/tmp/logbin/mysql-bin
Binlog-do-db =SIXFJ
Binlog-ignore-db=mysql
Replicate-do-db=sixfj
Replicate-ignore-db=mysql
Slave-skip-errors=all
Sync_binlog=1
auto_increment_increment=2
auto_increment_offset=2

The description of these parameters depends on the manual.

4. Restart the MySQL server.

Mysql>grant replication Slave on * * to [e-mail protected] ' 103.241.49.137 ' identified by ' aa12345q ';

5. Go to MySQL shell.

A:
Mysql> flush tables with Read lock/g
Query OK, 0 rows Affected (0.00 sec)

Mysql> Show Master status/g
1. Row ***************************
file:mysql-bin.000007
position:528
Binlog_do_db:test
Binlog_ignore_db:mysql
1 row in Set (0.00 sec)

B:
Mysql> flush tables with read lock;
Query OK, 0 rows Affected (0.00 sec)

Mysql> Show Master status/g
1. Row ***************************
file:mysql-bin.000004
position:595
Binlog_do_db:test
Binlog_ignore_db:mysql
1 row in Set (0.00 sec)
Then back up your data to keep the data of two machines consistent.
There are many ways. Look at the next step when you're done.

6. Perform the change MASTER to command on the respective machine.
A:
Mysql> Change Master to
Master_host= ' 103.240.182.191 ',
Master_user= ' backup_251 ',
-master_password= ' 123456 ',
Master_log_file= ' mysql-bin.000004 ',
master_log_pos=595;
Query OK, 0 rows affected (0.01 sec)
mysql> start slave;
Query OK, 0 rows Affected (0.00 sec)

B:
Mysql> Change Master to
Master_host= ' 103.241.49.137 ',
Master_user= ' backup_167 ',
-master_password= ' 123456 ',
Master_log_file= ' mysql-bin.000007 ',
master_log_pos=528;
Query OK, 0 rows affected (0.01 sec)
mysql> start slave;
Query OK, 0 rows Affected (0.00 sec)

7. See if both the IO process and the slave process on their respective machines are turned on.
A:

Mysql> show processlist/g;
*************************** 1. Row ***************************
Id:2
User:repl
Host: 103.240.182.191:54475
Db:null
command:binlog Dump
time:1590
State:has sent all Binlog to slave; waiting For Binlog to is updated
Info:null
*************************** 2. Row ***************************
Id:3
U Ser:system user
Host:
db:null
command:connect
time:1350
state:waiting for master to send EVENT
Info:null
*************************** 3. Row ***************************
Id:4
User:system User
Host: br> db:null
Command:connect
time:1149
State:has read all relay logs; Waiting for the slave I/O thread to upd Ate it
Info:null
*************************** 4. Row ***************************
Id:5
User:root
Host : localhost
db:test
command:query
time:0
state:null
info:show processlist
4 rows in Set (0.00 s EC)

B:

Mysql> Show processlist/g
*************************** 1. Row ***************************
Id:1
User: System User
Host:
db:null
command:connect
time:2130
state:waiting for Master to send event
INF O:null
*************************** 2. Row ***************************
Id:2
User:system User
Host:
D B:null
Command:connect
time:1223
State:has read all relay log; Waiting for the slave I/O thread to update I T
Info:null
*************************** 3. Row ***************************
Id:4
User:root
Host:loc Alhost
Db:test
command:query
time:0
state:null
info:show processlist
************************ 4. Row ***************************
Id:5
user:repl2
host:103.241.49.137:50718
Db:null
Command:binlog Dump
time:1398
State:has sent all binlog to slave, waiting for Binlog to being updated
Info:null
4 rows in Set (0.00 sec)

8, release the respective lock, and then the plug data test.
mysql> unlock tables;
Query OK, 0 rows Affected (0.00 sec)

Insert the comparison of the previous two machine tables:
A:

Mysql> Show tables;
+----------------+
| Tables_in_test |
+----------------+
| T11_innodb |
| t22 |
+----------------+
B:

Mysql> Show tables;
+----------------+
| Tables_in_test |
+----------------+
| T11_innodb |
| t22 |
+----------------+
Inserting from a machine
A:
Mysql> CREATE TABLE T11_replicas
(id int NOT NULL auto_increment primary key,
-Str varchar (255) NOT null) engine MyISAM;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT into T11_replicas (str) values
(' This was a master to master Test table ');
Query OK, 1 row affected (0.01 sec)

Mysql> Show tables;
+----------------+
| Tables_in_test |
+----------------+
| T11_innodb |
| T11_replicas |
| t22 |
+----------------+
3 Rows in Set (0.00 sec)

Mysql> select * from T11_replicas;
+----+---------------------------------------+
| ID | str |
+----+---------------------------------------+
| 1 | This was a master to master Test table |
+----+---------------------------------------+
1 row in Set (0.00 sec)

Now look at machine B:

Mysql> Show tables;
+----------------+
| Tables_in_test |
+----------------+
| T11_innodb |
| T11_replicas |
| t22 |
+----------------+
3 Rows in Set (0.00 sec)

Mysql> select * from T11_replicas;
+----+---------------------------------------+
| ID | str |
+----+---------------------------------------+
| 1 | This was a master to master Test table |
+----+---------------------------------------+
1 row in Set (0.00 sec)

Now in turn, insert the data from the B machine:
B:

mysql> INSERT into T11_replicas (str) VALUES (' This is a Test 2 ');
Query OK, 1 row Affected (0.00 sec)

Mysql> select * from T11_replicas;
+----+---------------------------------------+
| ID | str |
+----+---------------------------------------+
| 1 | This was a master to master Test table |
| 2 | This is a Test 2 |
+----+---------------------------------------+
2 rows in Set (0.00 sec)
Let's see A.
A:
Mysql> select * from T11_replicas;
+----+---------------------------------------+
| ID | str |
+----+---------------------------------------+
| 1 | This was a master to master Test table |
| 2 | This is a Test 2 |
+----+---------------------------------------+
2 rows in Set (0.00 sec)

All right. Now two tables are master to each other.

MySQL bidirectional sync hot backup settings