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

1.    引言

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

 

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

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

2.1        工作原理

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

pt-table-sync用来修复主从复制数据的不一致，使得它们修复到最终一致，也可以实现多个实例或者是应用双写或多写的多个不相关的数据库实例修复到一致。同时它还内部集成了pt-table-checksum的校验功能，可以一边校验一边修复，也可以基于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.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）判断主键

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

主键判断语句：

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 <> &#39;InnoDB&#39; OR c.constraint_name IS NULL ORs.index_type IN (&#39;FULLTEXT&#39;,&#39;SPATIAL&#39;)) 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-23T15:29:17    0     1    30000       1       0  1.270 testhx1.testhx1

解析：

TS ：完成检查的时间。

ERRORS ：检查时候发生错误和警告的数量。

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

ROWS ：表的行数。

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

SKIPPED ：由于错误或警告或过大，则跳过块的数目。

TIME ：执行的时间。

TABLE ：被检查的表名。

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

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

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 |  0.003661 | NULL        | NULL   | NULL| cac6c46f| 4 | cac6c46f  |  4 | 2016-03-23 15:29:16 |
+---------+---------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------

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

1 row in set (0.00 sec)

从库的test.checksums中输出this_crc和master_crc，不一致。

mysql>select * from checksums;
+---------+---------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+
|db      | tbl     | chunk | chunk_time | chunk_index |lower_boundary | upper_boundary | this_crc |this_cnt |master_crc | master_cnt|ts                |
+---------+---------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+
|testhx1 | testhx1 |     1 |   0.003661 | NULL    | NULL   | NULL  | 7c2e5f75|  5 | cac6c46f  |  4 | 2016-03-23 15:29:16 |
+---------+---------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+
1row in set (0.00 sec)

（5）主从数据修复

用pt-table-checksum工具确定确实存在数据不一致的情况下开始修复数据，数据修复使用工具pt-table-sync，内带校验功能，但前提是修复的表必须要有主键，这个工具也要在主库上执行。

方法1:语句量大的情况下将修复的语句导入到sql文件中，再直接导入执行

在主库用pt-table-sync打印出修复不一致数据的SQL，后将修复语句在从库执行。

举例：

pt-table-sync --print--sync-to-master h='SlaveIP',P=3306,u=hangxing,p='PASSWORD' --databases=db1--tables=tb1 > /tmp/repair.sql

方法2:语句量不大的情况下，将修复的语句print出来，再execute

举例：

打印数据修复语句

pt-table-sync--print --sync-to-master h='SlaveIP',P=3306,u=hangxing,p=' PASSWORD '--databases=testhx1 --tables=testhx1
DELETE FROM`testhx1`.`testhx1` WHERE `id`='11' LIMIT 1 /*percona-toolkit src_db:testhx1src_tbl:testhx1 src_dsn:P=3306,h=’MasterIP’,
p=...,u=checksums dst_db:testhx1dst_tbl:testhx1 dst_dsn:P=3306,h='SlaveIP',p=...,u=checksums lock:1transaction:1 changing_src:1 replicate:0 bidirectional:0 
pid:24745 user:hangxinghost:XXXXXXXXXX*/;
REPLACEINTO `testhx1`.`testhx1`(`name`, `age`, `id`) VALUES ('bobby', '6', '7')/*percona-toolkit 
src_db:testhx1 src_tbl:testhx1 src_dsn:P=3306,h=’MasterIP’,
p=...,u=hangxingdst_db:testhx1 dst_tbl:testhx1 dst_dsn:P=3306,h=’SlaveIP’,p=...,u=hangxinglock:1 transaction:1 changing_src:1 replicate:0 bidirectional:0 
pid:24745user:root host: XXXXXXXXXX */;REPLACEINTO `testhx1`.`testhx1`(`name`, `age`, `id`) VALUES ('lily', '5', '9')/*percona-toolkit 
src_db:testhx1 src_tbl:testhx1 src_dsn:P=3306,h=’MasterIP’,p=...,u=hangxing  
dst_db:testhx1 dst_tbl:testhx1dst_dsn:P=3306,h=’SlaveIP’,p=...,u=hangxing lock:1 transaction:1 changing_src:1replicate:0 bidirectional:0 
pid:24745 user:root host: XXXXXXXXXX */;

执行数据修复语句

pt-table-sync--execute --sync-to-master h='SlaveIP',P=3306,u=hangxing,p='PASSWORD'--databases=testhx1 --tables=testhx1

（6）再次校验

上述修复完成之后，需要再次执行一次数据校验，确保数据成功修复，校验方法同（4）主从数据校验。

2.4 值得注意的点

（1）校验修复工作每月定期开展；

（2）主从复制架构在割接操作前后均需执行数据校验和修复工作；

（3）主从复制出现故障后要开展数据校验和修复工作；

（4）校验修复需在业务低谷期进行，CPU利用率超过60%时不建议做数据校验和修复；

（5）校验和修复必须在主库进行；

（6）数据库的表要有主键，否则校验效率极差，并且修复不成功。

3.数据一致性校验和修复的自动化实现

理解上述方法后，我们可以顺利完成主从复制数据一致性的校验和修复工作，但是这项工作在MySQL主从复制架构维护中开展频率较高，包括定期和各种不定期的情况，每次都手工开展耗时耗力，并且容易出现人为错误及隐患，因此，我们考虑将这项工作通过脚本实现自动化。

3.1前提准备

创建校验账号，创建校验结果输出表，配置两台主机的ssh免密码登录。

3.2自动化实现

(1)部署自动化脚本和定时任务

理解数据一致性校验和修复的全部原理和详细步骤，将其转化为多个自动化脚本，分别部署在主从库上，每月定期执行可通过在主库制定crontab定时任务调用主从库脚本实现，不定期执行可通过手动调用主从库部署的自动化脚本来实现。

（2）自动化脚本实现步骤

a.将DB相关信息赋予对应参数，如账户密码、IP、端口、常用指令等等

dbuser=XXXX
dbpasswd="XXXXX"
port=3306
mysql_commend="mysql-u${dbuser} -p${dbpasswd} -P${port}"
master_ip=XXXXX
slave_ip=XXXXX
password="XXXXX"
date=`date+%Y%m%d`
logfile="XXXXX"
hostname=`XXXXX`

b.检查ssh免密码登录是否成功；

ssh_status=`XXXXX`
    if [ $ssh_status != $hostname ]; then
    echo -e "\nthe ssh should berepair" >$logfile
    exit
else
    echo -e "\nthe ssh is ok">$logfile
fi

c．脚本实现准备工作：包括账号密码的创建、建立校验结果输出表，代码可参见第2小节；

d．将主库的脚本执行校验和主键判断写成联合SQL语句，实现剔除无主键表的所有表的自动数据校验，并将结果存入所建表中；

selectXXXXX NOT IN XXXXX

e．从库部署检查校验结果输出表的脚本，主库执行d后自动登录从库调用这个脚本，实现对从库上输出表中校验字段的对比如master_crc 和 this_crc，找到数据不一致的表，并且通过执行调用修复工具的指令实现不一致数据修复语句的print；

master_cnt<> this_cnt OR master_crc <> this_crc OR isnull(master_crc)<> isnull(this_crc))

f．print结果自动存储从库的某个路径文件下；

intooutfile '/tmp/execute_sql.sh'

g．主库自动登录从库scp获取语句修复文件；

scp/tmp/execute_sql.sh root@$master_ip:/tmp/execute_sql.sh

h．主库上自动执行修复语句；

sh/tmp/execute_sql.sh

       i.清理掉各个中间文件，中间表等

       上述内容记录了该项工作的自动化实现思路及部分实现要点，自动化便是通过在这个思路的基础上编写主从库部署的脚本来实现，目前已亲测成功，已实现自动化的数据校验和修复，说明上述思路正确。

4.结语

    本文分享了MySQL复制数据一致性校验和修复的详细步骤及其自动化实现思路和方法，对MySQL复制架构运维中该项工作的实施及其自动化具有较好的借鉴意义。

以上就是MySQL主从复制数据一致性校验和修复方法及自动化实现的内容，更多相关内容请关注PHP中文网（www.php.cn）！