多源复制

GTID模式
基于多源复制的高可用架构
约束：
1、基于row格式的Binlog
2、启用GTID
3、使用多源复制
4、同一时间只在一个节点写入

 

5.7.16 Multi Source Replication
192.168.1.201  master
192.168.1.202  master
192.168.1.203  slave

 

MySQL5.7.16
安装略

 

192.168.1.201相关配置
server-id = 1921681201
gtid_mode=ON
enforce-gtid-consistency=ON
binlog_format = ROW
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=1
log_slave_updates = 1
创建主库授权从库同步的用户
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.%' IDENTIFIED BY 'repl';
mysql> flush privileges;

 

192.168.1.202相关配置
server-id = 1921681202
gtid_mode=ON
enforce-gtid-consistency=ON
binlog_format = ROW
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=2
log_slave_updates = 1
创建主库授权从库同步的用户
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.%' IDENTIFIED BY 'repl';
mysql> flush privileges;

 

192.168.1.203相关配置
server-id = 1921681203
gtid_mode=ON
enforce-gtid-consistency=ON
binlog_format = ROW
sync_binlog=1
log_slave_updates = 1
slave-parallel-type=LOGICAL_CLOCK
relay_log_recovery=ON
slave_parallel_workers = 4
#do not replicate db
replicate-ignore-db = mysql
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
replicate-ignore-db = sys
#MySQL5.7多源复制必须添加的参数
master_info_repository = table
relay_log_info_repository = table
创建复制用户
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.%' IDENTIFIED BY 'repl';
mysql> flush privileges;
#GTID模式
mysql> change master to master_host='192.168.1.201',master_port=3306,master_user='repl',master_password='repl',master_auto_position=1 for channel 'master-1';
mysql> change master to master_host='192.168.1.202',master_port=3306,master_user='repl',master_password='repl',master_auto_position=1 for channel 'master-2';
mysql> start slave for channel 'master-1';
mysql> start slave for channel 'master-2';
mysql> show slave status for channel 'master-1'\G
mysql> show slave status for channel 'master-2'\G
mysql> show slave status;
mysql> select * from performance_schema.replication_connection_configuration;
mysql> select * from performance_schema.replication_connection_status;
mysql> show processlist;
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time | State                                                  | Info             |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
|  2 | root        | localhost | NULL | Query   |    0 | starting                                               | show processlist |
|  3 | system user |           | NULL | Connect |  134 | Waiting for master to send event                       | NULL             |
|  4 | system user |           | NULL | Connect |  134 | Slave has read all relay log; waiting for more updates | NULL             |
|  5 | system user |           | NULL | Connect | 1295 | Waiting for an event from Coordinator                  | NULL             |
|  6 | system user |           | NULL | Connect |  134 | Waiting for an event from Coordinator                  | NULL             |
|  7 | system user |           | NULL | Connect |  134 | Waiting for an event from Coordinator                  | NULL             |
|  8 | system user |           | NULL | Connect |  134 | Waiting for an event from Coordinator                  | NULL             |
|  9 | system user |           | NULL | Connect |  125 | Waiting for master to send event                       | NULL             |
| 10 | system user |           | NULL | Connect |  125 | Slave has read all relay log; waiting for more updates | NULL             |
| 11 | system user |           | NULL | Connect | 1280 | Waiting for an event from Coordinator                  | NULL             |
| 12 | system user |           | NULL | Connect |  125 | Waiting for an event from Coordinator                  | NULL             |
| 13 | system user |           | NULL | Connect |  125 | Waiting for an event from Coordinator                  | NULL             |
| 14 | system user |           | NULL | Connect |  125 | Waiting for an event from Coordinator                  | NULL             |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
13 rows in set (0.00 sec)

 

192.168.1.201上执行
create database master1;
use master1;
create table t(id int primary key auto_increment,name varchar(20));
insert into t(name) values('allen');
insert into t(name) values('bob');

 

192.168.1.202上执行
create database master2;
use master2;
create table t(id int primary key auto_increment,name varchar(20));
insert into t(name) values('click');
insert into t(name) values('david');

 

192.168.1.203上执行
show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| master1            |
| master2            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

 

使用mysqldump在从库导入第二个库的数据的时候，操作流程
reset master;
set global gtid_purged='第一个库的,第二个库的';
change master to ... for channel 1;
change master to ... for channel 2;
start slave;