MySQL

This is an old revision of the document!

Kill every MySQL SELECT older than X seconds

f kill.sh

#!/bin/bash
SEC=$1
IFS=’|’
if [[ $SEC -lt 1 ]]; then
   echo “Usage: $0 SECONDS”
   exit 1
fi
mysqladmin proc -v|grep Query|grep -Evi “delete|update|insert|alter table” |while read dummy qid qusr qhost qdb qstat qsec qstat2 query
do
   if [ $qsec -gt $SEC ]; then
      echo “Killing query $qid…”
      mysqladmin kill $qid
   fi
done

Sniff the MySQL traffic on the fly

# tcpdump -i eth0 -s 0 -l -w - dst port 3306 | strings | perl -e '
while(<>) { chomp; next if /^[^ ]+[ ]*$/;
  if(/^(SELECT|UPDATE|DELETE|INSERT|SET|COMMIT|ROLLBACK|CREATE|DROP|ALTER)/i) {
    if (defined $q) { print "$q\n"; }
    $q=$_;
  } else {
    $_ =~ s/^[ \t]+//; $q.=" $_";
  }
}'