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)