mysql多源复制

43212gg

GTID复制环境搭建
基本环境
                        master1                                master2                 slave
mysql版本        mysql-5.7.14x86_64  mysql-5.7.14x86_64    mysql-5.7.14x86_64
ip                        192.168.0.100                192.168.0.100         192.168.0.101
port                3306                                3307                  3306

搭建注意事项
master1和master2上面不要有重复的dbname,不然多源复制会报错


主库配置
#master1
gtid-mode=on
enforce-gtid-consistency=1
binlog_format=row
server-id=3306100
log-bin=/data/mysql/mysql3306/log/mysql-bin
binlog_cache_size=4M
skip_slave_start=1

#master2
gtid-mode=on
enforce-gtid-consistency=1
binlog_format=row
server-id=3307100
log-bin=/data/mysql/mysql3307/log/mysql-bin
binlog_cache_size=4M
skip_slave_start=1


主库创建复制账号
create user 'repl'@'192.168.0.%' identified by 'repl4slave';
grant replication slave on *.* to 'repl'@'192.168.0.%';
如果从库上没有忽略mysql库的同步，请使用 stop slave sql_thread;change replication filter Replicate_Ignore_DB=(mysql);


从库配置
#slave
gtid-mode=on
enforce-gtid-consistency=1
binlog_format=row
server-id=3306101
log-bin=/data/mysql/mysql3306/log/mysql-bin
binlog_cache_size=4M
skip_slave_start=1

master-info-repository=TABLE                  #多源复制必须
relay-log-info-repository=TABLE                #多源复制必须

查看复制：show slave status\G;
开始复制：start slave
停止复制: stop salve

mysql全库备份 如果有数据，可以备份
mysqldump -uroot -p  --single-transaction --master-data=2 -A > liangxiaoming_20161224.sql

参数查看master
show global variables like "%server%"
show global variables like "%gtid%"
show global variables like "%log_bin%"
show master status;


从库恢复数据
mysql -u root -p <liangxiaoming_20161224.sql

如果恢复不了
show global variables like "%gtid%"
reset master ;
show global variables like "%gtid%"

#GTID多源复制
change master to master_host='192.168.0.100',master_port=3306,master_user='repl',master_password='repl4slave',master_auto_position=1 for channel 'master-3306100';
change master to master_host='192.168.0.100',master_port=3307,master_user='repl',master_password='repl4slave',master_auto_position=1 for channel 'master-3307100';

show slave status for channel 'master-3307100'\G;
show slave status for channel 'master-3306100'\G;
start slave for channel 'master-3306100';
start slave for channel 'master-3307100';


#binlog+position多源复制
change master to master_host='192.168.0.100',master_port=3306,master_user='repl',master_password='repl4slave',master_log_file='mysql-bin.000001',master_log_pos=154 for channel 'master-3306100';
change master to master_host='192.168.0.100',master_port=3307,master_user='repl',master_password='repl4slave',master_log_file='mysql-bin.000001',master_log_pos=154 for channel 'master-3307100';



#配置从服务器Slave：#########################################################################################################################################
#   mysql>change master to master_host='192.168.0.100',master_port=3306,master_user='repl',master_password='repl4slave',master_auto_position=1;                         #
#   Mysql>start slave;    //启动从服务器复制功能                                                                                                            #
#############################################################################################################################################################
其他问题：
1、单个复制出错处理
(1)binlog+position
stop slave sql_thread;
set global sql_slave_skip_counter=1;
start slave sql_thread;

(2)GTID
stop slave sql_thread;
set gtid_next='uuid:N'; ####注意
begin;commit;                        #####注意
set gtid_next='automatic';
start slave sql_thread;


2、多个复制出错处理
(1)binlog+position
stop slave sql_thread for channel 'master-3306100';
set global sql_slave_skip_counter=1;
start slave sql_thread for channel 'master-3306100';

(2)GTID
stop slave sql_thread for channel 'master-3306100';
set gtid_next='uuid:N'; ####注意
begin;commit;                        #####注意
set gtid_next='automatic';
start slave sql_thread for channel 'master-3306100';

3、重复账号重复问题
set sql_log_bin=0;
create user 'wubx'@'%' identified by 'wubxwubx';
grant all privileges on *.* to 'wubx'@'%';
set sql_log_bin=1;
stop slave sql_thread;change replication filter Replicate_Ignore_DB=(mysql)


对一个运行好久的库做备份恢复建同步
不能使用xtrabackup

mysqldump
mysqldump -uroot -p  --single-transaction --master-data=2 -S /tmp/mysql3306.sock wubx3306  > wubx3306.sql
mysqldump -uroot -p  --single-transaction --master-data=2 -S /tmp/mysql3307.sock wubx3307  > wubx3307.sql



mysql -S /tmp/mysql3306.sock -p <wubx3306.sql
mysql -S /tmp/mysql3306.sock -p <wubx3307.sql

#########################################################################################################################################################


create table bb(
uid int not null auto_increment,
name varchar(32),
add_time datetime,
primary key(uid)
)



create table t2(
uid int not null auto_increment,
name varchar(32),
primary key(uid)
)

error**
           Retrieved_Gtid_Set: 6e5d664c-c7e5-11e6-88b1-000c296ae445:1-15
            Executed_Gtid_Set: 6e5d664c-c7e5-11e6-88b1-000c296ae445:1-14,
9e007a75-c7e8-11e6-b388-000c292c614e:1-3

stop slave sql_thread ;
set gtid_next='6e5d664c-c7e5-11e6-88b1-000c296ae445:15';
begin;commit;               
set gtid_next='automatic';
start slave sql_thread;