percona-toolkit的安装与使用

一、percona-toolkit的安装请参考：https://blog.csdn.net/yuanyk1222/article/details/100066788

二、下面来说说此工具的一些用法：

pt-online-schema-change

功能可以在线整理表结构，收集碎片，给大表添加字段和索引。避免出现锁表导致阻塞读写的操作。不过针对 MySQL 5.7 版本，就可以不需要使用这个命令，直接在线 online DDL 就可以了。

Mysql数据库中有如下表：

mysql> desc t;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| k     | int(11)     | NO   | MUL | NULL    |       |
| b     | varchar(10) | YES  |     | NULL    |       |
| name  | varchar(16) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

如果我们想在线增加一个字段，可以执行如下命令：

[root@i-vvwtw5ne pt-kill]# pt-online-schema-change --user=root --password=Falsesoul1207 --host=192.192.18.34 --alter="add column c int after b" D=test,t=t --execute
Found 1 slaves:
  i-s70m0mun
Will check slave lag on:
  i-s70m0mun
*******************************************************************
 Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
 is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER
 possibly with SSL_ca_file|SSL_ca_path for verification.
 If you really don"t want to verify the certificate and keep the
 connection open to Man-In-The-Middle attacks please set
 SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
*******************************************************************
  at /usr/bin/pt-online-schema-change line 6576.
Operation, tries, wait:
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `test`.`t`...
Creating new table...
Created new table test._t_new OK.
Altering new table...
Altered `test`.`_t_new` OK.
2019-12-08T00:17:36 Creating triggers...
2019-12-08T00:17:36 Created triggers OK.
2019-12-08T00:17:36 Copying approximately 1 rows...
2019-12-08T00:17:36 Copied rows OK.
2019-12-08T00:17:36 Swapping tables...
2019-12-08T00:17:36 Swapped original and new tables OK.
2019-12-08T00:17:36 Dropping old table...
2019-12-08T00:17:36 Dropped old table `test`.`_t_old` OK.
2019-12-08T00:17:36 Dropping triggers...
2019-12-08T00:17:36 Dropped triggers OK.
Successfully altered `test`.`t`.

再查看原来的表结构，发再增加字段成功，在b字段后面成功增加了一个字段c：

mysql> desc t;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| k     | int(11)     | NO   | MUL | NULL    |       |
| b     | varchar(10) | YES  |     | NULL    |       |
| c     | int(11)     | YES  |     | NULL    |       |
| name  | varchar(16) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

重点说下pt-kill用法：

show  PROCESSLIST结果如下：

 print执行时间超过10s的查询:

[root@i-vvwtw5ne ~]# pt-kill -h192.192.18.34 -P3306 -uroot -pFalsesoul1207  --busy-time=10 --print 
# 2019-12-08T19:53:06 KILL 67432267 (Query 33 sec) do sleep(60000)

kill执行时间超过11s的查询:

[root@i-vvwtw5ne ~]# pt-kill -h192.192.18.34 -P3306 -uroot -pFalsesoul1207  --busy-time=10 --kill

每隔10s执行kill所有的“Command”条件为“Query”的查询:

[root@i-vvwtw5ne ~]# pt-kill -h192.192.18.34 -P3306 -uroot -pFalsesoul1207  --busy-time=10 --match-command=Query --victims=all --interval=10 --kill

print所以“State”条件为“User sleep”的查询:

[root@i-vvwtw5ne ~]# pt-kill -h192.192.18.34 -P3306 -uroot -pFalsesoul1207  --busy-time=10 --match-state="User sleep" --victims=all --interval=10 --print
# 2019-12-08T19:59:34 KILL 67432969 (Query 12 sec) do sleep(6000000)

每10s打印一次数据库为test state=User sleep的sql语句

[root@i-vvwtw5ne ~]# pt-kill -h192.192.18.34 -P3306 -uroot -pFalsesoul1207 --match-db="test"  --busy-time=10 --match-state="User sleep" --victims=all --interval=10 --print