关于Mysql查看某个ip连接数及删除掉这个ip连接的方法（也可以进行mysql锁表解锁）...
某天无意间发现一台mysql服务器写数据不正常，分析发现是有两台游戏服务器连接着这个mysql服务器，由于其中一台游戏服务器已经退掉但并没有停掉dbserver服务从而造成mysql写不正常，所以必须删除掉问题游戏服务器的连接，具体方法如下：
统计mysql各ip的连接总数：
mysql> select SUBSTRING_INDEX(host,':',1) as ip , count(*) from information_schema.processlist group by ip;
状态如下：
+----------------+----------+ | 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命令远程查看其状态。
mysql -u root -h127.0.0.1 -e "show processlist\G;"| egrep "Host\:" | awk -F: '{ print $2 }'| sort | uniq -c
或
mysql -u root -h127.0.0.1 --skip-column-names -e "show processlist;"|awk '{print $3}'|awk -F":" '{print $1}'|sort|uniq –c
使用如下命令具体查看此ip的具体连接状态:
mysql> show full processlist;
状态如下：
+--------+-----------------+----------------------+-----------------+-------------+----------+-----------------------------------------------------------------------+------------------------+ | 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:
mysql> kill 573982;即杀掉10.190.249.204的连接。
在处理解表之前，先查看哪个表所住了：
show OPEN TABLES where In_use > 0;
查看其进程show...，kill掉即可。
转载于:https://blog.51cto.com/bovin/1858137