mysql杀死锁死的进程

Sometimes MySQL queries take a very long time and slow the database processing. We can find and kill these stuck processes.

有时，MySQL查询会花费很长时间，并减慢数据库处理速度。 我们可以找到并消除这些卡住的进程。

MySQL Kill过程步骤 (MySQL Kill Process Steps)

MySQL Kill Process involves running two commands.

MySQL Kill Process涉及运行两个命令。

1. Find the running processes using show processlist command.
   使用show processlist命令查找正在运行的进程。
2. Kill the process using kill ID command.
   使用kill ID命令杀死该进程。

1.查找卡住MySQL进程 (1. Find the Stuck MySQL Process)

I am using root user to show processes for all the users. If you are using a database-specific user, you will get the results for that database only.

我正在使用root用户来显示所有用户的进程。 如果您使用的是特定于数据库的用户，则将仅获得该数据库的结果。

MariaDB [(none)]> show processlist;
+--------+------------+-----------+------------+---------+------+---------------------------------+-----------------------------+----------+
| Id     | User       | Host      | db         | Command | Time | State                           | Info                        | Progress |
+--------+------------+-----------+------------+---------+------+---------------------------------+-----------------------------+----------+
| 566697 | jour_wp    | localhost | jour_wp    | Query   | 1130 | Waiting for table metadata lock | OPTIMIZE TABLE wp_comments  |    0.000 |
| 566698 | jour_wp    | localhost | jour_wp    | Query   | 1130 | Waiting for table level lock    | OPTIMIZE TABLE wp_comments  |    0.000 |

It’s clear from the output that these processes are stuck for more than 1000 seconds.

从输出中可以明显看出，这些进程被卡滞了1000秒钟以上。

The Query shows only 100 characters of the Query. If that’s not sufficient then use the SHOW FULL PROCESSLIST command to get the complete query information.

查询仅显示查询的100个字符。 如果这还不够，请使用SHOW FULL PROCESSLIST命令获取完整的查询信息。

2.通过ID杀死MySQL进程 (2. Kill MySQL Process by ID)

The show processlist command shows the MySQL process IDs. We can run the kill ID command to terminate them.

show processlist命令显示MySQL进程ID。 我们可以运行kill ID命令终止它们。

MariaDB [(none)]> kill 566697;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> kill 566698;
Query OK, 0 rows affected (0.00 sec)

We can check the process list again to make sure all the stuck processes are cleared.

我们可以再次检查进程列表，以确保清除所有卡住的进程。

翻译自: https://www.journaldev.com/41769/kill-mysql-process

mysql杀死锁死的进程