Batch kill SQL statements that run for a long time in mysql, killmysql

Batch kill SQL statements that run for a long time in mysql, killmysql

KILL syntax

KILL [CONNECTION | QUERY] thread_id
Each connection to mysqld runs in an independent thread. You can use the show processlist statement to check which threads are running and use the KILL thread_id statement to terminate a thread.

KILL allows the optional CONNECTION or QUERY modifier:

· Kill connection is the same as KILL without a modifier: It terminates the CONNECTION related to the given thread_id.
· Kill query will terminate the statement currently being executed, but the connection will remain the same.

If you have the PROCESS permission, you can view all threads. If you have SUPER permissions, 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 internal planting Server only runs the thread of the Host application. It cannot create any connection thread of its own.

When you perform a KILL operation, set a unique termination flag for the thread. In most cases, thread termination may take some time because the termination mark will only be checked at a specific interval:

· In the SELECT, order by, and group by loops, check the mark after reading a GROUP of rows. If the termination flag is set, the statement is abandoned.
· During the alter table process, check the termination mark before each group of rows are read from the original TABLE. If the termination flag is set, the statement is abandoned and the temporary table is deleted.
· During the UPDATE or DELETE operation, check the termination mark after each group is read and after each row has been changed or deleted. If the termination mark is set, the statement is abandoned. Note: If you are using a transaction, the change will not be rolled back.
· GET_LOCK () will discard and return NULL.
· The insert delayed thread will quickly refresh (INSERT) all its rows in the memory and terminate.
· If the thread is in the table Lock Management Program (Status: Locked), the table lock will be quickly abandoned.
· If the thread is waiting for idle disk space during the write call, the write will be abandoned with the "disk full" error message.

· Warning: terminating a repair table or optimize table operation on the MyISAM TABLE will lead to a corrupted useless TABLE. Any reading or writing of such a table will fail until you optimize or fix it again (without interruption ).

1. Use the connection information in the information_schema.processlist table to generate a temporary MySQL connection statement file to be processed, and then execute the command generated in the temporary 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 $2}' | xargs-n 1 mysqladmin-uroot-p kill

Kill the connection run by the specified user. Here it is Mike.

Mysqladmin-uroot-p processlist | awk-F "|" '{if ($3 = "Mike ") print $2} '| xargs-n 1 mysqladmin-uroot-p kill

3. Implemented through SHEL script

# Kill the locked MySQL connection for id in 'mysqladmin processlist | grep-I locked | awk' {print $1} ''do mysqladmin kill $ {id} done

4. Run the mk-kill command provided in the Maatkit tool.

Copy codeThe Code is as follows:
# Killing SQL statements that exceed 60 seconds
Mk-kill-busy-time 60-kill
# If you want to stop running SQL statements for more than 60 seconds
Mk-kill-busy-time 60-print
# If you want to kill and output the processes killed at the same time
Mk-kill-busy-time 60-print-kill

For more information about mk-kill, see:
Http://www.maatkit.org/doc/mk-kill.html
Http://www.sbear.cn/archives/426
For other usage of the Maatkit tool set, refer:
Http://code.google.com/p/maatkit/wiki/TableOfContents? Tm = 6
Reference:
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/