Written by arstercz - 2014-03-10

MySQL主从数据一致性校验

pt-table-checksum如何校验主从数据的一致性

一.综述

在MySQL中，master和slave的数据一致性校验是一个繁琐且重要的环节，在master和slave同时对外提供服务的业务中, 保证数据的一致性更为重要；这个要求提出如何校验一致性的问题, 在校验的过程中，如何避免对线上业务造成冲击，不影响主从复制等都需要我们密切关注, 下面部分详细介绍如何通过percona-toolkit的pt-table_checksum校验主从的数据一致性。

二.词语解释

chunk: 分组校验，对大表来说，分组校验是个很好的方法，可以避免引起slave过多的delay于master, 同时也避免了wait_timeout参数影响脚本的执行; CRC32: 循环冗余校验，脚本通过校验码来判断是否存在数据不一致,使用者可以通过method方法更改校验的方法，如MD5, SHA1, UDF等; key: 确保要校验的表有主键或唯一键(没有唯一性,MySQL通过_rowid来标识唯一性),pt-table-checksum通过键来确保待校验行数的一致；其它: 其它性能优化参数(lag, load, current thread等)见 http://www.percona.com/doc/percona-toolkit/2.2/pt-table-checksum.html

三.校验过程说明

[root@cz ~]# pt-table-checksum h=10.3.254.110,u=root,p=xxxxxx,P=30587 --databases="part1" --tables="book" --nocheck-replication-filters 
            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
04-22T16:09:46      0      1   168949       4       0   1.218 part1.book

#pt-table-checksum h=10.3.254.110,u=root,p=qwer1234,P=30587 –databases=”part1” –tables=”book” –nocheck-replication-filters

注:DSN中的h,u,p确保能够使脚本连接master和slave.

--
-- part1.book
--

REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT ?, ?, ?, ?, ?, ?, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `id`, `name`, `comment`, CONCAT(ISNULL(`name`), ISNULL(`comment`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `part1`.`book` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) AND ((`id` <= ?)) /*checksum chunk*/

REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT ?, ?, ?, ?, ?, ?, COUNT(*), '0' FROM `part1`.`book` FORCE INDEX(`PRIMARY`) WHERE ((`id` < ?)) ORDER BY `id` /*past lower chunk*/ 

REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT ?, ?, ?, ?, ?, ?, COUNT(*), '0' FROM `part1`.`book` FORCE INDEX(`PRIMARY`) WHERE ((`id` > ?)) ORDER BY `id` /*past upper chunk*/ SELECT /*!40001 SQL_NO_CACHE */ `id` FROM `part1`.`book` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= ?)) ORDER BY `id` LIMIT ?, 2 /*next chunk boundary*/

checksums表结构:

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_cnt` int(11) DEFAULT NULL,
  `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=latin1

从执行计划来看，

REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'part1', 'book', '1', 'PRIMARY', '1', '1501', COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `id`, `name`, `comment`, CONCAT(ISNULL(`name`), ISNULL(`comment`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `part1`.`book` FORCE INDEX(`PRIMARY`) WHERE ((`id` >= '1')) AND ((`id` <= '3')) /*checksum chunk*/;

如何产生指定行的校验码，按照chunk方式取出指定的行数据，对整个chunk做CRC32校验，并将校验值转换为16进制的值串,再做其它处理，如下:

— CONCAT_WS 连接字串信息，通过第一个参数’#’连接指定的信息;

    +------------------------------------------------------------------------------------+
    | CONCAT_WS('#', `id`, `name`, `comment`, CONCAT(ISNULL(`name`), ISNULL(`comment`))) |
    +------------------------------------------------------------------------------------+
    | 1#黄岛#热血，激情，爽文#00                                               |
    | 2#砍途#都市玄幻,热血青春#00                                              |
    | 3#王梓一声#穿越爽文,拯救世界#00                                        |
    +------------------------------------------------------------------------------------+

— CRC32 求冗余校验值

    +-------------------------------------------------------------------------------------------+
    | crc32(CONCAT_WS('#', `id`, `name`, `comment`, CONCAT(ISNULL(`name`), ISNULL(`comment`)))) |
    +-------------------------------------------------------------------------------------------+
    |                                                                                 418643389 |
    |                                                                                2289975389 |
    |                                                                                1403158700 |
    +-------------------------------------------------------------------------------------------+

+— CAST 声明为无符号类型

    +-------------------------------------------------------------------------------------------------------------+
    | cast(crc32(CONCAT_WS('#', `id`, `name`, `comment`, CONCAT(ISNULL(`name`), ISNULL(`comment`)))) AS UNSIGNED) |
    +-------------------------------------------------------------------------------------------------------------+
    |                                                                                                   418643389 |
    |                                                                                                  2289975389 |
    |                                                                                                  1403158700 |
    +-------------------------------------------------------------------------------------------------------------+

— BIT_XOR 异或求值, 多条记录异或后生成一条信息, 理论上讲异或的方式有一定的概率相同的结果，见下文异或的运算.

    +----------------------------------------------------------------------------------------------------------------------+
    | BIT_XOR(cast(crc32(CONCAT_WS('#', `id`, `name`, `comment`, CONCAT(ISNULL(`name`), ISNULL(`comment`)))) AS UNSIGNED)) |
    +----------------------------------------------------------------------------------------------------------------------+
    |                                                                                                           3274653004 |
    +----------------------------------------------------------------------------------------------------------------------+

      11000111100111111110110111101
    1010011101000101000000010101100
   10001000011111100100000001011101

XOR

   11000011001011110011110101001100    = 3274653004

— CONV 转换10进制到16进制

    +----------------------------------------------------------------------------------------------------------------------------------+
    | conv(BIT_XOR(cast(crc32(CONCAT_WS('#', `id`, `name`, `comment`, CONCAT(ISNULL(`name`), ISNULL(`comment`)))) AS UNSIGNED)),10,16) |
    +----------------------------------------------------------------------------------------------------------------------------------+
    | C32F3D4C                                                                                                                         |
    +----------------------------------------------------------------------------------------------------------------------------------+

— LOWER 大写转小写

    +-----------------------------------------------------------------------------------------------------------------------------------------+
    | lower(conv(BIT_XOR(cast(crc32(CONCAT_WS('#', `id`, `name`, `comment`, CONCAT(ISNULL(`name`), ISNULL(`comment`)))) AS UNSIGNED)),10,16)) |
    +-----------------------------------------------------------------------------------------------------------------------------------------+
    | c32f3d4c                                                                                                                                |
    +-----------------------------------------------------------------------------------------------------------------------------------------+

— COALESCE 返回表达式中第一个非NULL值

    +-----------------------------------------------------------------------------------------------------------------------------------------------------+
    | COALESCE(lower(conv(BIT_XOR(cast(crc32(CONCAT_WS('#', `id`, `name`, `comment`, CONCAT(ISNULL(`name`), ISNULL(`comment`)))) AS UNSIGNED)),10,16)),0) |
    +-----------------------------------------------------------------------------------------------------------------------------------------------------+
    | c32f3d4c                                                                                                                                            |
    +-----------------------------------------------------------------------------------------------------------------------------------------------------+

master中找出chunk 1的this_crc和this_cnt作为checksums表的master_crc和master_cnt:

SELECT this_crc, this_cnt FROM `percona`.`checksums` WHERE db = 'part1' AND tbl = 'book' AND chunk = '1';
UPDATE `percona`.`checksums` SET chunk_time = '0.002874', master_crc = 'c94c8baa', master_cnt = '1000' WHERE db = 'part1' AND tbl = 'book' AND chunk = '1';

slave依旧执行上述的校验和操作，并更新在slave更新this_crc和this_cnt;详见slave的general log文件.

slave更新几行数据，比较主从的一致性，在slave上执行单表的比较, cnt_diff表示主从数据行数是否一致,为int型，该值体现了slave和master在同chunk组内相差多少行记录，可能为整数或负数；crc_diff表示主从在chunk 1中存在数据不一致的问题,该值为布尔值:

slave2 [localhost] {root} (percona) > SELECT CONCAT(db, '.', tbl) AS `table`, chunk, chunk_index, lower_boundary, upper_boundary, COALESCE(this_cnt-master_cnt, 0) AS cnt_diff, COALESCE(this_crc <> master_crc OR ISNULL(master_crc) <> ISNULL(this_crc), 0) AS crc_diff, this_cnt, master_cnt, this_crc, master_crc FROM  `percona`.`checksums` WHERE (master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc)) AND (db='part1' AND tbl='book');
+------------+-------+-------------+----------------+----------------+----------+----------+----------+------------+----------+------------+
| table      | chunk | chunk_index | lower_boundary | upper_boundary | cnt_diff | crc_diff | this_cnt | master_cnt | this_crc | master_crc |
+------------+-------+-------------+----------------+----------------+----------+----------+----------+------------+----------+------------+
| part1.book |     1 | PRIMARY     | 1              | 1501           |        0 |        1 |     1000 |       1000 | b5b49cc5 | 44c74af1   |
+------------+-------+-------------+----------------+----------------+----------+----------+----------+------------+----------+------------+

四.线上操作说明

任何对MySQL的修改操作都会对线上DB有影响，管理员操作的时候确保在低峰期，阅读doc文件设置load, lag,chunk-size相关参数确保slave延迟较大的时候pt-table_checksum脚本可以暂停执行而不会影响业务的访问;
确定是否库里的每张表都需要校验，非强一致性要求的表数据可以不用校验，强一致性的表(比如充值信息)建议定期校验;

多表校验后，可以使用以下SQL查看一致性检查的整体情况

SELECT db, tbl, SUM(this_cnt) AS total_rows, COUNT(*) AS chunks FROM checksums WHERE ( master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc)) GROUP BY db, tbl;

← → Top