mysql多源复制

来源：互联网发布：淘宝药店提交需求编辑：程序博客网时间：2024/06/04 08:51

多源复制就是多个master复制，允许一个slave对应多个master；

change master语法（参考：http://dev.mysql.com/doc/refman/5.7/en/change-master-to.html）：

CHANGE MASTER TO option [, option] ... [ channel_option ] option:    MASTER_BIND = 'interface_name'  | MASTER_HOST = 'host_name'  | MASTER_USER = 'user_name'  | MASTER_PASSWORD = 'password'  | MASTER_PORT = port_num  | MASTER_CONNECT_RETRY = interval  | MASTER_RETRY_COUNT = count  | MASTER_DELAY = interval  | MASTER_HEARTBEAT_PERIOD = interval  | MASTER_LOG_FILE = 'master_log_name'  | MASTER_LOG_POS = master_log_pos  | MASTER_AUTO_POSITION = {0|1}  | RELAY_LOG_FILE = 'relay_log_name'  | RELAY_LOG_POS = relay_log_pos  | MASTER_SSL = {0|1}  | MASTER_SSL_CA = 'ca_file_name'  | MASTER_SSL_CAPATH = 'ca_directory_name'  | MASTER_SSL_CERT = 'cert_file_name'  | MASTER_SSL_CRL = 'crl_file_name'  | MASTER_SSL_CRLPATH = 'crl_directory_name'  | MASTER_SSL_KEY = 'key_file_name'  | MASTER_SSL_CIPHER = 'cipher_list'  | MASTER_SSL_VERIFY_SERVER_CERT = {0|1}  | MASTER_TLS_VERSION = 'protocol_list'  | IGNORE_SERVER_IDS = (server_id_list)channel_option:     FOR CHANNEL channelserver_id_list:    [server_id [, server_id] ... ]

一、环境配置

master 192.168.0.109

master 192.168.0.112

slave 192.168.0.110

确保都开启gtid_mode，基于事务的复制。配置如下：

[mysqld]

gtid-mode=on

enforce-gtid-consistency=true

避免relay.info更新不及时，SLAVE 重启后导致的主从复制出错，，在从服务器上实现事故安全功能，增加配置：

master_info_repository=TABLE

relay_log_info_repository=TABLE

对于多源复制是必须的不然会出现错误：

ERROR 3077 (HY000): To have multiple channels, repository cannot be of type FILE; Please check the repository configuration and convert them to TABLE.

查看：show variables like 'gtid_mode';

二、配置步骤

1.在master创建复制用户。

mysql>create user 'repltest'@'192.168.0.%' identified by 'Repltest123#@!';

mysql>grant replication slave on *.* to 'repltest'@'192.168.0.%';

2.在slave配置change master

change master to master_host='192.168.0.109',

master_user='repltest',

master_password='Repltest123#@!',

master_auto_position=1 FOR CHANNEL 'channe1';

启动复制 start slave FOR CHANNEL 'channe1';

change master to master_host='192.168.0.112',

master_user='repltest',

master_password='Repltest123#@!',

MASTER_AUTO_POSITION=1 FOR CHANNEL 'channe2';

启动复制 start slave FOR CHANNEL 'channe2';

可以通过show slave status \G查看复制状态。如果同步相同库，同一张表，需要处理，库和表创建冲突以及，表主键冲突情况。

3.可以查看performance_schema下的复制表变化

show tables like 'replication%';

另外可以通过如下全局参数设置多线程复制，提高复制速度：

在slave服务器上停止所有链路的复制

stop slave [for channel 'channel']

set global slave_parallel_type='logical_clock' 并发类型，默认DATABASE

set global slave_parallel_workers=4 并发工作线程数默认为0

start slave [for channel 'channel']

重新启动复制链接。

通过查看线程数：

show processlist;

查询系统视图的变化：

replication_applier_status_by_coordinator 协调复制线程

replication_applier_status_by_worker 复制线程的工作状态

参考文章：

1.MySQL 5.7 的多源复制

2.MySQL 5.7的多源复制

3.MySQL5.6中设置relay_log_info_repository 为TABLE

4.http://dev.mysql.com/doc/refman/5.7/en/slave-logs.html

5.http://dev.mysql.com/doc/refman/5.7/en/change-master-to.html

0 0