首页 > 代码库 > pt-table-checksum、pt-table-sync核对主从库一致性

pt-table-checksum、pt-table-sync核对主从库一致性

一.下载并安装工具
http://www.percona.com/downloads/percona-toolkit/
目前最新的版本是percona-toolkit_2.2.12.tar.gz
上传到服务器后,解压缩,并设置到环境变量
在mysql用户的环境变量文件增加路径
vi .bash_profile
export PATH=$PATH:/mysqldata/soft/percona-toolkit-2.2.12/bin

二.使用pt-table-checksum命令查找不一致的数据
主要关注的列是DIFFS,为0则一致,为1则不一致
-bash-4.1$ pt-table-checksum --nocheck-replication-filters --no-check-binlog-format h=localhost,u=root,p=passwd,P=3306
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
01-21T14:00:20 0 0 34 1 0 0.304 test.test_inv_log
01-21T14:00:20 0 0 9 1 0 0.304 test.test_inventory
01-21T14:00:21 0 0 2 1 0 0.061 test.test_lastexpressno
01-21T14:00:21 0 0 38 1 0 0.070 test.test_location
01-21T14:00:21 0 0 97 1 0 0.068 test.test_login_history
01-21T14:00:21 0 1 33 1 0 0.065 test.test_menu
01-21T14:00:21 0 0 11 1 0 0.061 test.test_pkd
01-21T14:00:21 0 0 1 1 0 0.061 test.test_promotion
01-21T14:00:21 0 0 1 1 0 0.064 test.test_promotion_condition
01-21T14:00:21 0 0 2 1 0 0.060 test.test_promotion_gift

实验1:在从库多插入记录,制造差异
上面标红的一行是我故意到从库手工插入的一条记录
为了制造不一致的数据,我停掉了双主复制的中的从库到主库的反向复制,关闭了只读参数,手工插入了一条记录。

pt-table-checksum还可以带很多参数,比较重要的用法如下:
-bash-4.1$ pt-table-checksum --nocheck-replication-filters --no-check-binlog-format [--replicate=rep_test.checksums --databases=rep_test --tables=test1] h=localhost,u=root,p=passwd,P=3306

中间的黑色部分,中括号里面的部分是可选的,
--nocheck-replication-filters :不检查复制过滤器,建议启用。后面可以用--databases来指定需要检查的数据库。
--no-check-binlog-format : 不检查复制的binlog模式,要是binlog模式是ROW,则会报错。
--replicate-check-only :只显示不同步的信息。
--replicate= :把checksum的信息写入到指定表中,建议直接写到被检查的数据库当中,如果不指定,默认会在主库新建一个database叫percona,检查结果存放在percona的checksums表中。
--databases= :指定需要被检查的数据库,多个则用逗号隔开。
--tables= :指定需要被检查的表,多个用逗号隔开

我们再次尝试一下,加2个好用的参数,replicate-check-only和database参数
-bash-4.1$ pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --replicate-check-only --databases=test h=localhost,u=root,p=passwd,P=3306

使用这命令效果不好,不建议带replicate-check-only参数,因为输出的列不全
只使用—databases参数指定要核对的数据库比较好
-bash-4.1$ pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --databases=test h=localhost,u=root,p=passwd,P=3306

TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
01-21T14:34:30 0 0 34 1 0 0.303 test.test_inv_log
01-21T14:34:30 0 0 9 1 0 0.061 test.test_inventory
01-21T14:34:30 0 0 2 1 0 0.306 test.test_lastexpressno
01-21T14:34:30 0 0 38 1 0 0.322 test.test_location
01-21T14:34:31 0 0 97 1 0 0.307 test.test_login_history
01-21T14:34:31 0 1 33 1 0 0.305 test.test_menu
01-21T14:34:31 0 0 11 1 0 0.060 test.test_pkd
01-21T14:34:31 0 0 1 1 0 0.055 test.test_promotion
01-21T14:34:31 0 0 1 1 0 0.057 test.test_promotion_condition
01-21T14:34:31 0 0 2 1 0 0.068 test.test_promotion_gift
01-21T14:34:31 0 0 2 1 0 0.058 test.test_promotion_rule
01-21T14:34:31 0 0 33 1 0 0.056 test.test_role
01-21T14:34:31 0 0 95 1 0 0.059 test.test_search_tab
01-21T14:34:32 0 0 25 1 0 0.303 test.test_send_template

执行后,可以发现差异,但是核查结果表percona.checksums,却发现有BUG,结果表显示无差异,结果表主要关注两列this_crc和master_crc
mysql> show tables ;
+-------------------+
| Tables_in_percona |
+-------------------+
| checksums |
+-------------------+
1 row in set (0.00 sec)


mysql> select db,tbl,chunk,this_crc,this_cnt,master_crc,master_cnt,ts from checksums ;
+-------+---------------------------+-------+----------+----------+------------+------------+---------------------+
| db | tbl | chunk | this_crc | this_cnt | master_crc | master_cnt | ts |
+-------+---------------------------+-------+----------+----------+------------+------------+---------------------+
| test | test_inv_adjust_header | 1 | 2a7935e8 | 1 | 2a7935e8 | 1 | 2015-01-21 14:34:29 |
| test | test_inv_log | 1 | efc2d050 | 34 | efc2d050 | 34 | 2015-01-21 14:34:29 |
| test | test_lastexpressno | 1 | 96b2f90d | 2 | 96b2f90d | 2 | 2015-01-21 14:34:30 |
| test | test_location | 1 | d5582f63 | 38 | d5582f63 | 38 | 2015-01-21 14:34:30 |
| test | test_login_history | 1 | db189cf9 | 97 | db189cf9 | 97 | 2015-01-21 14:34:30 |
| test | test_menu | 1 | 6046676 | 33 | 6046676 | 33 | 2015-01-21 14:34:31 |
| test | test_pkd | 1 | ee959bc2 | 11 | ee959bc2 | 11 | 2015-01-21 14:34:31 |
| test | test_promotion | 1 | 2020c504 | 1 | 2020c504 | 1 | 2015-01-21 14:34:31 |
| test | test_promotion_condition | 1 | a71da967 | 1 | a71da967 | 1 | 2015-01-21 14:34:31 |
| test | test_promotion_gift | 1 | 5d6b31f | 2 | 5d6b31f | 2 | 2015-01-21 14:34:31 |
| test | test_promotion_rule | 1 | 76daf3aa | 2 | 76daf3aa | 2 | 2015-01-21 14:34:31 |

标红的部分显示,没有发现主从库的差异
因此,我推断核查差异的时候,以命令输出为准,检查结果表的数据不准确,后面的实验否定了这个结论

实验2:修改主库的一条数据,制造差异
停止从库的slave,在主库修改一条数据,和从库内容不一致
从库:
mysql> stop slave ;
Query OK, 0 rows affected (0.02 sec)

主库:
mysql> select * from test.test_menu where id=60 \G
*************************** 1. row ***************************
id: 60
createTime: 2015-01-21 13:58:18
updateTime: 2015-01-21 13:58:20
domain_id: -1
creator_id: NULL
updator_id: NULL
status_id: NULL
version: 0
menuName: MENU_TEST2
parent_id: 54
role_id: 57
sortIndex: 6
menuDescr: TEST2
remark: NULL
udf1: NULL
udf2: NULL
udf3: NULL
udf4: NULL
1 row in set (0.00 sec)

mysql> update test.test_menu set menuDescr=‘TEST2222‘ where id=60 ;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

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

用pt-table-check命令核查,竟然没发现差异,难道是因为slave没有开启的缘故吗,但是一旦开启从库的话,同步了binlog,差异又没了,因此打算重新搭建slave,跳过几个transacation,试试看

从库执行
mysql> change master to master_host=‘192.168.2.195‘,master_user=‘repl‘,master_password=‘slave123‘,master_log_file=‘mysql-bin.000006‘,master_log_pos=692149 ;

mysql> start slave ;

配合后,主备复制正常,主备差异存在
主库:
mysql> select * from test.test_menu where id=60 \G
*************************** 1. row ***************************
id: 60
createTime: 2015-01-21 13:58:18
updateTime: 2015-01-21 13:58:20
domain_id: -1
creator_id: NULL
updator_id: NULL
status_id: NULL
version: 0
menuName: MENU_TEST2
parent_id: 54
role_id: 57
sortIndex: 6
menuDescr: TEST2222
remark: NULL
udf1: NULL
udf2: NULL
udf3: NULL
udf4: NULL
1 row in set (0.00 sec)

从库:
mysql> select * from test.test_menu where id=60 \G
*************************** 1. row ***************************
id: 60
createTime: 2015-01-21 13:58:18
updateTime: 2015-01-21 13:58:20
domain_id: -1
creator_id: NULL
updator_id: NULL
status_id: NULL
version: 0
menuName: MENU_TEST2
parent_id: 54
role_id: 57
sortIndex: 6
menuDescr: TEST2
remark: NULL
udf1: NULL
udf2: NULL
udf3: NULL
udf4: NULL
1 row in set (0.00 sec)
标红的就是主从库的差异

在主库再次执行pt-table-checksum命令
-bash-4.1$ pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --databases=test h=localhost,u=root,p=passwd,P=3306
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
01-21T15:17:41 0 0 4440 4 0 0.283 test.test_address
01-21T15:17:42 0 0 340 1 0 0.304 test.test_allocation
01-21T15:17:42 0 0 2 1 0 0.059 test.test_allocationexp
01-21T15:17:42 0 0 10 1 0 0.055 test.test_asn_detail
01-21T15:17:42 0 0 9 1 0 0.051 test.test_asn_header
01-21T15:17:42 0 0 8 1 0 0.051 test.test_biz_inventory
01-21T15:17:42 0 0 0 1 0 0.300 test.test_combination_map
01-21T15:17:42 0 0 20 1 0 0.055 test.test_common_property
01-21T15:17:42 0 0 103 1 0 0.055 test.test_doc_log
01-21T15:17:42 0 0 0 1 0 0.047 test.test_etrackno_used
01-21T15:17:42 0 0 20 1 0 0.051 test.test_express_company
01-21T15:17:42 0 0 2 1 0 0.051 test.test_inter_promotion_rule
01-21T15:17:42 0 0 1 1 0 0.062 test.test_inv_adjust_detail
01-21T15:17:43 0 0 1 1 0 0.056 test.test_inv_adjust_header
01-21T15:17:43 0 0 34 1 0 0.058 test.test_inv_log
01-21T15:17:43 0 0 9 1 0 0.054 test.test_inventory
01-21T15:17:43 0 0 2 1 0 0.058 test.test_lastexpressno
01-21T15:17:43 0 0 38 1 0 0.056 test.test_location
01-21T15:17:43 0 0 97 1 0 0.058 test.test_login_history
01-21T15:17:43 0 1 34 1 0 0.067 test.test_menu
01-21T15:17:43 0 0 11 1 0 0.161 test.test_pkd
01-21T15:17:44 0 0 1 1 0 0.347 test.test_promotion
01-21T15:17:44 0 0 1 1 0 0.409 test.test_promotion_condition
01-21T15:17:44 0 0 2 1 0 0.334 test.test_promotion_gift
01-21T15:17:45 0 0 2 1 0 0.163 test.test_promotion_rule
01-21T15:17:45 0 0 33 1 0 0.059 test.test_role
01-21T15:17:45 0 0 95 1 0 0.055 test.test_search_tab
01-21T15:17:45 0 0 25 1 0 0.309 test.test_send_template
01-21T15:17:45 0 0 4 1 0 0.057 test.test_sequence
01-21T15:17:45 0 0 30 1 0 0.300 test.test_sku
01-21T15:17:46 0 0 21 1 0 0.298 test.test_so_detail
01-21T15:17:46 0 0 15 1 0 0.069 test.test_so_header
01-21T15:17:46 0 0 2 1 0 0.056 test.test_soh_ee_relation
01-21T15:17:46 0 0 14 1 0 0.308 test.test_soh_eo_relation
01-21T15:17:46 0 0 12 1 0 0.060 test.test_store
01-21T15:17:46 0 0 23 1 0 0.059 test.test_sys_code
01-21T15:17:46 0 0 3 1 0 0.054 test.test_sys_config
01-21T15:17:47 0 0 32 1 0 0.055 test.test_sys_domain
01-21T15:17:47 0 0 19 1 0 0.061 test.test_taobao_so_detail
01-21T15:17:47 0 0 14 1 0 0.312 test.test_taobao_so_header
01-21T15:17:47 0 0 50 1 0 0.063 test.test_trackprint_template
01-21T15:17:47 0 0 32 1 0 0.057 test.test_user
01-21T15:17:47 0 0 31 1 0 0.053 test.test_user_role
01-21T15:17:47 0 0 31 1 0 0.060 test.test_warehouse
终于发现了差异

在主库检查结果表percona.checksums,没有显示差异,难道还是有BUG ?
mysql> select db,tbl,chunk,this_crc,this_cnt,master_crc,master_cnt,ts from checksums ;
+-------+---------------------------+-------+----------+----------+------------+------------+---------------------+
| db | tbl | chunk | this_crc | this_cnt | master_crc | master_cnt | ts |
+-------+---------------------------+-------+----------+----------+------------+------------+---------------------+
| test | test_lastexpressno | 1 | 96b2f90d | 2 | 96b2f90d | 2 | 2015-01-21 15:17:43 |
| test | test_location | 1 | d5582f63 | 38 | d5582f63 | 38 | 2015-01-21 15:17:43 |
| test | test_login_history | 1 | db189cf9 | 97 | db189cf9 | 97 | 2015-01-21 15:17:43 |
| test | test_menu | 1 | d9d69755 | 34 | d9d69755 | 34 | 2015-01-21 15:17:43 |
| test | test_pkd | 1 | ee959bc2 | 11 | ee959bc2 | 11 | 2015-01-21 15:17:43 |
| test | test_promotion | 1 | 2020c504 | 1 | 2020c504 | 1 | 2015-01-21 15:17:43 |
| test | test_promotion_condition | 1 | a71da967 | 1 | a71da967 | 1 | 2015-01-21 15:17:44 |

68 rows in set (0.00 sec)

在从库检查结果表percona.checksums
mysql> select db,tbl,chunk,this_crc,this_cnt,master_crc,master_cnt,ts from checksums ;
+-------+---------------------------+-------+----------+----------+------------+------------+---------------------+
| db | tbl | chunk | this_crc | this_cnt | master_crc | master_cnt | ts |
| test | test_lastexpressno | 1 | 96b2f90d | 2 | 96b2f90d | 2 | 2015-01-21 15:17:43 |
| test | test_location | 1 | d5582f63 | 38 | d5582f63 | 38 | 2015-01-21 15:17:43 |
| test | test_login_history | 1 | db189cf9 | 97 | db189cf9 | 97 | 2015-01-21 15:17:43 |
| test | test_menu | 1 | 631fa3bf | 34 | d9d69755 | 34 | 2015-01-21 15:17:43 |
| test | test_pkd | 1 | ee959bc2 | 11 | ee959bc2 | 11 | 2015-01-21 15:17:43 |
| test | test_promotion | 1 | 2020c504 | 1 | 2020c504 | 1 | 2015-01-21 15:17:43 |
| test | test_promotion_condition | 1 | a71da967 | 1 | a71da967 | 1 | 2015-01-21 15:17:44 |
| test | test_promotion_gift | 1 | 5d6b31f | 2 | 5d6b31f | 2 | 2015-01-21 15:17:44 |

+-------+---------------------------+-------+----------+----------+------------+------------+---------------------+
68 rows in set (0.01 sec)
终于显示了差异,原来查看结果表记录的差异,要在slave库查看才行
前面在从库多插入的记录的那个实验,在从库查看的话,应该也没有问题,是我查看选了不正确的库查看导致的
结论:查看差异结果表,应该在slave库上查询

下面的结果是我后来又重做的实验,在从库查询的
select db,tbl,chunk,this_crc,this_cnt,master_crc,master_cnt,ts from checksums where this_crc != master_crc ;
mysql> select db,tbl,chunk,this_crc,this_cnt,master_crc,master_cnt,ts from checksums where this_crc != master_crc ;
+-----+----------+-------+----------+----------+------------+------------+---------------------+
| db | tbl | chunk | this_crc | this_cnt | master_crc | master_cnt | ts |
+-----+----------+-------+----------+----------+------------+------------+---------------------+
| test | test_menu | 1 | 3be44ed2 | 35 | d9d69755 | 34 | 2015-01-21 16:41:25 |
+-----+----------+-------+----------+----------+------------+------------+---------------------+
1 row in set (0.00 sec)
三.用pt-table-sync修复不一致数据
-bash-4.1$ pt-table-sync --print --replicate=percona.checksums h=localhost,u=root,p=passwd h=192.168.2.196,u=root,p=passwd

REPLACE INTO `test`.`test_menu`(`id`, `createtime`, `updatetime`, `domain_id`, `creator_id`, `updator_id`, `status_id`, `version`, `menuname`, `parent_id`, `role_id`, `sortindex`, `menudescr`, `remark`, `udf1`, `udf2`, `udf3`, `udf4`) VALUES (‘60‘, ‘2015-01-21 13:58:18‘, ‘2015-01-21 13:58:20‘, ‘-1‘, NULL, NULL, NULL, ‘0‘, ‘MENU_TEST2‘, ‘54‘, ‘57‘, ‘6‘, ‘TEST2222‘, NULL, NULL, NULL, NULL, NULL) /*percona-toolkit src_db:test src_tbl:test_menu src_dsn:h=localhost,p=...,u=root dst_db:test dst_tbl:test_menu dst_dsn:h=192.168.2.196,p=...,u=root lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:8016 user:mysql host:open_mysql1*/;


-bash-4.1$ pt-table-sync --print --sync-to-master h=192.168.2.196,u=root,p=passwd --databases=test --tables=test_menu
REPLACE INTO `test`.`test_menu`(`id`, `createtime`, `updatetime`, `domain_id`, `creator_id`, `updator_id`, `status_id`, `version`, `menuname`, `parent_id`, `role_id`, `sortindex`, `menudescr`, `remark`, `udf1`, `udf2`, `udf3`, `udf4`) VALUES (‘60‘, ‘2015-01-21 13:58:18‘, ‘2015-01-21 13:58:20‘, ‘-1‘, NULL, NULL, NULL, ‘0‘, ‘MENU_TEST2‘, ‘54‘, ‘57‘, ‘6‘, ‘TEST2222‘, NULL, NULL, NULL, NULL, NULL) /*percona-toolkit src_db:test src_tbl:test_menu src_dsn:P=3306,h=192.168.2.195,p=...,u=root dst_db:test dst_tbl:test_menu dst_dsn:h=192.168.2.196,p=...,u=root lock:1 transaction:1 changing_src:1 replicate:0 bidirectional:0 pid:8022 user:mysql host:open_mysql1*/;

上面两个命令等价:
参数的意义:
--replicate= :指定通过pt-table-checksum得到的表,这2个工具差不多都会一直用。
--databases= : 指定执行同步的数据库,多个用逗号隔开。
--tables= :指定执行同步的表,多个用逗号隔开。
--sync-to-master :指定一个DSN,即从的IP,他会通过show processlist或show slave status 去自动的找主。
h=127.0.0.1 :服务器地址,命令里有2个ip,第一次出现的是M的地址,第2次是Slave的地址。
u=root :帐号。
p=123456 :密码。
--print :打印,但不执行命令。
--execute :执行命令。

修复方法:上面生成的语句直接在slave库执行

注意如果从库比主库记录多,得到的是delete语句,也在从库执行
-bash-4.1$ pt-table-sync --print --replicate=percona.checksums h=localhost,u=root,p=passwd h=192.168.2.196,u=root,p=passwd
DELETE FROM `test`.`test_menu` WHERE `id`=‘61‘ LIMIT 1 /*percona-toolkit src_db:test src_tbl:test_menu src_dsn:h=localhost,p=...,u=root dst_db:test dst_tbl:test_menu dst_dsn:h=192.168.2.196,p=...,u=root lock:1 transaction:1 changing_src:percona.checksums replicate:percona.checksums bidirectional:0 pid:8029 user:mysql host:open_mysql1*/;

pt-table-checksum、pt-table-sync核对主从库一致性