MySQL Master (based on Bin-log)

Prepare two machines and install the mysql5.6.29 separately:

192.168.220.143 (Server1)

192.168.220.144 (Server2)

MySQL Main master

Start a dual master database to create a synchronized user

Server1:

Grant Replication Slave on * * to ' server ' @ ' 192.168.220.144 ' identified by ' 123456 ';

Flush privileges;

Server2:

Grant Replication Slave on * * to ' server ' @ ' 192.168.220.143 ' identified by ' 123456 ';

Flush privileges;

MY.CNF Configuration

Server1:

[Mysqld1]
Basedir =/usr/local/mysql
DataDir =/data/mysql
Port = 3306
Socket =/tmp/mysql.sock
Log-bin=mysql-bin
server-id=10
Auto-increment-increment = 2
Auto-increment-offset = 1
Log-slave-updates
Slave-skip-errors=all
Sync_binlog=1
Innodb_data_file_path=ibdata1:76m;ibdata2:12m:autoextend

Server2:

Basedir =/usr/local/mysql
DataDir =/data/mysql
Port = 3306
Log-bin=mysql-bin
Server-id=20
Auto-increment-increment = 2
Auto-increment-offset = 2
Log-slave-updates
Slave-skip-errors=all
Sync_binlog=1

Innodb_data_file_path=ibdata1:76m;ibdata2:12m:autoextend

Record Bin-log and POS locations, Server1 and Server2 separately

Server1:

Show master status;

Mysql> Show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |
+------------------+----------+--------------+------------------+-------------------+
|     mysql-bin.000002 |              423 |                  |                   | |
+------------------+----------+--------------+------------------+-------------------+

Server2:

Show master status;

Mysql> Show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |
+------------------+----------+--------------+------------------+-------------------+
|     mysql-bin.000002 |              423 |                  |                   | |
+------------------+----------+--------------+------------------+-------------------+

Start replication

Server1:

Stop slave;

Change Master to master_host= ' 192.168.220.144 ', master_user= ' server ', master_password= ' 123456 ', master_log_file= ' Mysql-bin.000002 ', master_log_pos=423;

Start slave;

Server2:

Stop slave;

Change Master to master_host= ' 192.168.220.143 ', master_user= ' server ', master_password= ' 123456 ', master_log_file= ' Mysql-bin.000002 ', master_log_pos=423;

Start slave;

show slave status\g;

Ensure status:
Slave_io_running:yes
Slave_sql_running:yes

Parameter description
Auto-increment-increment= 2 # should be set to the total number of servers in the entire structure
Auto-increment-offset = 1 # Sets the starting point for automatic growth in the database, avoiding primary key conflicts when two server data is synchronized
Log-slave-updates # This parameter is used to configure whether updates from the server are written to the binary log, this option is not opened by default, but if this slave server B is server A from the server, but also as Server C's primary server, then you need to develop this option, So that it can synchronize operations from server C to get its binary log
Slave-skip-errors # During the copy process, because of various reasons, from the server may encounter the execution of Binlog in the case of SQL error, by default, the server will stop the replication process, no longer synchronize, wait until the user self-processing. Slave-skip-errors is used to define the error number that can be skipped automatically from the server during the copy process, and when the replication process encounters a defined error number, it can be skipped automatically and executed immediately after the SQL statement. --SLAVE-SKIP-ERRORS=[ERR1,ERR2,....... all]

However, it must be noted that starting this parameter, if not properly handled, it is likely to cause the master-slave database data is out of sync, in the application needs according to the actual situation, if the data integrity requirements are not very strict, then this option does reduce maintenance costs.

Sync_binlog=0, when the transaction commits, MySQL does not do fsync such as the disk synchronization instructions to refresh Binlog_cache information to disk, and let filesystem decide when to synchronize, or cache full after the synchronization to disk.
Sync_binlog=n, after every n transaction commits, MySQL will perform a disk synchronization instruction such as Fsync to force the data in the Binlog_cache to disk.

MySQL Master (based on Bin-log)