Long-running SQL in bulk kill MySQL

Turn from: think-run long sql in bulk kill MySQL

The following is from the MySQL manual:

13.5.5.3. Kill syntax
KILL [CONNECTION | QUERY] thread_id
Each connection to the mysqld is run in a separate thread, and you can use the show Processlist statement to see which threads are running and terminate a thread with the kill thread_id statement.

Kill allows optional connection or query modifiers:

· Kill connection is the same as a kill without modifiers: it terminates the connection associated with a given thread_id.

· Kill query Terminates the statement that the connection is currently executing, but keeps the connection intact.

If you have process permissions, you can view all the threads. If you have super privileges, you can terminate all threads and statements. Otherwise, you can only view and terminate your own threads and statements.

You can also use the Mysqladmin processlist and mysqladmin kill commands to check and terminate threads.

Note: You cannot use both the kill and embedded MySQL server libraries because the embedded server only runs the thread of the host application. It cannot create any of its own connection threads.

When you make a kill, a unique termination token is set on the thread. In most cases, thread termination can take some time, because the terminating token is only checked at a specific interval:

· In the Select, ORDER by and group by loops, the tag is checked after reading a set of rows. If a termination token is set, the statement is discarded.

· In the ALTER TABLE procedure, the terminating token is checked before each set of rows is read from the original table. If a termination token is set, the statement is discarded and the temporary table is deleted.

· During the update or delete run, after each group has been read and after each row that has been more or deleted, the termination token is checked. If the termination token is set, the statement is discarded. Note that if you are using transactions, the changes are not rolled back.

· Get_lock () discards and returns NULL.

· The insert delayed thread quickly refreshes (inserts) all of its rows in memory and then terminates.

· If the thread is in the table lock Manager (state: Locked), the table lock is quickly discarded.

· If the thread is waiting for free disk space in the write call, the write is discarded and accompanies the "Disk full" error message.

· Warning: Terminating a repair table or optimize table operation on MyISAM tables results in a corrupted, unused table. Any read or write to such a table will fail until you optimize or repair it again (without interruption).

1. Generate statement temporary files for MySQL connections that need to be processed through the connection information in the Information_schema.processlist table, then execute the instructions generated in the temp file

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 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)

2.

Kill all current MySQL connections

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

Kill the connection run by the specified user, this is Mike

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

　

3, through the Shel script implementation

#杀掉锁定的MySQL连接for ID in ' mysqladmin processlist|grep-i Locked|awk ' {print $} ' do   mysqladmin kill ${id}done

4. Through the Mk-kill command provided in the Maatkit tool set

#杀掉超过60秒的sqlmk-kill-busy-time 60-kill# If you want to not kill first, see what SQL runs for more than 60 seconds Mk-kill-busy-time 60-print# if you want to kill, Simultaneously outputs which processes were killed Mk-kill-busy-time 60-print–kill

Mk-kill For more usage, refer to:
Http://www.maatkit.org/doc/mk-kill.html
http://www.sbear.cn/archives/426
Other uses of the Maatkit toolset can be consulted:
Http://code.google.com/p/maatkit/wiki/TableOfContents?tm=6
Reference Documentation:
http://www.google.com
http://www.orczhou.com/index.php/2010/10/kill-mysql-connectio-in-batch/
http://www.mysqlperformanceblog.com/2009/05/21/mass-killing-of-mysql-connections/

Long-running SQL in bulk kill MySQL