MySQL Primary master replication

1MySQL5.6The primary master replication environment is as follows: Centos6.4_64mysql5.6Master1:192.168.10.11Master2:192.168.10.121.1Configure Master1a. Modify the My.cnf file to add the following: Server-id=1#数据库 ID number log-bin=mysql-bin #启用二进制日志; Binlog- Do-db=Baba1 #需要同步的数据库名, which synchronizes BABA1, baba2 two database Binlog- Do-db=Baba2binlog-ignore-db=MySQL #不同步的数据库名, if not set it can be commented out log-bin=/var/log/mysqlbin/bin_log #设定生成的 log file name, if no/var/log/Mysqlbin directory, create, and execute Chown-R Mysql.mysql/var/log/Mysqlbinlog-slave-updates #把从库的写操作, recorded in Binlog expire_logs_days=365#日志文件过期天数, the default is0, indicating that auto is not expired-increment-increment=2#设定为主服务器的数量 to prevent auto_increment fields from repeating auto-increment-offset=1#自增长字段的初始值, in multiple master environments, the self-growth ID repetition does not occur B. Add a replication account Backup[[email protected]~]# Mysql-uroot-P123456mysql>grant replication Slave on * * to [email protected]'%'Identified by'123456'; MySQL>flush privileges;c. On Master2 to test if you can connect to Master1, remember to3306add to iptables [[email protected]~]# mysql-ubackup-h192.168.10.11-p1234561.2Configure MASTER2A. Modify the My.cnf file to add the following: Server-id=2#数据库 ID number log-bin=mysql-bin #启用二进制日志; Binlog- Do-db=Baba1 #需要同步的数据库名, which synchronizes BABA1, baba2 two database Binlog- Do-db=Baba2binlog-ignore-db=MySQL #不同步的数据库名, if not set it can be commented out log-bin=/var/log/mysqlbin/bin_log #设定生成的 log file name, if no/var/log/mysqlbin directory, BA Ba Network (www.baba.io) to provide you with books, documents, source code, tools and other resources to download, is your best resources to download the network. This series of documents, exclusively issued by the BA-BA Network (Www.baba.io), for the latest version, please visit Www.baba.io to create and execute the Chown-R Mysql.mysql/var/log/Mysqlbinlog-slave-updates #把从库的写操作, recorded in Binlog expire_logs_days=365#日志文件过期天数, the default is0, indicating that auto is not expired-increment-increment=2#设定为主服务器的数量 to prevent auto_increment fields from repeating auto-increment-offset=2#自增长字段的初始值, in multiple master environments, the self-growth ID repetition does not occur B. Add a replication account Backup[[email protected]~]# Mysql-uroot-P123456mysql>grant replication Slave on * * to [email protected]'%'Identified by'123456'; MySQL>flush privileges;c. On Master1 to test if you can connect to Master2, remember to3306add to iptables [[email protected]~]# mysql-ubackup-h192.168.10.12-p1234561.3Set master-Master sync A. Restart Master1, Master2 on Mysql[[email protected]~]# service MySQL RESTARTB. View the status of Master1 [[email protected]~]# Mysql-uroot-P123456mysql>show master status;+----------------+----------+--------------+------------------+-------------------+| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |+----------------+----------+--------------+------------------+-------------------+| Bin_log.000028| -| Baba1,baba2 | MySQL | |+----------------+----------+--------------+------------------+-------------------+1Rowinch Set(0.00sec) C. View the status of Master2 [email protected]~]# Mysql-uroot-P123456mysql>show master status;+----------------+----------+--------------+------------------+-------------------+| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |+----------------+----------+--------------+------------------+-------------------+| Bin_log.000028| -| Baba1,baba2 | MySQL | |+----------------+----------+--------------+------------------+-------------------+1Rowinch Set(0.00sec) c. Set Master1 sync data from Master2 MySQL>change MASTER to Master_host='192.168.10.12', master_port=3306, BA Ba Network (www.baba.io) to provide you with books, documents, source code, tools and other resources to download, is your best resources to download the network. This series of documents, exclusively issued by the BA-BA Network (Www.baba.io), for the latest version, please visit Www.baba.ioMASTER_USER='Backup', master_password='123456', master_log_file='bin_log.000028', master_log_pos=120;Mysql>start slave; #查询数据库的 slave state MySQL>show slave status\g; #如果下面两个参数都是 Yes, the Master1 configuration succeeds Slave_IO_Running:YesSlave_SQL_Running:Yesd. Set Master2 from Maste R1 Synchronizing Data MySQL>change MASTER to Master_host='192.168.10.11', master_port=3306, Master_user='Backup', master_password='123456', master_log_file='bin_log.000028', master_log_pos=120;Mysql>start slave; #查询数据库的 slave state MySQL>show slave status\g; #如果下面两个参数都是 Yes, the Master1 configuration is successful Slave_IO_Running:YesSlave_SQL_Running:Yes1.4Test Primary master replication a. On Master1, do the following: [[email protected]~]# Mysql-uroot-P123456mysql>CREATE DATABASE Baba1;mysql>Use baba1;mysql> CREATE TABLE Test (IDintAuto_increment,name varchar ( -), PRIMARY KEY (' id ')); MySQL> INSERT into Test (name) VALUES ('AAA'); MySQL> INSERT into Test (name) VALUES ('BBB'); MySQL> INSERT into Test (name) VALUES ('CCC'); MySQL>Select* fromtest;+----+------+| ID | Name |+----+------+|1| AAA | |3| BBB | |5| CCC |+----+------+3Rowsinch Set(0.00sec) b. On the Master2, do the following: BA Ba Network (www.baba.io) to provide you with books, documents, source code, tools and other resources to download, is your best resources to download the network. This series of documents, exclusively issued by the BA-BA Network (Www.baba.io), for the latest version, please visit Www.baba.io[[email protected]~]# Mysql-uroot-P123456mysql>Use baba1;mysql>Select* fromtest;+----+------+| ID | Name |+----+------+|1| AAA | |3| BBB | |5| CCC |+----+------+3Rowsinch Set(0.00sec) MySQL> INSERT into Test (name) VALUES ('DDD'); MySQL> INSERT into Test (name) VALUES ('Eee'); MySQL> INSERT into Test (name) VALUES ('FFF'); MySQL>Select* fromtest;+----+------+| ID | Name |+----+------+|1| AAA | |3| BBB | |5| CCC | |6| DDD | |8| eee | |Ten| FFF |+----+------+6Rowsinch Set(0.00Sec

MySQL Primary master replication