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

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 <> '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-23T15:29:17    0    1    30000     
 1      0  1.270 testhx1.testhx1

解析:

TS         
:完成检查的时间。

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

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

ROWS      :表的行数。

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

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

TIME        :执行的时间。

TABLE      :被检查的表名。

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/39b587836e6b3fde04e1a02fe290b8e8.html