MySQL5.7主主双向复制

创建同步用户


在主服务器上为从服务器建立一个连接帐户，该帐户必须授予REPLICAITON SLAVE权限

这里2个服务器互为主从，所以都要分别建立一个同步用户
grant replication slave,super,replication client on *.* to 'repl'@'%' identified by 'repl';



主库1：
[mysqld]  
server-id = 91  
log-bin=binlog 
log_slave_updates=1 
sync_binlog=1
auto_increment_offset=1
auto_increment_increment=2

主库2：
[mysqld]  
server-id = 92
log-bin = binlog
log_slave_updates=1
sync_binlog=1
auto_increment_offset=2
auto_increment_increment=2




mysqldump -uroot -p --single-transaction --master-data=2 --flush-logs --flush-privileges --routines --events --triggers -A > db_fullbackup.sql
scp db_fullbackup.sql 192.168.56.92:/home/mysql


重新初始化B库

[root@localhost local]# rm -rf /mysql/data/*
[root@localhost local]# rm -rf /mysql/log/*
[root@localhost local]# mysqld --defaults-file=/etc/my.cnf  --initialize
[root@localhost local]# cat /mysql/log/error.log
[root@localhost local]# mysql -uroot -p
alter user user() identified by '123';



reset master;
cat db_fullbackup.sql |grep -i 'SET @@GLOBAL.GTID_PURGED'
mysql -uroot -p < db_fullbackup.sql
   
reset slave all;


指定同步位置并启动slave 线程
--主库A：
change master to master_host='192.168.56.92', master_user='repl', master_password='repl', master_log_file='mysql-bin.000001', master_log_pos=154;
start slave;

--主库B：
change master to master_host='192.168.56.91', master_user='repl', master_password='repl', master_log_file='mysql-bin.000001', master_log_pos=154;
start slave;


测试主主同步

--A
create database cndba;
use cndba;
create table dave(id int,name varchar(100));
insert into dave value(1,'www.cndba.cn');

--B
show databases;
use cndba;
show tables;
select * from dave;
insert into dave value(2,'www.cndba.cn');
select * from dave;