MySQL Master master (dual master) data synchronization

1. Two MySQL can read and write, the main preparation, the default is only one (Mastera) responsible for data writing, the other (Masterb) standby;
2. Mastera is the main library of Masterb, Masterb is the main library of Mastera, they are mainly from each other;

Deficiencies:
1. Masterb may be in a suppressed idle state (can be used when he is from the library, responsible for part of the query);
2. After the main library to provide services from the library to wait for the Masterb to synchronize the data before the Masterb up to synchronize data, Ken can cause a certain degree of synchronization delay;

Build the Environment

System: CentOS7
database version:5.6.36
Mastera Address:192.168.188.2
Masterb Address:192.168.188.3
installation directory:/usr/local/mysql/
Data Catalog:/data/mysql/
A and B machines are installed MySQL, the database and the table are synchronized well in advance;

Firewall configuration Iptables

Add MySQL classmate port (default 3306)

vim /etc/sysconfig/iptables                //编辑iptables文件，也可以用iptables命令来添加，不过要保存命令-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT        //添加允许3306端口通过service iptables restart            //重启iptables服务

Turn off SELinux

vim /etc/selinux/config                //编辑SELinux配置文件SELINUX=disabled                //修改值为disabled为关闭

Server Settings Mastera settings (192.168.188.2)

vim /etc/my.cnf                //编辑my.cnf文件server-id=2 //增加server-id为2，一般为自己iplog_bin=test01 //设置log_bin名为test01

/etc/init.d/mysqld restart        //重启mysql服务

mysql -uroot -p123456            //登录mysql，不要照抄，-u后面是账号，-p后面是密码grant replication slave on *.* to ‘repl‘@‘192.168.188.3‘ identified by ‘123456‘;        //创建mysql账号repl，只容许指定ip访问，也可以指定ip范围192.168.188.%，其中%为通配符，表示所有；flush privileges;            //刷新授权表信息flush tables with read lock;    //锁定数据库表暂时无法写服务；show master status；        //查看binlog文件值与pos值stop slave；            //关闭同步

Masterb settings (192.168.188.3)

vim /etc/my.cnfserver-id=3                        //增加server-id为3，一般为自己iplog_bin=test02                    //设置log_bin名为test02

/etc/init.d/mysqld restart    //重启mysql服务

mysql -uroot -p123456        //登录mysqlgrant replication slave on *.* to ‘repl‘@‘192.168.188.2‘ identified by ‘123456‘;              //创建用户，允许192.168.188.2登录本机器flush privileges；                //刷新授权表stop slave；                        //关闭同步show master status；        //查看binlog文件值与pos值

change master to master_host=‘192.168.188.2‘, master_user=‘repl‘, master_password=‘123456‘, master_log_file=‘test01.000001‘, master_log_pos=664383;        //这里注意log_file与pos值都要对应对应A的show master status；值start slave；        //开启同步

Mastera settings

change master to master_host=‘192.168.188.3‘, master_user=‘repl‘, master_password=‘123456‘, master_log_file=‘test02.000001‘, master_log_pos=664343;     //这里log_file与pos值写的必须是B上show master status；的值start slave；                    //开启同步unlock tables；                //解锁写

Test the primary master

In a test database established T1 table, B on the query after the T1 table, proof B can synchronize a modified data;
In the test database B to establish the T2 table, a on the query, there is a T2 table, proof A can synchronize to B modified data;

Mastera settings

mysql -uroot -p123456                //登录mysqlmysql> use test；                //切换到数据库test

mysql> show tables;            //查看当前数据库的所有表，这里没有一个表Empty set (0.00 sec)

mysql> create table t1(`id` int(4),`name` char(40));            //插入一个表t1mysql> show tables;       //查看当前数据库的表+----------------+| Tables_in_test |+----------------+| t1             |+----------------+1 row in set (0.00 sec)

Masterb

mysql -uroot -p123456                //登录mysqluse test；                //切换到数据库testmysql> show tables;            //查询同步到数据库test下出现了t1表，证明B能同步A的数据；+----------------+| Tables_in_test |+----------------+| t1             |+----------------+1 row in set (0.00 sec)

create table t2(`id` int(4),`name` char(40));            //新建一个t2表，如果A能同步到，就证明A能同步到B

Switch Mastera (omit the command to log in and switch the database)

mysql> show tables;    //查询A的test数据库下所有的表+----------------+| Tables_in_test |+----------------+| t1             || t2             |+----------------+2 rows in set (0.00 sec)

MySQL Master master (dual master) data synchronization