乐趣区

关于mysql:故障分析-ptarchiver-归档丢失一条记录

jiezi

作者：王向

爱可生 DBA 团队成员，负责公司 DMP 产品的运维和客户 MySQL 问题的解决。善于数据库故障解决。对数据库技术和 python 有着浓重的趣味。

本文起源：原创投稿

* 爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。

前言

在不久前有位客户在进行数据迁徙时发现。本人应用 pt-archiver 备份时总是会少一条数据；如源数据库中某表数据为 2333，导入目标数据库后 select 后果只有 2332。

于是本篇文章就此开展，因为这个问题在先前钻研过，我这里就间接先上论断后试验了。

论断

在 pt-archiver 中有这样一个参数 –[no]safe-auto-increment 官网文档中作用如下：

指定不应用自增列 (AUTO_INCREMENT) 最大值对应的行进行归档默认开启，该选项在进行归档革除时会额定增加一条 WHERE 子句以避免工具删除单列升序字段具备的具备 AUTO_INCREMENT 属性最大值的数据行，为了在数据库重启之后还能应用到 AUTO_INCREMENT 对应的值，避免引起无奈归档或革除字段对应最大值的行。

简略总结以下外面蕴含的信息：

  1. pt-archiver 工具对自增列 (AUTO_INCREMENT) 最大值默认行为是：

    会在进行归档革除时额定增加一条 WHERE 子句避免对，自增列 (AUTO_INCREMENT) 字段的最大值如“max(id)”, 的数据行进行爱护。其行为是不归档也不删除此行。

  2. 为什么要爱护这一行数据？

    为了避免 AUTO_INCREMENT 值重置

  3. 避免 AUTO_INCREMENT 值重置的意义？

    避免数据抵触，一旦 AUTO_INCREMENT 值重置，将会呈现雷同自增 id。势必会导致下一次的归档失败，影响归档的继续进行。间接影响业务

综上所述，pt-archiver 工具默认开启 safe-auto-increment 参数是很有必要的。能够避免某些意外产生。

那么什么状况下须要敞开 safe-auto-increment 参数？

  1. 确定本人要对本表的全量数据进行归档时，应开启此参数 –nosafe-auto-increment
  2. 归档局部数据时蕴含自增列 (AUTO_INCREMENT) 字段的最大值时，应开启此参数 –nosafe-auto-increment
  3. 确定只归档数据不做删除数据的状况下。能够始终开启此参数 –nosafe-auto-increment
  4. 如果应用的是 MySQL8.0 版本请疏忽下面 3 条间接 –nosafe-auto-increment，因为 MySQL8 不会重置 AUTO_INCREMENT

问题重现

1. 筹备好源库数据如下

mysql> select count(*) from sbtest1; 
+----------+ 
| count(*) |
+----------+
|   100000 | 
+----------+ 
1 row in set (0.08 sec)

2. 进行归档

[[email protected] ~]# pt-archiver --source 
u=root,p=xxx,h=127.0.0.1,P=3306,D=test,t=sbtest1 --dest
u=root,p=xxx,h=10.186.61.9,P=3306,D=test,t=sbtest1 --where="1=1" --progress=1000 
--statistics --bulk-insert --bulk-delete --txn-size=1000 --limit=1000 --no-delete 
--no-check-charset --skip-foreign-key-checks 
TIME                ELAPSED  COUNT 
2021-09-09T23:23:38       0      0 
2021-09-09T23:23:38       0   1000 
2021-09-09T23:23:38       0   2000 
2021-09-09T23:23:38       0   3000 
2021-09-09T23:23:38       0   4000 
2021-09-09T23:23:38       0   5000 
2021-09-09T23:23:38       0   6000 
...1 
2021-09-09T23:23:46       7 2394000 
2021-09-09T23:23:46       7 95000 
2021-09-09T23:23:46       7 96000 
2021-09-09T23:23:46       8 97000 
2021-09-09T23:23:46       8 98000 
2021-09-09T23:23:46       8 99000 
2021-09-09T23:23:46       8 99999 
Started at 2021-09-09T23:23:38, ended at 2021-09-09T23:23:46 
Source: D=test,P=3306,h=127.0.0.1,p=...,t=sbtest1,u=root 
Dest: D=test,P=3306,h=10.186.61.9,p=...,t=sbtest1,u=root 
SELECT 99999 
INSERT 99999 # 只有 99999 行

3. 查看新表的行数

mysql> select count(*) from sbtest1;
+----------+ 
| count(*) | 
+----------+ 
|    99999 | 
+----------+ 
1 row in set (0.08 sec)

对于 AUTO_INCREMENT 值重置

通过这个试验咱们能够很轻易的发现 AUTO_INCREMENT 值的重置过程

删除 max 最大的 id，此时 AUTO_INCREMENT 值不会受到影响

当咱们重启数据库

systemctl restart mysqld_3306

此时 AUTO_INCREMENT 值会随着 max(id)而减少 1

MySQL 重启后自增列的初始化过程：

MySQL 通过一个计数器，实现自增值的保护和调配。但因为但该计数器仅存储在内存里，而没有刷新到磁盘，这就意味着，一旦 MySQL 重启，自增列会从初始值开始自增，而不是表中以后的最大值。所以 MySQL 重启后，须要从新初始化计数器为自增列最大值。

MySQL 8.0 auto-increment 计数器逻辑

在 MySQL 8.0 中，这个计数器的逻辑变了：

每当计数器的值有变，InnoDB 会将其写入 redo log，保留到引擎专用的零碎表中。MySQL 失常敞开后重启：从零碎表中获取计数器的值。MySQL 故障后重启：从零碎表中获取计数器的值；从最初一个检查点开始扫描 redo log 中记录的计数器值；取这两者的最大值作为新值。

参考：

https://docs.percona.com/perc…

https://zhuanlan.zhihu.com/p/…

https://dev.mysql.com/doc/ref…

退出移动版