percona-toolkit的安装与使用

in 编程
关注公众号【好便宜】( ID:haopianyi222 ),领红包啦~
阿里云,国内最大的云服务商,注册就送数千元优惠券:https://t.cn/AiQe5A0g
腾讯云,良心云,价格优惠: https://t.cn/AieHwwKl
搬瓦工,CN2 GIA 优质线路,搭梯子、海外建站推荐: https://t.cn/AieHwfX9

一、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

 

关注公众号【好便宜】( ID:haopianyi222 ),领红包啦~
阿里云,国内最大的云服务商,注册就送数千元优惠券:https://t.cn/AiQe5A0g
腾讯云,良心云,价格优惠: https://t.cn/AieHwwKl
搬瓦工,CN2 GIA 优质线路,搭梯子、海外建站推荐: https://t.cn/AieHwfX9
扫一扫关注公众号添加购物返利助手,领红包
Comments are closed.

推荐使用阿里云服务器

超多优惠券

服务器最低一折,一年不到100!

朕已阅去看看