MySQL管理与诊断工具箱Percona Toolkit

-- 1.1 Percona Toolkit工具介绍与安装使用
percona-toolkit是一组高级命令行工具的集合，功能包括:
1)检查master和slave数据的一致性
2)有效地对记录进行归档
3)查找重复的索引
4)对服务器信息进行汇总
5)分析来自日志和tcpdump的查询
6)当系统出问题的时候收集重要的系统信息
这些工具主要包括开发、性能、配置、监控、复制、系统、实用七大类.
1.下载与安装
https://www.percona.com/software/database-tools/percona-toolkit
https://www.percona.com/downloads/percona-toolkit/LATEST/
PT工具是使用Perl语言编写和执行的，所以需要系统中有Perl环境。
wget https://downloads.percona.com/downloads/percona-toolkit/3.1.0/binary/tarball/percona-toolkit-3.1.0_x86_64.tar.gz
-----建议安装在RHEL LINUX上面，单独一台服务器上,
安装MYSQL客户端/软件，不建议装在MYSQL自身服务器上面。
mount /dev/cdrom /mnt
yum install -y perl-DBI per1-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL perl-TermReadKeycd /soft/
tar xvf PerconaToolkit-3.0.1-el7-x86_64-bundle.tar
rpm -ivh percona-toolkit-3.0.11-1.el7.x86_64.rpm
rpm -ivh percona-toolkit-debuginfo-3.0.11-1.el7.x86_64.rpm
#pt-query-digest --help
#pt-table-checksum --help



2.命令分类
有的35个命令，可以分为7大类:
1)开发类
pt-duplicate-key-checker列出并删除重复的索引和外键
pt-online-schema-change在线修改表结构I
pt-query-advisor 分析查询语句,并给出建议,有bug已废弃
pt-show-grants规范化和打印权限
pt-upgrade在多个服务器上执行查询,并比较不同
2)性能类
pt-index-usage分析日志中索引使用情况,并出报告
pt-pmp为查询结果跟踪,并汇总跟踪结果
pt-visual-explain 格式化执行计划
pt-table-usage分析日志中查询并分析表使用情况 pt 2.2新增命令
3)配置类
pt-config-diff 比较配置文件和参数
pt-mysql-summary 对mysql配置和status进行汇总
pt-variable-advisor 分析参数,并提出建议
4)监控类
pt-deadlock-logger提取和记录mysql死锁信息
pt-fk-error-logger提取和记录外键信息
pt-mext并行查看status样本信息
pt-query-digest分析查询日志,并产生报告常用命令
pt-trend按照时间段读取slow日志信息已废弃
5)复制类
pt-heartbeat 监控mysql复制延迟
pt-slave-delay 设定从落后主的时间
pt-slave-find 查找和打印所有mysql复制层级关系
pt-slave-restart 监控salve错误,并尝试重启salve
pt-table-checksum 校验主从复制一致性
pt-table-sync 高效同步表数据
6)系统类
pt-diskstats查看系统磁盘状态
pt-fifo-split模拟切割文件并输出
pt-summary 收集和显示系统概况
pt-stalk 出现问题时,收集诊断数据
pt-sift 浏览由pt-stalk创建的文件pt 2.2新增命令
pt-ioprofile 查询进程IO并打印一个I0活动表pt 2.2新增命令
7)实用类
pt-archiver将表数据归档到另一个表或文件中
pt-find查找表并执行命令
pt-kill Ki1l掉符合条件的sql常用命令
pt-align 对齐其他工具的输出 pt 2.2新增命令
pt-fingerprint查询转换 pt 2.2新增命令


-- 1.2.Percona Toolkit开发类工具
-- 1.2.1.pt-duplicate-key-checker列出并删除重复的索引和外健
功能为从mysql表中找出重复的索引和外健
这个工具会将重复的索引和外健都列出来,
并生成了删除重复索引的语句
pt-duplicate-key-checker --host=192.168.1.51 --user=root --password=stc123 --databases=itpuxdb
pt-duplicate-key-checker --host=192.168.1.51 --user=root --password=stc123 --databases=itpuxdb --table=itpux_m5;

-- 1.2.2.pt-online-schema-change在线修改表结构
在线DDL操作改变表结构并且不阻塞（正常情况下在线ALTER一个表增加一个字段、一个索
引的话MySQL会锁表)
使用pt-online-schema-change在线更新表
不停止业务使用的在线更新表结构:
pt-online-schema-change --user=root --host=192.168.1.51 --port=3306 --password=stc123 --execute --alter "ADD COLUMN fgmember VARCHAR(20)" D=itpuxdb,t=itpux_m5
在检测到服务器选项中有任何复制相关的筛选会退出，需要指定: --no-check-replication-filters
除了add column， 也可以modify column, drop column， 对于change column 则需要指定:--no-check-alter
mysql -uroot -proot -e "desc itpuxdb.itpux_m5; "

修改表结构并保留原表
--no-drop-old-table
表:
mysql -uroot -proot
use bm;
CREATE TABLE itpux_pt (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(10) DEFAULT NULL,
age int(11) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
exit;

mysql -uroot -pstc123 -e ' desc itpuxdb. itpux_pt' ;

1、增加字段:
pt-online-schema-change --user=stc --password=stc123 --host=192.168.56.96 --alter "ADD COLUMN content text" D=bm,t=itpux_pt --no-check-replication-filters --alter-foreign-keys-method=auto --recursion-method=none --print --execute

2、删除字段:
pt-online-schema-change --user=stc --password=stc123 --host=192.168.56.96 --alter "DROP COLUMN content" D=bm,t=itpux_pt --no-check-replication-filters --alter-foreign-keys-method=auto --recursion-method=none --quiet --execute

3、修改字段:
pt-online-schema-change --user=stc --password=stc123 --host=192.168.56.96 --alter "MODIFY COLUMN age TINYINT NOT NULL DEFAULT 0" D=bm,t=itpux_pt --no-check-replication-filters --alter-foreign-keys-method=auto --recursion-method=none --quiet --execute

4，字段改名:
pt-online-schema-change --user=stc --password=stc123 --host=192.168.56.96 --alter "CHANGE COLUMN age address varchar(30)" D=bm,t=itpux_pt --no-check-alter --no-check-replication-filters --alter-foreign-keys-method=auto --recursion-method=none --quiet --execut

5，增加索引:
pt-online-schema-change --user=stc --password=stc123 --host=192.168.56.96 --alter "ADD INDEX idx_address(address)" D=bm,t=itpux_pt --no-check-alter --no-check-replication-filters --alter-foreign-keys-method=auto --recursion-method=none --print --execute
6，删除索引:
pt-online-schema-change --user=stc --password=stc123 --host=192.168.56.96 --alter "DROP INDEX idx_address" D=bm,t=itpux_pt --no-check-alter --no-check-replication-filters --alter-foreign-keys-method=auto --recursion-method=none --print --execute

总结:
1、当业务量较大时，修改操作会等待没有数据修改后，执行最后的rename操作。因此，在修改表结构时,应该尽量选择在业务相对空闲时，至少修改表上的数据操作较低时，执行较为妥当。
2、如果对外键表操作时，四种外键操作类型需要根据表的数据量和可靠程度，进行选择。处于可靠性的原因，
尽量使用 rebuild_constraints 类型， 如果没有可靠性要求，可以使用auto类型。
3、由于可能存在一定的风险，在操作之前，建议对数据表进行备份，可以使得操作更安全、可靠。
使用该工具的前提是处理的表需要有主键或唯一索引。
当处理有外键的表时，需要加 --alter-foreign-keys-method参数，值可以根据情况设置。
当是主从环境，不在乎从的延迟，则需要加--recursion-method=none参数。
当需要尽可能的对服务产生小的影响，则需要加上--max-load参数。
--recursion-method
默认是show processlist， 发现从的方法，也可以是host,但需要在从上指定report_host,
通过show slave hosts来找到，可以指定none来不检查SLave.
METHOD USES
processlist SHOW PROCESSLIST
hosts SHOW SLAVE HOSTS
dsn=DSN DSNs from a table
none Do not find slaves

-- 1.2.3. pt-query-advisor分析查询语句，并给出建议,有bug已 废弃

-- 1.2.4. pt-show-grants 显示权限
查看指定mysq1的所有用户权限:
pt-show-grants --host=192.168.56.96 --user=root --password=stc123
查看执行数据库的权限:
pt-show-grants --host=192.168.56.96 --user=stc --password=stc123 --database=bm
查看每个用户权限生成revoke收回权限的语旬:
pt-show-grants --host=192.168.56.99 --user=stc --password=root --

-- 1.2.5. pt-upgrade在多个服务器上执行查询,并比较不同(实用)
这个工具用来检查在新版本中运行的SQL是否与老版本一样,
返回相同的结果，最好的应用场景就是数据迁移的时候。这在升级服务器的时候非常有用,
可以先安装并导数据到新的服务器上，然后使用这个工具跑一下sql看看有什么不同，可以找出不同版本之间的差异。
pt-upgrade h=192.168.1.51 h=192.168.1.52 --user=root --password=root /tmp/itpux.sql
pt-upgrade h=192.168.1.51 h=192.168.1.52 --user=root --password=root /mysql/log/3306/itpuxdb-query.err