mysql多源复制配置及注意事项

MySQL多源复制使副本能够并行接收来自多个数据源的事务。在多源复制拓扑中，副本应该为每个数据源创建一个复制通道。在多源复制拓扑中的一个从服务器上最多可以创建256个通道。每个复制通道必须具有唯一的（非空）名称；

多源复制可以满足的场景：

（1）将多个服务器备份到单个服务器。

（2）合并表碎片。

（3）将数据从多个服务器整合到单个服务器。

mysql 多源复制配置：

1、配置多源复制之前必须满足如下条件，这2个参数可以动态调整。

mysql> STOP SLAVE;

mysql> SET GLOBAL master_info_repository = 'TABLE';

mysql> SET GLOBAL relay_log_info_repository = 'TABLE';

如果没有配置这2个参数为TABLE，则会报错。

root@(none) 11:11:44>SET GLOBAL master_info_repository = 'file';
Query OK, 0 rows affected (0.00 sec)

root@(none) 11:11:59>SET GLOBAL relay_log_info_repository = 'file';
Query OK, 0 rows affected (0.00 sec)

root@(none) 11:12:11>CHANGE MASTER TO MASTER_HOST="10.21.108.43", MASTER_USER="ted", 
MASTER_PASSWORD="password", 
MASTER_LOG_FILE='mysql_bin.000017', 
MASTER_LOG_POS=730 FOR CHANNEL "source_1";

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



root@(none) 11:12:29>SET GLOBAL relay_log_info_repository = 'table';

root@(none) 11:12:47>SET GLOBAL master_info_repository = 'table';
Query OK, 0 rows affected (0.00 sec)

root@(none) 11:12:52>CHANGE MASTER TO MASTER_HOST="10.21.108.43", MASTER_USER="ted", MASTER_PASSWORD="password", 
    -> MASTER_LOG_FILE='mysql_bin.000017', MASTER_LOG_POS=730 FOR CHANNEL "source_1";

2、配置多源复制

1）基于普通二进制配置多源复制：

mysql> CHANGE MASTER TO MASTER_HOST="source1", 
MASTER_USER="ted", MASTER_PASSWORD="password", 
MASTER_LOG_FILE='source1-bin.000006',
MASTER_LOG_POS=628 FOR CHANNEL "source_1";


mysql> CHANGE MASTER TO MASTER_HOST="source2", 
MASTER_USER="ted", 
MASTER_PASSWORD="password", 
MASTER_LOG_FILE='source2-bin.000018',
MASTER_LOG_POS=104 FOR CHANNEL "source_2";

2） 基于GTID 配置多源复制：

mysql> CHANGE MASTER TO MASTER_HOST="source1", 
MASTER_USER="ted", 
MASTER_PASSWORD="password",
MASTER_AUTO_POSITION=1 FOR CHANNEL "source_1";


mysql> CHANGE MASTER TO MASTER_HOST="source2",
MASTER_USER="ted", 
MASTER_PASSWORD="password", 
MASTER_AUTO_POSITION=1 FOR CHANNEL "source_2";

3.监控状态

1）使用show slave status

2)  查看performance_schema 库中的replication_connection_status 表

use performance_schema

mysql> SELECT * FROM replication_connection_status\G;
*************************** 1. row ***************************
CHANNEL_NAME: source_1
GROUP_NAME:
SOURCE_UUID: 046e41f8-a223-11e4-a975-0811960cc264
THREAD_ID: 24
SERVICE_STATE: ON
COUNT_RECEIVED_HEARTBEATS: 0
LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00
RECEIVED_TRANSACTION_SET: 046e41f8-a223-11e4-a975-0811960cc264:4-37
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00

*************************** 2. row ***************************
CHANNEL_NAME: source_2
GROUP_NAME:
SOURCE_UUID: 7475e474-a223-11e4-a978-0811960cc264
THREAD_ID: 26
SERVICE_STATE: ON
COUNT_RECEIVED_HEARTBEATS: 0
LAST_HEARTBEAT_TIMESTAMP: 0000-00-00 00:00:00
RECEIVED_TRANSACTION_SET: 7475e474-a223-11e4-a978-0811960cc264:4-6
LAST_ERROR_NUMBER: 0
LAST_ERROR_MESSAGE:
LAST_ERROR_TIMESTAMP: 0000-00-00 00:00:00
2 rows in set (0.00 sec)