MySQL(十三)管理PT-tools工具安装与数据归档-在线DDL-在线查杀与慢查询

MySQL管理PT-tools工具安装与数据归档-在线DDL-在线查杀与慢查询

PT-Tools工具介绍与安装：

• Percona Toolkit简称pt工具 PT-Tools，是Percona公司开发用于管理MySQL的工具，功能包括检查主从复制的数据一致性、检查重复索引、定位IO占用高的表文件、在线DDL等，DBA熟悉掌握后将极大提高工作效率。
• 根据你的系统，MySQL版本选择不同的版本，MySQL 5.1之前的好像用不了，具体记得不太清楚了，有兴趣的可以研究试一下（说真的现在还在用5.1版本的MySQL ,就太OUT了，抓紧upgrade.)

下载地址： https://www.percona.com/downloads/percona-toolkit/LATEST/

功能使用级别：

主要就是如下这些工具，很多用的都不是特别多，下面我主要给大家介绍对DBA来说是杀手锏的几个，用的很多，效率也极高。

• Level 1 (必须一定掌握)

1. pt-archive ----MySQL的在线归档，无影响生产
2. pt-kill -----自定义查杀。确保慢查询及恶性攻击对生产无影响
3. pt-online-schema-change ----在线DDL操作，对上亿的大表加索引加字段且对生产无影响
4. pt-query-digest -----慢查询Log的分析。

• Level 2（大家可以了解，遇到问题可以想到这个工具可以协助大家解决很多问题）

1. pt-slave-delay ---就是指定从库比主库延迟多长时间
2. pt-table-checksum & pt-table-sync-----检查主从是否一致性-----检查主从不一致之后用这个工具进行处理这两个一搬是搭配使用
3. pt-find ---找出几天之前建立的表
4. pt-slave-restart -----主从报错，跳过报错
5. pt-summary ---整个系统的的概述
6. pt-mysql-summary ---MySQL的表述，包括配置文件的描述
7. pt-duplicate-key-checker ---检查数据库重复索引

安装PT工具：

[root@db01 ~]# wget https://www.percona.com/downloads/percona-toolkit/3.0.6/binary/redhat/7/x86_64/percona-toolkit-3.0.6-1.el7.x86_64.rpm
[root@db01 ~]# yum -y localinstall percona-toolkit-3.0.6-1.el7.x86_64.rpm

pt-archive在线归档：

• 用此操作的表必须有主键。如果没有主键，DBA也可以下岗了，没有主键你竟然生产，给自己挖坑，怪不得别人。（你来之前的就再说）

1. 归档历史数据；
2. 在线删除大批量数据；
3. 数据导出和备份；
4. 数据远程归档；
5. 数据清理

pt-archive参数解释：

# pt-archive –help 有很多参数，用这可以help一下如：几个常用说明

① --limit=10000      每次取1000行数据用pt-archive处理
① --txn-size 1000    设置1000行为一个事务提交一次，
① --where'id<3000'   设置操作条件
① --progress 5000    每处理5000行输出一次处理信息
① --statistics       输出执行过程及最后的操作统计。（只要不加上--quiet，默认情况下ptarchive都会输出执行过程的）
① --charset=UTF8     指定字符集为UTF8—这个最后加上不然可能出现乱码。
① --bulk-delete      批量删除source上的旧数据(例如每次1000行的批量删除操作)
① --purge            删除条件内的数据

pt-archive 删除数据：

# --source               指定MySQL实例源
# -h                     指定地址
# -P                     指定端口
# -u                     指定用户
# -p                     指定密码
# -D                     指定要操作的库名
# -t                     指定要操作的表名
# --where                指定条件
# --purge                指定是要删除条件内的数据
# --limit                指定每次处理10行数据
# --no-check-charset     不检查字符集
# --statistics           输出执行过程和操作统计
# --progress             指定每处理10行显示一次处理信息
[root@db01 ~]# pt-archiver --source h=172.18.1.76,P=3306,u=root,p='123456',D=course,t=temp --where 'id<=100' --purge --limit=10 --no-check-charset --statistics --progress 10
TIME                ELAPSED   COUNT
2019-11-16T13:50:23       0       0
2019-11-16T13:50:23       0      10
2019-11-16T13:50:23       0      20
2019-11-16T13:50:24       0      30
2019-11-16T13:50:24       0      40
2019-11-16T13:50:24       0      50
2019-11-16T13:50:24       0      60
2019-11-16T13:50:24       1      70
2019-11-16T13:50:24       1      80
2019-11-16T13:50:24       1      90
2019-11-16T13:50:24       1     100
2019-11-16T13:50:24       1     100
Started at 2019-11-16T13:50:23, ended at 2019-11-16T13:50:24
Source: D=course,P=3306,h=172.18.1.76,p=...,t=temp,u=root
SELECT 100
INSERT 0
DELETE 100
Action        Count       Time        Pct
commit          101     1.2160      91.26
deleting        100     0.0873       6.55
select           11     0.0162       1.21
other             0     0.0130       0.98

pt-archive 归档删除数据：

• 之前删除了 150行数据，所以现在id < 1000 的只有850行了
• 源表 与 目标表表结构要一致

# --charset      指定操作的表字符集
# --source       指定MySQL实例源
# -h             指定地址
# -P             指定端口
# -u             指定用户
# -p             指定密码
# -D             指定要操作的库名
# -t             指定要操作的表名
# --dest         指定目标MySQL实例源
# --where        指定条件
# --limit        指定每次处理100行数据
# --statistics   输出执行过程和操作统计
# --progress     指定每处理100行显示一次处理信息
[root@db01 ~]# pt-archiver --charset=utf8mb4 --source h=172.18.1.76,P=3306,u=root,p='123456',D=course,t=temp --dest h=172.18.1.76,P=3306,u=root,p='123456',D=course,t=temp2 --where 'id<=1000'  --limit=100 --statistics --progress 100
TIME                ELAPSED   COUNT
2019-11-16T14:03:49       0       0
2019-11-16T14:03:51       1     100
2019-11-16T14:03:52       3     200
2019-11-16T14:03:54       4     300
2019-11-16T14:03:55       6     400
2019-11-16T14:03:57       7     500
2019-11-16T14:03:58       9     600
2019-11-16T14:04:00      10     700
2019-11-16T14:04:01      12     800
2019-11-16T14:04:02      13     850
Started at 2019-11-16T14:03:49, ended at 2019-11-16T14:04:02
Source: A=utf8mb4,D=course,P=3306,h=172.18.1.76,p=...,t=temp,u=root
Dest:   A=utf8mb4,D=course,P=3306,h=172.18.1.76,p=...,t=temp2,u=root
SELECT 850
INSERT 850
DELETE 850
Action         Count       Time        Pct
commit          1702    12.3622      93.53
deleting         850     0.3413       2.58
inserting        850     0.3302       2.50
select            10     0.0080       0.06
other              0     0.1750       1.32

# MySQL数据库查看数据状态
mysql> select count(1) from temp;
+----------+
| count(1) |
+----------+
| 29999000 |
+----------+
1 row in set (18.46 sec)

mysql> select count(1) from temp2;
+----------+
| count(1) |
+----------+
|      850 |
+----------+
1 row in set (0.03 sec)

pt-archive数据归档批量与非批量性能对比

一、数据批量归档与非批量归档性能对比

• pt-archive使用批量与非批量模式归档 10 万条数据，对比两种方式性能。

1. 非批量归档数据

[root@dmp-udp1 ~]# pt-archiver --source h=10.186.61.211,P=3306,u=root,p='123456',D=testdb,t=sbtest1 --dest h=10.186.61.213,P=3307,u=root,p='123456',D=testdb,t=sbtest1 \
--where 'id<=500000' --no-check-charset --txn-size 1000 --progress 20000 --limit=1000 --statistics --purge
TIME                ELAPSED   COUNT
2020-06-24T03:02:31       0       0
2020-06-24T03:02:42      11   20000
2020-06-24T03:02:54      23   40000
2020-06-24T03:03:07      36   60000
2020-06-24T03:03:21      50   80000
2020-06-24T03:03:34      63  100000
2020-06-24T03:03:34      63  100000
Started at 2020-06-24T03:02:31, ended at 2020-06-24T03:03:34
Source: D=testdb,P=3306,h=10.186.61.211,p=...,t=sbtest1,u=root
Dest:   D=testdb,P=3307,h=10.186.61.213,p=...,t=sbtest1,u=root
SELECT 100000
INSERT 100000
DELETE 100000
Action         Count       Time        Pct
deleting      100000    20.8761      33.00
commit           202    17.2942      27.34
inserting     100000    16.9534      26.80
select           101     0.6427       1.02
other              0     7.5005      11.86

2. 批量归档数据

[root@dmp-udp1 ~]# pt-archiver --source h=10.186.61.211,P=3306,u=root,p='123456',D=testdb,t=sbtest1 --dest h=10.186.61.213,P=3307,u=root,p='123456',D=testdb,t=sbtest1 \
--where 'id<=600000' --no-check-charset --txn-size 1000 --progress 20000 --limit=1000 --bulk-delete --bulk-insert --statistics --purge
TIME                ELAPSED   COUNT
2020-06-24T03:11:32       0       0
2020-06-24T03:11:33       1   20000
2020-06-24T03:11:35       3   40000
2020-06-24T03:11:36       4   60000
2020-06-24T03:11:38       6   80000
2020-06-24T03:11:41       9  100000
2020-06-24T03:11:41       9  100000
Started at 2020-06-24T03:11:32, ended at 2020-06-24T03:11:41
Source: D=testdb,P=3306,h=10.186.61.211,p=...,t=sbtest1,u=root
Dest:   D=testdb,P=3307,h=10.186.61.213,p=...,t=sbtest1,u=root
SELECT 100000
INSERT 100000
DELETE 100000
Action              Count       Time        Pct
commit                202     2.2180      23.97
bulk_deleting         100     1.7307      18.70
bulk_inserting        100     1.4240      15.39
select                101     1.0553      11.41
print_bulkfile     100000    -0.6739      -7.28
other                   0     3.4986      37.81

二、总结

模拟场景	非批量操作	批量操作
归档 10 万条数据，删除原表数据	63s	10s

pt-kill在线查杀：

• pt-kill 是一个优秀的kill MySQL连接的一个工具，是percona toolkit的一部分，这个工具可以kill掉你想Kill的任何语句，特别出现大量的阻塞，死锁，某个有问题的sql导致mysql负载很高黑客攻击。当有很多语句时你不可能用show processlist去查看，当QPS很高时，你根本找不到你找的语句或ID,这时就可以用Ptkill来帮你帮完成。他可以根据运行时间，开源IP,用户名，数据库名。SQL语句,sleep,running 等状态进行匹配然后kill.能匹配的太多了一一举例肯定不现实，我会拿几个案例让大家看看。。大家可以用pt-kill –help 进行查看帮助。

pt-kill参数解释：

# pt-kill –help 有很多参数，用这可以help一下如：几个常用说明

① --daemonize          放在后台以守护进程的形式运行；
① --interval           多久运行一次，单位可以是s,m,h，d等默认是s –不加这个默认是5秒
① --victims            默认是oldest,只杀最古老的查询。这是防止被查杀是不是真的长时间运行的查询，他们只是长期等待 这种种匹配按时间查询，杀死一个时间最高值。
① --all                杀掉所有满足的线程
① --kill-query         只杀掉连接执行的语句，但是线程不会被终止
① --print              打印满足条件的语句
① --busy-time          批次查询已运行的时间超过这个时间的线程；
① --idle-time          杀掉sleep 空闲了多少时间的连接线程，必须在--match-command sleep时才有效—也就是匹配使用
① -- –match-command    匹配相关的语句。
① ----ignore-command   忽略相关的匹配。 # 这两个搭配使用一定是ignore-commandd在前 match-command在后，
① --match-db cdelzone  匹配哪个库

① command有：          Query、Sleep、Binlog Dump、Connect、Delayed insert、Execute、Fetch、Init DB、Kill、Prepare、Processlist、Quit、Reset stmt、Table Dump

pt-kill查杀慢查询与恶性攻击：

# --interval           指定多久检查一次查询等待时间超过阀值得语句
# --log-dsn
# -D                   指定记录信息存放的数据库
# -t                   指定存放记录信息的表名
# --create-log-table   如果没有指定的记录信息的表就自动创建
# --host               指定地址
# --user               指定用户
# --password           指定密码
# --port               指定端口
# --busy-time          指定语句执行超过时间则被记录
# --print              打印被kill杀死与记录的语句到控制台
# --kill-query         杀死达到执行时间阀值得语句
# --match-info         指定语句类型（ pt-kill区分大小写，所以SELECT与select都要写 ）
# --victims            
# all                  杀掉满足条件的所有的线程

[root@db01 ~]# pt-kill --interval 3 --log-dsn D=course,t=killed_sql_table --create-log-table --host=172.18.1.76 --user=root --password='123456' --port=3306 --busy-time=10 --print --kill-query --match-info "SELECT|select" --victims all &
[1] 6790

# 实现死锁慢查询10秒

# ---------------- 会话 1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from temp where id=1001 for update;
+------+---------+------+------+---------------------+
| id   | num     | k1   | k2   | dt                  |
+------+---------+------+------+---------------------+
| 1001 | 7122849 | AA   | BCFG | 2019-11-16 14:27:56 |
+------+---------+------+------+---------------------+
1 row in set (0.00 sec)

mysql> update temp set k1='BB' where id=1001;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

# ---------------- 会话 2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

# ---------------- 查看pt-kill杀死并记录的SQL语句信息
mysql> select * from temp where id=1001 for update;
ERROR 1317 (70100): Query execution was interrupted

mysql> select * from killed_sql_table\G
*************************** 1. row ***************************
   kill_id: 1
 server_id: 1
 timestamp: 2019-11-16 14:28:45
    reason: Exceeds busy time
Query matches Info spec
kill_error: 
        Id: 65
      User: root
      Host: localhost
        db: course
   Command: Query
      Time: 10
     State: statistics
      Info: select * from temp where id=1001 for update
   Time_ms: NULL
1 row in set (0.00 sec)

pt-kill自动化：

pt-osc在线DDL:

• pt-online-schema-change 业界简称 pt-osc 在线更改表结构
• MySQL 大字段的DDL操作：加减字段、索引、修改字段属性等，在5.1之前都是非常耗时耗力的，特别是会对MySQL服务产生影响。在5.1之后随着Plugin Innodb的出现在线加索引的提高了很多，但是还会影响（时间缩短了），主要是出现了MDL锁(MySQL为了保护数据字典元数据，使用了metadata lock)。不过5.6可以避免上面的情况，但目前大部分在用的版本都是5.6之前的，所以DDL操作一直是运维人员“头疼"的事。那如何在不锁表的情况下安全快速地更新表结构？
• pt-osc模仿MySQL内部的改表方式进行改表，但整个改表过程是通过对原始表的拷贝来完成的，即在改表过程中原始表不会被锁定，并不影响对该表的读写操作。
• 首先，osc创建与原始表相同的不包含数据的新表（下划线开头）并按照需求进行表结构的修改，然后将原始表中的数据按逐步拷贝到新表中，当拷贝完成后，会自动同时修改原始表和新表的名字并默认将原始表删除。
• 有两个注意点：被操作的表如果有 触发器，或外键用不了。要特别注意（标准规范MySQL是不建议用外键与触发器的）如果有，要把外键与触发器去掉再操作。
• pt-online-schema-change ----在线DDL操作，对上亿的大表加索引加字段且对生产无影响

工作原理：

1. 创建一个和要执行 alter 操作的表一样的新的空表结构(是alter之前的结构)

2. 在新表执行alter table 语句（速度应该很快）

3. 在原表中创建触发器3个触发器分别对应insert,update,delete操作

4. 以一定块大小从原表拷贝数据到临时表，拷贝过程中通过原表上的触发器在原表进行的写操作都会更新到新建的临时表

5. Rename 原表到old表中，在把临时表Rename为原表

6. 如果有参考该表的外键，根据alter-foreign-keys-method参数的值，检测外键相关的表，做相应设置的处理

7.  默认最后将旧原表删除如果执行失败了，或手动停止了，需要手动删除下划线开头的表(_表名)及三个触发器

pt-osc参数解释：

• 参考连接：https://www.cnblogs.com/wsl222000/p/9072750.html

① --max-load            默认为Threads_running=25。每个chunk拷贝完后，会检查SHOW GLOBAL STATUS的内容，检查指标是否超过了指定的阈值。如果超过，则先暂停。这里可以用逗号分隔，指定多个条件，每个条件格式： status指标=MAX_VALUE或者status指标:MAX_VALUE。如果不指定MAX_VALUE，那么工具会这只其为当前值的120%。

① --critical-load       默认为Threads_running=50。用法基本与--max-load类似，如果不指定MAX_VALUE，那么工具会这只其为当前值的200%。如果超过指定值，则工具直接退出，而不是暂停。

① --user: -u，          连接的用户名
① --password： -p，     连接的密码
① --database： -D，     连接的数据库
① --port -P，           连接数据库的端口
① --host: -h，          连接的主机地址
① --socket: -S，        连接的套接字文件
① --statistics          打印出内部事件的数目，可以看到复制数据插入的数目。
① --dry-run             创建和修改新表，但不会创建触发器、复制数据、和替换原表。并不真正执行，可以看到生成的执行语句，了解其执行步骤与细节。--dry-run与--execute必须指定一个，二者相互排斥。和--print配合最佳。
① --execute             确定修改表，则指定该参数。真正执行。--dry-run与--execute必须指定一个，二者相互排斥。
① --print               打印SQL语句到标准输出。指定此选项可以让你看到该工具所执行的语句，和--dry-run配合最佳。
① --progress            复制数据的时候打印进度报告，二部分组成：第一部分是百分比，第二部分是时间。
① --quiet -q，          不把信息标准输出。

pt-osc在线创建索引：

# 参数可以在参考连接取到
[root@db01 ~]# pt-online-schema-change --user=root --password='123456' --port=3306 --host=172.18.1.76 --critical-load Threads_running=100 --alter "add index idx_k1(k1)" D=course,t=temp --print --execute

No slaves found.  See --recursion-method if host db01.novalocal has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  analyze_table, 10, 1
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `course`.`temp`...
Creating new table...
CREATE TABLE `course`.`_temp_new` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `num` int(11) DEFAULT NULL,
  `k1` char(2) DEFAULT NULL,
  `k2` char(4) DEFAULT NULL,
  `dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_num` (`num`)
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Created new table course._temp_new OK.
Altering new table...
ALTER TABLE `course`.`_temp_new` add index idx_k1(k1)
Altered `course`.`_temp_new` OK.
2019-11-16T16:30:55 Creating triggers...
2019-11-16T16:30:55 Created triggers OK.
2019-11-16T16:30:55 Copying approximately 850 rows...
INSERT LOW_PRIORITY IGNORE INTO `course`.`_temp_new` (`id`, `num`, `k1`, `k2`, `dt`) SELECT `id`, `num`, `k1`, `k2`, `dt` FROM `course`.`temp` LOCK IN SHARE MODE /*pt-online-schema-change 7093 copy table*/
2019-11-16T16:30:55 Copied rows OK.
2019-11-16T16:30:55 Analyzing new table...
2019-11-16T16:30:55 Swapping tables...
RENAME TABLE `course`.`temp` TO `course`.`_temp_old`, `course`.`_temp_new` TO `course`.`temp`
2019-11-16T16:30:55 Swapped original and new tables OK.
2019-11-16T16:30:55 Dropping old table...
DROP TABLE IF EXISTS `course`.`_temp_old`
2019-11-16T16:30:55 Dropped old table `course`.`_temp_old` OK.
2019-11-16T16:30:55 Dropping triggers...
DROP TRIGGER IF EXISTS `course`.`pt_osc_course_temp_del`
DROP TRIGGER IF EXISTS `course`.`pt_osc_course_temp_upd`
DROP TRIGGER IF EXISTS `course`.`pt_osc_course_temp_ins`
2019-11-16T16:30:55 Dropped triggers OK.
Successfully altered `course`.`temp`.

# 查看MySQL数据库 temp表k1字段是否加了索引
mysql> desc temp;
+-------+-----------+------+-----+-------------------+-----------------------------------------------+
| Field | Type      | Null | Key | Default           | Extra                                         |
+-------+-----------+------+-----+-------------------+-----------------------------------------------+
| id    | int(11)   | NO   | PRI | NULL              | auto_increment                                |
| num   | int(11)   | YES  | MUL | NULL              |                                               |
| k1    | char(2)   | YES  | MUL | NULL              |                                               |
| k2    | char(4)   | YES  |     | NULL              |                                               |
| dt    | timestamp | NO   |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------------------------+
5 rows in set (0.00 sec)