Four ways to kill Mysql connections in batches _mysql

Source: Internet
Author: User
Tags what sql

Method One
Generate the statement temporary files that need to be processed by the connection information in the Information_schema.processlist table, and then execute the instructions generated in the temporary file.

Copy Code code as follows:

Mysql> Select Concat (' KILL ', id, '; ') from information_schema.processlist where user= ' root ';
+------------------------+
| Concat (' KILL ', id, '; ') |
+------------------------+
|             KILL 3101; |
|             KILL 2946; |
+------------------------+
2 rows in Set (0.00 sec)

Mysql>select concat (' KILL ', id, '; ') from the information_schema.processlist where user= ' root ' into outfile '/tmp/a.txt ';
Query OK, 2 rows Affected (0.00 sec)

mysql>source/tmp/a.txt;
Query OK, 0 rows Affected (0.00 sec)

Method Two
Kill all of the current MySQL connections

Copy Code code as follows:

Mysqladmin-uroot-p processlist|awk-f "|" ' {print $} ' |xargs-n 1 mysqladmin-uroot-p Kill

Kill the connection that the specified user is running, this is Mike.
Copy Code code as follows:

Mysqladmin-uroot-p processlist|awk-f "|" ' {if ($ = = ' Mike ') print $} ' |xargs-n 1 mysqladmin-uroot-p Kill

Method Three
Implemented by Shel script
Copy Code code as follows:

#杀掉锁定的MySQL连接
For ID in ' mysqladmin processlist|grep-i Locked|awk ' {print $} '
Todo
Mysqladmin Kill ${id}
Done

method Four
Through the Mk-kill command provided by the Maatkit tool set
Copy Code code as follows:

#杀掉超过60秒的sql
Mk-kill-busy-time 60-kill
#如果你想先不杀, let's see what SQL runs for more than 60 seconds.
Mk-kill-busy-time 60-print
#如果你想杀掉, at the same time the output killed what process
Mk-kill-busy-time 60-print–kill

Mk-kill more usage can refer to:
Http://www.maatkit.org/doc/mk-kill.html

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.