Synchronization from the MASTER node of MYSQL to the MASTER node cyclically. _ MySQL

MASTER-MASTER cyclic synchronization from the MASTER node of MYSQL to the MASTER node.
However, being prepared will have a certain impact on performance!
Write down the steps and update them at any time for any problems. Here, the database I synchronized is TEST.
1. environment description.
Host: 192.168.0.231 ()
Host: 192.168.0.20.( B)
MYSQL version 5.1.21
2. authorize the user.
A:
Mysql> grant replication slave, file on *. * to 'repl1' @ '192. 168.0.20.' identified
By '123 ';
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 'repl2' @ '192. 168.0.231 'identified
By '123 ';
Query OK, 0 rows affected (0.00 sec)

Mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
Then stop the MYSQL server.

3. configuration file.
Enable binary logs in my. cnf on both machines.
A:
User = mysql
Log-bin = mysql-bin
Server-id = 1
Binlog-do-db = test
Binlog-ignore-db = mysql
Replicate-do-db = test
Replicate-ignore-db = mysql
Log-slave-updates
Slave-skip-errors = all
Sync_binlog = 1
Auto_increment_increment = 2
Auto_increment_offset = 1

B:
User = mysql
Log-bin = mysql-bin
Server-id = 2
Binlog-do-db = test
Binlog-ignore-db = mysql
Replicate-do-db = test
Replicate-ignore-db = mysql
Log-slave-updates
Slave-skip-errors = all
Sync_binlog = 1
Auto_increment_increment = 2
Auto_increment_offset = 2
For more information about these parameters, see the manual.
The red part is very important. if one MASTER fails, the other MASTER will take over immediately.
The red part indicates frequent log refreshing on the server. This ensures that the log is refresh to the other if one fails. This ensures data synchronization.
4. restart the MYSQL server.
Perform the same steps on A and B
[Root @ localhost ~] #/Usr/local/mysql/bin/mysqld_safe &
[1] 4264
[Root @ localhost ~] #071213 14:53:20 mysqld_safe Logging to '/usr/local/mysql/data/localhost. localdomain. err '.
/Usr/local/mysql/bin/mysqld_safe: line 366: [:-eq: unary operator expected
071213 14:53:20 mysqld_safe Starting mysqld daemon with databases from/usr/local/mysql/data

5. enter the 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)
Back up your data to ensure the data consistency between the two machines.
There are many methods. Check the next step.
6. execute the change master to command on each machine.
A:
Mysql> change master
-> Master_host = '1970. 168.0.20 ',
-> Master_user = 'repl2 ',
-> Master_password = '123 ',
-> 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
-> Master_host = '192. 168.0.231 ',
-> Master_user = 'repl1 ',
-> Master_password = '123 ',
-> 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. check whether the I/O and SLAVE processes on the respective machines are enabled.
A:

Mysql> show processlist/G
* *************************** 1. row ***************************
Id: 2
User: repl
Host: 192.168.0.20.: 54475
Db: NULL
Command: Binlog Dump
Time: 1590
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
* *************************** 2. row ***************************
Id: 3
User: 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:
Db: NULL
Command: Connect
Time: 1149
State: Has read all relay log; waiting for the slave I/O thread to update 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 sec)

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
Info: NULL
* *************************** 2. row ***************************
Id: 2
User: system user
Host:
Db: NULL
Command: Connect
Time: 1223
State: Has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
* *************************** 3. row ***************************
Id: 4
User: root
Host: localhost
Db: test
Command: Query
Time: 0
State: NULL
Info: show processlist
* *************************** 4. row ***************************
Id: 5
User: repl2
Host: 192.168.0.231: 50718
Db: NULL
Command: Binlog Dump
Time: 1398
State: Has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
4 rows in set (0.00 sec)

If the red part does not appear, check the error file in the DATA directory.

8. release the locks and test data insertion.
Mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

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 |
+ ---------------- +
Insert from 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 is 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 is a master to master test table |
+ ---- + --------------------------------------- +
1 row in set (0.00 sec)

Now let's 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 is a master to master test table |
+ ---- + --------------------------------------- +
1 row in set (0.00 sec)

Insert data from the B server in turn:
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 is a master to master test table |
| 2 | This is a test 2 |
+ ---- + --------------------------------------- +
2 rows in set (0.00 sec)
Let's look at.
A:
Mysql> select * from t11_replicas;
+ ---- + --------------------------------------- +
| Id | str |
+ ---- + --------------------------------------- +
| 1 | This is a master to master test table |
| 2 | This is a test 2 |
+ ---- + --------------------------------------- +
2 rows in set (0.00 sec)

Okay. Now the two tables are MASTER nodes.

Multi-MASTER auto-increment field conflict.