8.0.23基于gtid的多源复制
8.0.23基于gtid的多源复制
#################################### 8.0.23 新语法 mysql> show warnings; +---------+------+-------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------------------------------------------------------------------+ | Warning | 1287 | 'RESET SLAVE' is deprecated and will be removed in a future release. Please use RESET REPLICA instead | +---------+------+-------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> reset replica; Query OK, 0 rows affected (0.00 sec) mysql> reset master; Query OK, 0 rows affected (0.01 sec) reset master; reset replica all; CHANGE REPLICATION SOURCE TO SOURCE_HOST='10.100.19.214', SOURCE_USER='mysqlsync', SOURCE_PASSWORD='mysqlsync123', SOURCE_PORT=3306, MASTER_AUTO_POSITION = 1; start replica; show slave status \G; mysql> stop replica; Query OK, 0 rows affected (0.02 sec) mysql> start replica; Query OK, 0 rows affected (0.04 sec) mysql> show replica status\G;
一、环境介绍
| 主机名 | IP | 角色 |
| dbm1 | 10.100.19.214 | M1 |
| dbm2 | 10.100.19.215 | M2 |
| dbs | 10.100.19.216 | S |
二、主库分别导入测试数据
# 主库模拟数据 10.100.19.214 create database testdb214; use testdb214; CREATE TABLE `info_area` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '地区ID', `name` varchar(20) NOT NULL DEFAULT '' COMMENT '名称', `rel_id` varchar(50) NOT NULL DEFAULT '' COMMENT '关系ID', `pid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '父ID', `level` int(11) NOT NULL DEFAULT '0' COMMENT '类别，1、省份 2、市 3、区 4、县', PRIMARY KEY (`id`), UNIQUE KEY `uq_rel_id` (`rel_id`), KEY `idx_pid` (`rel_id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='地区表'; INSERT INTO `info_area` (`id`, `name`, `rel_id`, `pid`, `level`) VALUES ('10000', '北京', '10000', '10000', '1'); INSERT INTO `info_area` (`id`, `name`, `rel_id`, `pid`, `level`) VALUES ('10001', '三环以内', '100001000110001', '10001', '3'); INSERT INTO `info_area` (`id`, `name`, `rel_id`, `pid`, `level`) VALUES ('10002', '三环到四环之间', '100001000110002', '10002', '2'); INSERT INTO `info_area` (`id`, `name`, `rel_id`, `pid`, `level`) VALUES ('10003', '三环到四环之间', '100001000110003', '10003', '3'); INSERT INTO `info_area` (`id`, `name`, `rel_id`, `pid`, `level`) VALUES ('10004', '三环到四环之间', '100001000110004', '10004', '4');
# 主库模拟数据 10.100.19.215 create database testdb215; use testdb215; CREATE TABLE `info_area215` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '地区ID', `name` varchar(20) NOT NULL DEFAULT '' COMMENT '名称', `rel_id` varchar(50) NOT NULL DEFAULT '' COMMENT '关系ID', `pid` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '父ID', `level` int(11) NOT NULL DEFAULT '0' COMMENT '类别，1、省份 2、市 3、区 4、县', PRIMARY KEY (`id`), UNIQUE KEY `uq_rel_id` (`rel_id`), KEY `idx_pid` (`rel_id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COMMENT='地区表'; INSERT INTO `info_area215` (`id`, `name`, `rel_id`, `pid`, `level`) VALUES ('10000', '北京', '10000', '10000', '1'); INSERT INTO `info_area215` (`id`, `name`, `rel_id`, `pid`, `level`) VALUES ('10001', '三环以内', '100001000110001', '10001', '3'); INSERT INTO `info_area215` (`id`, `name`, `rel_id`, `pid`, `level`) VALUES ('10002', '三环到四环之间', '100001000110002', '10002', '2'); INSERT INTO `info_area215` (`id`, `name`, `rel_id`, `pid`, `level`) VALUES ('10003', '三环到四环之间', '100001000110003', '10003', '3'); INSERT INTO `info_area215` (`id`, `name`, `rel_id`, `pid`, `level`) VALUES ('10004', '三环到四环之间', '100001000110004', '10004', '4');
# 备份主库10.100.19.214： mysqldump --defaults-extra-file=/root/.mysql.root.cnf --single-transaction --master-data=2 --set-gtid-purged=on --triggers --routines --events --databases testdb214 > /root/testdb214.sql head -30 testdb214.sql |tail -- GTID state at the beginning of the backup -- SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '7b68143a-645e-11eb-b60f-fa163ea9cdf0:1-32'; -- -- Position to start replication or point-in-time recovery from -- -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-binlog.000002', MASTER_LOG_POS=11809; # 传输至从库： scp testdb214.sql m@10.100.19.216:/home/m/
# 导入数据库 mysql.root </tmp/testdb214.sql
# 开启同步服务 reset master; SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '7b68143a-645e-11eb-b60f-fa163ea9cdf0:1-32'; CHANGE MASTER TO MASTER_HOST='10.100.19.214', MASTER_USER='repluser', MASTER_PASSWORD='i9794Flhv0kk', MASTER_PORT=3306, MASTER_AUTO_POSITION = 1 FOR CHANNEL 'testdb214'; start slave FOR CHANNEL 'testdb214'; show slave status FOR CHANNEL 'testdb214'\G;
# 备份主库10.100.19.215： mysqldump --defaults-extra-file=/root/.mysql.root.cnf --single-transaction --master-data=2 --set-gtid-purged=on --triggers --routines --events --databases testdb215 > /root/testdb215.sql head -30 testdb215.sql |tail -- GTID state at the beginning of the backup -- SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '8d94b44b-645e-11eb-b232-fa163ee8385c:1-26'; -- -- Position to start replication or point-in-time recovery from -- -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-binlog.000002', MASTER_LOG_POS=8928; # 传输至从库： scp testdb215.sql m@10.100.19.216:/home/m/
# 导入数据库 mysql.root </tmp/testdb215.sql reset master; SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '8d94b44b-645e-11eb-b232-fa163ee8385c:1-26'; CHANGE MASTER TO MASTER_HOST='10.100.19.215', MASTER_USER='repluser', MASTER_PASSWORD='i9794Flhv0kk', MASTER_PORT=3306, MASTER_AUTO_POSITION = 1 FOR CHANNEL 'testdb215'; start slave FOR CHANNEL 'testdb215'; show slave status FOR CHANNEL 'testdb215'\G;
部署完毕。
mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.100.19.214 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-binlog.000002 Read_Master_Log_Pos: 13557 Relay_Log_File: relay-log-testdb214.000002 Relay_Log_Pos: 2175 Relay_Master_Log_File: mysql-binlog.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: test.% Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 13557 Relay_Log_Space: 2388 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 10402 Master_UUID: 7b68143a-645e-11eb-b60f-fa163ea9cdf0 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 7b68143a-645e-11eb-b60f-fa163ea9cdf0:33-37 Executed_Gtid_Set: 7b68143a-645e-11eb-b60f-fa163ea9cdf0:35-37, 8d94b44b-645e-11eb-b232-fa163ee8385c:1-26 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: testdb214 Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: *************************** 2. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.100.19.215 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-binlog.000002 Read_Master_Log_Pos: 8928 Relay_Log_File: relay-log-testdb215.000002 Relay_Log_Pos: 427 Relay_Master_Log_File: mysql-binlog.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: test.% Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 8928 Relay_Log_Space: 640 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 13502 Master_UUID: 8d94b44b-645e-11eb-b232-fa163ee8385c Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: 7b68143a-645e-11eb-b60f-fa163ea9cdf0:35-37, 8d94b44b-645e-11eb-b232-fa163ee8385c:1-26 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: testdb215 Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 Network_Namespace: 2 rows in set, 1 warning (0.01 sec) ERROR: No query specified mysql>
# 模拟数据变化 create table t2 like info_area; insert into t2 select * from info_area; select count(*) from t2; create table t3 like info_area215; insert into t3 select * from info_area215; select count(*) from t3;
################################
# 这里在主库中执行了 reset master操作，导致从库同步异常（如果不是人为模拟的故障，强烈建议重新导一份数据，部署同步，
一般是服务重启，或者执行了reset master操作，即使跳过，重新修复好同步服务，但无法确保是否数据一致性）
*************************** 1. row *************************** Slave_IO_State: Master_Host: 10.100.19.214 Master_User: repluser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-binlog.000002 Read_Master_Log_Pos: 13557 Relay_Log_File: relay-log-testdb214.000002 Relay_Log_Pos: 2175 Relay_Master_Log_File: mysql-binlog.000002 Slave_IO_Running: No Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: test.% Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 13557 Relay_Log_Space: 2388 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 13114 Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'could not find next log; the first event '' at 4, the last event read from '/data/mysql/mysql_3306/logs/mysql-binlog.000002' at 13557, the last byte read from '/data/mysql/mysql_3306/logs/mysql-binlog.000002' at 13557.' Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 10402 Master_UUID: 7b68143a-645e-11eb-b60f-fa163ea9cdf0 Master_Info_File: mysql.slave_master_info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: 210202 14:01:21 Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: 7b68143a-645e-11eb-b60f-fa163ea9cdf0:33-37 Executed_Gtid_Set: 7b68143a-645e-11eb-b60f-fa163ea9cdf0:35-37, 8d94b44b-645e-11eb-b232-fa163ee8385c:1-26 Auto_Position: 1 Replicate_Rewrite_DB: Channel_Name: testdb214 Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0 Network_Namespace:
######## 最好是重新搭建，避免数据不一致了，也无法排查。 # 主库备份文件 # mysqldump --defaults-extra-file=/root/.mysql.root.cnf --single-transaction --master-data=2 --set-gtid-purged=on --triggers --routines --events --databases testdb214 > /root/testdb214.sql # 从库清理环境 mysql.root <testdb214.sql # head -30 /tmp/testdb214.sql |tail -- GTID state at the beginning of the backup -- SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ ''; -- -- Position to start replication or point-in-time recovery from -- -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-binlog.000001', MASTER_LOG_POS=156; stop slave for channel 'testdb214'; reset master; # 非常重要 reset slave all for channel 'testdb214'; # 非常重要 CHANGE MASTER TO MASTER_HOST='10.100.19.214', MASTER_USER='repluser', MASTER_PASSWORD='i9794Flhv0kk', MASTER_PORT=3306, MASTER_AUTO_POSITION = 1 FOR CHANNEL 'testdb214'; start slave FOR CHANNEL 'testdb214'; show slave status FOR CHANNEL 'testdb214'\G;
此外，还可以在从库中添加如下配置
# 如果需要过滤，可添加如下参数 replicate-do-db = testdb214 replicate-do-db = testdb215 replicate_wild_do_table=testdb214.% replicate_wild_do_table=testdb215.%
