percona toolkit工具使用

percona-toolkit是一组命令行工具的集合,用来管理MySQL,使用命令 yum install percona-toolkit 安装,percona-toolkit有以下类别:

开发类工具

pt-duplicate-key-checker

从MySQL表中找出重复的重复的索引和外键,将重复的索引和外键都列出来,并生成删除重复索引的语句。

pt-online-schema-change

在alter更改表结构的时候不用锁定表,也就是在执行alter的时候不会阻塞写和读取操作。

原理步骤如下:
1. 首先创建一个和你要执行的alter操作的表一样的空的表结构,后缀默认是new。
2. 在新表执行alter table语句,因为是空表,所以执行速度很快。
3. 在原表创建3个触发器分别对应insert/update/delete操作。
4. 以一定块大小从原表拷贝数据到新表,拷贝过程中由于触发器的存在,任何在原表进行的写操作都会更新到新建的临时表。
5. 在拷贝完数据之后,使用rename table命令将原表名(tablename)修改为(tablename.old),将(tablename.new)修改为(tablename)。
6. 修改完成之后,将原表删除。

参数

–alter
用于指定该表的操作,可以同时指定多个操作。

# 添加字段
# pt-online-schema-change -u root -p "" --alter "ADD COLUMN domain_id INT" D=employees,t=dept_manager --execute

注意:
(1)改表名时不可用RENAME
(2)修改列名时不可以通过drop该列然后重新add该列来完成,这样会导致无法拷贝原始列的数据出来
(3)如果add一个列没设默认值且设置为‘not null’,工具会拒绝执行,因为工具不会设置默认值
(4)drop外键时,外键名字前需要加 ‘_’ 而不是单纯的就一个外键名字,如drop下面这个外键时:
CONSTRAINT `fk_foo` FOREIGN KEY (`foo_id`) REFERENCES `bar` (`foo_id`)
必须这样指定:–alter “DROP FOREIGN KEY _fk_foo”

–charset
默认字符类型,例如如果值为utf8,就将输出的字符设置为utf8格式,将mysql_enable_utf8传递给DBD::mysql,然后连接MySQL后运行SET NAMES UTF8命令

–[no]check-alter
用于改表安全警告,目前支持两种报警:
(1)Column renames
该工具以前的版本中,用CHANGE COLUMN name new_name来改变列名时会导致该列的数据丢失,后来的版本中修补了这个bug,但是这段代码仍然不够万无一失,所以在操作前最好用–dry-run 和 –print去检测一下确定可以正确的rename column
(2)DROP PRIMARY KEY
除非指定–dry-run,否则一旦执行DROP PRIMARY KEY就会发出警告。该工具的触发器,尤其是delete trigger,主要是l利用主键来执行触发的,所以会受到很大的影响。所以最好先用–dry-run 和 –print去检测一下确保trigger可以正确执行

–[no]check-replication-filters
如果主从复制中有复制过滤就报错退出,即如果检测到有binlog_ignore_db、replicate_do_db等过滤手段,就报错退出。

–check-slave-lag
如果复制延迟大于–max-lag就停止数据拷贝,设置该参数后会监控所有的复制进程,一旦发现任何大于–max-lag时间的就停止数据拷贝,如果不想监控所有的复制进程,可以通过–recursion-method来指定想要监控的对象。

–dry-run
create并alter新表,但不创建trigger,不拷贝数据以及替换原始表。

–execute
该参数用于执行alter操作,如果不加的话,只会做一些安全检查后退出。一定要确保知道如何使用该工具并有合适的备份后,再添加该参数。使用该参数时,除了对对象表所需的权限外,还需要SUPER, REPLICATION SLAVE两种权限。

示例

# 更改表的引擎
# pt-online-schema-change -u root -p "" --alter "ENGINE=InnoDB" D=employees,t=dept_manager --execute

下面是对该命令原理步骤的一个模拟。

假设原命令为

# pt-online-schema-change -u root -p "" --alter 'add column c1 int' D=employees,t=dept_manager --execute

1. 检查表的情况,各种show,各种set,主要就是对权限的检查,超时时间的设定,当前系统的繁忙程度;然后就是对表的检查,如是否有触发器的存在。

>SHOW CREATE TABLE `testrep`.`t`\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  KEY `a` (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

2. 新建表

-- 新建表
>CREATE TABLE `_t_new` (
    ->   `a` int(11) DEFAULT NULL,
    ->   `b` int(11) DEFAULT NULL,
    ->   KEY `a` (`a`),
    ->   KEY `b` (`b`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.01 sec)

-- 在这个控表上增加表字段
>ALTER TABLE `_t_new` add column c1 int;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 再次查看表结构
>SHOW CREATE TABLE `_t_new`\G
*************************** 1. row ***************************
       Table: _t_new
Create Table: CREATE TABLE `_t_new` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  `c1` int(11) DEFAULT NULL,
  KEY `a` (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

从上述结果可以看出,已成功添加了字段c1。

3. 建立触发器

>CREATE TRIGGER `pt_osc_test_a_del` AFTER DELETE ON `t` FOR EACH ROW DELETE IGNORE FROM `_t_new` WHERE `_t_new`.`id` <=> t.`id`;
 
>CREATE TRIGGER `pt_osc_test_a_upd` AFTER UPDATE ON `t` FOR EACH ROW REPLACE INTO `_t_new` (`a`, `b`) VALUES (NEW.`a`, NEW.`b`);
 
>CREATE TRIGGER `pt_osc_test_a_ins` AFTER INSERT ON `t` FOR EACH ROW REPLACE INTO `_t_new` (`a`, `b`) VALUES (NEW.`a`, NEW.`b`);

4.

pt-online-schema-change解读

pt-query-advisor

根据一些规则分析查询语句,对可能的问题提出建议。

pt-show-grants

规范化和打印SQL权限,使在复制、比较MySQL权限以及进行版本控制的时候更有效率。

pt-upgrade

比较多台服务器上执行的查询的不同。

性能类工具

pt-index-usage

从log文件中读取查询语句,并用explain分析它们是如何利用索引,分析完成之后会生成一份关于索引没有被查询使用过的报告。

pt-pmp

跟踪聚合查询的的GBD堆栈,并将跟踪信息汇总。

pt-visual-explain

格式化explain出来的执行计划,按照tree方式输出,方便阅读。

配置类工具

pt-config-diff

比较MySQL配置文件和服务器参数。

pt-mysql-summary

精细地对MySQL的配置和status信息进行汇总。

pt-variable-advisor

分析MySQL的参数变量,并对可能存在的问题提出建议。

监控类工具

pt-deadlock-logger

提取和记录MySQL死锁的相关信息。

pt-fk-error-logger

提取和记录MySQL外键错误信息

pt-mext

并行查看show global status的多个样本的信息

pt-query-digest

pt-query-digest是用于分析mysql慢查询的一个工具,它也可以分析binlog、General log、slowlog,也可以通过SHOWPROCESSLIST或者通过tcpdump抓取的MySQL协议数据来进行分析。可以把分析结果输出到文件中,分析过程是先对查询语句的条件进行参数化,然后对参数化以后的查询进行分组统计,统计出各查询的执行时间、次数、占比等,然后就可以借助分析结果找出问题进行优化。

参数

–review
将分析结果保存到表中,这个分析只是对查询条件进行参数化,一个类型的查询一条记录,比较简单。当下次使用–review时,如果存在相同的语句分析,就不会记录到数据表中。

–create-review-table
使用–review参数把分析结果输出到表中时,如果没有表就自动创建。

–history
将分析结果保存到表中,分析结果比较详细,下次再使用–history时,如果存在相同的语句,且查询所在的时间区间和历史表中的不同,则会记录到数据表中,可以通过查询同一CHECKSUM来比较某类型查询的历史变化。

–create-history-table
使用–history参数把分析结果输出到表中时,如果没有表就自动创建。

–filter
对输入的慢查询按指定的字符串进行匹配过滤后再进行分析。

–limit
限制输出结果百分比或数量,默认值是20,即将最慢的20条语句输出,如果是95%则按总响应时间占比从大到小排序,输出到总和达到95%位置截止。

–log=s
指定输出的日志文件。

–output
分析结果输出类型,值可以是report(标准分析报告)、slowlog(Mysql slow log)、json、json-anon,一般使用report,以便于阅读。

–since
从该指定日期开始分析。

–until
截止时间,配合—since可以分析一段时间内的慢查询。

示例

# 分析本地的慢查询文件
# pt-query-digest --report /opt/tmp/mysql.slow

# 3.5s user time, 150ms system time, 52.87M rss, 230.81M vsz
# Current date: Sat Aug 18 18:59:05 2018
# Hostname: gh-mysql-jp-test01.gh.sankuai.com
# Files: /opt/tmp/mysql.slow
# Overall: 640 total, 40 unique, 0 QPS, 0x concurrency ___________________
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time        373042s   103ms 227251s    583s   580ms   9269s   219ms
# Lock time            16s       0    93ms    26ms    56ms    25ms    40ms
# Rows sent          9.06M       0   2.71M  14.49k    1.96 155.91k       0
# Rows examine     134.32M       0   5.42M 214.91k 462.39k 417.90k       0
# Rows affecte      15.33M       0   2.71M  24.52k  24.75k 153.29k  20.37k
# Bytes sent         1.39G       0 163.93M   2.22M   1.96k  16.50M   54.21
# Merge passes           4       0       1    0.01       0    0.08       0
# Tmp tables            21       0       1    0.03       0    0.18       0
# Tmp disk tbl          21       0       1    0.03       0    0.18       0
# Tmp tbl size     336.00k       0  16.00k  537.60       0   2.84k       0
# Query size       314.48M      15 1005.72k 503.17k 961.27k 478.78k 717.31k
# InnoDB:
# IO r bytes       944.00k       0 944.00k   1.62k       0  37.88k       0
# IO r ops              59       0      59    0.10       0    2.36       0
# IO r wait              0       0       0       0       0       0       0
# pages distin     104.82k       0   6.50k  184.12 1012.63  463.97       0
# queue wait             0       0       0       0       0       0       0
# rec lock wai           0       0       0       0       0       0       0
# Boolean:
# Filesort       0% yes,  99% no
# Filesort on    0% yes,  99% no
# Full scan     12% yes,  87% no
# Tmp table      3% yes,  96% no
# Tmp table on   3% yes,  96% no

# Profile
# Rank Query ID           Response time     Calls R/Call     V/M   Item
# ==== ================== ================= ===== ========== ===== =======
#    1 0xCBC895EB5A636382 372803.7252 99.9%    13 28677.2096 12... ADMIN BINLOG DUMP
# MISC 0xMISC                238.1040  0.1%   627     0.3798   0.0 <39 ITEMS>

# Query 1: 0 QPS, 0x concurrency, ID 0xCBC895EB5A636382 at byte 152803249
# This item is included in the report because it matches --limit.
# Scores: V/M = 121460.68
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          2      13
# Exec time     99 372804s    148s 227251s  28677s  85838s  59018s   5411s
# Lock time      0       0       0       0       0       0       0       0
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0       0       0       0       0       0       0       0
# Rows affecte   0       0       0       0       0       0       0       0
# Bytes sent     5  75.59M     504  75.02M   5.81M 362.29k  19.22M   9.16k
# Merge passes   0       0       0       0       0       0       0       0
# Tmp tables     0       0       0       0       0       0       0       0
# Tmp disk tbl   0       0       0       0       0       0       0       0
# Tmp tbl size   0       0       0       0       0       0       0       0
# Query size     0     442      34      34      34      34       0      34
# String:
# Databases    employees
# Hosts
# Time         2018-07-20... (1/7%), 2018-07-20... (1/7%)... 11 more
# Users        replication
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s
#  10s+  ################################################################
administrator command: Binlog Dump\G

上述结果总共分为三部分

第一部分:总体统计结果
Overall:总共有多少条查询
Time range:查询执行的时间范围
unique:唯一查询数量,即对查询条件进行参数化以后,总共有多少个不同的查询
total:总计 min:最小 max:最大 avg:平均
95%:把所有值从小到大排列,位置位于95%的那个数,这个数一般最具有参考价值
median:中位数,把所有值从小到大排列,位置位于中间那个数

# 该工具执行日志分析的用户时间,系统时间,物理内存占用大小,虚拟内存占用大小
# 3.5s user time, 150ms system time, 52.87M rss, 230.81M vsz
# 工具执行时间
# Current date: Sat Aug 18 18:59:05 2018
# 运行分析工具的主机名
# Hostname: test01.gh.com
# 被分析的文件名
# Files: /opt/tmp/mysql.slow
# 语句总数量,唯一的语句数量,QPS,并发数
# Overall: 640 total, 40 unique, 0 QPS, 0x concurrency ___________________
# 属性 总计 最小 最大 平均 95% 标准 中等
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time        373042s   103ms 227251s    583s   580ms   9269s   219ms
# Lock time            16s       0    93ms    26ms    56ms    25ms    40ms
# Rows sent          9.06M       0   2.71M  14.49k    1.96 155.91k       0
# Rows examine     134.32M       0   5.42M 214.91k 462.39k 417.90k       0
# Rows affecte      15.33M       0   2.71M  24.52k  24.75k 153.29k  20.37k
# Bytes sent         1.39G       0 163.93M   2.22M   1.96k  16.50M   54.21
# Merge passes           4       0       1    0.01       0    0.08       0
# Tmp tables            21       0       1    0.03       0    0.18       0
# Tmp disk tbl          21       0       1    0.03       0    0.18       0
# Tmp tbl size     336.00k       0  16.00k  537.60       0   2.84k       0
# Query size       314.48M      15 1005.72k 503.17k 961.27k 478.78k 717.31k
# InnoDB:
# IO r bytes       944.00k       0 944.00k   1.62k       0  37.88k       0
# IO r ops              59       0      59    0.10       0    2.36       0
# IO r wait              0       0       0       0       0       0       0
# pages distin     104.82k       0   6.50k  184.12 1012.63  463.97       0
# queue wait             0       0       0       0       0       0       0
# rec lock wai           0       0       0       0       0       0       0
# Boolean:
# Filesort       0% yes,  99% no
# Filesort on    0% yes,  99% no
# Full scan     12% yes,  87% no
# Tmp table      3% yes,  96% no
# Tmp table on   3% yes,  96% no

第二部分:查询分组统计结果

Rank:所有语句的排名,默认按查询时间降序排列,通过–order-by指定
Query ID:语句的ID,(去掉多余空格和文本字符,计算hash值)
Response:总的响应时间
time:该查询在本次分析中总的时间占比
calls:执行次数,即本次分析总共有多少条这种类型的查询语句
R/Call:平均每次执行的响应时间
V/M:响应时间Variance-to-mean的比率
Item:查询对象

# Profile
# Rank Query ID           Response time     Calls R/Call     V/M   Item
# ==== ================== ================= ===== ========== ===== =======
#    1 0xCBC895EB5A636382 372803.7252 99.9%    13 28677.2096 12... ADMIN BINLOG DUMP
# MISC 0xMISC                238.1040  0.1%   627     0.3798   0.0 <39 ITEMS>

第三部分:每一种查询的详细统计结果
由下面查询的详细统计结果,最上面的表格列出了执行次数、最大、最小、平均、95%等各项目的统计。
ID:查询的ID号,和上图的Query ID对应
Databases:数据库名
Users:各个用户执行的次数(占比)
Query_time distribution :查询时间分布, 长短体现区间占比,本例中1s-10s之间查询数量是10s以上的两倍。
Tables:查询中涉及到的表
Explain:SQL语句

# Query 1: 0 QPS, 0x concurrency, ID 0xCBC895EB5A636382 at byte 152803249
# This item is included in the report because it matches --limit.
# Scores: V/M = 121460.68
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          2      13
# Exec time     99 372804s    148s 227251s  28677s  85838s  59018s   5411s
# Lock time      0       0       0       0       0       0       0       0
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0       0       0       0       0       0       0       0
# Rows affecte   0       0       0       0       0       0       0       0
# Bytes sent     5  75.59M     504  75.02M   5.81M 362.29k  19.22M   9.16k
# Merge passes   0       0       0       0       0       0       0       0
# Tmp tables     0       0       0       0       0       0       0       0
# Tmp disk tbl   0       0       0       0       0       0       0       0
# Tmp tbl size   0       0       0       0       0       0       0       0
# Query size     0     442      34      34      34      34       0      34
# String:
# Databases    employees
# Hosts
# Time         2018-07-20... (1/7%), 2018-07-20... (1/7%)... 11 more
# Users        replication
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s
#  10s+  ################################################################
administrator command: Binlog Dump\G
# 报告最近半个小时的慢查询:
# pt-query-digest --report --since 1800s /opt/tmp/mysql.slow

# 报告一个时间段的慢查询:
# pt-query-digest --report --since '2018-08-10 21:00:00' --until '2018-08-11 01:30:50' /opt/tmp/mysql.slow

# 报告只含select语句的慢查询
# pt-query-digest --filter '$event->{fingerprint} =~ m/^select/i' /opt/tmp/mysql.slow

#报告针对某个用户的慢查询
# pt-query-digest --filter '($event->{user} || "") =~ m/^root/i' /opt/tmp/mysql.slow

# 报告所有的全表扫描或full join的慢查询
# pt-query-digest --filter '(($event->{Full_scan} || "") eq "yes") || (($event->{Full_join} || "") eq "yes")' /opt/tmp/mysql.slow

# 重新回顾慢查询日志,并将结果保存到query_review
# 1. 创建表
> CREATE TABLE query_review (
    checksum BIGINT UNSIGNED NOT NULL PRIMARY KEY,
    fingerprint TEXT NOT NULL,
    sample TEXT NOT NULL,
    first_seen DATETIME,
    last_seen DATETIME,
    reviewed_by VARCHAR(20),
    reviewed_on DATETIME,
    comments TEXT
);
# 2. 执行命令
# pt-query-digest -u root -p "" --review D=employees,t=query_review /opt/tmp/mysql.slow

# 把查询保存到query_history表
# pt-query-digest -u root -p "" --history D=employees,t=query_history --create-history-table /opt/tmp/mysql.slow

# 分析binlog
# mysqlbinlog mysql-bin.000001 > mysql-bin000001.sql
# pt-query-digest --type=binlog mysql-bin000001.sql > slow_report.log
# cat slow_report.log

# 150ms user time, 10ms system time, 24.50M rss, 202.43M vsz
# Current date: Sat Aug 18 18:54:43 2018
# Hostname: test01.gh.com
# Files: mysql-bin000001.sql
# Overall: 0 total, 1 unique, 0 QPS, 0x concurrency ______________________
# Time range: unknown
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Query size            44      44      44      44      44       0      44


# Profile
# Rank        Query ID    Response time Calls       R/Call      V/M   Item
# =========== =========== =========== =========== =========== ===== ======

MySQL慢查询之pt-query-digest分析慢查询日志

pt-trend

基于一组时间序列的数据点做统计。

复制类工具

pt-heartbeat

监控MySQL主从复制延迟。

pt-slave-delay

设置从服务器落后于主服务器指定时间。

pt-slave-find

查找和打印MySQL所有从服务器复制层级关系。

pt-slave-restart

监视MySQL复制错误,并当复制停止的时候尝试重启MySQL复制。

pt-table-checksum

pt-table-checksum运行在主库上,通过show processlist或show slave hosts或DSN方式来确定从库并连接,默认使用crc32算法来进行数据校验,该工具需要把binlog设置为statement格式,因为该工具能得出主从是否一致所依赖的是statement基础上同样的SQL语句在主从库上各自的执行结果,主库进行检查后sql语句传给从库,从库执行一遍后,也得到自己的结果。如果主库和从库得出的这两个值都是一样的,那数据就是一致的,如果不一样,那就主从不一致。工具将主从各自得到的结果处理后放到checksums表中并呈现一些结果在屏幕输出中。

参数

(1) 指定库
–databases / –ignore-databases  要比较的库 / 比较过程中忽略这些库
–databases-regex / –ignore-databases-regex  同上,不过可以用正则匹配

(2) 指定表
–tables / –ignore-tables  要比较的表 / 比较过程中忽略这些表
–tables-regex / –ignore-tables-regex  同上,不过可以用正则匹配

(3) 指定列
–columns / –ignore-columns  要比较的列 / 比较过程中忽略这些列

(4) 直接指定表范围
–where 直接指定表中要比较的范围

(5) 根据引擎选表
–engines / –ignore-engines  比较指定的引擎表 / 比较过程中忽略含有这些引擎的表

(6) 指定连接中断后行为的参数
–resume 如果主从一致性检查中途中断的话,可以用这个参数来使工具从上次中断时检查的最后一个表开始继续检查
–retries 如果在检查过程中有非致命性的中断的话,如被kill或者从库延迟等,指定该参数后,工具会自动尝试重连

其他参数

–[no]check-binlog-format
默认会检查binlog-format,如果不是statment,就会报错退出,想避免该检查可以设置–no-check-binlog-format

–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

建议指定–recursion-method=processlist,no一般不使用。使用dsn=DSN时,需要先去库里创建一个表,比如在percona库中建一个dnsn表
建表语句是:

CREATE TABLE `dsns` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`parent_id` int(11) DEFAULT NULL,
`dsn` varchar(255) NOT NULL,
PRIMARY KEY (`id`));

建好后插入主从复制信息数据,如:
insert into table dsns(dsn) values(h=slave_host,u=repl_user,p=repl_password,P=port );
然后就可以使用DSN方法了:命令为:–recursion-method dsn=D=percona,t=dsns.

–replicate
用来指定存放计算结果的表名, 默认是percona.checksums,工具会默认自动创建库percona和表checksums并将checksum的检查结果输入到这个表中,如果自己用该参数去指定表的话,表结构必须是:
CREATE TABLE checksums (
db char(64) NOT NULL,
tbl char(64) NOT NULL,
chunk int NOT NULL,
chunk_time float NULL,
chunk_index varchar(200) NULL,
lower_boundary text NULL,
upper_boundary text NULL,
this_crc char(40) NOT NULL,
this_cnt int NOT NULL,
master_crc char(40) NULL,
master_cnt int NULL,
ts timestamp NOT NULL,
PRIMARY KEY (db, tbl, chunk),
INDEX ts_db_tbl (ts, db, tbl)
) ENGINE=InnoDB;
需要注意的是存储引擎设置,如果检查的表是innodb表,就设置innodb引擎,如果检查的表和checksums表的引擎不一致,如分别是myisam和innodb,会引起复制错误:“different error on master and slave.”

–[no]check-replication-filters

默认在检查到主从复制过程中有被用..ignore..过滤掉的表,检查会中断并退出,如果想避开这个检查可以设置–no-check-replication-filters

–chunk-index(type: string)
工具默认在分块时会选取最合适的索引来explain确定chunk的大小,但如果你希望用其他索引来执行,可以用该参数来指定,工具会以FORCE INDEX的形式把指定的索引加进去

–chunk-index-columns(type: int)
可以用来指定组合索引中使用前几个列来辅助分块

–chunk-size
直接确定chunk的大小,默认1000行数据,但不建议使用,建议使用–chunk-time代替。

–chunk-time
默认是0.5秒,工具会根据当前系统运行繁忙程度计算出在该指定时间内可以处理的数据行数(即chunk),比较灵活。

–[no]empty-replicate-table
默认yes,每次检查表之前都去把checksums表中已有的该表信息删掉,以利于后续重新插入新检查信息。

–float-precision(type: int)
设置浮点数的四舍五入方式,以避免不同版本间或其他特定情况中,主从间因浮点数四舍五入的方式不同而导致查出不一致,If you specify a value of 2, for example, then the values 1.008 and 1.009 will be rounded to 1.01, and will checksum as equal

–function
计算checksum值时的函数,默认是CRC32,其他还有FNV1A_64, MURMUR_HASH, SHA1, MD5等。

–max-lag
默认1S,主从最大延迟,超过这个延迟时间,就会停下来等待从库同步,确定方法是采用Seconds_Behind_Master的值。

–progress
指定后可以按设定的参数将执行过程中的运行情况输出到STDERR,如主从延迟时从库的等待,等待时间等,指定时后跟两个参数值,默认是 “time,30″,前一个参数有:percentage, time, or iterations;后一个指定百分比,具体时间或者间隔的数目。

示例

# 比较employees数据库同步是否一致,结果显示所有表
# pt-table-checksum --nocheck-replication-filters --databases=employees --replicate=employees.checksums --create-replicate-table --host=10.27 --port 5002 -u root -p ""
# 第一次运行该命令需要添加加--create-replicate-table参数

pt-table-checksum解读
pt-table-checksum使用实践

pt-table-sync

高效同步MySQL表的数据。

系统类工具

pt-diskstats

一个对GNU/LINUX的交互式监控工具。

pt-fifo-split

模拟切割文件并通过管道传递给先入先出队列而不用真正的切割文件。

pt-summary

友好地收集和显示系统细分析概况。

pt-stalk

出现问题的时候收集MySQL的用于诊断的数据。

实用类工具

pt-archiver

将MySQL数据表中的记录归档到另一个表或文件,也可以直接进行记录的删除操作。

pt-find

查找MySQL表并执行指定的命令,和find命令类似。

pt-kill

pt-kill是一个简单而且很实用的查杀mysql线程和查询的工具,是percona toolkit的一部分,在因为空闲连接较多导致超过最大连接数、一些大/复杂/长sql查询导致mysql负载很高时,都需要将一些连接kill掉,这个工具主要就是这个用途。该工具从show processlist 中获取满足条件的连接或者从包含show processlist的文件中读取满足条件的连接并打印或者杀掉或者执行其他操作。

参数

匹配processlist的查杀线程或者会话的重要参数
(1) 按照命令command查杀线程
–match-command多个command之间用 | 分隔,否则会失效。
command类型:Query、Sleep、Binlog Dump、Connect、Delayed insert、Execute、Fetch、Init DB、Kill、Prepare、Processlist、Quit、Reset stmt、Table Dump
(2) 按state 来杀掉线程
–match-state多个state之间用 | 分隔,否则会失效。
state类型有:Locked、login、copy to tmp table、Copying to tmp table、Copying to tmp table on disk、Creating tmp table、executing、Reading from net、Sending data、Sorting for order、Sorting result、Table lock、Updating
注意state 的内容一定要严格匹配大小写,否则会杀不掉。
(3) 按info关键字来查杀线程
–match-info多个info之间用 | 分隔,否则会失效。
注意info的内容一定要严格匹配大小写,否则会杀不掉。
–ignore-info 不匹配
–match-info 匹配
info可以使用select、update、insert、delete来进行匹配,并可使用”|”进行多项匹配,如:”select|SELECT|delete|DELETE|update|UPDATE”
(4) 按照访问来源host/ip查杀线程
–ignore-host
–match-host
(5) 按照DB来查杀线程
–ignore-db
–match-db
(6) 按照数据库用户
–ignore-user
–match-user

行为参数
–kill
杀掉连接并且退出
–kill-query
只杀掉连接执行的语句,但是线程不会被终止
–print
打印满足条件的语句

其他参数
–interval
运行检查query的间隔。
–victim
有三种情况:
oldest (默认值),只杀最老的查询(最先发起的最长时间) 。这是防止被查杀是不是真的长时间运行的查询,他们只是长期等待 。这种种匹配按时间查询,杀死一个时间最高值 。
all
杀掉所有满足的线程
all-but-oldest
杀死所有,但最长的保留不杀
–busy-time
批次查询已运行的时间超过这个时间的线程;
–idle-time
杀掉sleep 了多少时间的连接线程,必须在–match-command sleep时才有效

示例

-- 执行一个长查询
> select sleep(5);
# 显示运行时间超过2秒的语句,--interval 指定每隔2秒检查一次
# pt-kill --busy-time 2 --print -u root -p "" --interval 2;
# 2018-07-26T16:52:09 KILL 16945 (Query 2 sec) select sleep(4)
# 2018-07-26T16:52:11 KILL 16945 (Query 4 sec) select sleep(4)

# 杀死运行时间超过2秒的语句
# pt-kill --busy-time 2 --kill -u root -p "" --interval 2;
# 每10秒检查一次,发现有sleeping的进程就给干掉
# pt-kill --match-command Sleep --victims all --interval 10 -u root -p "" --kill --print -S

# 查杀sleep大于5s的会话
# pt-kill --match-command Sleep --busy-time 5 -u root -p "" --interval 10 --run-time 1 --print --kill --victims all

# 查杀select大于10s的会话
# pt-kill --match-info "select" --kill --victims all --busy-time 20 -u root -p ""
# pt-kill --match-info "Select" --kill --victims all --busy-time 20 -u root -p ""
# pt-kill --match-info "SELECT" --kill --victims all --busy-time 20 -u root -p ""

# 查杀某IP来源的会话
# pt-kill --match-host "10.100.50.29" --print --victims all --busy-time 20  -u root -p ""

# 查杀访问某用户的会话
# pt-kill --match-user "" --kill --victims all --busy-time 20  -u root -p ""

# 杀掉正在进行filesort的sql
# pt-kill --match-command Query --match-state “Sorting result” busy-time 10 -u root -p "" --interval 10 --run-time 1  -print --kill --victims all
# pt-kill --match-command Query --match-state “Sorting result” busy-time 5 -u root -p "" --interval 10 --run-time 1  --print --kill --victims all

# 杀掉正在Sending data的sql
# pt-kill --match-command Query --match-state “Copying to tmp table” busy-time 10 -u root -p "" --interval 10 --run-time 1  --print --kill --victims all
# pt-kill --match-command Query --match-state “Copying to tmp table” busy-time 5 -u root -p "" --interval 10 --run-time 1  --print --kill --victims all

# 杀掉正在Copying to tmp table的sql
# pt-kill --match-command Query --match-state “Sending data”busy-time 10 -u root -p "" --interval 10 --run-time 1  --print --kill --victims all

percona-toolkit 使用教程
pt工具5-介绍pt-kill的使用

gh-ost

gh-ost是GitHub在2016年5月份开源的MySQL在线更改表定义工具,目的是解决一个经常碰到的问题:不断变化的产品需求会不断要求更改MySQL表结构。gh-ost通过一种影响小、可控制、可审计、操作简单的方案来改变线上表结构。它抛弃了pt-online-schema-change使用trigger来同步增量数据的方法,而通过模拟slave获取row格式的binlog的方式来获取增量数据。

参数

–host
数据库实例地址。

–port
数据库实例端口。

–user
数据库实例用户名。

–password
数据库实例密码。

–database
数据库名称。

–table
表名称。

–alter
ALTER语句的body部分,如”ALTER TABLE wing ADD COLUMN id int not null default 0”,使用gh-ost的-alter参数时,写成–alter ADD COLUMN id int not null default 0即可。

–allow-on-master
默认情况下gh-ost希望你连接一个从库进行binlog获取。如果你想连接主库进行整个迁移操作,需要加上此选项即可。gh-ost提供了三种方案连接方案。

–max-load
迁移过程中,gh-ost会时刻关注负载情况,负载阀值是使用者自己定义,比如数据库的最大连接数,如果超过阀值,gh-ost不会退出,会等待到负载在阀值以下继续执行。

–critical-load
这个指的是gh-ost退出阀值,当负载超过这个阀值,gh-ost会停止并退出。

–max-lag-millis
会监控从库的主从延迟情况,如果延迟秒数超过这个阀值,迁移不会退出,等待延迟秒数低于这个阀值继续迁移。这个是迁移中很大的一个问题,特别是在从库迁移时。
gh-ost监控复制延迟是通过检查gh-ost本身在实用程序更新日志表中注入的心跳事件来衡量的。也就是说,为了测量这个复制延迟,gh-ost不需要发出show slave status命令,也没有任何外部心跳机制。
当提供–throttle-control-replicas时,限流还会考虑指定主机上的延迟。通过查询gh-ost的更新日志表(其中gh-ost注入心跳)完成列出的主机上的延迟时间测量。
gh-ost能够利用毫秒测量复制延迟,当–max-lag-millis小于1000,即小于1秒时,gh-ost将进行限流。

–throttle-control-replicas
和–max-lag-millis参数相结合,这个参数指定主从延迟的数据库实例。

–initially-drop-ghost-table
gh-ost执行前会创建两张_xx_ghc和_xx_gho表,如果这两张表存在,且加上了这个参数,那么会自动删除原gh表,重新创建,否则退出。_xx_gho表相当于老表的全量备份,_xx_ghc表数据是数据更改日志,理解成增量备份。

–initially-drop-ghost-table
gh-ost操作之前,检查并删除已经存在的ghost表。该参数不建议使用,请手动处理原来存在的ghost表。默认不启用该参数,gh-ost直接退出操作。

–initially-drop-socket-file
gh-ost执行时会创建socket文件,退出时不会删除,下次执行gh-ost时会报错,加上这个参数,gh-ost会强制删除已经存在的socket文件。该参数不建议使用,可能会删除一个正在运行的gh-ost程序,导致DDL失败。

–initially-drop-old-table
gh-ost操作之前,检查并删除已经存在的旧表。该参数不建议使用,请手动处理原来存在的ghost表。默认不启用该参数,gh-ost直接退出操作。

–ok-to-drop-table
gh-ost执行完以后是否删除老表,加上此参数会自动删除老表。

–cut-over
自动执行rename操作,选择cut-over类型:atomic/two-step,atomic(默认)类型的cut-over是github的算法,two-step采用的是facebook-OSC的算法。

–cut-over-lock-timeout-seconds
gh-ost在cut-over阶段最大的锁等待时间,当锁超时时,gh-ost的cut-over将重试。(默认值:3)

–switch-to-rbr
让gh-ost自动将从库的binlog_format转换为ROW格式。

–assume-rbr
确认gh-ost连接的数据库实例的binlog_format=ROW的情况下,可以指定–assume-rbr,这样可以禁止从库上运行stop slave,start slae,执行gh-ost用户也不需要SUPER权限。

–panic-flag-file
这个文件被创建,迁移操作会被立即终止退出。

–throttle-flag-file
此文件存在时操作暂停,删除文件操作会继续。

–postpone-cut-over-flag-file
当这个文件存在的时候,gh-ost的cut-over阶段将会被推迟,直到该文件被删除。

–concurrent-rowcount
该参数如果为True(默认值),则进行row-copy之后,估算统计行数(使用explain select count(*)方式),并调整ETA时间,否则,gh-ost首先预估统计行数,然后开始row-copy。

–exact-rowcount
准确统计表行数(使用select count(*)的方式),得到更准确的预估时间。

–execute
如果确定执行,加上这个参数。

–default-retries
各种操作在panick前重试次数。(默认为60)

–chunk-size
迁移过程是一步步分批次完成的,这个参数是指事务每次提交的行数,默认是1000。

–timestamp-old-table
使旧表包含时间戳值,旧表是在成功迁移结束时将原始表重新命名的内容。例如,如果表是gh_ost_test,那么旧表通常是_gh_ost_test_del。使用–timestamp-old-table后,它将是_gh_ost_test_20170221103147_del。

–throttle-http
提供一个HTTP端点,gh-ost将在给定的URL上发出HEAD请求,并在响应状态码不是200时进行限流。URL可以通过交互式命令动态查询和更新,空的URL表示禁用HTTP检查。

–approve-renamed-columns
当做(change old_name new_name …)动作时,gh-ost分析语句以尝试将旧列名称与新列名称相关联,如果它检测到确实是重命名操作,默认情况下将会打印出信息并退出。但除非你提供–approve-renamed-columns,强制发出迁移操作。
如果你认为gh-ost解析错误,并且实际上并且没有重命名,你可以改为传入–skip-renamed-columns,这将导致gh-ost取消关联列值,数据将不会在这些列之间复制。

–skip-foreign-key-checks
默认情况下,gh-ost会验证迁移表中存不存在外键,如果存在就会报错并退出;在具有大量表的服务器上,此检查可能需要很长时间。如果你确定没有外键存在(表没有引用其他表,也没有被其他表引用)并希望保存检查时间,可以使用–skip-foreign-key-checks。但如果表上有外键,使用这个参数则会清除外键,千万注意。

–discard-foreign-keys
该操作很危险,意味着将默默丢弃表上存在的任何外键。目前,gh-ost不支持迁移表上的外键(当它在迁移表上注意到外键时,它会保留)。但是,它能够支持通过此标志删除外键,如果你想这么干,这是一个有用的选项。使用下来感觉跟–skip-foreign-key-checks参数作用一样。

–replica-server-id
gh-ost原理是通过模拟slave从而获得binlog,其默认server-id为99999,如果你运行多个迁移,那么你必须为每个gh-ost进程提供一个不同的,唯一的server-id。也可以使用进程ID当做server-id,例如:–replica-server-id = $((1000000000 + ))。

–migrate-on-replica
通常,gh-ost用于在主服务器上迁移表。如果你只希望在从库上执行全部迁移,使用–migrate-on-replica参数将gh-ost连接到从库进行迁移。

–assume-master-host
默认情况下,gh-ost更倾向连接从库来进行迁移。gh-ost通过爬取复制拓扑来推断主服务器的身份,你可以通过–assume-master-host = the.master.com明确告诉gh-ost主服务器的身份。这在以下方面很有用:
主 – 主拓扑结构(与–allow-master-master一起使用),其中gh-ost可以随意选择其中一个主协同者,这种情况你可以选择一个特定的主库。
tungsten复制器拓扑结构(与–tungsten一起使用),其中gh-ost无法抓取并检测主节点。

–dml-batch-size
gh-ost从二进制日志读取事件,并将它们应用到ghost表上。它采用的方式是将多个事件分组应用于单个事务中。这可以提供更好的写入吞吐量,因为我们不需要将每个事务日志同步到的磁盘。
此选项就是控制批量写入的大小,允许的值是1 – 100,其中1表示不分组处理(二进制日志中的每个事件在其自己的事务中应用到ghost表上)。默认值是10。

–heartbeat-interval-millis
用来控制注入心跳事件的频率(就是_xx_ghc表),用来测量主从延迟。你应该设置heartbeat-interval-millis <= max-lag-millis。否则,将失去粒度和效果。默认值100。其–max-lag-millis值应该在300-500之间。

–conf
指定gh-ost凭据的文件,如下格式。
[client]
user=gromit
password=123456

–debug
输出详细日志。

–verbose
执行过程输出日志。

示例

安装

 

使用

GH-OST:配置使用实践
gh-ost —— Github 的在线 MySQL 数据库移植工具