MySQL Master Master sync mode

Master 192.168.56.20 and new database from both DB1 DB2 DB3 (if the database is in use, need to be locked manually from the primary backup and then from recovery)
mysql> CREATE DATABASE db1;
Query OK, 1 row Affected (0.00 sec)

Mysql> CREATE DATABASE DB2;
Query OK, 1 row Affected (0.00 sec)

mysql> CREATE DATABASE db3;
Query OK, 1 row affected (0.01 sec)

Master authorization from the account

Grant replication Slave on . to ' repl ' @ ' 192.168.56.21 ' identified by ' repl ';
Flush privileges;
Config file open Bin-log
Vim/etc/my.cnf
"MySQL"
Server-id=101 #每个节点必须不一样
Log-bin=/var/lib/mysql/mysql-bin

Restarting the Master service
Service mysqld Restart

View log files and offsets
Mysql> Show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 120 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in Set (0.00 sec)

From 192.168.56.21
Modifying a configuration file
Vim/etc/my.cnf
server-id=109
Restart Slave node Service
Service mysqld Restart

Configure from Library
Turn off the replication feature
mysql> stop Slave;
Mysql>change Master to master_host= ' 192.168.56.20 ', master_user= ' repl ', master_password= ' repl ', master_log_file= ' Mysql-bin.000001 ', master_log_pos=120;
Start replication
Mysql>start slave;
Check Replication functionality
Mysql>show slave Status\g
Two processes require yes to be normal
Slave_io_running:yes
Slave_sql_running:yes

Create a new database in the main library, and then go from the library to see if it is synchronized, which means that synchronization is normal.
Create Database hi_db;

The master-Slave synchronization configuration is complete and the primary master is now configured.

To configure the master configuration file 192.168.56.20
Vim/etc/my.cnf
"MySQL"
Server-id=101 #每个节点必须不一样
Log-bin=/var/lib/mysql/mysql-bin
auto_increment_increment=2 #步进值auto_imcrement. Normally there are N main MySQL to fill n
Auto_increment_offset=1 #起始值. Generally fill the nth master MySQL. This is the first master MySQL
#binlog-ignore=mysql #忽略mysql库 "I don't usually write."
#binlog-ignore=information_schema #忽略information_schema库 "I don't usually write."
#replicate-do-db=aa #要同步的数据库, all libraries by default
Restart the Master node service
Service mysqld Restart

Configuring the 192.168.56.21 from a configuration file
Vim/etc/my.cnf
server-id=109

Log-bin=/var/lib/mysql/mysql-bin
auto_increment_increment=2 #步进值auto_imcrement. Normally there are N main MySQL to fill n
auto_increment_offset=2 #起始值. Generally fill the nth master MySQL. This is the 2nd master MySQL

Restart Slave node Service
Service mysqld Restart
Create a master copy account from a node
Grant replication Slave on . to ' repl1 ' @ ' 192.168.56.20 ' identified by ' repl1 ';
Flush privileges;

viewing binary files
Mysql> Show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 420 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in Set (0.00 sec)

In the main execution
Turn off the replication feature
mysql> stop Slave;
Mysql>change Master to master_host= ' 192.168.56.21 ', master_user= ' repl1 ', master_password= ' repl1 ', master_log_file = ' mysql-bin.000001 ', master_log_pos=420;
Start replication
Mysql>start slave;
Check Replication functionality
Mysql>show slave Status\g
Two processes require yes to be normal
Slave_io_running:yes
Slave_sql_running:yes

Then create the database separately on both sides to see if they are all synchronized.

1, the primary master replication configuration file Auto_increment_increment and Auto_increment_offset can only guarantee that the primary key is not duplicated, but does not guarantee the primary key order.

 2、当配置完成Slave_IO_Running、Slave_SQL_Running不全为YES时，show slave status\G信息中有错误提示，可根据错误提示进行更正。 3、Slave_IO_Running、Slave_SQL_Running不全为YES时，大多数问题都是数据不统一导致。 常见出错点： 1、两台数据库都存在db数据库，而第一台MySQL db中有tab1，第二台MySQL db中没有tab1，那肯定不能成功。 2、已经获取了数据的二进制日志名和位置，又进行了数据操作，导致POS发生变更。在配置CHANGE MASTER时还是用到之前的POS。 3、stop slave后，数据变更，再start slave。出错。 终极更正法：重新执行一遍CHANGE MASTER就好了。

MySQL Master Master sync mode