MySQL5.7 多源复制
1.多源复制介绍
MySQL5.7 开始支持多源复制（Multi-Source Replication）也就是多主一从的复制架构，架构图如下：
2.为什么要使用多源复制
（1）灾备作用：将各个库汇总在一起，就算是其他库都挂了（整个机房都无法连接了），还有最后一个救命稻草；
（2）备份：直接在这个从库中做备份，不影响线上的数据库；
（3）减少成本：不需要每个库都做一个实例，也减少了 DBA 的维护成本；
（4）数据统计：后期的一些数据统计，需要将所有的库汇总在一起。
二、搭建 MySQL 多源复制
1.实验环境
多源复制是支持 GTID 和 Binlog + Position ，这篇文档以 GTID 复制来编写
搭建注意事项：
（1）每个实例上 DB 名字不能一样
（2）创建用户或是复制用户时名字不能一样，如果经常遇到一样的账号，或是为了规范创建的账号是一样的，考虑在 slave 的过滤规则把 mysql 复制忽略掉，
stop slave sql_thread; change replication filter Replicate_ignore_DB=(mysql)；
2.配置文件
master1:
gtid-mode = on
enforce-gtid-consistency=1
binlog_format = row
server-id = 1
log-bin = /data/mysql/mysql3306/logs/mysql-bin
skip_slave_start = 1
master2:
gtid-mode = on
enforce-gtid-consistency=1
binlog_format = row
server-id = 2
log-bin = /data/mysql/mysql3306/logs/mysql-bin
skip_slave_start = 1
slave:
gtid-mode = on
enforce-gtid-consistency=1
binlog_format = row
server-id =3
log-bin = /data/mysql/mysql3307/logs/mysql-bin
skip_slave_start = 1
# multi-source-replication # 使用多源复制，一些信息只能存放在 table 里面，不能存放到文件里面；
master-info-repository=TABLE
relay-log-info-repository=TABLE
replicate_ignore_db=mysql # 忽略 mysql 库同步
3.启动数据库并确认 server_uuid
启动数据库：
# /etc/init.d/mysql start
确认 server-uuid
master1:
root@localhost [unixfbi1]>show global variables like '%uuid%';
+---------------+--------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------+
| server_uuid | b5a3240c-8946-11e7-bf07-d067e528dfb8 |
+---------------+--------------------------------------+
1 row in set (0.00 sec)
master2:
root@localhost [unixfbi2]>show global variables like '%uuid%';
+---------------+--------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------+
| server_uuid | 3229c868-9d0f-11e7-9053-7845c401c236 |
+---------------+--------------------------------------+
1 row in set (0.01 sec)
slave:
root@localhost [(none)]>show global variables like '%uuid%';
+---------------+--------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------+
| server_uuid | b4300f44-86e3-11e7-868a-2c27d72e9d73 |
+---------------+--------------------------------------+
1 row in set (0.00 sec)
在复制环境或者多源复制环境中每个数据库的 server_uuid 不要相同，否则无法同步。
4.创建复制账号
master1:
mysql> grant replication slave,replication client on *.* to 'repl'@'192.168.199.%' identified by 'unixfbi';
mysql> flush privileges;
master2:
mysql> grant replication slave,replication client on *.* to 'repl'@'192.168.199.%' identified by 'unixfbi';
mysql> flush privileges;
5.从库启动复制
我们这里都是新环境搭建的复制，所以没有 mysqldump 备份原来的数据，如果是旧环境中配置多源复制，就需要使用 mysqldump 备份数据并导入到 slave 库中。
# mysqldump -uuser -p --single-transaction –master-data=2 --add-drop-database -B db1 > master1_db1.sql
GTID
mysql> change master to master_host='192.168.199.230',master_port=3306,master_user='repl',master_password='unixfbi',master_auto_position=1 for channel 'master-1';
mysql> change master to master_host='192.168.199.231',master_port=3306,master_user='repl',master_password='unixfbi',master_auto_position=1 for channel 'master-2';
mysql> start slave;
# 也可以 start slave for channel 'master-1'; 启动单个 channel 的复制。
Binlog + Position
如果是 Binlog + Postion 方式搭建的多源复制，请使用欧冠如下命令
mysql> change master to master_host='192.168.199.230',master_port=3306,master_user='repl',master_password='unixfbi',master_log_file='mysql-bin.000001' ,master_log_pos=154 for channel 'master-1';
mysql> change master to master_host='192.168.199.231',master_port=3306,master_user='repl',master_password='unixfbi',master_log_file='mysql-bin.000001' ,master_log_pos=154 for channel 'master-2';
mysql> start slave;
6.查看多源复制状态
查看所有多源 channel 复制状态
root@localhost [(none)]>show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.199.230
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 154
Relay_Log_File: relay-bin-master@002d1.000002
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 581
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: b5a3240c-8946-11e7-bf07-d067e528dfb8
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name: master-1
Master_TLS_Version:
*************************** 2. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.199.231
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000003
Read_Master_Log_Pos: 154
Relay_Log_File: relay-bin-master@002d2.000002
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running