MySQL主从的一致性校验及修复

主从的一致性校验 场景： 有人会问道：如何验证主从的一致性 又或者问：一个库里有几十张表 主从结构数据是否一致？ 简单来讲可以在低峰期主从上分别使用select count(*)来看一下，这种方式是最古老的，准确度不是很高 盗贴 麻烦 说一声，本文来自 yijiu.blo

主从的一致性校验

场景：

有人会问道：如何验证主从的一致性

又或者问：一个库里有几十张表　主从结构数据是否一致？

简单来讲可以在低峰期主从上分别使用select count(*)来看一下，这种方式是最古老的，准确度不是很高

盗贴 麻烦 说一声，本文来自 yijiu.blog.51cto.com

主流方法：

使用pt-table-checksum验证主从的一致性

盗贴 麻烦 说一声，本 文l来自 yijiu.blog.51cto.com

Pt-table-checksum的工作流程：

在某些数据不超过1千行则立刻显示出；如果超过1千行，会进行分段，至于如何分段其内部有特定机制

计算之后会在主库建一个表。默认表名为checksums

生成表后将session 会话级别的binlog格式改为语句级别格式，而后对每张表进行以下语句：

select .. count (*), bit_xor(crc32(#id#col1#col2...))from tb where id > xxx and id

而后执行以下函数

#主要是将一个列拼成了一个用#号隔开的字符串

replace into checksums select .. count(*), bit_xor(crc32(#id#col1#col2...))from tb where id>xxx and id

将语句全部写到checksums表中，这样如果主库执行了crc32函数的话，那么从库再执这些列进行crc32匹配，如果值不一样的话，则数据不一致

因为语句级的复制，只是将语句传递到不同的库去执行，这样的话不管有多少个从库都会将在主库将此语句跑完而后在从库上也执行此条语句并在从库执行

执行完后将语句全部写chechksums中，而后通过pt-table-checksum去读这个表，是否跟主库的crc32去对比，如果不一样则认为数据是不正确的，而且会分段列出，而后尝试修复

pt-table-checksum修复的过程

依旧将语句改为行格式，而后在主库执行replace into，其作用是主要修复主库上存在的语句，而从库没有的数据，或者主从都有主键，但是从库和主库的数据有冲突

另外一种是从库存在数据，而主库没有数据，则在主库执行delete操作，而后将语句记录在binlog中，再将binlog同步到relay log使从库上删掉

盗贴 a麻烦 说一d声，本 文l来自 yijiu.blog.51cto.com

安装pt-table-checksum

下载pt-table-checksum

wget http://www.percona.com/get/percona-toolkit.tar.gz

或者使用yum安装，需要指定epel

[root@mysql_node1 ~]# yum install percona-toolkit

pt-table-checksum的参数

--recursion-method 指定找出从库的方法

--recursion-method

METHOD USES
========================================================
processlist SHOW PROCESSLIST
hosts SHOW SLAVE HOSTS
cluster SHOW STATUS LIKE 'wsrep\_incoming\_addresses'
dsn=DSN DSNs from a table
none Do not find slaves

盗贴 a麻烦 说一d声，本 文l来自 yijiu .blo g.51cto.com

--recursion-method=processlist

执行结果显示参数意义：

TS ：完成检查的时间。

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

DIFFS ：0表示一致，大于0表示不一致。当指定--no-replicate-check时，会一直为0，当指定--replicate-check-only会显示不同的信息。

ROWS ：表的行数。

CHUNKS ：被划分到表中的块的数目。 比如个表N条记录，那么会分成几十个chunks 每个N行的去检测

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

TIME ：执行的时间。

TABLE ：被检查的表名。

例：

pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=testdb.checksums --create-replicate-table --databases=testdb --tables=t5 -h 1.1.1.1 -P 3306 -u testdb -p testdatabase --recursion-method="processlist"

参数解释：

nocheck-replication-filters #不检查加载项

no-check-binlog-format 不检查binlog

replicate = testdb.checksums #checksums表在哪个库中

create-replicate-table #上面的库或表如果不存在则自动创建

databases=testdb #表示检查哪个库，如果检查整个库里面表，那么后面的--tables=t5 参数可以去掉，但是生产环境中表很多或者很大，或者每天晚上就跑一张表则需要加下面参数

tables=t5

盗贴 a麻烦 说一w声，本 文l来自 yijiu .blo g.51cto.com

执行完后最后查看结果，如果是diffs出现大于0则表示不一致

检测当前数据库test110库是否错误，并查看结果

[root@mysql_node1 ~]# pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=testdb.checksums --create-replicate-table --databases=test110 -h 10.12.33.61 -P 3306 -u root -p mypass --recursion-method="processlist"

Diffs cannot be detected because no slaves were found. Please read the --recursion-method documentation for information.

TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE

11-12T14:03:11 0 0 1000 1 0 0.016 test110.yw

尝试模拟出错并使用pt-table-checksum进行检测

在从库删除某条信息然后再次检测

在从库上操作：

首先忽略错误

盗贴 a麻烦 说一w声，本 文al来自 yijiu.blo g.51 cto.com

mysql> stop slave;

Query OK, 0 rows affected (0.00 sec)

mysql> set global sql_slave_skip_counter = 1;

Query OK, 0 rows affected (0.00 sec)

主库执行

mysql> call insert_yw(1);

Query OK, 1 row affected (0.01 sec)

开启从库

mysql>start slave;

检测一致性

再次在主库上执行tp-table-checksum

[root@node1 tools]# pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=testdb.checksums --create-replicate-table --databases=test110 -h 10.12.33.58 -P 3306 -u root -p mypass --recursion-method="processlist"

TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE

11-12T16:40:55 0 1 1011 1 0 0.044 test110.yw

修复不一致

修复不一致可以直接调用pt-table-sync脚本

pt-table-sync工作流程

首先将sql打印出来，然后再去执行execute

比如，之前的库发现不一致，那么接下来使用pt-table-sync进行修复

如下所示

#pt-table-sync --replicate=testdb.checksums --databases=testdb --charset=utf8 h=10.12.33.58,u=testdb,p=testdbtestdb -print

参数解释：

#指定哪个库、字符集以及连接进来的DSN

#DSN: h=10.12.33.58,u=testdb,p=testdbtestdb

#-print 表示只打印出来修复的SQL,但是不执行

尝试修复

pt-table-sync --replicate=testdb.checksums --databases=testdb --charset=utf8 h=10.12.33.58,u=testdb,p=testdbtestdb --execute

#主库完后在从库修改数据，并执行checksum

盗贴麻烦 说一w声，本 文来自 yijiu.blo g.51 cto.com

[root@node1 tools]# pt-table-sync --replicate=testdb.checksums --databases=test110 h=10.12.33.58,u=root,p=mypass --execute

[root@node1 tools]# echo $?

0

[root@node1 tools]# pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=testdb.checksums --create-replicate-table --databases=test110 -h 10.12.33.58 -P 3306 -u root -p mypass --recursion-method="processlist"

TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE

11-12T16:49:12 0 0 1011 1 0 0.068 test110.yw

案例

比如一个很大的库，白天发生数据的不一致，尝试着手动修复，比如1062 1032等

但是1032的数据非常的多，这个时间从库一方面可以下线并重做

（1062,1032 可以使用slave_skip_errors=all 方式，然后用check-sum 进行修复）

另一种方法将从库不提供服务，并执行顺序：

slave_skip_errors = all #将所有的错误都跳过，让从库继续工作

pt-table-checksum #在业务低峰期的时候执行，对出现错误的表进行检查并尝试修复

pt-table-sync #把不一致的数据补上来

#将slave_skip_errors = all关闭

check-sum #唯一的不足是将数据全部读完，会将buffer pool热数据冲掉

盗贴麻烦 说一w声，本 文来自 yijiu.blog.51 cto.com

模拟场景

从库执行

mysql> call insert_yw(1);

主库操作

mysql> call insert_yw(1);

Query OK, 1 row affected (0.01 sec)

再次查看从库

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 10.12.33.58

Master_User: repl

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000002

Read_Master_Log_Pos: 573305

Relay_Log_File: node2-relay-bin.000003

Relay_Log_Pos: 5426

Relay_Master_Log_File: mysql-bin.000002

Slave_IO_Running: Yes

Slave_SQL_Running: No

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 1062

Last_Error: Could not execute Write_rows event on table test110.yw; Duplicate entry '1013' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000002, end_log_pos 572982

Skip_Counter: 0

如果线程不工作的话 pt-table-checksum是无法检测的，所以要将线程启动

盗贴麻烦 说一a声，本 文来自 yijiu.blog.51 cto.com

将所有错误跳过

[root@node2 mysql]# grep errors /etc/my.cnf

slave_skip_errors = all

并重启服务

使用pt-table-checksum检测

[root@node1 tools]# pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=testdb.checksums --create-replicate-table --databases=test110 -h node1.test.com -P 3306 -u root -p mypass --recursion-method="processlist"

TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE

11-12T17:19:33 0 1 1015 1 0 0.033 test110.yw

进行修复

[root@node1 tools]# pt-table-sync --replicate=testdb.checksums --databases=test110 h=10.12.33.58,u=root,p=mypass --execute

[root@node1 tools]# echo $?

0

在双方都确认行数

mysql> select count(*) from yw;

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

| count(*) |

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

| 1015 |

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

1 row in set (0.00 sec)

面试中会问到tp-check-sum的不足：

1.会将所有数据重新读一遍

2.读数据的同时会将buffer池中的热数据冲掉

3.在主库有大量的读,但是不会锁表是个非常不错的功能

如果是库非常小的话，或者几百G的数据，直接将重建主从即可

如果全库修复的话，量大的话则需要5、6个小时，具体需要机器配置

盗贴麻烦 说一a声，本文来自 yijiu.blog.51 cto.com

pt-table-checksum的工作过程

在主库将general log 将主从都打开并运行pt-table-checksum进行观察

mysql> set global general_log=1;

Query OK, 0 rows affected (0.00 sec)

mysql> show global variables like '%gen%';

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

| Variable_name | Value |

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

| general_log | ON |

| general_log_file | /mydata/data/node2.log |

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

2 rows in set (0.00 sec)

Pt-table-checksum的工作流程：

在某些数据不超过1千行则立刻显示出；如果超过1千行，会进行分段，至于如何分段其内部有特定机制

开启general log后在主库执行tp-table-checksum

[root@node1 ~]# pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate=testdb.checksums --create-replicate-table --databases=test110 -h node1.test.com -P 3306 -u root -p mypass --recursion-method="processlist"

TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE

11-13T16:25:47 0 0 1015 1 0 0.048 test110.yw

主库：

[root@node1 ~]# cat /mydata/data/node1.log

141113 16:25:47 63 Connect root@node1.test.com on

63 Queryset autocommit=1

63 QuerySELECT @@SQL_MODE

63 QuerySHOW VARIABLES LIKE 'innodb\_lock_wait_timeout'

63 QuerySET SESSION innodb_lock_wait_timeout=1

63 QuerySHOW VARIABLES LIKE 'wait\_timeout'

63 QuerySET SESSION wait_timeout=10000

63 QuerySET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION'*/

63 QuerySELECT @@server_id /*!50038 , @@hostname*/

63 QuerySHOW VARIABLES LIKE 'version%'

63 QuerySHOW ENGINES

63 QuerySHOW VARIABLES LIKE 'innodb_version'

63 QuerySELECT @@binlog_format

#生成表后将session 会话级别的binlog格式改为语句级别格式

63 Query/*!50108 SET @@binlog_format := 'STATEMENT'*/ #更改日志格式为STAEMENT

63 QuerySET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ

63 QuerySHOW VARIABLES LIKE 'wsrep_on'

63 QuerySELECT @@SERVER_ID

63 QuerySHOW GRANTS FOR CURRENT_USER()

63 QuerySHOW PROCESSLIST

63 QuerySELECT @@server_id

63 QuerySELECT @@server_id

63 QuerySHOW DATABASES LIKE 'testdb'

63 QueryCREATE DATABASE IF NOT EXISTS `testdb` /* pt-table-checksum */

63 QueryUSE `testdb`

63 QuerySHOW TABLES FROM `testdb` LIKE 'checksums'

#经过上面一系列检测、创建库、进入库，接下来创建checksums表

63 QueryCREATE TABLE IF NOT EXISTS `testdb`.`checksums` (

db char() NOT NULL,

tbl char() NOT NULL,

chunk int NOT NULL,

chunk_time float NULL,

chunk_index varchar(200) NULL,

lower_boundary text NULL,

upper_boundary text NULL,

this_crc char(40) NOT NULL,

this_cnt int NOT NULL,

master_crc char(40) NULL,

master_cnt int NULL,