MySQL表不能修改、删除等操作，卡死、锁死情况的处理办法

---------+--------+-----------+-------+------------------+---------+-------------------------------------+--------------------------------------+-------------+----------------------+-----------+ | Id | User | Host | db | Command | Time | State | Info | Memory_used | Memory_used_by_query | CPU_time | +---------+--------+-----------+-------+------------------+---------+-------------------------------------+--------------------------------------+-------------+----------------------+-----------+ | 28 | rdsRepl| xxx:51284 | NULL | Binlog Dump GTID | 1220403 | Master has sent all binlog to slave;| NULL | 18117 | 8208 | 239225 | | 1676492 | root | xxx:9183 | sync | Sleep | 5394 | (这条上面的waiting for more updates)| NULL | 895362 | 8208 | 32741806 | | 1688104 | root | xxx:11900 | sync | Sleep | 3766 | | NULL | 268480 | 8208 | 155952228 | | 1689762 | root | xxx:12057 | xtemp | Query | 1116 | Waiting for table metadata lock | SHOW TABLE STATUS | 813027 | 11024 | 5439530 | | 1690466 | root | xxx:12632 | xtemp | Query | 950 | Waiting for table metadata lock | SELECT TABLE_NAME, CHECK_OPTION, IS_U| 114628 | 9776 | 5811522 | | 1693651 | root | xxx:14234 | sync | Query | 1338 | Sending data | CREATE TABLE xtemp.yxy_transfer AS S| 1338135 | 15384 | 2542049 | | 1693702 | root | xxx:14342 | sync | Query | 1302 | Waiting for table metadata lock | DROP TABLE IF EXISTS xtemp.yxy_transf| 17149 | 8208 | 2428293 | | 1694006 | root | xxx:14584 | sync | Query | 1085 | Waiting for table metadata lock | DROP TABLE IF EXISTS xtemp.yxy_transf| 17149 | 8208 | 2397726 | | 1694108 | root | xxx:14594 | xtemp | Query | 936 | Waiting for table metadata lock | show create table xtemp.yxy_transfer | 37856 | 8208 | 953411 | | 1694417 | root | xxx:14771 | xtemp | Query | 762 | Waiting for table metadata lock | SELECT TABLE_NAME, CHECK_OPTION, IS_U| 111067 | 9776 | 892068 | | 1694515 | root | xxx:14869 | xtemp | Query | 683 | Waiting for table metadata lock | SELECT TABLE_NAME, CHECK_OPTION, IS_U| 127905 | 9776 | 831436 | | 1694540 | root | xxx:14875 | xtemp | Query | 651 | Waiting for table metadata lock | DROP TABLE IF EXISTS xtemp.yxy_transf| 16638 | 8208 | 138945 | | 1694650 | root | xxx:1032 | xtemp | Query | 572 | Waiting for table metadata lock | SELECT TABLE_NAME, CHECK_OPTION, IS_U| 117220 | 9776 | 937266 | | 1694666 | root | xxx:1033 | xtemp | Query | 0 | starting | show full processlist | 16470 | 8208 | 973631 | +---------+--------+-----------+-------+------------------+---------+-------------------------------------+--------------------------------------+-------------+----------------------+-----------+

 

再执行：

kill processid;  

kill 1693651;

//processid表示process的id，比如kill 3301，就会将id为3301的process杀死。

 

+---------+---------+------+-------+------------------+---------+---------------------------------------------------------------+-----------------------+-------------+----------------------+-----------+
| Id      | User    | Host | db    | Command          | Time    | State                                                         | Info                  | Memory_used | Memory_used_by_query | CPU_time  |
+---------+---------+------+-------+------------------+---------+---------------------------------------------------------------+-----------------------+-------------+----------------------+-----------+
|      28 | rdsRepl |xxx   | NULL  | Binlog Dump GTID | 1220453 | Master has sent all binlog to slave; waiting for more updates | NULL                  |       18117 |                 8208 |    239225 |
| 1676492 | root    |xxx   | sync  | Sleep            |    5444 |                                                               | NULL                  |      895362 |                 8208 |  32741806 |
| 1688104 | root    |xxx   | sync  | Sleep            |    3816 |                                                               | NULL                  |      268480 |                 8208 | 155952228 |
| 1689762 | root    |xxx   | xtemp | Sleep            |    1166 |                                                               | NULL                  |      428709 |                 8208 |   6319722 |
| 1690466 | root    |xxx   | xtemp | Sleep            |    1000 |                                                               | NULL                  |       20046 |                 8208 |   6517271 |
| 1694108 | root    |xxx   | xtemp | Sleep            |     986 |                                                               | NULL                  |       39912 |                 8208 |   1055550 |
| 1694417 | root    |xxx   | xtemp | Sleep            |     812 |                                                               | NULL                  |       16485 |                 8208 |   1390755 |
| 1694515 | root    |xxx   | xtemp | Sleep            |     733 |                                                               | NULL                  |       27155 |                 8208 |   1320086 |
| 1694540 | root    |xxx   | xtemp | Sleep            |     701 |                                                               | NULL                  |       85811 |                 8208 |  16689270 |
| 1694650 | root    |xxx   | xtemp | Sleep            |     622 |                                                               | NULL                  |       16470 |                 8208 |   1428922 |
| 1694666 | root    |xxx   | xtemp | Query            |       0 | starting                                                      | show full processlist |       16470 |                 8208 |   1351151 |
+---------+---------+------+-------+------------------+---------+---------------------------------------------------------------+-----------------------+-------------+----------------------+-----------+

 

MySQL表不能修改、删除等操作，卡死、锁死情况的处理办法

标签：start   exist   proc   pre   create   more   option   卡住   ast