Batch kill MySQL Sql_mysql with long running time

Kill syntax

KILL [CONNECTION | QUERY] thread_id
Each connection to the mysqld runs 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 you to connection or query modifiers of your choice:

· Kill connection is the same as a kill with no modifier: it terminates the connection associated with a given thread_id.
· Kill query Terminates the connection to the currently executing statement, but maintains the status of the connection.

If you have process permissions, you can view all 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 kill and embedded MySQL server libraries, because the embedded server runs only the threads of the host application. It cannot create any of its own connection threads.

When you make a Kill, set a unique termination tag on the thread. In most cases, thread termination may take some time because the termination tag is only checked at a specific interval:

· In the Select, order by, and group by loops, check the tags after reading a set of rows. If a stop tag is set, the statement is discarded.
· During ALTER TABLE, check the stop mark before each set of rows is read from the original table. If a stop tag is set, the statement is discarded and the temporary table is deleted.
· During update or delete runs, check the stop tag after each group is read and after each row that has been made more or deleted. If the stop tag is set, the statement is discarded. Note that if you are using a transaction, the change is not rolled back.
· Get_lock () discards and returns NULL.
· The insert delayed thread quickly refreshes (inserts) all of its rows in the 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 a write call, the write is discarded and accompanied by a "disk full" error message.

· Warning: Terminating a repair table or a optimize table operation on a MyISAM table will cause a corrupted table to appear. Any read or write to such a table will fail until you optimize or fix it again (uninterrupted).

1, through the Information_schema.processlist table of the connection information generated by the need to deal with the MySQL connection statement temporary files, and then execute the instructions 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 ', IDs, '; ') 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 the current MySQL connection

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.

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 Maatkit tool set to provide Mk-kill command

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 to refer to:
http://www.maatkit.org/doc/mk-kill.html
http://www.sbear.cn/archives/426
Maatkit Tool 　　Other uses of the set refer to:
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/