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
clause:channel
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
clause:channel
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
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步