mysql集群数据一致性校验_MySQL

目前，mysql在互联网行业使用地如火如荼，很多大型网站都在使用MySQL数据库，通过搭建mysql主备集群，实现高性能，高可用的存储方案。mysql集群的共同特性是通过复制来实现主备间的同步，保证主备数据的一致性。这样才能保证读写分离，备库为主库分担压力，提高整个集群的可用性和性能。

为什么需要数据一致性校验？由于大部分搭建mysql服务的都是PC集群，尤其是在集群达到一定规模后，硬件出故障几乎是必然的。mysql复制是异步复制，当主机出现故障时，就会出现丢数据的可能，造成主备数据不一致，无法正常对外提供服务。另外，当现有的PC集群容量不足时，需要对集群扩容，扩容就涉及到数据迁移。迁移一般都包括全量和增量，在不停服务的情况下，当迁移完数据后，需要校验数据的一致性，保证迁移后不对业务造成影响。

什么是数据一致性？这里仅仅针对mysql，或是关系型数据库，一致性主要包括两方面，表结构一致和数据内容一致。一般情况下，表结构变更相对是少的，而且不一致的概率也很小，即使检查，也相对容易；而导致数据内容不一致的情况很多，所以我们更关心的数据内容的一致性。

如何实现数据一致性校验？一种思路就是逐行逐字段比较主库和备库的表；另外一种思路是，不逐行逐字段比较，取而代之的是分别对主库和备库计算校验和，通过判断校验和是否相同，确定主备库数据是否一致。两种思路都很简单，第一种思路正确性高，但性能比较差，因为返回大量的结果集导致大量的网络IO和磁盘IO；而第二种思路则恰好相反，性能会更好，少了IO，多消耗了一些CPU资源(计算校验和)，正确性不如第一种思路。但是考虑到生产环境下，数据时时刻刻都是动态变化的，就没那么简单了。通过对表加锁，可以保证我们在校验时，数据是静态的，待我们顺利完成校验后，再解锁。mysql自带命令CHECKSUM TABLE，就是通过锁表方式来保证数据是静态的。这种方式对于小表，访问量小的表还好，若表非常大，校验需要很长时间，生产环境是不能容忍的。既然要保持静态就需要锁表，可不可以缩短锁表时间呢？pt-table-checksum通过将表分片，每次只对一部分行上锁，这样在校验过程中，一时刻只有部分行被锁住，减少对业务的影响。

目前业界使用比较广泛的是percona公司的pt-table-checksum，下文我将详细介绍该工具的使用和原理，并分析其不足以及可以改进的地方。

pt-table-checksum工具通过在主库上执行一个校验和的sql语句，然后通过复制，相同语句会在从库执行(pt-table-checksum要求复制工作在语句级复制模式下)。通过replace...select语句将校验和结果存储在结果表，然后对比主库和从库的相同块的记录数目和校验和，判断主备库数据是否一致。这里要注意的是， pt-table-checksum 不是对一个表仅作一个校验和，因为如果表特别大，将会对DB造成很大的负载，影响正常业务。一个表一个校验和就退化到mysql自带命令CHECKSUM TABLE了，不仅需要锁表，而且不准确。pt-table-checksum将表按用户设置的块大小，将表分成若干份，然后对每个块计算一个校验和。这样即使表特别大，分块后也只会锁住部分记录，对DB的负载压力也大大降低。由于多个表校验可以并发，可以大大提高校验效率，通过参数-max-load可以防止load过大。

pt-table-checksum基本能满足我们的日常需求，但是它还有一些需要完善的地方，首先，仅仅支持表粒度的并发，当检查一个大表时，需要耗费大量的时间，另外多表并行执行时，并行度也不能通过参数的设置，而是通过--max-load间接设置。其次，通过分块生成校验和虽然加快了校验速度，但1000行算一个4字节的校验值(默认是一个块1000行)，产生冲突的可能性很大，虽然pt-table-checksum设计的校验和算法很复杂。最后，由于校验和sql是分别在主库和备库上面执行，存在一定的时差，若在这个时间段，有新的数据写入，就会造成误判。所以哪位同学有兴趣，还可以对其进行进一步优化。

最后，我简单介绍下pt-table-checksum的使用，关于里面的参数的配置我就不一一列举了，感兴趣的同学可以参考http://www.percona.com/doc/percona-toolkit/2.2/pt-table-checksum.html

1.创建用于校验的用户，并授权

grant all privileges on *.* to ptcheck@'%' identified by 'ptcheck';

2.测试table_pt_check表结构

Create Table: CREATE TABLE `table_pt_check` (

`c1` int(11) NOT NULL AUTO_INCREMENT,

`c2` int(11) DEFAULT NULL,

PRIMARY KEY (`c1`)

) ENGINE=InnoDB AUTO_INCREMENT=26672747 DEFAULT CHARSET=utf8

3.校验chuck库中 table_pt_check表

pt-table-checksum --host='127.0.0.1' --user='ptcheck' --password='ptcheck' --port=3306 --databases='chuck' --tables='table_pt_check' --replicate=test.checksums

--replicate=test.checksums，指定校验结果存储在test库中的checksums中。通过上述3个步骤就能检查主备库的数据是否一致了。

校验结果存储表结构如下：

Create Table: CREATE TABLE `checksums` (

`db` char(64) NOT NULL, //库名

`tbl` char(64) NOT NULL, //表名

`chunk` int(11) NOT NULL, //分块号

`chunk_time` float DEFAULT NULL, //分块执行时间

`chunk_index` varchar(200) DEFAULT NULL, //分块使用的索引，主键索引或唯一索引

`lower_boundary` text, //分块的下界值

`upper_boundary` text, //分块的上界值

`this_crc` char(40) NOT NULL, //分块的哈希值

`this_cnt` int(11) NOT NULL, //分块的记录数目

`master_crc` char(40) DEFAULT NULL, //master上分块的哈希值

`master_cnt` int(11) DEFAULT NULL, //master上分块的记录数目

`ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

PRIMARY KEY (`db`,`tbl`,`chunk`),

KEY `ts_db_tbl` (`ts`,`db`,`tbl`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

校验主备是否一致的SQL如下：

SELECT db,

tbl,

Sum(this_cnt) AS total_rows,

Count(*) AS chunks

FROM test.checksums

WHERE ( master_cnt <> this_cnt

OR master_crc <> this_crc

OR Isnull(master_crc) <> Isnull(this_crc) )

GROUP BY db, tbl;

通过--explain参数可以展示pt-table-checksum在执行过程的SQL：

replace INTO `test`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc)

select 'chuck', 'table_pt_check', '7', 'PRIMARY', '21685456', '26100570', COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `c1`, `c2`, CONCAT(ISNULL(`c2`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `chuck`.`table_pt_check` FORCE INDEX(`PRIMARY`) WHERE ((`c1` >= '21685456')) AND ((`c1` <= '26100570'))

注意：计算校验和的关键函数BIT_XOR，通过这个聚合函数，将分块中每一行每一列的纳入计算对象，理论上保证了通过一个校验和可以判断主备分块数据是否一致。