MySQL5.7多源复制实践

STOP SLAVE;
SET GLOBAL master_info_repository = ‘TABLE‘;
SET GLOBAL relay_log_info_repository = ‘TABLE‘;

在从上添加对基于GTID的主库的通道

CHANGE MASTER TO MASTER_HOST=‘master1‘, MASTER_USER=‘rpl‘, MASTER_PORT=3451, MASTER_PASSWORD=‘‘, MASTER_AUTO_POSITION = 1 FOR CHANNEL ‘master-1‘;

开始复制

#默认开启所有的复制通道
START SLAVE thread_types;
#指定复制通道开启
START SLAVE thread_types FOR CHANNEL channel;

停止复制

#停止所有的复制通道
STOP SLAVE thread_types;
#指定复制通道停止
STOP SLAVE thread_types FOR CHANNEL channel;

重置多源复制的从库

通过reset slave语句可以重置多源复制的从库，默认是重置所有的复制通道，也可以指定通道进行重置

RESET SLAVE;
RESET SLAVE FOR CHANNEL channel;

多源复制监控

通过联合查询performance schema中的复制表进行监控复制线程的状态

select conn.CHANNEL_NAME,conn.SERVICE_STATE io_status,applier.SERVICE_STATE sql_status from performance_schema.replication_applier_status applier  join performance_schema.replication_connection_status conn on applier.CHANNEL_NAME=conn.CHANNEL_NAME;
+--------------+-----------+------------+
| CHANNEL_NAME | io_status | sql_status |
+--------------+-----------+------------+
| master_3300  | ON        | ON         |
| master_3301  | ON        | ON         |
+--------------+-----------+------------+

MySQL5.7多源复制实践

标签：库存   service   语句   osi   ble   div   ast   pes   冲突