MySQL DBA - Tips and Techniques Magazine

11 Nov 2014

Kill Mysql Session

Use show processlist to get the id of the process that needs to be killed

 

mysql> show processlist;

+--------+-------------+--------------------+---------+---------+---------+-----------------------------------------------------------------------+------------------+

| Id     | User        | Host               | db      | Command | Time    | State                                                                 | Info             |

+--------+-------------+--------------------+---------+---------+---------+-----------------------------------------------------------------------+------------------+

| 462797 | system user |                    | NULL    | Connect | 3524653 | Waiting for master to send event                                      | NULL             |

| 462996 | system user |                    | NULL    | Connect |       3 | Has read all relay log; waiting for the slave I/O thread to update it | NULL             |

| 611892 | replanyprov | 10.254.25.40:19302 | ANYPROV | Sleep   |       2 | NULL                                                                  | NULL             |

| 613554 | replanyprov | 10.254.25.36:27834 | ANYPROV | Sleep   |       4 | NULL                                                                  | NULL             |

| 614046 | replanyprov | 10.44.80.144:3862  | ANYPROV | Sleep   |       4 | NULL                                                                  | NULL             |

| 633573 | replanyprov | 10.254.25.36:14726 | ANYPROV | Sleep   |    4573 | Select * from users;                                                                  | NULL             |

| 633863 | replanyprov | 10.254.25.36:16141 | ANYPROV | Sleep   |       0 | NULL                                                                  | NULL             |

| 633864 | root        | localhost          | NULL    | Query   |       0 | NULL                                                                  | show processlist |

+--------+-------------+--------------------+---------+---------+---------+-----------------------------------------------------------------------+------------------+

 

The Id column indicates the process id, e.g. if you wanted to kill the select statement

 

mysql> kill 633573;

 

If too many sessions can send output to a file, e.g. on portal

 

mysql -u$user -p$pswd -S /tmp/mysqlx-x.sock -e 'show processlist' > /tmp/a.out

No comments:

Post a comment