MySQL主从数据一致性校验及修复

    在主从复制环境中，可能有种种原因导致主、从库数据不一致的情况，例如网络中断，程序本身的BUG等，在错误日志中即使没有明显的提示，但并不保证数据是一致的，好在percona提供了pt-table-checksum检测工具和pt-table-sync修复工具，接下来本文将初步展示这俩工具的使用（仔细阅读官方文档后，发现里面需要理解的地方有很多，很难一下给消化掉，会在今后使用的过程注意积累并继续补充，顺例吐槽下percona pt-tookit 官方文档写得太糙了，可能它得靠服务挣钱吧）。

    本文模拟的场景如下：
   1> 复制架构为一主两从，如下所示：

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      120 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> show processlist;
+------+-------+----------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
| Id   | User  | Host                 | db   | Command     | Time | State                                                                 | Info             |
+------+-------+----------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
| 6453 | root  | localhost            | NULL | Query       |    0 | init                                                                  | show processlist |
| 6454 | slave | 192.168.15.112:54830 | NULL | Binlog Dump |  189 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL             |
| 6455 | slave | 192.168.15.98:55507  | NULL | Binlog Dump |   49 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL             |
+------+-------+----------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)

    2> 分别在两从库上delete 一表，提供主从数据不一致场景

[root@lvcx02 ~]# mysql -S  /tmp/lvtu3306.sock  -u root -p
mysql> use blues;
Database changed
mysql> show tables;
+-----------------+
| Tables_in_blues |
+-----------------+
| customer        |
| district        |
| history         |
| item            |
| new_orders      |
| order_line      |
| orders          |
| stock           |
| warehouse       |
+-----------------+
9 rows in set (0.00 sec)


mysql> select  count(*) from  order_line;
+----------+
| count(*) |
+----------+
|   299575 |
+----------+
1 row in set (0.76 sec)


mysql> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.02 sec)


mysql> delete from order_line ;
Query OK, 299575 rows affected (3.65 sec)


mysql> select  count(*) from  order_line;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.29 sec)
mysql> show global variables like '%server_id%';
+----------------+--------+
| Variable_name  | Value  |
+----------------+--------+
| server_id      | 330602 |
| server_id_bits | 32     |
+----------------+--------+
2 rows in set (0.00 sec)

[root@lvcx04 ~]# mysql -S /tmp/lvtu3306.sock  -u root -p
Enter password:
mysql> use tpcc;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------+
| Tables_in_tpcc |
+----------------+
| customer       |
| district       |
| history        |
| item           |
| new_orders     |
| order_line     |
| orders         |
| stock          |
| warehouse      |
+----------------+
9 rows in set (0.00 sec)

mysql> select count(*) from order_line ;
+----------+
| count(*) |
+----------+
|  4650236 |
+----------+
1 row in set (3 min 17.77 sec)


mysql> select count(*) from warehouse ;
+----------+
| count(*) |
+----------+
|      100 |
+----------+
1 row in set (0.97 sec)


mysql> show global variables like 'server_id';
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| server_id     | 330604 |
+---------------+--------+
1 row in set (0.00 sec)


mysql> show global variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)


mysql> delete from warehouse;
Query OK, 100 rows affected (0.45 sec)


mysql> commit;
Query OK, 0 rows affected (0.00 sec)

第三步：通过pt-table-checksum检查主从一致性，注意连接的主库，其原理为在主库上执行insert......checksum查询，并将checksum结果保存，默认保存在percona.checksums表中，并通过复制在从库执行，然后比较主从checksum的结果，以确定是否一致，是通过一张张表的检查来实现，里面有很多概念和需要注意的地方，例如chunk，复制filter,为便于理解，我特意将一些默认参数给加上了，例外由于机器性能不高，只针对上面删除的两张表进行较验，实际上是可针对整个实例、schema、table进行较验的。另外重要的事情说三遍：要提前备份；要提前备份；要提前备份。

[root@lvcx01 ~]# pt-table-checksum --socket=/tmp/lvtu3306.sock --user=root  --password=lvtu  --no-check-binlog-format --check-plan --create-replicate-table  --empty-replicate-table  --replicate=percona.checksums  --replicate-check  --check-replication-filters --no-check-plan --tables=tpcc.warehouse   
            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
11-22T03:39:46      0      1      100       1       0   0.121 tpcc.warehouse   //执行完后如果某个表主从间存在着不一致的情况，会在[DIFFS]例显示
[root@lvcx01 ~]# pt-table-checksum --socket=/tmp/lvtu3306.sock --user=root  --password=lvtu  --no-check-binlog-format --check-plan --create-replicate-table  --empty-replicate-table  --replicate=percona.checksums  --replicate-check  --check-replication-filters --no-check-plan --tables=blues.order_line
            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
11-22T03:38:17      0      5   299575       7       0   1.308 blues.order_line
默认情况：pt-table-checksum 会检查binlog format,进行checksum较验，将检查保存在percona.checksums表中[如果该表不存在，会直接创建]，各表在进行checksum前，会将该先前checksum结果清空，并检查主从间各表数据的一致性，同时会检查query plan 以及复制过滤规则

第四步：根据pt-table-checksum的结果，利用pt-table-sync将数据同步，pt-table-sync的使用方法很灵活。例如：
pt-table-sync --execute --replicate  percona.checksums   master   //将DSN指向主库，表示根据较验结果，从主库将数据同步到各从库上
pt-table-sync --execute --replicate  percona.checksums   --sync-to-master slave  //将DNS指向从库，表示根据校验结果，只将数据同步到某个从库上
需要指出的是--relicate和--sync-to-master方法同步数据，会先在主库执行操作，而不是直接操作从库
本例子采用的第一种方法，在操作前，重要的事情说三遍：要提前备份；要提前备份；要提前备份。具体内容如下：

[root@lvcx02 lvtu3306]# mysql -S  /tmp/lvtu3306.sock  -u root -p -e 'select count(*) from blues.order_line\G'
Enter password:
*************************** 1. row ***************************
count(*): 0
[root@lvcx02 lvtu3306]# mysql -S  /tmp/lvtu3306.sock  -u root -p -e 'select count(*) from tpcc.warehouse\G'  
Enter password: 
*************************** 1. row ***************************
count(*): 100


[root@lvcx04 ~]# mysql -S  /tmp/lvtu3306.sock  -u root -p -e 'select count(*) from tpcc.warehouse\G'
Enter password: 
*************************** 1. row ***************************
count(*): 0
[root@lvcx04 ~]# mysql -S  /tmp/lvtu3306.sock  -u root -p -e 'select count(*) from blues.order_line\G'
Enter password: 
*************************** 1. row ***************************
count(*): 299575
[root@lvcx02 ~]# pt-table-sync --execute --replicate  percona.checksums  h='192.168.15.117',u='root',p='lvtu' //DSN指向主库
//同步操作完后，进行后续确认
[root@lvcx02 ~]# mysql -S  /tmp/lvtu3306.sock  -u root -p -e 'select count(*) from blues.order_line\G'                     
Enter password: 
*************************** 1. row ***************************
count(*): 299575
[root@lvcx02 ~]# mysql -S  /tmp/lvtu3306.sock  -u root -p -e 'select count(*) from tpcc.warehouse\G'                       
Enter password: 
*************************** 1. row ***************************
count(*): 100

[root@lvcx04 ~]# mysql -S  /tmp/lvtu3306.sock  -u root -p -e 'select count(*) from blues.order_line\G'
Enter password: 
*************************** 1. row ***************************
count(*): 299575
[root@lvcx04 ~]# mysql -S  /tmp/lvtu3306.sock  -u root -p -e 'select count(*) from tpcc.warehouse\G'  
Enter password: 
*************************** 1. row ***************************
count(*): 100

通过查询，可以看出lvcx02所运行从库blues.order_line表和lvcx04所运行从库tpcc.warehouse表数据都已被同步。