mysql_tools_pt_percona_tookits
目录
1.下载安装
2.部分工具
- pt-table-checksum
- pt-table-sync
- pt-pmp
1.下载安装
下载地址：http://www.percona.com/software/percona-toolkit rpm安装包
依赖：perl-IO
安装：sudo rpm -ivh percona-toolkit-2.2.8-1.noarch.rpm
2.部分工具
percona-toolkits是一个整合的安装包，包含很多小工具。详见http://www.percona.com/doc/percona-toolkit/2.2/，分不同小工具来测试
pt-table-checksum
作用：检查mysql主从数据是否一致
用法：pt-table-checksum [OPTIONS] [DSN]
参数：
u='',p='',h='127.0.0.1',P='3306',登陆参数，用户需要有
GRANT CREATE,SELECT,DELETE,UPDATE,INSERT, PROCESS, SUPER, REPLICATION SLAVE ON *.* TO 'repl'@'192.168.%' IDENTIFIED BY '123456';
--ask-pass：连接时手动输入密码
--[no]check-binlog-format：不检查主从服务器日志格式是否相同。默认检查。binlog-format是row，报错示例：
sudo /usr/bin/pt-table-checksum u=repl,P=3306,h=192.168.1.241,p='123456'
Replica hd-dm-test03 has binlog_format MIXED which could cause pt-table-checksum to break replication. Please read "Replicas using row-based replication" in the LIMITATIONS section of the tool's documentation. If you understand the risks, specify --no-check-binlog-format to disable this check.
Replica hd-dm-test02 has binlog_format MIXED which could cause pt-table-checksum to break replication. Please read "Replicas using row-based replication" in the LIMITATIONS section of the tool's documentation. If you understand the risks, specify --no-check-binlog-format to disable this check.
--check-interval：每次check的时间间隔，默认1s。
--[no]check-plan：检查计划，默认yes。
--[no]check-replication-filters：不检查复制过滤器，默认检查。需要设置启动--no-check-replication-filters ，启用--databases参数，指定检查的库
sudo /usr/bin/pt-table-checksum --no-check-binlog-format u=repl,P=3306,h=192.168.1.241,p='123456'
06-30T07:07:08 Replication filters are set on these hosts:
hd-dm-test03
replicate_ignore_db = mysql
hd-dm-test02
replicate_ignore_db = mysql
Please read the --check-replication-filters documentation to learn how to solve this problem. at /usr/bin/pt-table-checksum line 9535.
--check-slave-lag：
--replicate：输出结果至表checksums。缺少建表权限，删改查等权限，加上就行；
sudo /usr/bin/pt-table-checksum --no-check-binlog-format --no-check-replication-filters --databases=mydb --replicate=mydb.checksums u=repl,P=3306,h=192.168.1.241,p='123456'
06-30T07:15:01 --create-replicate-table failed: DBD::mysql::db do failed: CREATE command denied to user 'repl'@'192.168.1.241' for table 'checksums' at /usr/bin/pt-table-checksum line 11036.
06-30T07:15:01 --replicate table checksums does not exist and it cannot be created automatically. You need to create the table.
输出：
TS: 完成检测的时间；
ERRORS：检测过程碰到的错误数；
DIFFS：差异点
ROWS：表行数
CHUNKS：被划分到表中的块的数据，就是page
SKIPPED：由于错误或警告或过大，则跳过块的数目。
TIME：检查消耗的时间，单位s
TABLE：被检查的表名
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
06-30T07:20:16 0 0 3 1 0 0.016 mydb.people
06-30T07:20:16 0 0 3 1 0 0.014 mydb.people2
06-30T07:20:16 0 0 3 1 0 0.015 mydb.people_car
06-30T07:20:16 0 0 3 1 0 0.014 mydb.people_car2
06-30T07:20:16 0 0 0 1 0 0.016 mydb.t2
查看表中数据：
db：库名，ta1：表名，chunk：表占用的page数，chunk_time：检查消耗的时间，chunk_index：检查的索引页，this_crc：从的校验码，master_crc：主的校验码，this_cnt：从的行数，master_cnt：主的行数，ts：检测完成的时间点
mysql> select * from checksums;
+------+-------------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+
| db | tbl | chunk | chunk_time | chunk_index | lower_boundary | upper_boundary | this_crc | this_cnt | master_crc | master_cnt | ts |
+------+-------------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+
| mydb | people | 1 | 0.000785 | NULL | NULL | NULL | 8255d963 | 3 | 8255d963 | 3 | 2014-06-30 07:20:16 |
| mydb | people2 | 1 | 0.00064 | NULL | NULL | NULL | 8255d963 | 3 | 8255d963 | 3 | 2014-06-30 07:20:16 |
| mydb | people_car | 1 | 0.000883 | NULL | NULL | NULL | 4f6817d5 | 3 | 4f6817d5 | 3 | 2014-06-30 07:20:16 |
| mydb | people_car2 | 1 | 0.000696 | NULL | NULL | NULL | 4f6817d5 | 3 | 4f6817d5 | 3 | 2014-06-30 07:20:16 |
| mydb | t2 | 1 | 0.000689 | NULL | NULL | NULL | 0 | 0 | 0 | 0 | 2014-06-30 07:20:16 |
+------+-------------+-------+------------+-------------+----------------+----------------+----------+----------+------------+------------+---------------------+
