pt-kill工具的使用-每日运维

pt-kill工具的使用

一命令介绍

1.1 pt-kill - Kill

MySQL queries that match certain criteria.
终止指定连接

1.2 查看help帮助命令

[root@node3 ~]# pt-kill --help
pt-kill kills MySQL connections. pt-kill connects to MySQL and gets queries from
SHOW PROCESSLIST if no FILE is given. Else, it reads queries from one or more
FILE which contains the output of SHOW PROCESSLIST. If FILE is -, pt-kill reads
from STDIN. For more details, please use the --help option, or try ‘perldoc
/root/percona-toolkit-3.6.0/bin/pt-kill’ for complete documentation.

Usage: pt-kill [OPTIONS] [DSN]

Options:

–ask-pass Prompt for a password when connecting to MySQL
– -A Default character set
–config=A Read this comma-separated list of config files; if
specified, this must be the first option on the
command line
–create-log-table Create the --log-dsn table if it does not exist
–daemonize Fork to the background and detach from the shell
–database=s -D The database to use for the connection
–defaults-file=s -F Only read mysql options from the given file
–filter=s Discard events for which this Perl code doesn’t
return true
–group-by=s Apply matches to each class of queries grouped by
this SHOW PROCESSLIST column
–help Show help and exit
–host=s -h Connect to host (default localhost)
–interval=m How often to check for queries to kill. Optional
suffix s=seconds, m=minutes, h=hours, d=days; if no
suffix, s is used.
–json Prints killed queries as JSON, must be used with –
print
–json-fields=s Specify a list of additional key:value pairs to
include in JSON output when using --json, the value
of this parameter must be specified in the format of:
–kill-busy-commands=s group: Actions (default Query)
–log=s Print all output to this file when daemonized
–log-dsn=d Store each query killed in this DSN
–password=s -p Password to use when connecting
–pid=s Create the given PID file
–port=i -P Port number to use for connection
–query-id Prints an ID of the query that was just killed
–rds Denotes the instance in question is on Amazon RDS
–run-time=m How long to run before exiting. Optional suffix s=
seconds, m=minutes, h=hours, d=days; if no suffix, s
is used.
–sentinel=s Exit if this file exists (default /tmp/pt-kill-
sentinel)
–set-vars=A Set the MySQL variables in this comma-separated list
of variable=value pairs
–slave-password=s Sets the password to be used to connect to the slaves
–slave-user=s Sets the user to be used to connect to the slaves
–socket=s -S Socket file to use for connection
–stop Stop running instances by creating the --sentinel file
–[no]strip-comments Remove SQL comments from queries in the Info column
of the PROCESSLIST (default yes)
–user=s -u User for login if not current user
–verbose -v Print information to STDOUT about what is being done
–version Show version and exit
–[no]version-check Check for the latest version of Percona Toolkit,
MySQL, and other programs (default yes)
–victims=s Which of the matching queries in each class will be
killed (default oldest)
–wait-after-kill=m Wait after killing a query, before looking for more
to kill. Optional suffix s=seconds, m=minutes, h=
hours, d=days; if no suffix, s is used.
–wait-before-kill=m Wait before killing a query. Optional suffix s=
seconds, m=minutes, h=hours, d=days; if no suffix, s
is used.

Actions:

–execute-command=s Execute this command when a query matches
–fingerprint Modifies the --print behaviour and forces the query
fingerprint to be displayed instead of an original
query
–kill Kill the connection for matching queries
–kill-query Kill matching queries
–print Print a KILL statement for matching queries; does not
actually kill queries

二实验部分：终止执行时间超过30秒的慢查询

案例一：pt-kill命令中对Command列为Query的操作进行终止

2.1 开启general log日志

vim /etc/my.cnf
#开启general log日志
general_log=1
general_log_file=/data/mysql/log/query.log

2.2 查看pt_user用户权限

[root@node3 bin]# mysql -upt_user -ppt_pass -P3306 -h192.168.100.55
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.35-27.1 Percona XtraDB Cluster (GPL), Release rel27, Revision 84d9464, WSREP version 26.1.4.3

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

show grants for ‘pt_user’@’%’\G
*************************** 1. row ***************************
Grants for pt_user@%: GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON . TO pt_user@%
*************************** 2. row ***************************
Grants for pt_user@%: GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,TELEMETRY_LOG_ADMIN,XA_RECOVER_ADMIN ON . TO pt_user@%
2 rows in set (0.00 sec)

2.3 终止执行时间超过30秒的慢查询

具体命令如下

pt-kill h=192.168.100.55,u=pt_user,p=pt_pass,P=3306 --busy-time 30 --interval 10 --print --kill --match-info “(?i-xsm:select)”

2.4 模拟慢查询

select sleep(1000)

2.5 查看general_log日志

2024-07-06T17:02:28.826054Z 18 Query select sleep(1000)
2024-07-06T17:02:38.823592Z 17 Query SHOW FULL PROCESSLIST
2024-07-06T17:02:48.829087Z 17 Query SHOW FULL PROCESSLIST
2024-07-06T17:02:58.833705Z 17 Query SHOW FULL PROCESSLIST
2024-07-06T17:02:58.836946Z 17 Query KILL ‘18’
2024-07-06T17:02:58.851566Z 19 Connect pt_user@node3 on db1 using SSL/TLS
2024-07-06T17:02:58.853084Z 19 Query select sleep(1000)
2024-07-06T17:03:08.856268Z 17 Query SHOW FULL PROCESSLIST
2024-07-06T17:03:18.864543Z 17 Query SHOW FULL PROCESSLIST
2024-07-06T17:03:28.878991Z 17 Query SHOW FULL PROCESSLIST
2024-07-06T17:03:28.881816Z 17 Query KILL ‘19’
2024-07-06T17:03:38.884463Z 17 Query SHOW FULL PROCESSLIST
2024-07-06T17:03:48.897437Z 17 Query SHOW FULL PROCESSLIST
2024-07-06T17:03:58.912519Z 17 Query SHOW FULL PROCESSLIST

案例二：pt-kill命令中对Command列为Sleep列的操作进行终止

3.1 创建用户u1，赋权，验证远程登录

[root@node3 ~]# mysql -uroot -p123456

root@db 23:37: [test]> create user ‘u1’@’%’ identified with mysql_native_password by ‘123456’;

GRANT all privileges ON . TO ‘u1’@’%’;

flush privileges;

show grants for ‘u1’@’%’\G

验证远程登陆是否成功:

[root@node3 ~]# mysql -uu1 -p123456 -h192.168.100.55 -P3306

3.2 查看连接connection_id

select connection_id();

u1@db 01:20: [(none)]> select connection_id();
±----------------+
| connection_id() |
±----------------+
| 21 |
±----------------+
1 row in set (0.00 sec)

3.3 模拟数据库插入100000条数据

set global innodb_flush_log_at_trx_commit=0;
create database db1 charset=utf8;
use db1;
create table t(id int primary key, a int, b int, index(a)) engine=innodb;
delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i show full processlist;
±—±----------------±------------±-----±--------±-----±-----------------------±----------------------±--------±----------±--------------+
| Id | User | Host | db | Command | Time | State | Info | Time_ms | Rows_sent | Rows_examined |
±—±----------------±------------±-----±--------±-----±-----------------------±----------------------±--------±----------±--------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 4621 | Waiting on empty queue | NULL | 4620453 | 0 | 0 |
| 33 | u1 | node3:40621 | NULL | Sleep | 6 | | NULL | 5611 | 0 | 0 |
| 35 | root | localhost | NULL | Query | 0 | init | show full processlist | 0 | 0 | 0 |
| 37 | u1 | node3:40627 | NULL | Sleep | 4 | | NULL | 3402 | 1 | 1 |
±—±----------------±------------±-----±--------±-----±-----------------------±----------------------±--------±----------±--------------+
4 rows in set, 1 warning (0.00 sec)

session3：

[root@node3 ~]# pt-kill h=192.168.100.55,u=u1,p=123456,P=3306 --busy-time 30 --interval 10 --print --kill --match-user u1

2024-07-07T01:24:15 KILL 21 (Sleep 3 sec) NULL

2024-07-07T01:27:36 KILL 23 (Sleep 3 sec) NULL

2024-07-07T01:28:46 KILL 24 (Sleep 10 sec) NULL

2024-07-07T01:29:36 KILL 26 (Sleep 3 sec) NULL

2024-07-07T01:30:36 KILL 27 (Sleep 4 sec) NULL

2024-07-07T01:30:46 KILL 28 (Sleep 8 sec) NULL

2024-07-07T01:31:16 KILL 29 (Sleep 3 sec) NULL

2024-07-07T01:32:16 KILL 30 (Sleep 3 sec) NULL

2024-07-07T01:33:59 KILL 34 (Sleep 8 sec) NULL

2024-07-07T01:34:40 KILL 36 (Sleep 5 sec) NULL

2024-07-07T01:35:40 KILL 37 (Sleep 8 sec) NULL

三实验总结

pt-kill命令中只对Command列为Query的操作才有效果。

pt-kill工具的使用