How to kill MySQL connections

Question

I m building a website with MySQL   I m using TOAD for MySQL and suddenly I can t connect to the database as I m getting an error       Too many connections    Is there any way in Toad for MySQL to view existing connections to be able to kill them or simple close all connections all together

User · Answer

In MySQL Workbench   Left-hand side navigator   Management   Client Connections  It gives you the option to kill queries and connections   Note  this is not TOAD like the OP asked  but MySQL Workbench users like me may end up here

User · Answer

mysql gt  SHOW PROCESSLIST   ----- ------ ----------------- ------ --------- ------ ------- ---------------    Id    User   Host              db     Command   Time   State   Info         ----- ------ ----------------- ------ --------- ------ ------- ----------------    143   root   localhost 61179   cds    Query        0   init    SHOW PROCESSLIST     192   root   localhost 53793   cds    Sleep        4           NULL         ----- ------ ----------------- ------ --------- ------ ------- ----------------  2 rows in set  0 00 sec   mysql gt  KILL 192  Query OK  0 rows affected  0 00 sec    USER 192    mysql gt  SELECT   FROM exept   ----    id    ----     1    ----  1 row in set  0 00 sec   mysql gt  SELECT   FROM exept  ERROR 2013  HY000   Lost connection to MySQL server during query

User · Answer

As above mentioned  there is no special command to do it  However  if all those connection are  inactive  using  flush tables   is able to release all those connection which are not active

User · Answer

I would recommend checking the connections to show the maximum thread connection is  show variables like  max connections     sample    ----------------- -------    Variable name     Value    ----------------- -------    max connections   13     ----------------- -------  1 row in set   Then increase it by example  set global max connections   500

User · Answer

While you can t kill all open connections with a single command  you can create a set of queries to do that for you if there are too many to do by hand   This example will create a series of KILL  lt pid gt   queries for all some user s connections from 192 168 1 1 to my db   SELECT  CONCAT  KILL    id        FROM INFORMATION SCHEMA PROCESSLIST  WHERE  User     some user   AND  Host     192 168 1 1   AND  db     my db

User · Answer

No  there is no built-in MySQL command for that  There are various tools and scripts that support it  you can kill some connections manually or restart the server  but that will be slower    Use SHOW PROCESSLIST to view all connections  and KILL the process ID s you want to kill   You could edit the timeout setting to have the MySQL daemon kill the inactive processes itself  or raise the connection count  You can even limit the amount of connections per username  so that if the process keeps misbehaving  the only affected process is the process itself and no other clients on your database get locked out   If you can t connect yourself anymore to the server  you should know that MySQL always reserves 1 extra connection for a user with the SUPER privilege  Unless your offending process is for some reason using a username with that privilege     Then after you can access your database again  you should fix the process  website  that s spawning that many connections

[mysql] How to kill MySQL connections

Examples related to mysql

Examples related to connection