主从的一致性校验 场景： 有人会问道：如何验证主从的一致性 又或者问：一个库里有几十张表 主从结构数据是否一致？ 简单来讲可以在低峰期主从上分别使用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: 564426
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(64) NOT NULL,
tbl char(64) 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,
