How can I kill MySQL queries or sleeping connections every 60 seconds in Windows?

Question

I want to check my MySQL server every minute and kill queries that have run longer than 150 seconds. The main reason I want to do this is because I don't want queries from certain people to lock up the DB for everyone else. I know this is not the ultimate solution to the problem, but at least it's a fallback in case something goes wrong with a query. I don't have a slave DB (this is just an at-home project).

I'd like to schedule a script to run that does this for me. I'm unfamiliar with Perl or Ruby and I need it done on my Windows 2008 Server box. I've looked into creating a simple cmd line script, but that doesn't seem to be possible. I know currently I can do something like this but I have to do it manually:

mysqladmin processlist
mysqladmin kill

Anyone have any ideas or examples on how I could do this?

Answer 1

If you have Wscript installed - I think it should be - you can try this. Save as "somethingorother.vbs" and execute every now and then. Or you can use the provided loop. Both loop and kill-for-real options are commented; test the script before uncommenting on a production server.

I assume that 'mysqladmin processlist' outputs something like this (taken from my XP):

+----+------+----------------+----+---------+------+-------+------------------+
| Id | User | Host           | db | Command | Time | State | Info             |
+----+------+----------------+----+---------+------+-------+------------------+
| 21 | root | localhost:1648 |    | Query   | 0    |       | show processlist |
+----+------+----------------+----+---------+------+-------+------------------+

So we have to eliminate rows containing "----" and rows containing "Id | User", and what remains is

| 21 | root | localhost:1648 |    | Query   | 0    |       | show processlist |

which we can split by pipe signs '|', getting nine fields from 0 to 8:

0   1     2          3          4      5        6      7           8
 | 21 | root | localhost:1648 |    | Query   | 0    |       | show processlist |

Field #1 is Id, and field #6 yields the run time.

Option Explicit

Dim objShell, objWshScriptExec, objStdOut, strLine
Dim id, fields, rt, Killer, KillRun

' While True
'     WScript.Sleep 150000

Set objShell = CreateObject("WScript.Shell")
Set objWshScriptExec = objShell.Exec("mysqladmin processlist")
Set objStdOut = objWshScriptExec.StdOut

While Not objStdOut.AtEndOfStream
   strLine = objStdOut.ReadLine
   If (InStr(strLine, "----") = 0 and InStr(strLine, "| Id | User |") = 0) Then
       fields = Split(strLine, "|")
       id = trim(fields(1))
       rt = trim(fields(6))
       If rt > 150 Then
           ' Set Killer = CreateObject("WScript.Shell")
           ' Set KillRun = objShell.Exec("mysqladmin kill " & id)
           echo 
       End If
   End If
Wend

' Wend