MySQL Master master slave Environment setup

master1:192.168.128.47

Master2:192.168.128.96slave:192.168.128.97 master1 and Master2, master2 as slave Master①, On the Master1 side: Modify My.cnfserver-id = 1auto_increment_increment = 2auto_increment_offset = 1#binlog_do_db = Sssjpt,sakila,test  --optional, to specify those DB operations records on the master side binlog#binlog_ignore_db = mysql--optional, the DB operations used to specify the master end do not log binlog#replicate_do_db = Sssjpt,sakila,test-optional, which specifies which db binlog#replicate_ignore_db from the master side of the slave-side SQL thread to apply = mysqllog_slave_updates-- Synchronizing the binary logs on the master side to complete synchronization also records the current library binary log so that the slave can be interpreted as the master parameter of another slave: Auto_increment_increment = 2auto_increment_ Offset = 1 is primarily used for Master-master, because both master nodes may have simultaneous write operations, so there is a possibility of auto_increment key-value conflicts, which will now auto_increment_increment= 2,auto_increment_offset=1, each time a self-increment operation is performed at the Master1 end, the data is increased by 2 larger than the maximum value of the current data, and is incremented by 2 numbers at a time from the 1th position after the maximum value. For example, 1,3,5,7 ... The auto_increment_increment=2,auto_increment_offset=2 will increase the data by 2 greater than the current maximum value at the Master2 end of each self-increment operation, and start at the 2nd position after the maximum value. Add 2 numbers at a time, such as 2,4,6,8 ... If only one master is written in master-slave synchronization, there is no comparison setting auto_increment_increment,auto_increment_offset parameter but in the new version of MySQL, you can innodb_ Autoinc_lock_mode parameter to limit  ②,master1:grant replication slave to * * on [email protected] ' 192.168.128.96 ' identified by ' 123456 '; show master status;mysqldump-uroot-p--databases db1 DB2--lock-all-tables >/tmp/dump.sqlscp /tmp/ Dump.sql [Email protected]:/tmpmaster2:mysql-uroot-p </tmp/dump.sqlchange master to master_host= ' 192.168.128.47 ', master_user= ' backup ', master_password= ' 123456 ', master_log_file= ' mysql-bin.000001 ', Master_log_ Pos=120;start Slave; ③, on Master2 side: Modify My.cnfserver-id = 2 auto_increment_increment = 2auto_increment_offset = 2#binlog_do_db = sssjpt,sakila,test#binlog_ignore_db = mysql#replicate_do_db = Sssjpt,sakila,test#replicate_ignore_ db = mysqllog_slave_updates--Synchronizing the binary logs on the master side to complete synchronization also records the current library binary log so that the slave can be used as another slave master ④,master2: Grant replication Slave to * * on [email protected] ' 192.168.128.47 ' identified by ' 123456 '; show Master status;  Master1:change Master to master_host= ' 192.168.128.96 ', master_user= ' backup ', Master_password= ' 123456 ', master_log_file= ' mysql-bin.000001 ', Master_log_pos=120;start slave; ⑤,master1:scp /tmp/ Dump.sql [Email protected]:/tmpslave: Modify My.cnfserver-id = 3  #binlog_do_db = Sssjpt,sakila,test#binlog_ ignore_db = mysql#replicate_do_db = sssjpt,sakila,test#replicate_ignore_db = Mysql mysql-uroot-p </tmp/ Dump.sqlchange Master to master_host= ' 192.168.128.97 ', master_user= ' backup ', master_password= ' 123456 ', Master_log_ File= ' mysql-bin.000001 ', Master_log_pos=120;start slave;

MySQL Master master slave Environment Setup