- 论坛徽章:
- 2
|
pt-table-checksums工具我在用,2.*版本比1.*好用的多.
下面是一些笔记
准备工作:
1.创建用户checksums
权限:
GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE ON *.* TO 'checksums'@'192.168.250.249' IDENTIFIED BY 'xxxx';
select 查看所有库的表,原理可加 --explain选项查看
process show processlist
super set binlog_format='statement'
replication slave show slave hosts
连接范围:
允许master连接(当前写库)
2.参数解释:
DSN h='192.168.250.249',u='checksums',p='xxxx',P=3306
-d 指定校验的库
--nocheck-replication-filters 忽略replication-do-db规则
--replicate=test.checksums 在test库下创建checksums表,并将数据写入
使用举例:
1.登录主库,增加授权
GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE ON *.* TO 'checksums'@'192.168.250.249' IDENTIFIED BY 'xxxx'
2.登录主库,执行pt-table-checksums脚本
# 只检查mysql库
pt-table-checksum h='192.168.250.249',u='checksums',p='xxxx',P=3306 -d mysql --nocheck-replication-filters --replicate=test.checksums
# 只检查mysql库,但是过滤mysql.user表
pt-table-checksum h='192.168.250.249',u='checksums',p='xxxx',P=3306 -d mysql --ignore-tables=mysql.user --nocheck-replication-filters --replicate=test.checksums
问题处理:
1.在主库上执行
pt-table-checksum h='192.168.250.249',u='checksums',p='xxxx',P=3306 -d mysql --nocheck-replication-filters --replicate=test.checksums
需要注意test.checksums是否存在,如果存在,则手工删除,否则会报如下错误
06-07T16:54:23 User does not have all privileges on --replicate table `test`.`checksums`.
2.binlog_format问题
06-07T19:06:07 Error checksumming table mysql.columns_priv: Error executing checksum query: Checksum query for table mysql.columns_priv caused MySQL error 1592:
Level: Note
Code: 1592
Message: Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. REPLACE...
......
SELECT is unsafe because the order in which rows are retrieved by the SELECT determines which (if any) rows are replaced. This /*checksum table*/
该问题与复制过滤器replicate-do-db 和 pt-table-checksums,innodb_strict_mode = 1均没有关系
脚本的bug,在mysql 5.5.18版本之后,都会出现这个问题
https://bugs.launchpad.net/percona-toolkit/+bug/919499
下载最新的版本,已经修复
http://bazaar.launchpad.net/~per ... n/pt-table-checksum
索引问题
06-07T21:19:31 Cannot checksum table db.table: There is no good index and the table is oversized. at ./pt-table-checksum line 3899.
|
|