MySQL 5.7的多源复制搭建

MySQL 5.7的多源复制搭建一.基本环境
主库1: 10.24.65.30 3306 MySQL 5.7.14
主库2: 10.24.65.30 3307 mysql 5.7.14
从库：10.24.65.33 3306 mysql 5.7.14
基于gtid复制二.搭建注意事项
1.每个实例上DB名字不能一样。
2.创建或是复制用户时名字不能一样，如果经常遇到一样的帐号若是为了规范创建的帐
号是一样的，考虑在slave的过滤规则把mysql复制忽略掉。
[stop slave sql_thread;change replication filter replicate_ignore_db=mysql;]3307启停：
mysqld_safe --defaults-file=/data/mysql/mysql3307/data/my3307.cnf &
mysqladmin shutdown -S /tmp/mysql3307.sock
mysql -S /tmp/mysql3307.sock三.主库配置
1.master1参数设置
gtid-mode=on
enforce-gtid-consistency=1
skip_slave_start=1
2.master2参数设置
gtid-mode=on
enforce-gtid-consistency=1
skip_slave_start=1
3.复制帐号建立
grant replication slave on *.* to repl@"10.24%" identified by "replsafe";
flush privileges;
4.slave参数设置
gtid-mode=on
enforce-gtid-consistency=1
skip_slave_start=1
master-info-repository=table
relay-log-info-repository=table
5.slave配置连接主库
change master to
master_host="10.24.65.30",
master_port=3306,
master_user="repl",
master_password="replsafe",
master_auto_position=1 for channel "master-65303306";change master to
master_host="10.24.65.30",
master_port=3307,
master_user="repl",
master_password="replsafe",
master_auto_position=1 for channel "master-65303307";四.常用命令：
1.开关复制
start slave io_thread|sql_thread for channel "master-65303306";
stop slave io_thread|sql_thread for channel "master-65303306"2.监控复制
show slave status for channel "master-65303306";五.校验
master1：
create database mdb1;
use mdb1;
create table t1（id int）;master2:
create database mdb2;
use mdb2;
create table t2（id int）;slave:
（product）root@localhost [（none）]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mdb1 |
| mdb2 |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set （0.00 sec）（product）root@localhost [（none）]> use mdb1;
Database changed
（product）root@localhost [mdb1]> show tables;
+----------------+
| Tables_in_mdb1 |
+----------------+
| t1 |
+----------------+
1 row in set （0.00 sec）（product）root@localhost [mdb1]> use mdb2;
Database changed
（product）root@localhost [mdb2]> show tables;
+----------------+
| Tables_in_mdb2 |
+----------------+
| t2 |
+----------------+
1 row in set （0.00 sec）六.相关问题
1.多源复制处理之一：
binlog+position:
stop slave sql_thread for channel "master-65303306";
set sql_slave_skip_counter=n;
start slave sql_thread for channel "master-65303306";gtid:
stop slave sql_thread for channel "master-65303306";
set gtid_next="uuid-N";
begin;commit;
set gtid_next="automatic";
start slave sql_thread for channel "master-65303306";
2.从多源复制处理之二：
Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: "A slave with the same server_uuid/server_id as this slave has connected to the master;
the first event "mysql-bin.000181" at 154, the last event read from "/data/mysql/mysql3306/logs/mysql-bin.000181" at 123, the last byte read from "/data/mysql/mysql3306/logs/mysql-bin.000181" at 154."解决方法：
a.由于两主库的server-uuid相同，更改其中一个auto.cnf中的server-uuid号
b.并在从库上执行reset slave all清除slave信息
c.重新change master本文永久更新链接地址