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

数据库主从不一致一直是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/