MySQL如何解决主从数据不一致问题

IT那活儿 2022-03-24

3048

点击上方“IT那活儿”，关注后了解更多内容，不管IT什么活儿，干就完了！！！

背景介绍

两台MySQL,发生了种种种种，导致了两个表的数据不一致，但是同步还在正常进行，后来意识到这种问题（可能之前skip啊，或者一开始搭建的时候就是不一致的状态），该如何修复呢？

我们可以来看下percona-toolkit这个工具是如何修复这种情况的。

环境搭建

1. 校验

主库：192.168.100.8 3306
从库：192.168.100.12 3305
MySQL version：MySQL-5.6.30

1）master 服务器安装yum依赖包：

yum install perl perl-devel perl-Time-HiRes perl-DBI perl-DBD-MySQL

2）安装percona-toolkit工具包：

wget http://www.percona.com/get/percona-toolkit.tar.gztar zxf percona-toolkit-2.2.13.tar.gzcd percona-toolkit-2.2.13perl Makefile.PLmake && make install

3）master与slave数据库创建以及用户授权：

Create database pt CHARACTER SET utf8;GRANT UPDATE,INSERT,DELETE,SELECT, PROCESS, SUPER, REPLICATION SLAVE ON . TO 'backup'@'192.168.100.8' identified by 'abc123';GRANT ALL ON pt.* TO 'backup'@'192.168.100.8' IDENTIFIED BY 'abc123';flush privileges;use pt;CREATE TABLE IF NOT EXISTS checksums (db char(64) NOT NULL,tbl char(64) NOT NULL,chunk int NOT NULL,chunk_time float NULL,chunk_index varchar(200) NULL,lower_boundary text NULL,upper_boundary text NULL,this_crc char(40) NOT NULL,this_cnt int NOT NULL,master_crc char(40) NULL,master_cnt int NULL,ts timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,PRIMARY KEY (db, tbl, chunk),INDEX ts_db_tbl (ts, db, tbl)) ENGINE=InnoDB;

4）进行校验 master上执行：

pt-table-checksum --nocheck-binlog-format --nocheck-plan --nocheck-replication-filters --replicate=pt.checksums --set-vars innodb_lock_wait_timeout=120 --databases tmp -u'backup' -p'abc123' -h192.168.100.8 -P3306

-h -u -p -P -S -d 连接信息；
--nocheck-replication-filters 检测中忽略mysql 配置参数binlog_ignore_db等；
--nocheck-binlog-format 不检测日志格式；
--replicate 指定checksum 存储的db和表，如pt.checksum；
--chunk-size， --chunk-size-limit 用于指定检测块的大小，可控性更强；
--ignore-databases/tables/column 跳出指定元素的过滤；
--lock-wait-timeout innodb 锁的超时设定，默认为1；
--max-load 设置最大并发连接数；
--replicate-check-only 只输出数据不一致的信息；
--help。

2. 校验结果

A software update is available:* The current version for Percona::Toolkit is 2.2.14.TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE01-21T12:21:59 0 0 3523 4 0 0.385 tmp.COMM_REGION01-21T12:21:59 0 0 0 1 0 0.011 tmp.UCT_USER01-21T12:01:04 0 0 115020 1 0 5.001 tmp.UCT_USER_101-21T12:01:04 0 0 0 1 0 0.017 tmp.UCT_USER_201-21T12:01:04 0 0 710 1 0 0.009 tmp.VOX_APPLICATION_MICROPHONE01-21T12:01:04 0 0 3778 1 0 0.084 tmp.VOX_CLASS

TS ：完成检查的时间；
ERRORS ：检查时候发生错误和警告的数量；
DIFFS ：0表示一致，1表示不一致。当指定--no-replicate-check时，会一直为0，当指定--replicate-check-only会显示不同的信息；
ROWS ：表的行数；
CHUNKS ：被划分到表中的块的数目；
SKIPPED ：由于错误或警告或过大，则跳过块的数目；
TIME ：执行的时间；
TABLE ：被检查的表名。

注意：

要是在执行命令的过程遇到找不到从服务器的错误，默认是通过show processlist 找到host的值或show slave hosts 找到host的值。

Diffs cannot be detected because no slaves were found. Please read the --recursion-method documentation for information.

1）关于--recursion-method参数的设置：

METHOD USES=========== =============================================processlist SHOW PROCESSLISThosts SHOW SLAVE HOSTScluster SHOW STATUS LIKE 'wsrep_incoming_addresses'dsn=DSN DSNs from a tablenone Do not find slavespt-table-checksum --nocheck-binlog-format --nocheck-plan --nocheck-replication-filters --replicate=pt.checksums --set-vars innodb_lock_wait_timeout=120 --recursion-method dsn=h=10.0.1.73,D=stats,t=dsns -u'backup' -p'abc123' --host=10.0.1.72 -P3306 --databases=HS_Order（校验）[root@goufu data]# pt-table-sync --print --sync-to-master h=10.0.1.73,P=3306,u=backup,p='abc123' --replicate pt.checksums --databases=HS_Order --tables=VOX_REWARD_ORDER>zzz.sql （也可直接加--execute自动修复）mysql> show slave hosts;+-----------+------+------+-----------+--------------------------------------+| Server_id | Host | Port | Master_id | Slave_UUID |+-----------+------+------+-----------+--------------------------------------+| 123305 | | 3305 | 533306 | 9645c4a3-178a-11e6-ac5d-d4ae52d034a7 |+-----------+------+------+-----------+--------------------------------------+1 row in set (0.00 sec)

在从库的配置文件里加：report_host = 192.168.200.25 #设置成本地地址。

mysql> show slave hosts;+-----------+----------------+------+-----------+--------------------------------------+| Server_id | Host | Port | Master_id | Slave_UUID |+-----------+----------------+------+-----------+--------------------------------------+| 123305 | 192.168.100.12 | 3305 | 533306 | 9645c4a3-178a-11e6-ac5d-d4ae52d034a7 |+-----------+----------------+------+-----------+--------------------------------------+

最后再执行以上命令（多加--recursion-method=hosts 参数）：

pt-table-checksum --nocheck-binlog-format --nocheck-plan --nocheck-replication-filters --replicate=pt.checksums --set-vars innodb_lock_wait_timeout=120 --recursion-method=hosts --databases tmp -u'backup' -p'abc123' -h192.168.100.8 -P3306

先校验出哪些库的表不同步，然后指定库与表生成语句，采用第二种方法，将生成的SQL语句在从库执行即可。

2）数据同步，复制，消除差异（Master服务器运行）要是有中文的则需要加上：--charset=utf8，防止乱码。

i. 自动消除差异（不推荐）：

pt-table-sync --print --execute --sync-to-master h=192.168.100.87,P=3341,u=backup,p='abc123' --databases=test --tables=goufu

ii. 打印出sql语句，人工干预到Slave库执行（推荐）：

[root@goufu data]# pt-table-sync --print --sync-to-master h=192.168.100.87,P=3341,u=goufu,p='abc123' --databases=test --tables=goufu REPLACE INTO test.goufu(a) VALUES ('1') *percona-toolkit src_db:test src_tbl:goufusrc_dsn:P=3340,h=192.168.100.87,p=...,u=goufu dst_db:test dst_tbl:goufu dst_dsn:P=3341,h=192.168.100.87,p=...,u=backup lock:1 transaction:1 changing_src:1 replicate:0 bidirectional:0 pid:7020 user:root host:VM-TEST-87*/;pt-table-sync --print --sync-to-master h=10.1.1.7,P=3306,u=backup,p='abc123'--replicate pt.checksums

--sync-to-master ：指定一个DSN，即从的IP，他会通过show processlist或show slave status 去自动的找主；
--replicate ：指定通过pt-table-checksum得到的表，这2个工具差不多都会一直用；
--print ：打印，但不执行命令；
--execute ：执行命令。

解决方案

[root@goufu ~]# pt-table-checksum --nocheck-binlog-format --nocheck-plan --nocheck-replication-filters --replicate=pt.checksums --set-vars innodb_lock_wait_timeout=120 --recursion-method=hosts --databases bbs,HomeworkSitter_UAT --tables=dz_common_session,VOX_VOICE_RECOMMEND_DATA_SENTENCE_REF -u 'backup' -p'goufu' -h192.168.100.8 -P3306TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE01-21T15:37:03 0 2 106683 4 0 0.588 HomeworkSitter_UAT.VOX_VOICE_RECOMMEND_DATA_SENTENCE_REF01-21T15:37:03 0 1 3 1 0 0.026 bbs.dz_common_session

由于其中一个表的数据较大，采用dump导出的方式在从库上恢复。

1. 第一个执行采用pt-table-sync方式恢复：

pt-table-sync --print --sync-to-master h=192.168.100.12,P=3305,u=goufu,p='abc123' --databases=bbs --tables=dz_common_session

2. 第二个表采用导出导入的方式恢复：

pt-table-sync --print --sync-to-master h=192.168.100.12,P=3305,u=goufu,p='abc123' --databases=bbs --tables=dz_common_session

3. 同步完成后再校验：

[root@goufu ~]# pt-table-checksum --nocheck-binlog-format --nocheck-plan --nocheck-replication-filters --replicate=pt.checksums --set-vars innodb_lock_wait_timeout=120 --recursion-method=hosts --databases bbs,HomeworkSitter_UAT --tables=dz_common_session,VOX_VOICE_RECOMMEND_DATA_SENTENCE_REF -u'backup' -p'abc123' -h192.168.100.8 -P3306 TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE01-21T16:50:19 0 0 106683 5 0 1.006 HomeworkSitter_UAT.VOX_VOICE_RECOMMEND_DATA_SENTENCE_REF01-21T16:50:19 0 0 3 1 0 0.013 bbs.dz_common_session

经验总结

1. 采用replace into来修复主从不一致，必须保证被replace的表上有主键或唯一键，否则replace into退化成insert into，起不到修复的效果。

这种情况下pt-table-sync会采用其他校验和修复算法，但是效率非常低，例如对所有列的group by然后求count(*)(表一定要有主键！)。

2. 主从数据不一致需要通过replace into来修复，该sql语句必须是语句级。

pt-table-sync会把它发起的所有sql语句都设置为statement格式，而不管全局的binlog_format值。这在级联A-B-C结构中，也会遇到pt-table-checksum曾经遇到的问题，引起行格式的中继库的从库卡库是必然。

不过pt-table-sync默认会无限递归的对从库的binlog格式进行检查并警告。

3. 由于pt-table-sync每次只能修复一个表，所以如果修复的是父表，则可能导致子表数据连带被修复，这可能会修复一个不一致而引入另一个不一致；如果表上有触发器，也可能遇到同样问题。

所以在有触发器和主外键约束的情况下要慎用。

pt-table-sync工具同样也不欢迎主从异构的结构。pt-table-sync工具默认会进行先决条件的检查。

4. pt-table-sync在修复过程中不能容忍从库延迟，这正好与pt-table-checksum相反。

如果从库延迟太多，pt-table-sync会长期持有对chunk的for update锁，然后等待从库的master_pos_wait执行完毕或超时。从库延迟越大，等待过程就越长，主库加锁的时间就越长，对线上影响就越大。

因此要严格设置max-lag。

5. 对从库数据的修复通常是在主库执行sql来同步到从库。

因此，在有多个从库时，修复某个从库的数据实际会把修复语句同步到所有从库。数据修复的代价取决于从库与主库不一致的程度，如果某从库数据与主库非常不一致。

举例说，这个从库只有表结构，那么需要把主库的所有数据重新灌一遍，然后通过binlog同步，同时会传递到所有从库。这会给线上带来很大压力，甚至拖垮集群。

正确的做法是：先用pt-table-checksum校验一遍，确定不一致的程度：如果不同步的很少，用pt-table-sync直接修复；否则，用备份先替换它，然后用pt-table-sync修复。

说明：

这实际提供了一种对myisam备份的思路：如果仅有一个myisam的主库，要为其增加从库，则可以：先mysqldump出表结构到从库上，然后启动同步，然后用pt-table-sync来修复数据。

本文作者：王家福

本文来源：IT那活儿（上海新炬王翦团队）

mysql

文章转载自IT那活儿，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL如何解决主从数据不一致问题

点击上方“IT那活儿”，关注后了解更多内容，不管IT什么活儿，干就完了！！！

本文作者：王家福

本文来源：IT那活儿（上海新炬王翦团队）

评论