MySQL 主从复制场景单表数据出错导致复制终止如何快速修复

2021-08-14 19:18:49

场景描述:

如果从库上表 t 数据与主库不一致，导致复制错误，整个库的数据量很大，重做从库很慢，如何单独恢复这张表的数据？
通常认为是不能修复单表数据的，因为涉及到各表状态不一致的问题。
下面就列举备份单表恢复到从库会面临的问题以及解决办法

一、本次演示环境描述:

Dell物理服务器r620 两台
网络环境都是内网
master:192.168.1.220
slave:192.168.1.217
OS系统环境：centos7.8 X86_64位最小化安装，关闭iptables，关闭selinux
测试软件版本:mysql5.7.27二进制包
提前配置好基于Gtid的MySQL主从复制
创建模拟测试数据，模拟故障场景
修复MySQL主从复制
pt-table-checksum 校验修复后的MySQL主从复制数据是否一致

二、配置主从复制

MySQL的安装过程此处不再描述，自行百度

配置主从复制
给一个master机器配置一个新的slave的话，记得在mysqldump备份数据时加参数--set-gtid-purged=ON
知识补充：
1.常规备份是要加--set-gtid-purged=OFF解决备份时的警告
[root@localhost ~]# mysqldump -uroot -p‘dXdjVF#(y3lt‘ --set-gtid-purged=OFF --single-transaction -A -B |gzip > 2020-09-17.sql.gz
2.构建主从时做的备份，不需要加--set-gtid-purged=OFF 这个参数,而是要加--set-gtid-purged=ON
[root@localhost ~]# mysqldump -uroot -p‘dXdjVF#(y3lt‘ --set-gtid-purged=ON --single-transaction -A -B --master-data=2 |gzip > 2020-09-17.sql.gz
提示:
在构建主从复制是，千万不要OFF。在日常备份时，可以OFF。
--set-gtid-purged=AUTO,ON,OFF
1.--set-gtid-purged=OFF可以使用在日常备份参数中。
2.--set-gtid-purged=ON在构建主从复制环境时需要的参数配置

基于Gtid配置主从复制具体步骤如下:

master库：

 grant replication slave on *.* to rep@‘192.168.1.217‘ identified by ‘JuwoSdk21TbUser‘; flush privileges;
 mysqldump -uroot -p‘dXdjVF#(y3lt‘ --set-gtid-purged=ON --single-transaction -A -B --master-data=2 |gzip > 2020-09-20.sql.gz

slave库操作:

 [root@mysql02 ~]# mysql < 2020-09-17.sql 
 mysql>  change master to master_host=‘192.168.1.220‘,master_user=‘rep‘,master_password=‘JuwoSdk21TbUser‘,MASTER_AUTO_POSITION = 1;start slave;show slave status\G
ERROR 29 (HY000): File ‘/data1/mysql/3306/relaylog/relay-bin.index‘ not found (Errcode: 2 - No such file or directory)
ERROR 29 (HY000): File ‘/data1/mysql/3306/relaylog/relay-bin.index‘ not found (Errcode: 2 - No such file or directory)
Empty set (0.00 sec)

原因是slave机器配置my.cnf中配置了relay-log的存放路径，但是slave服务器实际不存在这个路径，导致的报错，把这目录新建出来，授权mysql权限，然后重新change master

mkdir -p /data1/mysql/3306/relaylog/
cd /data1/mysql/3306/
chown -R mysql.mysql relaylog
mysql> change master to master_host=‘192.168.1.220‘,master_user=‘rep‘,master_password=‘JuwoSdk21TbUser‘,MASTER_AUTO_POSITION = 1;start slave;show slave status\G

主从复制配置完成。

三、准备测试数据并模拟故障

在master库上创建模拟演示表，已经定时器和存储过程，定时写入数据到测试表，方便下面主从复制故障恢复演示
创建测试表：

 CREATE TABLE `test_event` (
`id` int(8) NOT NULL AUTO_INCREMENT, 
`username` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
`password` varchar(20) COLLATE utf8_unicode_ci NOT NULL, 
`create_time` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`) #主键ID
) ENGINE=innodb AUTO_INCREMENT=0 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

创建定时器，从当前时间1分钟后每一秒写入一条数据：

delimiter $$
create event event_2 
on schedule every 1 second STARTS   CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
COMMENT ‘xiaowu create‘
do 
    BEGIN
           insert into test_event(username,password,create_time) values("李四","tomcat",now());
    END $$
delimiter ;

类似上面的方式再新建一个测试表txt，定时写入数据

slave库模拟故障：


 insert into test_event(username,password,create_time) values("李四","tomcat",now());
 insert into test_event(username,password,create_time) values("李四","tomcat",now());
 delete from txt where id=200;

然后在master库上再删除id=200的记录
master端操作： delete from txt where id=200;

此时slave库查看复制状态已经停止复制:

[root@mysql02 ~]#  mysql -e "show slave status\G"|grep -A 1 ‘Last_SQL_Errno‘
               Last_SQL_Errno: 1062
               Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction ‘8a9fb9a3-f579-11ea-830d-90b11c12779c:42083‘ at master log mysql-bin.000001, end_log_pos 18053730. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

四、故障恢复

场景 1

如果复制报错后，没有使用跳过错误、复制过滤等方法修复主从复制。主库数据一直在更新，从库数据停滞在报错状态（假设GTID 为8a9fb9a3-f579-11ea-830d-90b11c12779c:1-42083）。
修复步骤：
在主库上备份表test_event （假设备份快照 GTID 为 8a9fb9a3-f579-11ea-830d-90b11c12779c:1-42262)；
恢复到从库；
启动复制。
这里的问题是复制起始位点是8a9fb9a3-f579-11ea-830d-90b11c12779c:42084，从库上表test_event 的数据状态是领先其他表的。
8a9fb9a3-f579-11ea-830d-90b11c12779c:42084-42262 这些事务中只要有修改表test_event数据的事务，就会导致复制报错 ，比如主键冲突、记录不存在（而8a9fb9a3-f579-11ea-830d-90b11c12779c:1-42084这个之前复制报错的事务必定是修改表 t 的事务）
解决办法：启动复制时跳过8a9fb9a3-f579-11ea-830d-90b11c12779c:42084-42262 这些事务中修改表 t 的事务。

正确的修复步骤：

  1. 在主库上备份表test_event（备份快照 GTID 为 8a9fb9a3-f579-11ea-830d-90b11c12779c:1-42262），恢复到从库；
  2. 设置复制过滤，过滤表 t：
    CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE = (‘dbtest01.test_event‘);
  3. 启动复制，回放到8a9fb9a3-f579-11ea-830d-90b11c12779c:1-42262时停止复制（此时从库上所有表的数据都在同一状态，是一致的）;
    START SLAVE UNTIL SQL_AFTER_GTIDS = ‘8a9fb9a3-f579-11ea-830d-90b11c12779c:1-42262‘;
  4. 删除复制过滤，正常启动复制。
    注意事项：这里要用 mysqldump --single-transaction --master-data=2，记录备份快照对应的 GTID

具体的详细步骤如下:

A.要在master库上dump出导致复制停止的表test_event：

mysqldump -uroot -p‘dXdjVF#(y3lt‘  --single-transaction dbtest01 test_event --master-data=2 |gzip >$(date +%F).test_event.sql.gz
[root@localhost ~]# mysqldump -uroot -p‘dXdjVF#(y3lt‘  --single-transaction dbtest01 test_event --master-data=2 |gzip >$(date +%F).test_event.sql.gz
mysqldump: [Warning] Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don‘t want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.

B.获取出单独备份表的快照gtid值：

8a9fb9a3-f579-11ea-830d-90b11c12779c:1-42262

[root@mysql02 ~]# gzip -d 2020-09-17.test_event.sql.gz
[root@mysql02 ~]# grep -A6 ‘GLOBAL.GTID_PURGED‘ 2020-09-17.test_event.sql 
SET @@GLOBAL.GTID_PURGED=‘8a9fb9a3-f579-11ea-830d-90b11c12779c:1-42262‘;
--
-- Position to start replication or point-in-time recovery from
--
-- CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000001‘, MASTER_LOG_POS=18130552;

C.恢复此表到slave库上，由于GTID_EXECUTED不是空值，导致导入表test_event到slave库失败,具体报错如下:
slave库操作:

[root@mysql02 ~]#  mysql dbtest01 < 2020-09-17.test_event.sql 
ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

 mysql> select  @@GLOBAL.GTID_EXECUTED;
+----------------------------------------------------------------------------------------+
| @@GLOBAL.GTID_EXECUTED                                                                 |
+----------------------------------------------------------------------------------------+
| 5ec577a4-f401-11ea-bf6d-14187756553d:1-2,
8a9fb9a3-f579-11ea-830d-90b11c12779c:1-42082 |
+----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 368620
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 5ec577a4-f401-11ea-bf6d-14187756553d:1-2,
8a9fb9a3-f579-11ea-830d-90b11c12779c:1-42082
1 row in set (0.00 sec)

解决办法就是登陆slave库：
mysql> reset master;
这个操作可以将当前库的GTID_EXECUTED值置空

[root@mysql02 ~]#  mysql dbtest01 < 2020-09-17.test_event.sql 

mysql> show master status\G
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 154
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 8a9fb9a3-f579-11ea-830d-90b11c12779c:1-42262
1 row in set (0.00 sec)

D.在线开启复制过滤:

mysql> CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE = (‘db_name.test_event‘);
Query OK, 0 rows affected (0.00 sec)

[root@mysql02 ~]# mysql -e "show slave status\G"|egrep ‘db_name.test_event‘
  Replicate_Wild_Ignore_Table: db_name.test_event

E.启动复制，回放到8a9fb9a3-f579-11ea-830d-90b11c12779c:42262时停止复制（此时从库上所有表的数据都在同一状态，是一致的）

mysql> START SLAVE UNTIL SQL_AFTER_GTIDS =‘8a9fb9a3-f579-11ea-830d-90b11c12779c:42262‘;
Query OK, 0 rows affected, 1 warning (0.03 sec)

mysql>

虽然此时SQL线程是no，但是复制不再报错:

[root@mysql02 ~]# mysql -e "show slave status\G"|egrep ‘Last_SQL_Error|Slave_IO|Slave_SQL‘
               Slave_IO_State: Waiting for master to send event
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
               Last_SQL_Error:

F.在线关闭复制过滤:

mysql> CHANGE REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE = ();
Query OK, 0 rows affected (0.00 sec)

mysql> 
[root@mysql02 ~]# mysql -e "show slave status\G"|egrep ‘db_name.test_event|IO_Running|SQL_Running‘
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
      Slave_SQL_Running_State:

G.开启slave复制SQL线程：


mysql> start slave sql_thread;
Query OK, 0 rows affected (0.04 sec)

主从复制恢复：

[root@mysql02 ~]# mysql -e "show slave status\G"|egrep ‘IO_Running|SQL_Running‘
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates

注意事项：这里要用 mysqldump --single-transaction --master-data=2，记录备份快照对应的 GTID

五、校验主从数据一致性

采用校验工具pt-table-checksum来验证。具体如何安装使用参考下面的博文地址：
https://blog.51cto.com/wujianwei/2409523


[root@localhost bin]# time /usr/local/percona-toolkit/bin/pt-table-checksum h=192.168.1.220,u=ptsum,p=‘ptchecksums‘,P=3306 --ignore-databases sys,mysql  --truncate-replicate-table  --replicate=percona.ptchecksums --no-check-binlog-format --nocheck-replication-filters --recursion-method="processlist"   2>&1 | tee 2020-09-18-pt-checksum.log

Checking if all tables can be checksummed ...
Starting checksum ...
            TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE
09-18T07:49:09      0      0     9739          0       4       0   0.747 dbtest01.hlz_ad
09-18T07:49:10      0      0    64143          0       4       0   0.968 dbtest01.hlz_ad_step
09-18T07:49:16      0      0   741424          0      10       0   6.014 dbtest01.hlz_bubble
09-18T07:49:18      0      0   499991          0       5       0   1.610 dbtest01.test01
09-18T07:49:25      0      0  3532986          0      13       0   7.802 dbtest01.test02
09-18T07:49:26      0      0   126863          0       1       0   0.976 dbtest01.test_event
09-18T07:49:27      0      1    30294          0       1       0   0.582 test01.txt

real    1m22.725s
user    0m0.387s
sys 0m0.078s

发现主库的 test01.txt 这个表和slave中的test01.txt存在不一致。

原因是：刚才模拟演示，先在slave库上执行了删除动作
delete from txt where id=200;导致slave库表txt中比master库txt表少一条记录

修复数据: 

[root@localhost bin]# /usr/local/percona-toolkit/bin/pt-table-sync h=192.168.1.220,u=ptsum,p=ptchecksums,P=3306 --databases=test01 --tables=test01.txt  --replicate=percona.ptchecksums  --charset=utf8  --transaction --execute

再次校验，数据一致


[root@localhost bin]# time /usr/local/percona-toolkit/bin/pt-table-checksum h=192.168.1.220,u=ptsum,p=‘ptchecksums‘,P=3306 --ignore-databases sys,mysql  --truncate-replicate-table  --replicate=percona.ptchecksums --no-check-binlog-format --nocheck-replication-filters --recursion-method="processlist"   2>&1 | tee 2020-09-18-pt-checksum.log
Checking if all tables can be checksummed ...
Starting checksum ...
            TS ERRORS  DIFFS     ROWS  DIFF_ROWS  CHUNKS SKIPPED    TIME TABLE
09-18T09:48:10      0      0     9739          0       4       0   0.784 dbtest01.hlz_ad
09-18T09:48:11      0      0    64143          0       4       0   0.995 dbtest01.hlz_ad_step
09-18T09:48:16      0      0   741424          0       9       0   4.224 dbtest01.hlz_bubble
09-18T09:48:17      0      0   499991          0       5       0   1.470 dbtest01.test01
09-18T09:48:24      0      0  3532986          0      13       0   6.403 dbtest01.test02
09-18T09:48:24      0      0   133999          0       1       0   0.894 dbtest01.test_event
09-18T09:48:25      0      0    37431          0       1       0   0.511 test01.txt

real    0m15.676s
user    0m0.359s
sys 0m0.055s

六、附带共网友测试案例

附带案例二，基本和场景1是一样的。此处不再细说，留给感兴趣的网友们。
下面简单描述下场景和恢复方法：

如果复制报错后，使用跳过错误、复制过滤等办法修复了主从复制。主、从库数据一直在更新。
错误的修复步骤：
在主库上备份表 t （假设备份快照 GTID为 aaaa:1-10000）；
停止从库复制，GTID为 aaaa:1-20000；
恢复表 t 到从库；
启动复制。

原因分析：
这里的问题是复制起始位点是 aaaa:20001，aaaa:10000-20000 这些事务将不会在从库上回放，如果这里面有修改表 t 数据的事务，从库上将丢失这部分数据

解决办法：从备份开始到启动复制，锁定表 t，保证 aaaa:10000-20000 中没有修改表 t 的事务。

正确修复步骤：

对主库表 t 加读锁；
在主库上备份表 t；
停止从库复制，恢复表 t；
启动复制；
解锁表 t。

建议的解决方法：如果不想对表t进行加锁，可以直接把从库的复制暂停，然后采用场景一的方式进行恢复。这样就避免了锁表。

MySQL 主从复制场景单表数据出错导致复制终止如何快速修复

上一篇：BUUCTF PHP反序列化


下一篇：gofiber(fasthttp) 不重启动态改变路由设置