|
|
percona-toolkit
mysql utilities的作用跟percona-toolkit一样
https://dev.mysql.com/downloads/utilities/
mysql utilities使用python编写
http://www.cnblogs.com/MYSQLZOUQI/p/5090717.html
美图秀秀DBA谈MySQL运维及优化
http://www.cnblogs.com/zping/p/5678652.html
percona-toolkit3.0 增加了两个工具
/usr/bin/pt-mongodb-query-digest
/usr/bin/pt-mongodb-summary
每一个命令 每个文件都是perl脚本
file /usr/bin/pt-kill
/usr/bin/pt-kill: a /usr/bin/env perl script text executable
有的32个命令，可以分为7大类：
工具类别
| 工具命令
| 工具作用
| 备注
| 开发类
| pt-duplicate-key-checker
| 列出并删除重复的索引和外键
| | pt-online-schema-change
| 在线修改表结构
| | pt-query-advisor
| 分析查询语句，并给出建议，有bug
| 已废弃
| pt-show-grants
| 规范化和打印权限
| | pt-upgrade
| 在多个服务器上执行查询，并比较不同
| | 性能类
| pt-index-usage
| 分析日志中索引使用情况，并出报告
| | pt-pmp
| 为查询结果跟踪，并汇总跟踪结果
| | pt-visual-explain
| 格式化执行计划
| | pt-table-usage
| 分析日志中查询并分析表使用情况
| pt 2.2新增命令
| 配置类
| pt-config-diff
| 比较配置文件和参数
| | pt-mysql-summary
| 对mysql配置和status进行汇总
| | pt-variable-advisor
| 分析参数，并提出建议
| | 监控类
| pt-deadlock-logger
| 提取和记录mysql死锁信息
| | pt-fk-error-logger
| 提取和记录外键信息
| | pt-mext
| 并行查看status样本信息
| | pt-query-digest
| 分析查询日志，并产生报告
| 常用命令
| pt-trend
| 按照时间段读取slow日志信息
| 已废弃
| 复制类
| pt-heartbeat
| 监控mysql复制延迟
| | pt-slave-delay
| 设定从落后主的时间
| | pt-slave-find
| 查找和打印所有mysql复制层级关系
| | pt-slave-restart
| 监控salve错误，并尝试重启salve
| | pt-table-checksum
| 校验主从复制一致性
| | pt-table-sync
| 高效同步表数据
| | 系统类
| pt-diskstats
| 查看系统磁盘状态
| | pt-fifo-split
| 模拟切割文件并输出
| | pt-summary
| 收集和显示系统概况
| | pt-stalk
| 出现问题时，收集诊断数据
| | pt-sift
| 浏览由pt-stalk创建的文件
| pt 2.2新增命令
| pt-ioprofile
| 查询进程IO并打印一个IO活动表
| pt 2.2新增命令
| 实用类
| pt-archiver
| 将表数据归档到另一个表或文件中
| | pt-find
| 查找表并执行命令
| | pt-kill
| Kill掉符合条件的sql
| 常用命令 | pt-align
| 对齐其他工具的输出
| pt 2.2新增命令
| pt-fingerprint
| 将查询转成密文
| pt 2.2新增命令
|
目前使用的比较多的命令是： pt-query-digest ，pt-kill等命令。
percona-toolkit
注意：pt工具集里面各个工具都有相同参数，并且大部分工具都要求表要有主键或唯一索引
percona-toolkit安装
wget -c https://www.percona.com/downloads/percona-toolkit/3.0.1/binary/redhat/6/x86_64/percona-toolkit-3.0.1-1.el6.x86_64.rpm
PT工具是使用Perl语言编写和执行的，所以需要系统中有Perl环境
安装依赖包
yum install -y perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL perl-TermReadKey.x86_64
rpm -ivh percona-toolkit-3.0.1-1.el6.x86_64.rpm
主库：10.105.45.133
从库：10.105.9.115
1、pt-online-schema-change
https://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html#cmdoption-pt-online-schema-change--
gh-osc跟pt-osc不一样，gh-osc是读取binlog，把导数据期间的差异读出来
gh-osc可以中途停止，他可以从上次读到的binlog点继续
pt-osc因为利用触发器无法记录中途停止在哪个点所以pt-osc不能中途终止
要求表要有主键或唯一索引
pt-OSC一些坑：
添加唯一键，导致数据丢失
延时备份的问题
行格式下，在从库使用OSC，丢数据
工具一 pt-osc
MySQL的DDL
http://www.iyunv.com/Linux/2016-08/134762.htm
1 原表上不能有触发器存在
2 通通过触发器写数据到临时新表，会不会出现数据不一致或异常
update和insert都用REPLACE INTO
3 为什么外键那么特殊
4 在使用之前需要对磁盘容量进行评估
5 使用 pt-osc原生 5.6 online ddl相比，如何选择
无论哪种方式都选择的业务低峰期执行
主从加字段方法 邱治军 这个方法行不通 pt-online-schema-change你今天滥用了吗？ 从库如果用row格式复制，从库不能执行触发器导致数据丢失，http://hcymysql.blog.iyunv.com/5223301/1879962
在从库上添加，再在主库上添加(set sql_log_bin=0;)，处理完成后再开启
1、在从库添加字段，用pt-osc
2、在主库设置set sql_log_bin=0;
3、主库加字段 online ddl或pt-osc
4、在主库设置set sql_log_bin=1;
表小于1G直接做，大于1G，用online ddl或pt-osc
online ddl：
1、修改外键
2、改列名
3、改字段默认值 建表时不加默认值，默认就是default null，改默认值实际就是null to not null，不会更新表数据，表里面的null不会被更新，pt-osc也不会更新表数据
4、改字段长度
ALGORITHM=INPLACE lock=none
online ddl操作表总结
https://dev.mysql.com/doc/refman/5.7/en/innodb-create-index-overview.html#innodb-online-ddl-summary-grid
全局参数old_alter_table=1 相当于ALGORITHM=copy，也就是说执行alter table的时候总是使用ALGORITHM=copy方式执行alter table
https://dev.mysql.com/doc/refman/5.7/en/online-ddl-partitioning.html
表分区只能用 ALGORITHM=DEFAULT , LOCK=DEFAULT ，只能由mysql决定
下面情况不会拷数据 ALGORITHM=copy （建议用range方式分区表）
list或range方式分区表 加分区或减分区
truncate 某个分区数据
hash方式或list方式分区表，加分区或减分区的时候然只可以select表数据
linear方式，hash方式，list方式分区表 在重组分区，重建分区，加分区，减分区只可以select表数据
pt-osc：
1、添加字段、索引
2、删除字段（删字段实质重组表数据）
3、修改字段数据类型
4、变更表字符集
5、删除主键
不锁表操作
删除索引
删除InnoDB二级索引只需要更新内部视图，并标记这个索引的空间可用，去掉数据库元数据上该索引的定义
ALTER TABLE test22 DROP INDEX idx_test22_c6c4;
改列注释
alter table xxx modify comment = 'xxxx';
pt-osc
pt-online-schema-change
Usage: pt-online-schema-change [OPTIONS] DSN
Errors in command-line arguments:
* A DSN must be specified
* The DSN must specify a database (D) and a table (t)
pt-online-schema-change alters a table's structure without blocking reads or
writes. Specify the database and table in the DSN. Do not use this tool before
reading its documentation and checking your backups carefully. For more
details, please use the --help option, or try 'perldoc
/usr/bin/pt-online-schema-change' for complete documentation.
连接
DSN详解 DSN是Data Source Name（数据源名称）的首字母缩写。DSN提供连接数据库需要的信息
h=192.168.60.64 主机
P=3306 端口
u=root 用户名
p=123 密码
D=td 数据库名
t=so_item 表名
用缩写参数之间要加逗号：h=127.0.0.1,P=3306,u=root,p=123465,D=school,t=test22
DSN syntax is key=value[,key=value...] Allowable DSN keys:
KEY COPY MEANING
=== ==== =============================================
A yes Default character set
D yes Database for the old and new table
F yes Only read default options from the given file
P yes Port number to use for connection
S yes Socket file to use for connection
h yes Connect to host
p yes Password to use when connecting
t no Table to alter
u yes User for login if not current user
选项
--max-load
--max-load="Threads_running=200"
默认为Threads_running=25。每个chunk拷贝完后，会检查 SHOW GLOBAL STATUS 的内容，检查指标是否超过了指定的阈值。如果超过，则先暂停。这里可以用逗号分隔，指定多个条件，每个条件格式： status指标=MAX_VALUE或者status指标:MAX_VALUE。如果不指定MAX_VALUE，那么工具会这只其为当前值的120%。
因为拷贝行有可能会给部分行上锁，Threads_running 是判断当前数据库负载的绝佳指标。
一旦超过Threads_running的值，则pt-online-schema-change暂停拷贝数据，等负载下降之后重新开始拷贝数据
--critical-load
--critical-load="Threads_running=200"
跟--max-load的区别是一旦超过Threads_running值，则pt-online-schema-change终止整个操作
--chunk-time
默认0.5s，即拷贝数据行的时候，为了尽量保证0.5s内拷完一个chunk，动态调整chunk-size的大小，以适应服务器性能的变化。
也可以通过另外一个选项--chunk-size禁止动态调整，即每次固定拷贝 1k 行，如果指定则默认1000行，且比 chunk-time 优先生效
--set-vars
使用pt-osc进行ddl要开一个session去操作，set-vars可以在执行alter之前设定这些变量，比如默认会设置--set-vars "wait_timeout=10000,innodb_lock_wait_timeout=1,lock_wait_timeout=60"。
--dry-run
创建和修改新表，但不会创建触发器、不会复制数据、不会替换原表。并不真正执行，
可以看到生成的执行语句，了解其执行步骤与细节，和--print配合最佳。
--execute
确定修改表，则指定该参数。真正执行alter。–dry-run与–execute必须指定一个，二者相互排斥
--alter-foreign-keys-method
修改外键的方式，默认使用rebuild_constraints方式
--alter-foreign-keys-method=rebuild_constraints
--alter-foreign-keys-method=drop_swap
--alter-foreign-keys-method=none 不对外键做任何处理，外键依然引用_tablename_old
--alter-foreign-keys-method=auto 使用rebuild_constraints方式
--charset
--charset=utf8
指定字符集
--check-alter
默认是1，当修改列名时候，需要使用--no-check-alter
列名修改会丢失数据，所以--check-alter默认为1就是防止用户执行列名修改操作
drop主键时也会警告
pt-online-schema-change注意事项 --no-check-alter 修改列名丢失数据的原因
http://blog.csdn.net/oeleven123456789/article/details/51899890
drop主键实际上是导数据到新表，然后drop旧表，新表没有唯一索引或主键导致三个触发器扫描新表全表
replace into针对insert和update触发器，delete触发器因为新表没有主键可能会误删数据
解决方法是对表加唯一索引
--alter "DROP PRIMARY KEY,add unique key uk_id_k(id,k)"
使用pt-online-schema-change修改主键时注意
http://www.iyunv.com/Linux/2016-08/134766.htm
没有唯一索引或主键都不能用pt-osc！！！！！！！！！！！！！！！！！！！！！！！！！！！！！
The new table `school`.`_test88_new` does not have a PRIMARY KEY or a unique index which is required for the DELETE trigger.
--[no]analyze-before-swap
默认yes 在交换表名之前收集新表的统计信息，默认只在innodb_stats_persistent变量为on和mysql5.6或以上版本这个选项才生效
如果服务器负载很大，建议使用--no-analyze-before-swap
--no-drop-old-table
保留原表
--quiet 或 -q
不在标准输出打印进度
############################################
复制相关选项
--no-check-replication-filters
--check-replication-filters 默认
如果是主从复制环境，并且设置了复制过滤，replicate-ignore-db
可以指定--no-check-replication-filters让pt-osc继续运行
--recursion-method
如果是主从复制，默认检查主从环境，pt工具集都会有这些选项
--recursion-method=processlist
--recursion-method=hosts
--recursion-method=DSN
--recursion-method=none 忽略检查主从环境。就是不管从库有啥延迟，直接运行，一般使用这个
关于--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
默认是通过show processlist 获取从库的连接信息
pt工具连接到主库，然后自动发现主库的所有从库。默认采用show full processlist来查找从库，但是这只有在主从实例端口相同的情况下才有效
（1）show processlist方式
mysql> show processlist\G
*************************** 1. row ***************************
Id: 3
User: slave
Host: 192.168.0.20:52352
db: NULL
Command: Binlog Dump
Time: 4164
State: Master has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
*************************** 2. row ***************************
Id: 33
User: root
Host: localhost
db: NULL
Command: Query
Time: 0
State: NULL
Info: show processlist
2 rows in set (0.00 sec)
（2）show slave hosts方式 跟show processlist差不多主库都要连上从库获取信息
这种方式需要重启从库的mysql
还有一种方法是在主库执行show slave hosts；前提从库配置文件里面已经配置自己的地址和端口：
[iyunv@从库 ~]# grep 'report' /etc/my.cnf
report_host = 192.168.0.20
report_port = 3306
在主库执行
mysql> show slave hosts;
+-----------+--------------+------+-----------+
| Server_id | Host | Port | Master_id |
+-----------+--------------+------+-----------+
| 2 | 192.168.0.20 | 3306 | 1 |
+-----------+--------------+------+-----------+
1 row in set (0.00 sec)
（3）DSN方式
dsn指定的是某个表 DSN表（如 percona.dsns ），表记录的是该主库的每个从库的连接信息。适用以下任一情形：
主库不能自动发现从库
不想在从库添加额外配置（因为要重启）
主从检测连接用户信息不一样
这个DSN表只需要在主库上建立
--max-lag
--max-lag=5 --check-interval=2
默认1s。每个chunk拷贝完成后，会查看所有复制Slave的延迟情况（Seconds_Behind_Master）。
要是延迟大于该值，则暂停复制数据，直到所有从的滞后小于这个值。跟--check-interval配合使用，指定出现从库滞后超过 max-lag，则该工具将睡眠多长时间，默认1s，再检查。如--max-lag=5 --check-interval=2。
例子
可以同时执行多条语句
pt-online-schema-change --max-load="Threads_running=200" --no-drop-old-table --execute --alter "add index idx_test22_c4(c4),add index idx_test22_c6(c6)" h=127.0.0.1,P=3306,u='abc',p='123',A=utf8,D='school',t='test22'
加字段
ALTER TABLE test22 ADD column c1 int;
socket文件方式
pt-online-schema-change --max-load="Threads_running=200" --no-check-replication-filters --recursion-method=none --no-drop-old-table --no-analyze-before-swap --execute --alter "ADD COLUMN c1 INT" S='/data/mysql/mysql3306/tmp/mysql.sock',u='abc',p='123',A=utf8,D='school',t='test22'
ip方式
pt-online-schema-change --max-load="Threads_running=200" --no-check-replication-filters --recursion-method=none --no-drop-old-table --no-analyze-before-swap --execute --alter "ADD COLUMN c2 INT" h=127.0.0.1,P=3306,u='abc',p='123',A=utf8,D='school',t='test22'
主从不记录binlog
pt-online-schema-change --max-load="Threads_running=200" --no-check-replication-filters --recursion-method=none --no-drop-old-table --no-analyze-before-swap --set-vars='sql_log_bin=0' --execute --alter "ADD COLUMN c2 INT" h=127.0.0.1,P=3306,u='abc',p='123',A=utf8,D='school',t='test88'
修改字段数据类型
ALTER TABLE test22 CHANGE COLUMN c2 c2 bigint DEFAULT 1;
ALTER TABLE test22 MODIFY COLUMN c1 bigint;
pt-online-schema-change --max-load="Threads_running=200" --no-check-replication-filters --recursion-method=none --no-drop-old-table --no-analyze-before-swap --execute --alter "CHANGE c2 c2 bigint DEFAULT 1" h=127.0.0.1,P=3306,u='abc',p='123',A=utf8,D='school',t='test22'
pt-online-schema-change --max-load="Threads_running=200" --no-check-replication-filters --recursion-method=none --no-drop-old-table --no-analyze-before-swap --execute --alter "MODIFY COLUMN c1 bigint" h=127.0.0.1,P=3306,u='abc',p='123',A=utf8,D='school',t='test22'
添加索引
pt-online-schema-change --max-load="Threads_running=200" --no-check-replication-filters --recursion-method=none --no-drop-old-table --no-analyze-before-swap --execute --alter "add index idx_test22_c6c4(c6,c4)" h=127.0.0.1,P=3306,u='abc',p='123',A=utf8,D='school',t='test22'
ALTER TABLE test22 DROP INDEX idx_test22_c6c4;
show index from `school`.`test22`
show create table test22
mysql不支持直接修改索引，要先删后加
重整表空间
pt-online-schema-change --max-load="Threads_running=200" --no-check-replication-filters --no-analyze-before-swap --recursion-method=none --execute --alter "engine=innodb" h=127.0.0.1,P=3306,u='abc',p='123',A=utf8,D='school',t='test22'
删除主键
因为有自增列，自增列需要有索引，所以加了唯一索引
pt-online-schema-change --no-check-alter --max-load="Threads_running=200" --no-check-replication-filters --recursion-method=none --no-drop-old-table --execute --alter "drop primary key,add unique index idx_test22_id(id)" h=127.0.0.1,P=3306,u='abc',p='123',A=utf8,D='school',t='test22'
online ddl和pt-osc时间对比
opreport1.`FONTANA_BETS`
online ddl
ALTER TABLE FONTANA_BETS algorithm=inplace, lock=none, ADD INDEX idx_FONTANA_BETS_BETTIME(BETTIME)
1 queries executed, 1 success, 0 errors, 0 warnings
Query: ALTER TABLE FONTANA_BETS algorithm=inplace, lock=none, ADD INDEX idx_FONTANA_BETS_BETTIME(BETTIME)
0 row(s) affected 用的是inplace算法
Execution Time : 2 min 10 sec
Transfer Time : 1.050 sec
Total Time : 2 min 11 sec
pt-osc
pt-online-schema-change --max-load="Threads_running=200" --no-check-replication-filters --recursion-method=none --no-analyze-before-swap --execute --alter "ADD INDEX idx_FONTANA_BETS_BETTIME(BETTIME)" h=127.0.0.1,P=3306,u='monitor',p='123456',A=utf8,D='opreport1',t='FONTANA_BETS'
2017-04-26T16:22:36 Copying approximately 645414 rows...
Copying `opreport1`.`FONTANA_BETS`: 11% 03:57 remain
Copying `opreport1`.`FONTANA_BETS`: 18% 04:26 remain
Copying `opreport1`.`FONTANA_BETS`: 24% 04:38 remain
Copying `opreport1`.`FONTANA_BETS`: 28% 04:59 remain
Copying `opreport1`.`FONTANA_BETS`: 32% 05:11 remain
Copying `opreport1`.`FONTANA_BETS`: 37% 04:59 remain
Copying `opreport1`.`FONTANA_BETS`: 42% 04:48 remain
Copying `opreport1`.`FONTANA_BETS`: 47% 04:26 remain
Copying `opreport1`.`FONTANA_BETS`: 54% 03:46 remain
Copying `opreport1`.`FONTANA_BETS`: 60% 03:11 remain
Copying `opreport1`.`FONTANA_BETS`: 67% 02:37 remain
Copying `opreport1`.`FONTANA_BETS`: 73% 02:11 remain
Copying `opreport1`.`FONTANA_BETS`: 79% 01:43 remain
Copying `opreport1`.`FONTANA_BETS`: 85% 01:12 remain
Copying `opreport1`.`FONTANA_BETS`: 91% 00:40 remain
2017-04-26T16:30:28 Copied rows OK.
2017-04-26T16:30:28 Swapping tables...
2017-04-26T16:30:28 Swapped original and new tables OK.
2017-04-26T16:30:28 Dropping old table...
2017-04-26T16:30:29 Dropped old table `opreport1`.`_FONTANA_BETS_old` OK.
2017-04-26T16:30:29 Dropping triggers...
2017-04-26T16:30:29 Dropped triggers OK.
Successfully altered `opreport1`.`FONTANA_BETS`.
real 7m55.340s
user 0m3.380s
sys 0m0.784s
f
2、pt-table-checksum
https://www.percona.com/doc/percona-toolkit/2.2/pt-table-checksum.html
可以比对多个实例或PXC集群
要求表有唯一索引或主键
只能修复主从数据一致，不能修复表数量不一致，比如从库比主库多了两个表，pt-table-checksum不会检查从库多出的两个表
主从端口要一致，主库是用的3307端口，h=127.0.0.1,u=root,p=123456,P=3307 会自动扫描从库的3307端口，否则只能使用DSN表的方法发现从库
pt-table-checksum
pt-table-checksum假定主从的表结构是一模一样的，所以在执行pt-table-checksum时不要变更表结构
DSN账号必须既能登录主库也能登录从库，h=127.0.0.1,u=root,p=123456,P=3306
pt-table-checksum 不需要在主库上安装，随便一台机器，这台机器能连接主库和从库就可以，会根据DSN信息连接主库和从库
每次执行pt-table-checksum 前先TRUNCATE TABLE percona库下的checksums表
pt-table-checksum 连接主库的方式：通过命令行里DSN
pt-table-checksum 连接从库的方式：通过--recursion-method参数，大部分pt工具都有--recursion-method 参数怕影响主从同步
原理
在主库执行基于statement的sql语句来生成主库数据块的checksum，把相同的sql语句传递到从库执行，并在从库上计算相同数据块的checksum，
最后，比较主从库上相同数据块的checksum值，由此判断主从数据是否一致。检测过程根据唯一索引将表按row切分为块（chunk），以单位计算，可以避免锁表。
检测时会自动判断复制延迟、 master的负载， 超过阀值后会自动将检测暂停，减小对线上服务的影响，根据服务器负载动态改变 chunk 大小，减少从库的延迟。
流程
1. 连接到主库：pt工具连接到主库，然后自动发现主库的所有从库。默认采用show full processlist来查找从库，但是这只有在主从实例端口相同的情况下才有效。
2. 查找主库或者从库是否有复制过滤规则：这是为了安全而默认检查的选项。你可以关闭这个检查，但是这可能导致checksum的sql语句要么不会同步到从库，要么到了从库发现从库没有要被checksum的表，这都会导致从库同步卡库。
3. 开始获取表，一个个的计算。
4. 如果是表的第一个chunk，那么chunk-size一般为1000；如果不是表的第一个chunk，那么会根据当前负载动态调整chunk-size。
5. 检查表结构，进行数据类型转换等，生成checksum的sql语句 ，RR隔离级别，binlog格式为statement。
6. 根据表上的索引和数据的分布，选择最合适的split表的方法，根据唯一索引将表按row切分为块（chunk），以单位计算，可以避免锁表。
7. 开始checksum表。
8. 默认在chunk一个表之前，先删除上次这个表相关的计算结果。除非–-resume。
9. 根据计算结果，判断chunk-size是否超过了你定义的 --chunk-size-limit。如果超过了，为了不影响线上性能，这个chunk将被忽略。
10. 把被checksum的行加上for update排他锁，并计算。
11. 把计算结果存储到你所指定的库和表 master_crc 和 master_cnt 列中。
12. 动态调整下一个chunk的大小。
13. 等待从库追上主库。如果没有延迟的从库则继续，如果发现延迟最大的从库延迟超过max-lag秒，pt工具将在当前位置暂停。
14. 如果发现主库的max-load超过某个阈值，pt工具将在当前位置暂停。
15. 继续下一个chunk，直到这个表被chunk完毕。
16. 等待从库执行完checksum，便于生成汇总的统计结果。每个表汇总并统计一次。
17. 循环每个表，直到结束。
选项
--host
主库的ip地址
--user
主库用户，这个用户一定要在主库和所有从库都存在，并且有建库建表权限percona.checksums和super权限，所以推荐直接使用root用户
--password
主库用户的密码
--port
主库的端口
--databases
需要检查的数据库，多个库用逗号分隔，不加--databases和--tables默认检查所有库和所有表
--tables
需要检查的表，多个表用逗号分隔，可以使用--tables=dbname1.table1,dbname2.table2这样的形式，而不需要
--databases=dbname1 --tables=table1
--databases=dbname2 --tables=table2
--[no]replicate-check
默认是yes 即--replicate-check，在主从执行checksum sql，--noreplicate-check表示什么都不做
--[no]check-binlog-format：
默认yes 即--check-binlog-format，检查所有主从机器是否具有相同的binlog格式如果不同会报错，
如果检测到是row格式，会自动SET @@binlog_format := 'STATEMENT'，如果连接pt-table-checksum 的用户没有super权限那么需要
先改全局SET global @@binlog_format := 'STATEMENT'再执行pt-table-checksum
在主库执行checksum sql，这个checksum sql会保存到binlog发到从库去执行这个checksum sql，从库执行完这个checksum sql之后pt-table-checksum 连接从库把结果发回来主库
--[no]check-replication-filters
默认yes 即--check-replication-filters，检查复制过滤器。后面可以用--databases来指定需要检查的数据库。
--[no]create-replicate-table
默认yes 即--create-replicate-table，
如果指定了--replicate参数并且不是使用--replicate默认值percona.checksums或者本身已经存在percona.checksums现在是第二次运行pt-table-checksum ，
那么--nocreate-replicate-table
否则 --create-replicate-table 自动创建percona库和checksums表
--replicate
默认是percona.checksums，当然也可以自己指定一个库和表来保存checksum结果，主上和从上都会建立percona库和checksums表
--replicate-check-only
在输出结果里只显示数据不一致的表，数据一致的表不显示
--set-vars
设置pt-table-checksum 运行的时候会话级别参数
--where
指定检查的范围，要跟--tables参数合用
--recursion-method
--recursion-method=processlist
--recursion-method=hosts
--recursion-method=DSN （指定一张DSN表）
--recursion-method=none
查找从库的模式，默认processlist
例子
#第一次使用pt-table-checksum
表
pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --create-replicate-table --recursion-method=processlist --tables=school2.test22 --host=10.105.45.133 --user=root --password=123456 --port=3306
库
pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --create-replicate-table --recursion-method=processlist --databases='school2,school,mysql' --host=10.105.45.133 --user=root --password=123456 --port=3306
#第二次使用pt-table-checksum 先truncate table percona.checksums
truncate table percona.checksums
pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --nocreate-replicate-table --recursion-method=processlist --databases=school2 --host=10.105.45.133 --user=root --password=123456 --port=3306
排除mysql库其他库都检查
pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --create-replicate-table --recursion-method=processlist --ignore-databases='mysql' --host=10.105.45.133 --user=root --password=123456 --port=3306
对于一个大表只检查前2w行数据
pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --create-replicate-table --recursion-method=processlist --databases='mysql'
--tables='users' --where='uid > 2000 and uid <4000' --host=10.105.45.133 --user=root --password=123456 --port=3306
在主库执行下面查询，查看哪个表的数据不一致
select * from percona.checksums
where master_cnt <> this_cnt
OR master_crc <> this_crc
OR ISNULL(master_crc) <> ISNULL(this_crc)
GROUP BY db, tbl;
常见错误
1、Diffs cannot be detected because no slaves were found
不能自动找到从库，确认processlist或host或dsns方式用对了。
2、Cannot connect to h=slave1.*.com,p=…,u=percona_user
可以在pt-table-checksum命令前加PTDEBUG=1来看详细的执行过程，如端口、用户名、权限错误。
3、Waiting for the –replicate table to replicate to XXX
问题出在 percona.checksums 表在从库不存在，根本原因是没有从主库同步过来，所以看一下从库是否延迟严重。
4、Pausing because Threads_running=25
反复打印出类似上面停止检查的信息。这是因为当前数据库正在运行的线程数大于默认25，pt-table-checksum 为了减少对库的压力暂停检查了。等数据库压力过了就好了，或者也可以直接 Ctrl+C 终端，下一次加上--resume继续执行，或者加大--max-load=值。
输出，一般很少用
TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
10-20T08:36:50 0 0 200 1 0 0.005 db1.tbl1
10-20T08:36:50 0 0 603 7 0 0.035 db1.tbl2
10-20T08:36:50 0 0 16 1 0 0.003 db2.tbl3
10-20T08:36:50 0 0 600 6 0 0.024 db2.tbl4
TS：月日时
ERRORS：发出错误次数
DIFFS：发生几个chunk数据不一致，chunk默认是1000条记录
ROWS：表的总行数
CHUNKS：表拆分为多少份
SKIPED：错误跳过数目
TIME：检查一个表占用的时间
TABLE:对应表名称
pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --nocreate-replicate-table --recursion-method=processlist --databases=school --host=10.105.45.133 --user=root --password=123456 --port=3306
general_log里主库的记录
pt-table-checksum 连接入来myslq的线程号是36415
2017-06-06T07:46:44.638483Z 36415 Connect root@10.105.45.133 on using TCP/IP
2017-06-06T07:46:44.638755Z 36415 Query set autocommit=1
2017-06-06T07:46:44.639129Z 36415 Query SHOW VARIABLES LIKE 'innodb\_lock_wait_timeout'
2017-06-06T07:46:44.641824Z 36415 Query SET SESSION innodb_lock_wait_timeout=1
2017-06-06T07:46:44.642039Z 36415 Query SHOW VARIABLES LIKE 'wait\_timeout'
2017-06-06T07:46:44.643972Z 36415 Query SET SESSION wait_timeout=10000
2017-06-06T07:46:44.644171Z 36415 Query SELECT @@SQL_MODE
2017-06-06T07:46:44.644323Z 36415 Query SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'*/
2017-06-06T07:46:44.644483Z 36415 Query SELECT @@server_id /*!50038 , @@hostname*/
2017-06-06T07:46:44.644651Z 36415 Query SELECT @@SQL_MODE
2017-06-06T07:46:44.644780Z 36415 Query SET SQL_MODE=',NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
2017-06-06T07:46:44.645031Z 36415 Query SHOW VARIABLES LIKE 'version%'
2017-06-06T07:46:44.647471Z 36415 Query SHOW ENGINES
2017-06-06T07:46:44.648010Z 36415 Query SHOW VARIABLES LIKE 'innodb_version'
2017-06-06T07:46:44.650359Z 36415 Query SELECT @@binlog_format
2017-06-06T07:46:44.650501Z 36415 Query /*!50108 SET @@binlog_format := 'STATEMENT'*/ 重要 session级别binlog格式改为STATEMENT
2017-06-06T07:46:44.650597Z 36415 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 重要 session级别隔离基本改为RR
2017-06-06T07:46:44.650820Z 36415 Query SHOW VARIABLES LIKE 'wsrep_on' 重要 检查是否是PXC环境
2017-06-06T07:46:44.652889Z 36415 Query SELECT @@SERVER_ID
2017-06-06T07:46:44.653097Z 36415 Query SHOW GRANTS FOR CURRENT_USER() 检查用pt-table-checksum工具的用户的权限
2017-06-06T07:46:44.653294Z 36415 Query SHOW FULL PROCESSLIST 重要，用来发现从库
2017-06-06T07:46:44.667118Z 36415 Query SHOW VARIABLES LIKE 'wsrep_on'
2017-06-06T07:46:44.670075Z 36415 Query SELECT @@SERVER_ID
2017-06-06T07:46:44.673110Z 36415 Query SHOW VARIABLES LIKE 'wsrep_on'
2017-06-06T07:46:44.676778Z 36415 Query SELECT @@SERVER_ID
2017-06-06T07:46:44.679377Z 36415 Query SHOW DATABASES LIKE 'percona'
2017-06-06T07:46:44.679741Z 36415 Query USE `percona`
2017-06-06T07:46:44.679909Z 36415 Query SHOW TABLES FROM `percona` LIKE 'checksums'
2017-06-06T07:46:44.681114Z 36415 Query SHOW GLOBAL STATUS LIKE 'Threads_running' 重要 检查当前数据库是否繁忙
2017-06-06T07:46:44.682951Z 36415 Query SELECT CONCAT(@@hostname, @@port)
2017-06-06T07:46:44.683811Z 36415 Query SELECT CRC32('test-string')
2017-06-06T07:46:44.683964Z 36415 Query SELECT CRC32('a')
2017-06-06T07:46:44.684171Z 36415 Query SELECT CRC32('a')
2017-06-06T07:46:44.684361Z 36415 Query SHOW VARIABLES LIKE 'wsrep_on'
2017-06-06T07:46:44.686456Z 36415 Query SHOW DATABASES
2017-06-06T07:46:44.686912Z 36415 Query SHOW /*!50002 FULL*/ TABLES FROM `school`
2017-06-06T07:46:44.687259Z 36415 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOTE_SHOW_CREATE := 1 */
对于各个表的操作
2017-06-06T07:46:44.687366Z 36415 Query USE `school`
2017-06-06T07:46:44.687496Z 36415 Query SHOW CREATE TABLE `school`.`tt` 重要 检查主从表结构是否一致
2017-06-06T07:46:44.687873Z 36415 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
2017-06-06T07:46:44.688464Z 36415 Query EXPLAIN SELECT * FROM `school`.`tt` WHERE 1=1 分析表情况
2017-06-06T07:46:44.692885Z 36415 Query USE `percona`
2017-06-06T07:46:44.693074Z 36415 Query DELETE FROM `percona`.`checksums` WHERE db = 'school' AND tbl = 'tt'
2017-06-06T07:46:44.709566Z 36415 Query USE `school`
2017-06-06T07:46:44.709982Z 36415 Query EXPLAIN SELECT COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `id`, `name`, CONCAT(ISNULL(`name`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `school`.`tt` /*explain checksum table*/
2017-06-06T07:46:44.710618Z 36415 Query REPLACE INTO `percona`.`checksums` (db, tbl, chunk, chunk_index, lower_boundary, upper_boundary, this_cnt, this_crc) SELECT 'school', 'tt', '1', NULL, NULL, NULL, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `id`, `name`, CONCAT(ISNULL(`name`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `school`.`tt` /*checksum table*/ 计算checksum
2017-06-06T07:46:44.734384Z 36415 Query SHOW WARNINGS
2017-06-06T07:46:44.735195Z 36415 Query SELECT this_crc, this_cnt FROM `percona`.`checksums` WHERE db = 'school' AND tbl = 'tt' AND chunk = '1'
2017-06-06T07:46:44.735720Z 36415 Query UPDATE `percona`.`checksums` SET chunk_time = '0.023439', master_crc = '9b1f7b3c', master_cnt = '9' WHERE db = 'school' AND tbl = 'tt' AND chunk = '1'
2017-06-06T07:46:44.883676Z 36415 Query SHOW GLOBAL STATUS LIKE 'Threads_running' 重要 每检查完一个表都执行一次当前数据库是否繁忙
f
general_log里从库的记录
2017-06-06T07:46:44.638755Z 36415 Query set autocommit=1
2017-06-06T07:46:44.639129Z 36415 Query SHOW VARIABLES LIKE 'innodb\_lock_wait_timeout'
2017-06-06T07:46:44.641824Z 36415 Query SET SESSION innodb_lock_wait_timeout=1
2017-06-06T07:46:44.642039Z 36415 Query SHOW VARIABLES LIKE 'wait\_timeout'
2017-06-06T07:46:44.643972Z 36415 Query SET SESSION wait_timeout=10000
2017-06-06T07:46:44.644171Z 36415 Query SELECT @@SQL_MODE
2017-06-06T07:46:44.644323Z 36415 Query SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'*/
只会设置必要的参数，下面这些语句都是从主库复制过来
创建percona.checksums表
UPDATE `percona`.`checksums` SET chunk_time
REPLACE INTO `percona`.`checksums`
f
3、pt-table-sync
https://www.percona.com/doc/percona-toolkit/2.2/pt-table-sync.html
要求表有唯一索引或主键
只能修复主从数据一致，不能修复表数量不一致，比如从库比主库多了两个表，pt-table-sync不会删除从库多出的两个表
pt-table-sync
高效的同步MySQL表之间的数据，他可以做单向和双向同步的表数据（一般我们只用单向，双向是新出功能）。他可以同步单个表，也可以同步整个库。
它不同步表结构、索引、或任何其他模式对象。所以在修复一致性之前需要保证表存在。
注意：跟pt-table-checksum一样，如果检测到是row格式，会自动修改会话级SET @@binlog_format := 'STATEMENT'，
如果连接pt-table-sync的用户没有super权限那么需要
先改全局SET global @@binlog_format := 'STATEMENT'再执行pt-table-sync
注意：双主一定要慎用，必须要加--no-bin-log和--no-check-slave
原理
根据pt-table-checksum的checksums表定位到不一致的chunk，然后把chunk再切分成多个小chunk
对每一个小chunk进行crc校验，校验不通过的就修复，多了数据就删除delete，少了数据就增加replace into，不多也不少就继续切分成一行一行，对每行进行crc校验，校验不通过的就update修复
注意：校验过程中会连接到主库和从库，都计算crc值，并以主库的为准
流程
运行完pt-table-checksum之后，checksum表在每个从库都有
pt-table-sync会连接到从库，然后自动找主库，然后以主库为中心--sync-to-master，再根据pt-table-checksum的结果--replicate（因为checksum表在每个从库都有）将从库跟主库的差异修复
pt-table-sync 指定两个dsn，那么只检查这两个dsn库
pt-table-sync 指定从库的dsn，那么也要指定--sync-to-master
pt-table-sync 指定主库dsn，那么会检查连接这个主库的所有从库，也就是检查所有主从
选项
--print
只打印不执行，跟--dry-run共用
--dry-run
只打印不执行，跟--print共用
--execute
执行
--[no]bin-log
默认是yes 记录binlog(SET SQL_LOG_BIN=1)，--no-bin-log不记录binlog，SET SQL_LOG_BIN=0
--[no]check-child-tables
默认是yes 当要同步的表有外键约束的时候，并且ON DELETE CASCADE, ON UPDATE CASCADE会检查子表的数据
并且--replace, --replicate, --sync-to-master 这几个选项也指定了
--[no]foreign-key-checks
默认是yes 检查外键SET FOREIGN_KEY_CHECKS=1，--no-foreign-key-checks 设置SET FOREIGN_KEY_CHECKS=0
--databases
需要检查的数据库，多个库用逗号分隔
--tables
需要检查的表，多个表用逗号分隔，可以使用--tables=dbname1.table1,dbname2.table2这样的形式，而不需要
--databases=dbname1 --tables=table1
--databases=dbname2 --tables=table2
--replace
当表有唯一索引的时候会自动使用replace into来执行insert语句和update语句，因为--unique-checks默认检查唯一索引
--[no]unique-checks
默认是yes 检查唯一索引SET UNIQUE_CHECKS=1，--no-unique-checks，不检查唯一索引SET UNIQUE_CHECKS=0
--replicate
指定checksum表所在位置，默认是percona.checksums，最好跟pt-table-checksum所指定的checksum表一样
--sync-to-master
因为pt-table-sync不知道你在参数里指定的dsn是主还是从，当你指定--sync-to-master选项表示你在参数里指定的dsn是从库
并在从库执行SHOW SLAVE STATUS获取主库连接信息，并以主库为标准开始同步
--set-vars
设置pt-table-checksum 运行的时候会话级别参数
例子
只检查某个表 在从库执行
pt-table-sync --recursion-method=processlist --sync-to-master --replicate=percona.checksums --execute --tables=school2.test22 h=10.105.9.115,u=root,p=123456,P=3306 ;echo $?
检查某个库 在从库执行
pt-table-sync --recursion-method=processlist --sync-to-master --replicate='percona.checksums' --execute --databases='school2' h=10.105.9.115,u=root,p=123456,P=3306 ;echo $?
检查全实例 在从库执行
pt-table-sync --recursion-method=processlist --sync-to-master --replicate=percona.checksums --execute h=10.105.9.115,u=root,p=123456,P=3306 ;echo $?
只检查某个表 在主库执行
pt-table-sync --recursion-method=processlist --replicate=percona.checksums --execute --tables=school2.test22 h=10.105.45.133,u=root,p=123456,P=3306 ;echo $?
检查某个库 在主库执行
pt-table-sync --recursion-method=processlist --replicate='percona.checksums' --execute --databases='school2' h=10.105.45.133,u=root,p=123456,P=3306 ;echo $?
检查全实例 在主库执行
pt-table-sync --recursion-method=processlist --replicate=percona.checksums --execute h=10.105.45.133,u=root,p=123456,P=3306 ;echo $?
修复之后建议再检查一次主从数据一致性
truncate table percona.checksums
pt-table-checksum --nocheck-replication-filters --no-check-binlog-format --nocreate-replicate-table --recursion-method=processlist --databases=school2 --host=10.105.45.133 --user=root --password=123456 --port=3306
pt-table-sync的返回值
STATUS MEANING
====== =======================================================
0 Success.
1 Internal error.
2 At least one table differed on the destination.
3 Combination of 1 and 2.
获得每个chunk包含row：n_rows=chunk_size/avg_row_length
确定chunk数量：假设chunk size为10MB，则一个5GB的表分为5000/10=500
计算每一个chunk的起始值，假设表的主键为id，通过select min(id),max(id) from tt2;
假设最小，最大值为0，10000，avg_row_length为500KB，n_rows=chunk_size/avg_row_length 20=10MB/500KB
则第一个区间数据：id>=0 and id <20 ，以此类推
腾讯游戏 梁飞龙
http://pan.baidu.com/s/1qXA092G
general_log里主库的记录
2017-06-06T15:36:07.942774Z 36425 Connect root@10.105.45.133 on using TCP/IP
2017-06-06T15:36:07.942922Z 36425 Query set autocommit=0
2017-06-06T15:36:07.943130Z 36425 Query SHOW VARIABLES LIKE 'wait\_timeout'
2017-06-06T15:36:07.944942Z 36425 Query SET SESSION wait_timeout=10000
2017-06-06T15:36:07.945205Z 36425 Query SELECT @@SQL_MODE
2017-06-06T15:36:07.945340Z 36425 Query SET @@SQL_QUOTE_SHOW_CREATE = 1/*!40101, @@SQL_MODE='NO_AUTO_VALUE_ON_ZERO,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'*/
2017-06-06T15:36:07.945485Z 36425 Query /*!40101 SET @@SQL_MODE := CONCAT(@@SQL_MODE, ',NO_AUTO_VALUE_ON_ZERO')*/
2017-06-06T15:36:07.945695Z 36425 Query SHOW VARIABLES LIKE 'version%'
2017-06-06T15:36:07.947245Z 36425 Query SHOW ENGINES
2017-06-06T15:36:07.947636Z 36425 Query SHOW VARIABLES LIKE 'innodb_version'
2017-06-06T15:36:07.949098Z 36425 Query SELECT @@binlog_format
2017-06-06T15:36:07.949215Z 36425 Query /*!50108 SET @@binlog_format := 'STATEMENT'*/ 重要 session级别binlog格式改为STATEMENT
2017-06-06T15:36:07.949293Z 36425 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ 重要 session级别隔离基本改为RR
2017-06-06T15:36:07.956499Z 36425 Query SELECT @@SERVER_ID
2017-06-06T15:36:07.956778Z 36425 Query SELECT db, tbl, CONCAT(db, '.', tbl) AS `table`, chunk, chunk_index, lower_boundary, upper_boundary, COALESCE
(this_cnt-master_cnt, 0) AS cnt_diff, COALESCE(this_crc <> master_crc OR ISNULL(master_crc) <> ISNULL(this_crc), 0) AS crc_diff, this_cnt, master_cnt, this_c
rc, master_crc FROM percona.checksums WHERE master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc)
2017-06-06T15:36:07.957286Z 36425 Query SHOW GRANTS FOR CURRENT_USER() 检查用pt-table-sync工具的用户的权限
2017-06-06T15:36:07.957518Z 36425 Query SHOW FULL PROCESSLIST 重要，用来发现从库
2017-06-06T15:36:08.026386Z 36425 Query /*!40101 SET @OLD_SQL_MODE := @@SQL_MODE, @@SQL_MODE := '', @OLD_QUOTE := @@SQL_QUOTE_SHOW_CREATE, @@SQL_QUOT
E_SHOW_CREATE := 1 */
2017-06-06T15:36:08.026603Z 36425 Query USE `school`
2017-06-06T15:36:08.027452Z 36425 Query SHOW CREATE TABLE `school`.`tt2` 重要 检查主从表结构是否一致
2017-06-06T15:36:08.027740Z 36425 Query /*!40101 SET @@SQL_MODE := @OLD_SQL_MODE, @@SQL_QUOTE_SHOW_CREATE := @OLD_QUOTE */
2017-06-06T15:36:08.086795Z 36425 Query SELECT table_schema, table_name FROM information_schema.key_column_usage WHERE constraint_schema='school' AND
referenced_table_name='tt2'
2017-06-06T15:36:09.627810Z 36425 Query SELECT MIN(`id`), MAX(`id`) FROM `school`.`tt2` FORCE INDEX (`PRIMARY`) 计算chunk 的起始值
2017-06-06T15:36:09.628358Z 36425 Query EXPLAIN SELECT * FROM `school`.`tt2` FORCE INDEX (`PRIMARY`)
2017-06-06T15:36:09.628864Z 36425 Query SELECT CRC32('test-string')
2017-06-06T15:36:09.630057Z 36425 Query SELECT CRC32('a')
2017-06-06T15:36:09.630245Z 36425 Query SELECT CRC32('a')
2017-06-06T15:36:09.630639Z 36425 Query USE `school`
2017-06-06T15:36:09.633971Z 36425 Query SET @crc := '', @cnt := 0
2017-06-06T15:36:09.634723Z 36425 Query commit
2017-06-06T15:36:09.635202Z 36425 Query START TRANSACTION /*!40108 WITH CONSISTENT SNAPSHOT */ 开一个一致性快照事务
2017-06-06T15:36:09.635420Z 36425 Query SELECT /*school.tt2:1/1*/ 0 AS chunk_num, COUNT(*) AS cnt, COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('
#', `id`, `tt`, `name`, CONCAT(ISNULL(`tt`), ISNULL(`name`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `school`.`tt2` FORCE INDEX (`PRIMARY`) WHERE (1=1) FOR
UPDATE
2017-06-06T15:36:09.744901Z 36425 Query SET @crc := '', @cnt := 0
2017-06-06T15:36:09.765540Z 36425 Query SELECT /*rows in chunk*/ `id`, `tt`, `name`, CRC32(CONCAT_WS('#', `id`, `tt`, `name`, CONCAT(ISNULL(`tt`), IS
NULL(`name`)))) AS __crc FROM `school`.`tt2` FORCE INDEX (`PRIMARY`) WHERE (1=1) ORDER BY `id` FOR UPDATE
2017-06-06T15:36:09.808105Z 36425 Query SELECT `id`, `tt`, `name` FROM `school`.`tt2` WHERE `id`='6' LIMIT 1
2017-06-06T15:36:09.808597Z 36425 Query REPLACE INTO `school`.`tt2`(`id`, `tt`, `name`) VALUES ('6', '222', 'sys') /*percona-toolkit src_db:school sr
c_tbl:tt2 src_dsn:P=3306,h=10.105.45.133,p=...,u=root dst_db:school dst_tbl:tt2 dst_dsn:P=3306,h=10.105.9.115,p=...,u=root lock:1 transaction:1 changing_src:
percona.checksums replicate:percona.checksums bidirectional:0 pid:12019 user:steven host:VM_45_133_centos*/
2017-06-06T15:36:09.823579Z 36425 Query commit
2017-06-06T15:36:09.847471Z 36425 Query commit
2017-06-06T15:36:09.847602Z 36425 Quit
函数master_pos_wait
语法 select master_pos_wait(file, pos[, timeout]).
这里的file和pos对应主库show master status得到的值，代表执行位置。 函数逻辑是等待当前从库达到这个位置后返回, 返回期间执行的事务个数。
参数timeout可选，若缺省则无限等待，timeout<=0时与缺省的逻辑相同。若为正数，则等待这么多秒，超时函数返回-1.
其他返回值：若当前slave为启动或在等待期间被终止，返回NULL； 若指定的值已经在之前达到，返回0
master_pos_wait的实现逻辑
用户调用该函数后，根据传入参数调用pthread_cond_timedwait或pthread_cond_wait。 SQL_THREAD线程每次apply完一个事件后会触发更新relay info, 并通知上面等待的线程。因为可能有多个用户等待，因此用广播方式。
关于事件个数的计算比较复杂，不过在本文讨论的这个问题上，正数返回值并不重要。
f
4、pt-query-digest
https://www.percona.com/doc/percona-toolkit/2.2/pt-query-digest.html#cmdoption-pt-query-digest--
从下面源分析慢查询
tcpdump，slow log文件, general log文件,binlog文件
pt-query-digest [OPTIONS] [FILES] [DSN]
用法示例
(1)直接分析慢查询文件:
pt-query-digest slow.log > slow_report.log
(2)分析最近12小时内的查询：
pt-query-digest --since=12h slow.log > slow_report2.log
(3)分析指定时间范围内的查询：
pt-query-digest slow.log --since '2014-04-17 09:30:00' --until '2014-04-17 10:00:00' > slow_report3.log
(4)分析含有select语句的慢查询
pt-query-digest --filter '$event->{fingerprint} =~ m/^select/i' slow.log> slow_report4.log
(5) 针对某个用户的慢查询
pt-query-digest --filter '($event->{user} || "") =~ m/^root/i' slow.log> slow_report5.log
(6) 查询所有的全表扫描或full join的慢查询
pt-query-digest --filter '(($event->{Full_scan} || "") eq "yes") ||(($event->{Full_join} || "") eq "yes")' slow.log > slow_report6.log
(7)把查询保存到query_review表
pt-query-digest --review h=127.0.0.1,u=root,p=123456,P=3306,D=school,t=query_review --create-review-table slow.log
select * from query_review\G;
*************************** 1. row ***************************
checksum: 1453905285868423734
fingerprint: truncate table t?
sample: truncate table t2
first_seen: 2017-03-20 22:18:53
last_seen: 2017-03-20 22:18:53
reviewed_by: NULL
reviewed_on: NULL
comments: NULL
*************************** 2. row ***************************
checksum: 4462137618363992133
fingerprint: insert into test?(name) values(?+)
sample: insert into test22(name) values(99977)
first_seen: 2017-03-03 00:15:45
last_seen: 2017-03-05 19:59:07
reviewed_by: NULL
reviewed_on: NULL
comments: NULL
2 rows in set (0.00 sec)
(8)通过tcpdump抓取mysql的tcp协议数据，然后再分析
tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt
pt-query-digest --type tcpdump mysql.tcp.txt> slow_report9.log
(9)分析binlog
mysqlbinlog mysql-bin.000093 > mysql-bin000093.sql
pt-query-digest --type=binlog mysql-bin000093.sql > slow_report10.log
(10)分析general log
pt-query-digest --type=genlog mysql.log > slow_report11.log
报告解读
汇总
# 1.7s user time, 1.5s system time, 24.60M rss, 205.29M vsz
# Current date: Fri Apr 21 15:29:53 2017
# Hostname: GZNWX-VG-JMOPDB01
# Files: mysql.slow
# Overall: 66 total 慢查询日志里一共有66条语句, 14 unique, 0.00 QPS, 0.00x concurrency ______________
# Time range: 2017-04-13 18:32:12 to 2017-04-21 09:51:00
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 224s 1s 94s 3s 2s 12s 1s
# Lock time 75ms 97us 30ms 1ms 3ms 4ms 214us
# Rows sent 139.29k 0 134.63k 2.11k 441.81 15.88k 49.17
# Rows examine 48.84M 1 1.19M 757.74k 1.14M 516.98k 1.09M
# Query size 11.78k 30 1.14k 182.74 329.68 145.32 112.70
# Profile
# Rank Query ID Response time Calls R/Call V/M Item
# ==== ================== ============== ===== ======= ===== =============
# 1 0x789676C3CF76D963 141.8949 63.4% 3 47.2983 29.33 SELECT UNION FONTANA_BET opreport?.FONTANA_BETS
# 2 0x31A16EF87B88E544 42.8032 19.1% 36 1.1890 0.04 SELECT FONTANA_ANNOUNCEMENTSTATUS
# 3 0xC9BBA2610AE4EB2B 10.5110 4.7% 9 1.1679 0.01 SELECT sessions users
# 4 0x811D4AE3A1CACA8A 6.7305 3.0% 2 3.3652 0.32 SELECT opreport.FONTANA_GAMBLINGS
# 5 0x3FD9E9B98400A0D9 5.7541 2.6% 3 1.9180 0.01 SELECT UNION FONTANA_BET opreport?.FONTANA_BETS
# 6 0x4A9CF4735A0490F2 3.6935 1.6% 3 1.2312 0.02 SELECT history_uint
# 7 0x0E9D1E6D473DBD20 3.2511 1.5% 3 1.0837 0.00 SELECT hosts items functions triggers
# MISC 0xMISC 9.3433 4.2% 7 1.3348 0.0 <7 ITEMS>
看每个语句的响应时间应该看R/Call
下面是各个实际的查询语句
# Query 2: 0.00 QPS, 0.00x concurrency, ID 0x31A16EF87B88E544 at byte 33479
# Scores: V/M = 0.04
# Time range: 2017-04-14 17:24:20 to 2017-04-20 17:54:59
# Attribute pct total min max avg 95% stddev median 看平均值
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 54 36 本语句出现了36次占用整个慢查询日志的36/66=54% Overall: 66 total 慢查询日志里一共有66条语句, 14 unique, 0.00 QPS, 0.00x concurrency
# Exec time 19 43s 1s 2s 1s 1s 231ms 1s 执行时间
# Lock time 10 8ms 100us 769us 212us 348us 121us 167us 锁时间
# Rows sent 1 1.76k 50 50 50 50 0 50 发送了多少行
# Rows examine 82 40.23M 1.12M 1.12M 1.12M 1.09M 0 1.09M 扫描了多少行
# Query size 34 4.01k 114 114 114 114 0 114
# String:
# Databases cashFFmanager
# Hosts 192.168.0.49
# Users root
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms
# 1s ################################################################
# 10s+
# Tables
# SHOW TABLE STATUS FROM `cashFFmanager` LIKE 'FONTANA_ANNOUNCEMENTSTATUS'\G
# SHOW CREATE TABLE `cashFFmanager`.`FONTANA_ANNOUNCEMENTSTATUS`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM FONTANA_ANNOUNCEMENTSTATUS WHERE LOGINNAME = 'CN2016000004' ORDER BY UPDATETIME DESC Limit 0,50\G
# SHOW TABLE STATUS FROM `opmanager1` LIKE 'FONTANA_BET'\G
# SHOW CREATE TABLE `opmanager1`.`FONTANA_BET`\G
# SHOW TABLE STATUS FROM `opreport1` LIKE 'FONTANA_BETS'\G
# SHOW CREATE TABLE `opreport1`.`FONTANA_BETS`\G
方便你查询表数据量和表结构 是否有索引
EXPLAIN /*!50100 PARTITIONS*/
方便你查看执行计划，语句都生成给你了，你直接执行就可以
# Databases opmanager1
这个语句在哪个库执行的
如果是delete或update语句会自动帮你转换为select语句 方便explain
# Tables
# SHOW TABLE STATUS LIKE 'FONTANA_GAMBLING'\G
# SHOW CREATE TABLE `FONTANA_GAMBLING`\G
DELETE FROM FONTANA_GAMBLING WHERE STARTTIME < '1491559201342' LIMIT 20000\G
# Converted for EXPLAIN
# EXPLAIN /*!50100 PARTITIONS*/
select * from FONTANA_GAMBLING WHERE STARTTIME < '1491559201342' LIMIT 20000\G
f
5、pt-heartbeat
https://www.percona.com/doc/percona-toolkit/2.2/pt-heartbeat.html#cmdoption-pt-heartbeat--
监控mysql复制延迟
pt-heartbeat可以监控MySQL 和 PostgreSQL 的复制延迟
最好保证主从的操作系统时间是一致的，利用NTP协议保证一致
pt-heartbeat [OPTIONS] [DSN] --update|--monitor|--check|--stop
至少要定义这四个选项之一 --stop, --update, --monitor, --check.
-update, --monitor 和 --check 互斥
--daemonize 和 --check 互斥
选项
--check
只检查一次主从延迟然后退出
--check-read-only
检查主库是否定义了 read_only ，如果是，则pt-heartbeat自动退出
--database
指定保存heartbeat 表的数据库，这个数据库一定是参与复制的，不能是复制过滤了的数据库，否则从库无法获取主库复制过来的时间戳
--create-table
创建heartbeat 表
CREATE TABLE heartbeat (
ts varchar(26) NOT NULL,
server_id int unsigned NOT NULL PRIMARY KEY,
file varchar(255) DEFAULT NULL, -- SHOW MASTER STATUS
position bigint unsigned DEFAULT NULL, -- SHOW MASTER STATUS
relay_master_log_file varchar(255) DEFAULT NULL, -- SHOW SLAVE STATUS
exec_master_log_pos bigint unsigned DEFAULT NULL -- SHOW SLAVE STATUS
);
--table
指定一个表为heartbeat表，一般不需要指定这个选项，因为--create-table会自动创建一个表名为heartbeat的表
不要使用database.table用法
--daemonize
后台运行pt-heartbeat
--file
将监控输出到一个文件，必须要定义 --monitor 选项
--[no]insert-heartbeat-row
默认是yes pt-heartbeat需要插入一个时间值到heartbeat表，当指定了--update, --monitor, --check选项的时候
才能计算主从延迟，当连接用户没有insert权限的时候可以指定 --no-insert-heartbeat-row
--master-server-id
计算给定的主库的server-id的延迟值，当然你也可以不指定这个选项，pt-heartbeat自动搜索主库和它的server-id
--monitor
不断监控从库的延迟
每秒检查从库的延迟并输出到标准输出或--file选项
[ 0.00s, 0.00s, 0.00s ] 表示1m,5m,15m的平均值
--update
更新主库的heartbeat值
--replace
要跟-–update连用，使用replace into代替insert into，在第二次使用pt-heartbeat的时候使用
“master -> slave1 -> slave2” with corresponding server IDs 1, 2 and 3, you can:
pt-heartbeat --daemonize -D test --update -h master
pt-heartbeat --daemonize -D test --update -h slave1
Then check (or monitor) the replication delay from master to slave2:
pt-heartbeat -D test --master-server-id 1 --check slave2
Or check the replication delay from slave1 to slave2:
pt-heartbeat -D test --master-server-id 2 --check slave2
第一步
在主库执行 连接主库
pt-heartbeat --check-read-only --database=school --create-table --update --replace --daemonize h=10.105.45.133,u=root,p=123456,P=3306
ps aux |grep heartbeat
root 2216 0.0 1.4 202876 14920 ? Ss 17:49 0:00 perl /usr/bin/pt-heartbeat --check-read-only --database school --create-table --update --replace --daemonize h=10.105.45.133,u=root,p=123456,P=3306
root 2237 0.0 0.0 103244 876 pts/1 S+ 17:50 0:00 grep --color heartbeat
第二步
在从库执行 连接从库
pt-heartbeat --monitor --database=school --master-server-id=1333306 --file=/tmp/222.txt --daemonize h=10.105.9.115,u=root,p=123456,P=3306
ps aux |grep heartbeat
root 16580 0.1 1.7 205896 17440 ? Ss 17:57 0:00 perl /usr/bin/pt-heartbeat --monitor --database school --master-server-id=1333306 --daemonize --file=/tmp/222.txt h=10.105.9.115,u=root,p=123456,P=3306
root 16591 0.0 0.0 103244 880 pts/1 S+ 17:57 0:00 grep --color heartbeat
cat /tmp/222.txt
0.00s [ 0.00s, 0.01s, 0.00s ]
关闭pt-heartbeat 后台进程，后续要继续开启后台进行的话，需要把/tmp/pt-heartbeat-sentinel 文件删除，否则启动不了
pt-heartbeat --stop
cd /tmp/
rm -f pt-heartbeat-sentinel
在主库批量插入测试数据
DELIMITER $$
create procedure pro180()
begin
declare i int;
set i=0;
while i<50000 do
insert into test99(name) select 'ww';
set i=i+1;
end
while;
end $$
DELIMITER ;
call pro180();
在从库观察延迟变化
watch cat /tmp/222.txt
Every 2.0s: cat /tmp/222.txt Wed Apr 5 20:20:43 2017
150.00s [ 136.33s, 91.12s, 32.79s ]
heartbeat的结果中GTID复制没有relay_master_log_file和exec_master_log_pos
select * from heartbeat\G;
*************************** 1. row ***************************
ts: 2017-04-05T21:44:16.001330
server_id: 1333306
file: mysql-bin.000006
position: 49679285
relay_master_log_file: NULL
exec_master_log_pos: NULL
6、pt-config-diff
https://www.percona.com/doc/percona-toolkit/2.2/pt-config-diff.html
pt-config-diff - Diff MySQL configuration files and server variables.
pt-config-diff [OPTIONS] CONFIG CONFIG [CONFIG...]
options选项很多都用不到，而配置可以是DSN或者是配置文件
例子
1、pt-config-diff h=10.105.9.115,u=root,p=123456,P=3306 h=10.105.45.133,u=root,p=123456,P=3306
# A software update is available:
# * The current version for Percona::Toolkit is 3.0.1.
12 config differences
Variable VM_9_115_centos VM_45_133_centos
========================= ========================= =========================
binlog_cache_size 10485760 20971520
general_log_file /data/mysql/mysql3306/... /data/mysql/mysql3306/...
gtid_executed 3336f2c4-fce5-11e6-83f... 3336f2c4-fce5-11e6-83f...
gtid_purged 3336f2c4-fce5-11e6-83f... 3336f2c4-fce5-11e6-83f...
hostname VM_9_115_centos VM_45_133_centos
innodb_io_capacity 800 2000
innodb_io_capacity_max 2000 4000
interactive_timeout 1000 100
log_warnings 1 2
server_id 1153306 1333306
server_uuid aebf394d-fd00-11e6-84a... 3336f2c4-fce5-11e6-83f...
wait_timeout 1000 100
2、pt-config-diff /etc/my.cnf h=10.105.45.133,u=root,p=123456,P=3306
3、pt-config-diff /etc/my.cnf /tmp/my.cnf
7、pt-slave-restart
https://www.percona.com/doc/percona-toolkit/2.0/pt-slave-restart.html
解决复制错误
pt-slave-restart [OPTION...] [DSN]
建议线上不要用pt-slave-restart，因为对于任何错误都只是跳过错误，而不是解决错误
选项
--[no]check-relay-log
默认是yes 读取relay-log，如果这次检查跟上次检查时候发现relay-log的binlog点无变化，就会pt-slave-restart 就继续sleep
--no-check-relay-log
--daemonize
后台方式运行
--error-numbers
以逗号分隔，pt-slave-restart只处理--error-numbers里面指定的错误，其他错误不处理
--log
指定日志文件的位置，当以--daemonize方式运行的时候才能用这个选项
--max-sleep
默认是64秒，最大的sleep秒数
--min-sleep
默认是0.015625秒，最小的sleep秒数
--skip-count
默认1，跳过多少个出错的语句
--sleep
默认1，每隔多少秒检查一次slave
--sentinel
默认/tmp/pt-slave-restart-sentinel
如果这个文件存在，pt-slave-restart就会退出
--monitor
默认是yes 是否监控slave，默认是隐式的，可以用--stop 停止pt-slave-restart
如果显式在命令行中指定--monitor，则--stop 也无法停止pt-slave-restart，并删除sentinel文件
--stop
创建sentinel文件并停止pt-slave-restart，如果显式在命令行中指定--monitor，则pt-slave-restart不会退出
例子
启动,先删除sentinel文件如果存在
rm -f /tmp/pt-slave-restart-sentinel
pt-slave-restart --daemonize --error-numbers=1032 --log ='/tmp/slave-restart.log' --monitor h=10.105.9.115,u=root,p=123456,P=3306
停止
pt-slave-restart --stop h=10.105.9.115,u=root,p=123456,P=3306
可以加入到crontab里
0 * * * * :program:`pt-slave-restart` --monitor --stop --sentinel /tmp/pt-slave-restartup
f |
|
QQ群⑧：
窥视卡
雷达卡
发表于 2017-6-10 10:32:34
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
显身卡