EmergencyEMERGENCY? Get 24/7 Help Now!

Mass killing of MySQL Connections

 | May 21, 2009 |  Posted In: Insight for DBAs


Every so often I run into situation when I need to kill a lot of connections on MySQL server – for example hundreds of instances of some bad query is running making server unusable. Many people have special scripts which can take the user, source host or query as a parameter and perform the action. There is also a way to do it just using MySQL with a few commands:

In general this is very powerful approach which I use in a lot of cases to create set of SQL statements by SQL query and when execute it.

It would be nice and clean if MySQL would have some way to “eval” – execute the result set of the query as SQL commands. This would avoid requirement to use temporary file etc.

Peter Zaitsev

Peter managed the High Performance Group within MySQL until 2006, when he founded Percona. Peter has a Master's Degree in Computer Science and is an expert in database kernels, computer hardware, and application scaling.


  • I am facing the problem
    Warning: mysql_connect() [function.mysql-connect]: Too many connections in /home/road2l/public_html/connect.php on line 15
    Error connecting to mysqlToo many connections
    Please help me out.

  • Brian,

    Yes that is one possibility. Though I think it is nicer if result set can be executed line by line – the GROUP_CONCAT trick works but it is typically restricted in size.

  • I prefer the following as it will kill them in a multi threaded manner… (sometimes killing a single query can take a while)

    for i in $(mysql -uroot -pPASS -e ‘show processlist’ | grep ‘search_term’ | awk ‘{print $1}’); do
    mysql -uroot -pPASS -e “kill $i” &

  • Hi Peter,

    With INFORMATION_SCHEMA.PROCESSLIST one can create a stored procedure using server cursor to achieve this. The following procedure accepts a user name and kills all queries for that user.
    server cursors are a MySQL solution for ‘eval’.



    DROP PROCEDURE IF EXISTS test.kill_user_queries$$
    CREATE PROCEDURE test.kill_user_queries (kill_user_name VARCHAR(16) CHARSET utf8)
    DECLARE query_id INT;
    DECLARE iteration_complete INT DEFAULT 0;

    OPEN select_cursor;
    cursor_loop: LOOP
    FETCH select_cursor INTO query_id;
    IF iteration_complete THEN
    LEAVE cursor_loop;
    END IF;
    KILL QUERY query_id;
    CLOSE select_cursor;



  • Robert,

    I think you can use prepared statements and user variables to execute 1 statement. I’d like to execute many statements and it is best if they are returned as multiple rows in result set.

  • Shlomi,

    Thanks. Indeed I’d like some good set of useful Stored Procedures to be available for MySQL so we could just add them to any MySQL installation same as we do with Maatkit – there are many things which are relatively easy with stored procedures but you do not want to write it every time from scratch 🙂

  • Peter,

    I agree. I think there are some general-purpose stored procedures kits around. There are also many useful queries+views by Roland Bouman that I know of.
    Would be nice to gather them all into a “utilities” schema.

    PS a prepared statement will not allow “KILL” or “OPTIMIZE” or “ANALYZE” queries. Just the normal SELECT/INSERT/UPDATE/…

  • Check out mypgrep.py from the google mysql tools project on code.google.com. It basically is a supercharged process grep for mysql, including the ability to kill processes.

  • A few years ago I wrote an article about how to use Vim with mysql to generate and execute SQL statements. It’s here: http://peterstuifzand.nl/external-programs-in-vim.html

  • Thanks Chip,

    The good thing with having it done on SQL level is you do not need to install everything – not every server running MySQL has working perl python on php installed

  • If information_schema.processlist doesn’t exist on your version of MySQL, this works in a linux script:

    for each in mysqladmin -u root -prootpwd processlist | awk '{print $2, $4, $8}' | grep $dbname | grep $dbuser | awk '{print $1}';
    do mysqladmin -u root -prootpwd kill $each;

  • Bryan,

    Which version of Percona patches are you running ? It exists in recent ones for sure.

    Thanks for command line version though.

  • How about a technique like this:

    . File optimizeworld.sfs:

    # Optimize tables in the World database.

    . To execute:

    shell> mysql –skip-column-names < optimizeworld.sfs | mysql

  • I do this:

    mysqladmin proc | grep Sleep | sort -r -n -k6 | awk {‘print $1; ‘} | xargs mysqladmin kill

    or something to that effect…

  • I am wondering if there isn’t a symptom that is missing? I am running a high volume ecommerce site and we switched to Magento from custom app written in vb.net (.net 1.1). Shouldn’t MySql handle all of this? What I am wondering is why my old windows box running doesn’t crash with the same load as my high powered linux box?

  • I used the command and the query being run in mysql processlist still running for 28226 and l cant kill it with everything l did. what might else l should try ?

  • Hi, i am using mysql 5.0 which does not have processlist table in information_schema, now is there any way i can list all query which is taking more then x time. Any script will be really appriciated.

  • This may not directly related to this chain, but I landed here to kill massive connection due to the hung drop trigger – I have a dilemma with dropping a trigger on a percona 5.5.27 on a very large table ~ 68 Gig. I get the metadata lock and it hangs.

    I have tried everything. Is it safe to remove the trigger from DB.TRG file? Thanks a lot

Leave a Reply


Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.

Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.

No, thank you. Please do not ask me again.