mysql验证主从一致性_MySQL 主从数据一致性校验工具

主要设计思路和算法

主从数据校验的基本思路是，对主数据库某数据表使用一系列算法最终获得一个checksum，再分别从每个从数据库获得相应数据表的checksum进行比对，如果不一致，则表示主从不同步。

获得checksum的方法：将当前行所有字段使用MYSQL自带函数CONCAT_WS()连接，以‘#’分隔获得一个完整的字符串表示一行的数据，然后使用SHA1()对该字符串签名。在完成一行的签名后，将当前行与下一行CONCAT_WS()得到的字符串连在一起再次签名，如此迭代直到所有行签名完毕，实现循环冗余校验(CRC)。

为了提高速度，使用MYSQL的用户自定义变量代替从MYSQL读取到PHP的方式，由于避免了将庞大的结果集反复传递和保存，效率大幅提高。

算法流程：

SET @crc := '', @cnt := 0; // 初始化

SELECT MIN(LEAST(0, LENGTH(@crc := SHA1(CONCAT(@crc,

SHA1(CONCAT_WS('#', 数据表列名1,

数据表列名2,…..,ISNULL(数据表列名1),ISNULL(数据表列名2)….. )))))), @cnt := @cnt +

1)) AS len FROM 数据库名.数据表名 USE INDEX(PRIMARY); // 计算checksum

SELECT @crc, @cnt; // 取出变量结果打印

算法中需要说明的几点：

使用MIN()和LEAST()是为了使每一步中间结果在下一次计算时，可以自动被丢弃，不占用不必要的内存，提高速度。

使用USE

INDEX(PRIMARY)要求被检查表必须含有PRIMARY索引，如果要将一个数据表分多段校验，则进一步要求PRIMARY索引为自增ID。

使用USE INDEX()是由于不同机器数据表中内容存储顺序可能不同，必须指定PRIMARY KEY或者UNIQUE

KEY来保证多机签名顺序一致。

由于CONCAT_WS()自动忽略NULL值，为了防止出现由于NULL值导致原本不同的行checksum相同，在连接各字段内容的最后，跟随由ISNULL()函数获得的bitmap表示是否为NULL值。

由于主从同步、脚本运行都有时间间隔，在主库签名完成后，可能又有新的相同ID写入数据表被同步到从库，导致签名不一致。前期采用主库锁表的方法保证事务性，但由于实时校验运行频繁，锁表会影响用户体验，可以考虑跳过最新提交的很小部分，在SQL语句的WHERE字句中，增加偏移LIMIT

A,B，跳过前A条最新的，从A+1开始对后面的B条做签名。

具体实践

在4月18号重做百科线上从数据库数据时，使用了前面提到的技术对线上主从数据库进行表级别一致性校验，在校验速度上比较有优势，百万行在分钟级别。

在wiki 1.4.9.1项目中，增加了对百科线上主从数据库的几个关键数据表的实时增量数据同步监控。位置在cvspath:

app/search/wiki/script/data-check，主要是table_monitor.php(正在运行的主从实时校验脚本)和table_checksum.php(主从全表校验)。

参考资料：MySQL Table CheckSum?

MySQL?

Table Checksum，是sf上面一个开源项目MySQL

Toolkit的一部分，可以跨服务器的对任何数据引擎的数据表做校验。请参考这篇文章，作者是 MySQL

Toolkit项目的发起人：Introducing

MySQL Table

Checksum。这个工具目前已经经历l饿多次升级，具备分段校验的能力，具体请参考其对应的perldoc文档。

在具体checksum校验操作上，以及如果数据不一致应当如何处理，可以参考这篇文章： How to know if a MySQL slave is identical to its

master都是这一个作者写的，比较详细，提供给大家参考一下。

相关工具：MySQL Table Sync

在MySQL

Toolkit中，与之相配合的另一个工具是mysql-table-sync。这个工具主要用于将两个有轻微不一致的数据库的内容重新同步一致。工具分别读取两个数据表，并生成用于同步一致的SQL语句(算法上类似rsync)。通过mysql-table-checksum获得不一致结果的范围，导入mysql-table-sync，可以用于数据表同步。