某天无意间发现一台mysql服务器写数据不正常，分析发现是有两台游戏服务器连接着这个mysql服务器，由于其中一台游戏服务器已经退掉但并没有停掉dbserver服务从而造成mysql写不正常，所以必须删除掉问题游戏服务器的连接，具体方法如下：
统计mysql各ip的连接总数：
|
1
|
mysql>
select
SUBSTRING_INDEX(host,
':'
,1) as ip , count(*) from information_schema.processlist group by ip;
|
状态如下：
|
1
2
3
4
5
6
7
8
9
|
+----------------+----------+
| ip | count(*) |
+----------------+----------+
| | 3 |
| 10.182.41.191 | 1 |
| 10.190.249.204 | 1 |
| 10.204.161.60 | 10 |
| localhost | 1 |
+----------------+----------+
|
如ip:10.190.249.204有一个进程正在连接mysql，我们要将其杀掉。
注：还有其他方法来查看mysql各ip连接总数，如在linux上通过mysql命令远程查看其状态。
|
1
|
mysql -u root -h127.0.0.1 -e
"show processlist\G;"
|
egrep
"Host\:"
|
awk
-F:
'{ print $2 }'
|
sort
|
uniq
-c
|
或
|
1
|
mysql -u root -h127.0.0.1 --skip-column-names -e
"show processlist;"
|
awk
'{print $3}'
|
awk
-F
":"
'{print $1}'
|
sort
|
uniq
–c
|
使用如下命令具体查看此ip的具体连接状态:
|
1
|
mysql> show full processlist;
|
状态如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
+--------+-----------------+----------------------+-----------------+-------------+----------+-----------------------------------------------------------------------+------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+--------+-----------------+----------------------+-----------------+-------------+----------+-----------------------------------------------------------------------+------------------------+
| 1 | event_scheduler | localhost | NULL | Daemon | 16664843 | Waiting on empty queue | NULL |
| 3 | tencentroot | :34481 | NULL | Sleep | 5 | | NULL |
| 33 | tencentroot | :38939 | NULL | Binlog Dump | 16663717 | Master has sent all binlog to slave; waiting
for
binlog to be updated | NULL |
| 460426 | tencentroot | :45751 | NULL | Sleep | 2 | | NULL |
| 573982 | root | 10.190.249.204:41661 | db_gfxy_gdb_239 | Sleep | 24 | | NULL |
| 594340 | root | 10.204.161.60:40129 | db_gfxy_gdb_239 | Sleep | 6 | | NULL |
| 594341 | root | 10.204.161.60:40130 | db_gfxy_gdb_239 | Sleep | 7 | | NULL |
| 594342 | root | 10.204.161.60:40131 | db_gfxy_gdb_239 | Sleep | 6 | | NULL |
| 594343 | root | 10.204.161.60:40132 | db_gfxy_gdb_239 | Sleep | 6 | | NULL |
| 594344 | root | 10.204.161.60:40133 | db_gfxy_gdb_239 | Sleep | 6 | | NULL |
| 594345 | root | 10.204.161.60:40134 | db_gfxy_gdb_239 | Sleep | 6 | | NULL |
| 594346 | root | 10.204.161.60:40135 | db_gfxy_gdb_239 | Sleep | 6 | | NULL |
| 594347 | root | 10.204.161.60:40136 | db_gfxy_gdb_239 | Sleep | 6 | | NULL |
| 594348 | root | 10.204.161.60:40137 | db_gfxy_gdb_239 | Sleep | 6 | | NULL |
| 594349 | root | 10.204.161.60:40138 | db_gfxy_gdb_239 | Sleep | 6 | | NULL |
| 594402 | root | 10.182.41.191:55110 | mysql | Query | 0 | NULL | show full processlist |
|
Ip:10.190.249.204对应的id为573982，直接杀掉其id:
|
1
|
mysql> kill 573982;
|
即杀掉10.190.249.204的连接。
在处理解表之前，先查看哪个表所住了：
|
1
|
show OPEN TABLES where In_use > 0;
|
查看其进程show...，kill掉即可。
本文转自 SoulMio 51CTO博客，原文链接：http://blog.51cto.com/bovin/1858137，如需转载请自行联系原作者
