1. 查看应用
show index from table ge.T1;
MySQL中,每一个连接进来,都会对应一个独立的线程。可以使用mysqladmin命令的processlist参数来查看所有应用,或者使用SHOW PROCESSLIST命令;要查看当前的连接,可以使用CONNECTION_ID() 函数:
root@db2a:~# mysqladmin -pqingsong processlist
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
+----+------+------------+----+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+------------+----+---------+------+----------+------------------+
| 25 | root | localhost | | Sleep | 56 | | |
| 26 | root | localhost | | Sleep | 44 | | |
| 27 | root | db2b:51470 | | Sleep | 25 | | |
| 28 | root | localhost | | Query | 0 | starting | show processlist |
+----+------+------------+----+---------+------+----------+------------------+
root@db2a:~# mysql
..
mysql> SHOW PROCESSLIST;
+----+------+------------+------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+------------+------+---------+------+----------+------------------+
| 25 | root | localhost | NULL | Sleep | 86 | | NULL |
| 26 | root | localhost | NULL | Sleep | 74 | | NULL |
| 27 | root | db2b:51470 | NULL | Sleep | 55 | | NULL |
| 29 | root | localhost | NULL | Query | 0 | starting | SHOW PROCESSLIST |
+----+------+------------+------+---------+------+----------+------------------+
4 rows in set (0.00 sec)
mysql> SELECT CONNECTION_ID();
+-----------------+
| CONNECTION_ID() |
+-----------------+
| 29 |
+-----------------+
1 row in set (0.00 sec)
2. 杀掉应用
可以使用KILL命令,或者mysqladmin的KILL参数,杀掉某个连接,语法如下:
KILL [CONNECTION | QUERY] processlist_id
它有 CONNECTION 和 QUERY 两种模式,默认为CONNECTION,会将整个应用杀掉。而 QUERY 模式会将当前应用正在执行的语句杀掉,应用本身还是保持着连接。查看所有的应用需要 PROCESS 权限,杀掉应用需要 SUPER 权限,否则只能看到和杀掉自己。
发出命令之后,会给相应的线程设置一个kill flag,大部分情况下,线程需要花点时间才能终止,因为kill flag是以固定的周期来检查的。
示例,第一个KILL命令并没有终止连接,第二个和第三个命令终止了连接:
mysql> SHOW PROCESSLIST;
+----+------+------------+------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+------------+------+---------+------+----------+------------------+
| 25 | root | localhost | NULL | Sleep | 86 | | NULL |
| 26 | root | localhost | NULL | Sleep | 74 | | NULL |
| 27 | root | db2b:51470 | NULL | Sleep | 55 | | NULL |
| 29 | root | localhost | NULL | Query | 0 | starting | SHOW PROCESSLIST |
+----+------+------------+------+---------+------+----------+------------------+
4 rows in set (0.00 sec)
mysql> KILL QUERY 25;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW PROCESSLIST;
+----+------+------------+------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+------------+------+---------+------+----------+------------------+
| 25 | root | localhost | NULL | Sleep | 616 | | NULL |
| 26 | root | localhost | NULL | Sleep | 604 | | NULL |
| 27 | root | db2b:51470 | NULL | Sleep | 585 | | NULL |
| 29 | root | localhost | NULL | Query | 0 | starting | SHOW PROCESSLIST |
+----+------+------------+------+---------+------+----------+------------------+
4 rows in set (0.00 sec)
mysql> KILL 25;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW PROCESSLIST;
+----+------+------------+------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+------------+------+---------+------+----------+------------------+
| 26 | root | localhost | NULL | Sleep | 622 | | NULL |
| 27 | root | db2b:51470 | NULL | Sleep | 603 | | NULL |
| 29 | root | localhost | NULL | Query | 0 | starting | SHOW PROCESSLIST |
+----+------+------------+------+---------+------+----------+------------------+
3 rows in set (0.00 sec)
mysql> KILL CONNECTION 27;
Query OK, 0 rows affected (0.04 sec)
mysql> SHOW PROCESSLIST;
+----+------+-----------+------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+----------+------------------+
| 26 | root | localhost | NULL | Sleep | 674 | | NULL |
| 29 | root | localhost | NULL | Query | 0 | starting | SHOW PROCESSLIST |
+----+------+-----------+------+---------+------+----------+------------------+
2 rows in set (0.00 sec)
注意,一个KILL命令只能杀掉一个应用,如果要在一条命令里杀掉多个,可以考虑使用mysqladmin的kill参数,示例如下:
qingsong@db2b:~$ mysqladmin -h db2a -u root -pqingsong processlist
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
+----+------+------------+-------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+------------+-------+---------+------+----------+------------------+
| 26 | root | localhost | | Sleep | 878 | | |
| 29 | root | localhost | | Sleep | 184 | | |
| 30 | root | db2b:51472 | test1 | Sleep | 194 | | |
| 31 | root | db2b:51474 | | Query | 0 | starting | show processlist |
+----+------+------------+-------+---------+------+----------+------------------+
qingsong@db2b:~$ mysqladmin -h db2a -u root -pqingsong kill 26,29,30
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
qingsong@db2b:~$ mysqladmin -h db2a -u root -pqingsong processlist
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
+----+------+------------+----+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+------------+----+---------+------+----------+------------------+
| 33 | root | db2b:51478 | | Query | 0 | starting | show processlist |
+----+------+------------+----+---------+------+----------+------------------+
————————————————
版权声明:本文为CSDN博主「匿_名_用_户」的原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接及本声明。
原文链接:https://blog.csdn.net/qingsong3333/article/details/77170755