2.mysql之多源复制

1.多源复制

  MySQL 多源复制使副本能够并行接收来自多个直接源的事务。 在多源复制拓扑中,副本为应该从其接收事务的每个源创建一个复制通道。

2.多源复制用途

  • 将多个服务器备份到单个服务器。
  • 合并表的分片
  • 将多台服务器的数据整合到一台服务器上。

3.注意

  Multi-source replication does not implement any conflict detection or resolution when applying transactions, and those tasks are left to the application if required.

  多源复制在应用事务时不检测任何冲突。

4.配置多源复制

  4.1 多源复制技术至少要求两个源,首先需要在源库上创建复制账号(每一个都要配置) 

 mysql> CREATE USER 'ted'@'replicahost' IDENTIFIED BY 'password'; 
mysql> GRANT REPLICATION SLAVE ON *.* TO 'ted'@'replicahost';

    4.2 创建dump文件对于目标db1在source1和source2 

  mysqldump -u<user> -p<password> --single-transaction --triggers --routines --set-gtid-purged=ON --databases db1 > dumpM1.sql

  then run mysqldump for source2 as follows: 

 mysqldump -u<user> -p<password> --single-transaction --triggers --routines --set-gtid-purged=ON --databases db2 > dumpM2.sql

     4.3 记录gtid_purged的值对于每一个dump file文件。5.6版本和5.7版本

cat dumpM1.sql | grep GTID_PURGED | cut -f2 -d'=' | cut -f2 -d$'\''
cat dumpM2.sql | grep GTID_PURGED | cut -f2 -d'=' | cut -f2 -d$'\''

  或者8.0版本

cat dumpM1.sql | grep GTID_PURGED | perl -p0 -e 's#/\*.*?\*/##sg' | cut -f2 -d'=' | cut -f2 -d$'\''
cat dumpM2.sql | grep GTID_PURGED | perl -p0 -e 's#/\*.*?\*/##sg' | cut -f2 -d'=' | cut -f2 -d$'\''

  The result in each case should be a GTID set, for example: 

source1:   2174B383-5441-11E8-B90A-C80AA9429562:1-1029
source2:   224DA167-0C0C-11E8-8442-00059A3C7B00:1-2695

  4.4 Remove the line from each dump file that contains the SET @@GLOBAL.gtid_purged statement. For example:

sed '/GTID_PURGED/d' dumpM1.sql > dumpM1_nopurge.sql
sed '/GTID_PURGED/d' dumpM2.sql > dumpM2_nopurge.sql

  4.5 Use the mysql client to import each edited dump file into the replica. For example: 

mysql -u<user> -p<password> < dumpM1_nopurge.sql
mysql -u<user> -p<password> < dumpM2_nopurge.sql

  4.6 在副本上,发出 RESET MASTER 以清除 GTID 执行历史记录(如上所述,假设所有转储文件都已导入并且副本上没有带有 GTID 的所需事务)。 然后发@@GLOBAL.gtid_purged 语句,将 gtid_purged 值设置为所有转储文件中所有 GTID 集的并集,如您在步骤 2 中记录的那样。例如: 

mysql> RESET MASTER;
mysql> SET @@GLOBAL.gtid_purged = "2174B383-5441-11E8-B90A-C80AA9429562:1-1029, 224DA167-0C0C-11E8-8442-00059A3C7B00:1-2695";

  说明:4.6步其实可以直接在4.2步骤执行之后直接操作,设置gtid_purged的原因是为了后面搭建主从复制时(Gtid)直接跳过已经purge过的事务,不再从来开始拉取binlog

5. Adding GTID-Based Sources to a Multi-Source Replica

    5.1 to add source1 and source2 as sources to the replica, use the mysql client to issue the statement twice on the replica, like this: 

复制代码
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";

Or from MySQL 8.0.23:
mysql> CHANGE REPLICATION SOURCE TO SOURCE_HOST="source1", SOURCE_USER="ted", \
SOURCE_PASSWORD="password", SOURCE_AUTO_POSITION=1 FOR CHANNEL "source_1";
mysql> CHANGE REPLICATION SOURCE TO SOURCE_HOST="source2", SOURCE_USER="ted", \
SOURCE_PASSWORD="password", SOURCE_AUTO_POSITION=1 FOR CHANNEL "source_2";
复制代码

  如果要使副本仅从 source1 复制数据库 db1,并且仅从 source2 复制数据库 db2,请使用 mysql 客户端为每个通道发出 CHANGE REPLICATION FILTER 语句,如下所示:

mysql> CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('db1.%') FOR CHANNEL "source_1";
mysql> CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ('db2.%') FOR CHANNEL "source_2";

6.adding Binary Log Based Replication Sources to a Multi-Source Replica

复制代码
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";

Or from MySQL 8.0.23:
mysql> CHANGE REPLICATION SOURCE TO SOURCE_HOST="source1", SOURCE_USER="ted", SOURCE_PASSWORD="password", \
SOURCE_LOG_FILE='source1-bin.000006', SOURCE_LOG_POS=628 FOR CHANNEL "source_1";
mysql> CHANGE REPLICATION SOURCE TO SOURCE_HOST="source2", SOURCE_USER="ted", SOURCE_PASSWORD="password", \
SOURCE_LOG_FILE='source2-bin.000018', SOURCE_LOG_POS=104 FOR CHANNEL "source_2";
复制代码

7.Starting Multi-Source Replicas  

mysql> START SLAVE FOR CHANNEL "source_1";
mysql> START SLAVE FOR CHANNEL "source_2";
Or from MySQL 8.0.22:
mysql> START REPLICA FOR CHANNEL "source_1";
mysql> START REPLICA FOR CHANNEL "source_2";

 验证:

mysql> SHOW SLAVE STATUS FOR CHANNEL "source_1"\G
mysql> SHOW SLAVE STATUS FOR CHANNEL "source_2"\G
Or from MySQL 8.0.22:
mysql> SHOW REPLICA STATUS FOR CHANNEL "source_1"\G
mysql> SHOW REPLICA STATUS FOR CHANNEL "source_2"\G

8.Stopping Multi-Source Replicas

  • To stop all currently configured replication channels: 
mysql> STOP SLAVE;
Or from MySQL 8.0.22:
mysql> STOP REPLICA;
  • To stop only a named channel, use a FOR CHANNEL channel clause:
mysql> STOP SLAVE FOR CHANNEL "source_1";
Or from MySQL 8.0.22:
mysql> STOP REPLICA FOR CHANNEL "source_1";

9.Resetting Multi-Source Replicas

  • To reset all currently configured replication channels:
mysql> RESET SLAVE;
Or from MySQL 8.0.22:
mysql> RESET REPLICA;
  • To reset only a named channel, use a FOR CHANNEL channel clause:
mysql> RESET SLAVE FOR CHANNEL "source_1";
Or from MySQL 8.0.22:
mysql> RESET REPLICA FOR CHANNEL "source_1";

10.Monitoring Multi-Source Replication

    To monitor the connection status of all channels:

 This section explains how to use the replication Performance Schema tables to monitor channels. You can choose to monitor all channels, or a subset of the existing channels.

复制代码
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)
复制代码
复制代码
mysql> SELECT * FROM replication_connection_status WHERE CHANNEL_NAME='source_1'\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
1 row in set (0.00 sec)
复制代码

 参考:https://dev.mysql.com/doc/refman/8.0/en/replication-multi-source.html      

posted on   太白金星有点烦  阅读(280)  评论(0编辑  收藏  举报

努力加载评论中...

导航

点击右上角即可分享
微信分享提示