共计 2937 个字符，预计需要花费 8 分钟才能阅读完成。
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
本文永久更新链接地址：http://www.linuxidc.com/Linux/2016-12/138169.htm
