MySQL主从复制数据一致性校验和修复方法及自动化实现

微信扫1扫关注我的公众号或搜索添加“MySQL技术的学习分享”，可以更快速更实时地获得我的最新文章。

1.    引言

“MySQL主从复制”技术在互联网行业常见高可用架构中利用非常广泛，例如常见的1主1从复制架构、keepalived＋MySQL双主（主从）复制架构、MHA＋1主两从复制架构等等都利用了MySQL主从复制技术。但因主从复制是基于binlog的逻辑复制，难免出现复制数据不1致的风险，这个风险不但会引发用户数据访问前后不1致的风险，而且会致使后续复制出现1032、1062毛病进而引发复制架构停滞的隐患，为了及时发现并解决这个问题，我们需要定期或不定期地展开主从复制数据1致性的校验和修复工作，那末如何实现这项工作呢？又如何实现这项工作的自动化呢？我们来探讨这些问题。

2.    数据1致性校验和修复方法

为了实现主从复制数据1致性校验和修复，我们首先推荐两个热门工具，分别是percona公司的 pt-table-checksum和pt-table-sync，前者用来实现主从复制数据1致性的校验，后者实现数据修复，将数据修复到1致。

2.1        工作原理

        pt-table-checksum通过SQL在主库履行数据块的校验，再将相同的语句传送到从库，并在从库上计算数据块的校验，最后将主从库相同块的校验值进行对照，辨别主从数据是不是不1致。

pt-table-sync用来修复主从复制数据的不1致，使得它们修复到终究1致，也能够实现多个实例或是利用双写或多写的多个不相干的数据库实例修复到1致。同时它还内部集成了pt-table-checksum的校验功能，可以1边校验1边修复，也能够基于pt-table-checksum的计算结果来进行修复。

2.2        下载方法

     这两个工具均包括在percona-toolkit里，线上下载地址：  https://www.percona.com/downloads/percona-toolkit/2.2.2/。

     在装备上直接下载的指令以下，下载后解压使用：wget https://www.percona.com/downloads/percona-toolkit/2.2.2/percona-toolkit⑵.2.2.tar.gz

2.3        校验和修复方法

（1）在主库创建校验账号

GRANTUPDATE,INSERT,DELETE,SELECT, PROCESS, SUPER, REPLICATION SLAVE ON *.* TO 'hangxing'@'MasterIP'identified by 'PASSWORD';

GRANTALL ON test.* TO 'hangxing'@'MasterIP' IDENTIFIED BY 'PASSWORD';

（2）在主库创建校验信息表

CREATETABLE IF NOT EXISTS checksums (

db char(64)NOT NULL,

tblchar(64) NOT NULL,

chunk intNOT NULL,

chunk_timefloat NULL,

chunk_indexvarchar(200) NULL,

lower_boundarytext NULL,

upper_boundarytext NULL,

this_crcchar(40) NOT NULL,

this_cntint NOT NULL,

master_crcchar(40) NULL,

master_cntint NULL,

tstimestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

PRIMARY KEY(db, tbl, chunk),

INDEXts_db_tbl (ts, db, tbl)

)ENGINE=InnoDB;

（3）判断主键

若无主键做校验和修复对性能影响非常重，数据校验和修复最重要的束缚便是主健，无主键或唯1索引，将致使修复不成功。

主键判断语句：

SELECTDISTINCT CONCAT(t.table_schema,'.',t.table_name) astbl,t.engine,IF(ISNULL(c.constraint_name),'NOPK','') AS nopk,IF(s.index_type ='FULLTEXT','FULLTEXT','') as ftidx,IF(s.index_type = 'SPATIAL','SPATIAL','') asgisidx FROM information_schema.tables AS t LEFT JOINinformation_schema.key_column_usage AS c ON (t.table_schema =c.constraint_schema AND t.table_name = c.table_name AND c.constraint_name ='PRIMARY') LEFT JOIN information_schema.statistics AS s ON (t.table_schema =s.table_schema AND t.table_name = s.table_name AND s.index_type IN('FULLTEXT','SPATIAL')) WHERE t.table_schema NOT IN('information_schema','performance_schema','mysql') AND t.table_type = 'BASETABLE' AND (t.engine <> 'InnoDB' OR c.constraint_name IS NULL ORs.index_type IN ('FULLTEXT','SPATIAL')) ORDER BY t.table_schema,t.table_name;

（4）主从数据校验

       主从数据校验使用pt-table-checksum实现，要在主库上履行，履行校验通过参数控制校验全库全表还是只校验核心表。

校验指令举例：

./pt-table-checksum--nocheck-binlog-format --nocheck-plan --nocheck-replication-filters--replicate=test.checksums  --databases=db1--tables=tb1 -h 192.168.XXX.XX -P 3306-u'hangxing' -p'PASSOWRD' --recursion-method="processlist"

解析： 

--no-check-binlog-format 不检查复制的binlog模式。

       --nocheck-replication-filters 不检查复制过滤器，建议启用。

--replicate=test.checksums 检查结果写入test库的checksums表里。

--databases=db1  --tables=tb1 校验db1库里的tb1表，若无参数则校验全库全表。

-h 192.168.XXX.XX -P 3306 主库IP地址和3306端口。

-u'hangxing' -p'PASSOWRD' 校验账号密码。

--recursion-method="processlist" 用processlist的方法来发现从库。

      履行后的输出结果：

TS   ERRORS      DIFFS      ROWS  CHUNKS  SKIPPED   TIME  TABLE

03⑵3T15:29:17    0     1    30000       1       0  1.270 testhx1.testhx1

解析：

TS            ：完成检查的时间。 

ERRORS     ：检查时候产生毛病和正告的数量。 

DIFFS        ：0表示1致，大于0表示不1致。主要看这1列有没有不1致数据。

ROWS       ：表的行数。 

CHUNKS    ：被划分到表中的块的数目。 

SKIPPED    ：由于毛病或正告或过大，则跳过块的数目。 

TIME         ：履行的时间。 

TABLE       ：被检查的表名。 

    上述输出关键看DIFFS列，结果为0说明数据1致，无需进行数据修复，如果不为0则需要继续展开数据1致性修复工作。上述语句履行后也会将详细的内容会写入test库的checksums表中，可以查看这个库表得到详细的数据校验信息，此表中信息内容格式举例以下：  

        主库的test.checksums中输出this_crc和master_crc，无不1致。

mysql> select * fromtest.checksums;

+---------+---------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+

| db      | tbl    | chunk | chunk_time | chunk_index | lower_boundary | upper_boundary | this_crc | this_cnt | master_crc| master_cnt | ts                  |

+---------+---------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+

| testhx1 | testhx1 |     1 |