Monitoring MySQL Queries Using Bash Script

Monitoring MySQL queries is a favorite pastime of MySQL administrators especially for performance reasons. Here is a simple bash script to monitor long running MySQL queries in realtime using the ubiquitous ’show processlist’. The best part about this script is that you can use it to log your queries over time for later evaluation.

#!/bin/bash
while [ 1 ]
do
        mysql -N -u root -ppassword -e 'show processlist' |grep -v 'show processlist'
        sleep 2
done

Note: Replace password with your actual password.
Note: -N removes column headers.

This script excludes the show processlist thread itself. You may also exclude the Sleeping threads with the following modification:
mysql -N -u root -ppassword -e ’show processlist’ |egrep -v ‘Sleep|show processlist’

Note: This is not the only or best solution in market. There is mtop script with more functionality but written in perl. I am alergic to perl and also couldn’t get it working in my only attempt. So here is a simple solution in bash. Personally I don’t like hacking perl scripts.

Note: You can use slow query log too but in my experience a running top like display is better at finding bottlenecks. Slow query log doesn’t indicate why a query took long time. It could be because it was waiting on another query to complete.