MySQL检查修复主从数据一致性工具percona

xiaoxiao2021-02-28  24

数据库主从不一致一直是DBA比较头疼的事情,通常我们仅有一种办法,热备主库,然后替换掉所有的从库。这不仅代价非常大,而且类似治标不治本的方案,让人十分不安。因此我们需要合适的工具,至少帮我们回答下面三个问题:

是从库延迟导致了用户看到的数据不一致,还是真的主从数据就不一致?如果不一致,这个比例究竟多大?下次还会出现吗?

回答清楚这几个问题,有助于我们决定是否修复,以及修复的方式,还可以帮我们找出不一致的数据,进而定位问题根源。而percona的pt-table-checksum正是我们想要的。

pt-table-checksum是著名的 percona-toolkit工 具集的工具之一。它通过在主库执行基于statement的sql语句来生成主库数据块的checksum,把相同的sql语句传递到从库,并在从库上计 算相同数据块的checksum,最后,比较主从库上相同数据块的checksum值,由此判断主从数据是否一致。这种校验是分表进行的,在每个表内部又 是分块进行的,而且pt工具本身提供了非常多的限流选项,因此对线上服务的冲击较小

如何保持数据一致性?

当pt工具在计算主库上某chunk的checksum时,主库可能还在更新,同时从库可能延迟使得relay-log中还有与这个chunk数据相关的 更新,那该怎么保证主库与从库计算的是”同一份”数据?答案是加for update当前读锁,这保证了主库的某个chunk内部数据的一致性。否则,1000个人chekcusm同样的1000行数据,可能得到1000个不 同的结果,你无法避开mvcc的干扰!获得for update锁后,pt工具开始计算chunk的checksum值,并把计算结果保存到pt工具自建的结果表中(采用replace into select的方式),然后释放锁。该语句最终会传递到从库并执行相同的计算逻辑。

--nocheck-replication-filters :不检查复制过滤器,建议启用。后面可以用--databases来指定需要检查的数据库。

--no-check-binlog-format : 不检查复制的binlog模式,要是binlog模式是ROW,则会报错。

--replicate-check-only :只显示不同步的信息。

--replicate= :把checksum的信息写入到指定表中,建议直接写到被检查的数据库当中。

--databases= :指定需要被检查的数据库,多个则用逗号隔开。

--tables= :指定需要被检查的表,多个用逗号隔开

实验:

下载地址:wget  http://www.percona.com/redir/downloads/percona-toolkit/LATEST/deb/percona-toolkit_2.2.7.tar.gz

安装方法:

tar -zxf percona-toolkit_2.2.7.tar.gz cd percona-toolkit-2.2.7

perl Makefile.PL;

make;

make install

master:localhost slave:192.168.20.137 master: GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE ON *.* TO 'ops'@'localhost' IDENTIFIED BY 'pass'; ==>需要复制到slave CREATE TABLE test.checksums ( ==>工具默认创建percona.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, PRIMARY KEY (db, tbl, chunk), INDEX ts_db_tbl (ts, db, tbl) ) ENGINE=InnoDB; CREATE TABLE `test.dsns` ( ==>用于记录slave连接方式 `id` int(11) NOT NULL AUTO_INCREMENT, `parent_id` int(11) DEFAULT NULL, `dsn` varchar(255) NOT NULL, PRIMARY KEY (`id`) ); insert into dsns values(1,1,'h=192.168.20.137,u=ops,p=pass,P=3306'; ==>指定slave连接方式,多个slave的话插入多条 localhost:~/percona-toolkit-2.2.7# pt-table-sync --execute --print --replicate=test.checksums h='localhost',u='ops',p='pass',P=3306 h=192.168.20.137,u=ops,p=pass,P=3306 --databases='DBManage' --tables='DBServerList_tmp'dna-430:~/percona-toolkit-2.2.7# pt-table-checksum h='localhost',u='ops',p='pass',P=3306 --recursion-method=dsn=h='localhost',u=ops,p=pass,D=test,t=dsns --no-check-binlog-format --nocheck-replication-filters --set-vars innodb_lock_wait_timeout=500 --replicate-check --replicate=test.checksums --databases='DBManage' --tables=DBServerList_tmp Error setting innodb_lock_wait_timeout: DBD::mysql::db do failed: Variable 'innodb_lock_wait_timeout' is a read only variable [for Statement "SET SESSION innodb_lock_wait_timeout=500"]. The current value for innodb_lock_wait_timeout is 50. If the variable is read only (not dynamic), specify --set-vars innodb_lock_wait_timeout=50 to avoid this warning, else manually set the variable and restart MySQL. TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE 03-13T03:11:59 0 1 81 1 0 2.206 DBManage.DBServerList_tmp TS :完成检查的时间。 ERRORS :检查时候发生错误和警告的数量。 DIFFS :0表示一致,1表示不一致。当指定--no-replicate-check时,会一直为0,当指定--replicate-check-only会显示不同的信息。 ROWS :表的行数。 CHUNKS :被划分到表中的块的数目。 SKIPPED :由于错误或警告或过大,则跳过块的数目。 TIME :执行的时间。 TABLE :被检查的表名。

上面检验到主从不一致的表及数据,那么percona的另一个工具可以针对这些检测出来的不一致数据进行修复

pt-table-sync简介

顾名思义,它用来修复多个实例之间数据的不一致。它可以让主从的数据修复到最终一致,也可以使通过应用双写或多写的多个不相关的数据库实例修复到一 致。同时它还内部集成了pt-table-checksum的校验功能,可以一边校验一边修复,也可以基于pt-table-checksum的计算结果 来进行修复。

采用什么方式修复?

对找到的主从不一致的行,采用replace into语句,在主库执行一遍以生成该行全量的binlog,并同步到从库,这会以主库数据为基准来修复从库;对于主库有的行而从库没有的行,采用 replace在主库上插入(必须不能是insert);对于从库有而主库没有的行,通过在主库执行delete来删除(pt-table-sync强烈 建议所有的数据修复都只在主库进行,而不建议直接修改从库数据;但是也有特例)。

参数介绍

--replicate= :指定通过pt-table-checksum得到的表,这2个工具差不多都会一直用。

--databases= : 指定执行同步的数据库,多个用逗号隔开。

--tables= :指定执行同步的表,多个用逗号隔开。

--sync-to-master :指定一个DSN,即从的IP,他会通过show processlist或show slave status 去自动的找主。

h=127.0.0.1 :服务器地址,命令里有2个ip,第一次出现的是M的地址,第2次是Slave的地址。

u=root :帐号。

p=123456 :密码。

--print :打印,但不执行命令。

--execute :执行命令。

实验:

localhost:~/percona-toolkit-2.2.7# pt-table-sync --execute --print --replicate=test.checksums h='localhost',u='ops',p='pass',P=3306 h=192.168.20.137,u=ops,p=pass,P=3306 --databases='DBManage' --tables='DBServerList_tmp' REPLACE INTO `DBManage`.`DBServerList_tmp`(`server_name`, `server_ip`, `data_center`, `db_version`, `is_enabled`, `is_raid10`, `is_used`, `is_need_alarm`, `is_accessed`, `server_env`, `db_name`, `slavedb_server_name`, `master_name`, `master_slave`, `comment`, `user_name`, `pass_word`, `db_connection_status`, `db_connection_comment`, `updated_at`, `extra_server_ip`) VALUES ('dna-212', '192.168.110.22', 'eqx', '5.1.57-log', 'false', 'true', 'false', 'false', 'false', 'stage', '', '', '', 'master', 'staging1', 'monitor', 'monitor_dba', 'OK', '1 client connection threads | threads_connected=1;800;2000', '2014-03-13 01:49:26', NULL) /*percona-toolkit src_db:DBManage src_tbl:DBServerList_tmp src_dsn:P=3306,h=localhost,p=...,u=ops dst_db:DBManage dst_tbl:DBServerList_tmp dst_dsn:P=3306,h=218.108.245.166,p=...,u=ops lock:1 transaction:1 changing_src:test.checksums replicate:test.checksums bidirectional:0 pid:9946 user:root host:localhost*/; 检查: root@(none) 07:20:22>select * from test.checksums where master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc); Empty set (0.01 sec) 结果为空表示已经修复完成

注意事项:

pt-table-checksum:

如果表没有主键或唯一索引,或者干脆没有任何索引,那么pt工具在chunk表的时候,将无所适从。不过我们已强制在建表的时候,每个表都必须有主键。 –check-binlog-format是默认选项,建议不要关闭它。pt-table-checksum工具自身产生的所有sql语句要基于语句格式 同步到从库,这是由它的实现原理决定的。但是在A-B-C的级联复制结构中,如果B是行格式的复制,那么B与C的数据一致性校验就没法做了。在A上设置该 sql语句为语句级并不会把set这个动作记录到binlog中,这个属性无法级联传递。 主从异构的情况下,checksum语句可能在从库上执行失败,即使是索引的不一致。例如sql语句中有force index某个索引,但是从库的表上没有这个索引,就会导致卡库。

pt-table-sync:

采用replace into来修复主从不一致,必须保证被replace的表上有主键或唯一键,否则replace into退化成insert into,起不到修复的效果。这种情况下pt-table-sync会采用其他校验和修复算法,但是效率非常低,例如对所有列的group by然后求count(*)(表一定要有主键!)。 主从数据不一致需要通过replace into来修复,该sql语句必须是语句级。pt-table-sync会把它发起的所有sql语句都设置为statement格式,而不管全局的 binlog_format值。这在级联A-B-C结构中,也会遇到pt-table-checksum曾经遇到的问题,引起行格式的中继库的从库卡库是 必然。不过pt-table-sync默认会无限递归的对从库的binlog格式进行检查并警告: 由于pt-table-sync每次只能修复一个表,所以如果修复的是父表,则可能导致子表数据连带被修复,这可能会修复一个不一致而引入另一个不一致; 如果表上有触发器,也可能遇到同样问题。所以在有触发器和主外键约束的情况下要慎用。pt-table-sync工具同样也不欢迎主从异构的结构。pt- table-sync工具默认会进行先决条件的检查。 pt-table-sync在修复过程中不能容忍从库延迟,这正好与pt-table-checksum相反。如果从库延迟太多,pt-table- sync会长期持有对chunk的for update锁,然后等待从库的master_pos_wait执行完毕或超时。从库延迟越大,等待过程就越长,主库加锁的时间就越长,对线上影响就越 大。因此要严格设置max-lag。 对从库数据的修复通常是在主库执行sql来同步到从库。因此,在有多个从库时,修复某个从库的数据实际会把修复语句同步到所有从库。数据修复的代价取决于 从库与主库不一致的程度,如果某从库数据与主库非常不一致,举例说,这个从库只有表结构,那么需要把主库的所有数据重新灌一遍,然后通过binlog同 步,同时会传递到所有从库。这会给线上带来很大压力,甚至拖垮集群。正确的做法是,先用pt-table-checksum校验一遍,确定不一致的程度: 如果不同步的很少,用pt-table-sync直接修复;否则,用备份先替换它,然后用pt-table-sync修复。 说明: 这实际提供了一种对myisam备份的思路:如果仅有一个myisam的主库,要为其增加从库,则可以:先mysqldump出表结构到从库上,然后启动 同步,然后用pt-table-sync来修复数据。

参考资料:http://www.percona.com/doc/percona-toolkit/2.2/
转载请注明原文地址: https://www.6miu.com/read-2250041.html

最新回复(0)