mysqlsniffer :: MySQL network protocol sniffer

You’ve got a production database server, and you can’t enable query logging… so how do you see the queries being executed against the database?

The answer: use a modified network sniffer to parse out the MySQL packets and decode them. You’ll have to do a little compiling, but it’ll be worth it. Note that this will not usually work for local connections, although you are welcome to try.

mysqlsniffer is a tcpdump clone specifically for dumping/sniffing/watching MySQL network protocol traffic over TCP/IP networks. mysqlsniffer is coded in C using the pcap library and works with MySQL version 4.0 and newer. mysqlsniffer is the only MySQL-specific network sniffer.

mk-query-digest also understands the MySQL protocol. It’s not a sniffer, though. It reads packet dumps from tcpdump like a slowlog. If you want to analyze queries from the wire (i.e. from network traffic), mk-query-digest is what you want.

For more information about the MySQL protocol read MySQL Internals ClientServer Protocol.

mysqlsniffer latest version Aug 18 2006 v1.2 (what’s new) via mysqlsniffer.tgz if you don’t want to follow the instructions below.

First, you need to install libpcap-dev (libpcap-devel for Redhat/Centos), which is the development library that allows an application to sniff network packets.

Ubuntu/Debian

sudo apt-get install libpcap-dev

Redhat/CentOS

sudo yum install libpcap-devel

Now let’s make a directory, download the source code and compile it

mkdir mysqlsniffer
cd mysqlsniffer
wget http://hackmysql.com/code/mysqlsniffer.tgz
tar xvfz mysqlsniffer.tgz
gcc -O2 -lpcap -o mysqlsniffer mysqlsniffer.c packet_handlers.c misc.c

At this point, we have a shiny new executable named mysqlsniffer in our source directory. You can copy it wherever you like (somewhere in the path would be useful)

To run mysqlsniffer, you need to specify the network interface that MySQL is listening on. For me, it’s eth0.

sudo /path/to/mysqlsniffer eth0

Ah, now there we are… all sorts of query information, without having to restart MySQL.

Here are the full options for the command:

Usage: mysqlsniffer [OPTIONS] INTERFACE

OPTIONS:
–port N Listen for MySQL on port number N (default 3306)
–verbose Show extra packet information
–tcp-ctrl Show TCP control packets (SYN, FIN, RST, ACK)
–net-hdrs Show major IP and TCP header values
–no-mysql-hdrs Do not show MySQL header (packet ID and length)
–state Show state
–v40 MySQL server is version 4.0
–dump Dump all packets in hex
–help Print this

mysqlsniffer Capture Example

MySQL network protocol traffic from the command “mysqladmin -u root -h 127.0.0.1 processlist”:

Note: The capture interface here was lo instead of eth0.

mysqlsniffer listening for MySQL on interface lo port 3306
server > 127.0.0.1.3051: ID 0 len 70 Handshake  (Caps: Get all column flags, Connect w/DB, Compression, )
127.0.0.1.3051 > server: ID 1 len 38 Handshake (new auth) <user root db (null) max pkt 1073741824> (Caps: Long password, Get all column flags, LOAD DATA LOCAL, 4.1 protocol, Transactions, 4.1 authentication, )
server > 127.0.0.1.3051: ID 2 len 7 OK  (Status: Auto-commit, )
127.0.0.1.3051 > server: ID 0 len 17 COM_QUERY: show processlist
server > 127.0.0.1.3051: ID 1 len 1 8 Fields
        ID 2 len 24 Field: ..Id
        ID 3 len 26 Field: ..User
        ID 4 len 26 Field: ..Host
        ID 5 len 24 Field: ..db
        ID 6 len 29 Field: ..Command
        ID 7 len 26 Field: ..Time
        ID 8 len 27 Field: ..State
        ID 9 len 26 Field: ..Info
        ID 10 len 5 End  (Status: Auto-commit, )
        ID 11 len 49 || 1 | root | localhost:3051 | NULL | Query | 0 | NULL | show processlist ||
        ID 12 len 5 End  (Status: Auto-commit, )
127.0.0.1.3051 > server: ID 0 len 1 COM_QUIT
mysqlsniffer exited cleanly.
17 MySQL packets captured (469 bytes)

Loads of stuff starts flying by… let’s filter it out a little more so we can just get the queries and not all the excess data.

$ sudo /path/to/mysqlsniffer –no-mysql-hdrs eth0 | grep COM_QUERY

192.168.73.1.2622 > server: COM_QUERY: SELECT @@sql_mode
192.168.73.1.2622 > server: COM_QUERY: SET SESSION sql_mode=”
192.168.73.1.2622 > server: COM_QUERY: SET NAMES utf8
192.168.73.1.1636 > server: COM_QUERY: SELECT @@SQL_MODE
192.168.73.1.1636 > server: COM_QUERY: SHOW FULL COLUMNS FROM `db2842_howto`.`wp_users`

Original source code and more information at:

http://hackmysql.com/mysqlsniffer

If you are running on a development server, it would be easier to just turn on query logging.

iSCSI init script supporting MySQL checking

Here is a iSCSI init script for CentOS 5.x. I presume it will work on all flavors of Linux, but haven’t tested. This init script is the original init script but modified to support checking for MySQL databases that might be using the iSCSI mounted disk. So we do not want to umount the iSCSI disk while MySQL is running. I also added checks to see if iscsid is actually running, if it is then make sure you are not already logged in. If you are then don’t try to login again. Also once iSCSI is successfully started we want to mount the iSCSI disk so it is available.

*NOTE: Make sure to adjust your boot init setup. Normally mysql will start before iscsi and iscsid EXCEPT for init 5. Which by default init 5 is the only level which will start mysqld. If you have changed this or yours if not the same make sure you account for this.


chkconfig --list | egrep "iscsi|mysql"
iscsi 0:off 1:off 2:off 3:on 4:on 5:on 6:off
iscsid 0:off 1:off 2:off 3:on 4:on 5:on 6:off
mysqld 0:off 1:off 2:off 3:off 4:off 5:on 6:off


init level 3

ls -l /etc/rc.d/rc3.d/ | egrep -i "mysql|iscsi" | awk '{print $9, $10, $11, $12;}'
K36mysqld -> ../init.d/mysqld
S07iscsid -> ../init.d/iscsid
S13iscsi -> ../init.d/iscsi

init level 5

ls -l /etc/rc.d/rc5.d/ | egrep -i "mysql|iscsi" | awk '{print $9, $10, $11, $12;}'
S07iscsid -> ../init.d/iscsid
S13iscsi -> ../init.d/iscsi
S64mysqld -> ../init.d/mysqld

/etc/init.d/iscsi

#!/bin/sh
#
# chkconfig: 345 13 89
# description: Logs into iSCSI targets needed at system startup
#
# Source function library.
. /etc/init.d/functions

PATH=/sbin:/bin:/usr/sbin:/usr/bin

RETVAL=0

start()
{

        status iscsid > /dev/null 2>&1
        RETVAL=$?

        if [ $RETVAL -ne 0 ]; then
                /etc/init.d/iscsid start
        fi


        status iscsid > /dev/null 2>&1
        RETVAL=$?

        #
        # Verify iscsi is logged in to the iscsi disk
        # and iscsid is actually running.
        #
        iscsiadm -m session -R > /dev/null 2>&1
        iscsiadmcheck=$?
        if [ $RETVAL -eq  0 ]; then
           if [ $iscsiadmcheck -ne "0" ]; then
             echo -n $"Setting up iSCSI targets: "
             # this script is normally called from startup so log into
             # nodes marked node.startup=automatic
             iscsiadm -m node --loginall=automatic
             touch /var/lock/subsys/iscsi
             success
             echo
             sleep 5
             mount -a -O _netdev
           else
             action $"iscsid: " /bin/true
             echo "iscsi:   [ is running ]"
           fi
        else
           action $"Starting iscsid: " /bin/false
           RETVAL=1
        fi
}

stop()
{
        rm -f /var/lock/subsys/iscsi

        # If this is a final shutdown/halt, do nothing since
        # lvm/dm, md, power path, etc do not always handle this
        if [ "$RUNLEVEL" = "6" -o "$RUNLEVEL" = "0" -o "$RUNLEVEL" = "1" ]; then
                success
                return
        fi

        # Do NOT turn off iscsi if root is possibly on a iscsi disk
        rootopts=$(awk '{ if ($1 !~ /^[ \t]*#/ && $2 == "/") { print $4; }}' /etc/mtab)
        if [[ "$rootopts" =~ "_netdev" ]] ; then
                action $"Stopping iscsid: " /bin/false
                echo $"Can not shutdown iSCSI. Root is on a iSCSI disk."
                exit 1
        fi

        #
        # MySQL checking. If MySQL loads a DB from an iscsi disk we need to make sure
        # MySQL is NOT running before we shutdown iscsi and umount the iscsi disk.
        #
        mysqlopts=$(awk '{  if ( $1 ~ /^datadir/ ) { gsub("datadir=", "", $1); print $1; } }' /etc/my.cnf)
        iscsiopts=$(awk -v mysqlopts=$mysqlopts '{ if ($1 !~ /^[ \t]*#/ && $2 != "/" && mysqlopts ~ $2 ) { print $4; }}' /etc/mtab)
        /etc/init.d/mysqld status > /dev/null 2>&1
        mysqlcheck=$?
        if [[ "$iscsiopts" =~ "_netdev" ]] ; then
                if [ "$mysqlcheck" -eq 0 ]; then
                  action $"Stopping iscsid: " /bin/false
                  echo "Can not shutdown iSCSI. MySQL is mounted on a iSCSI disk."
                  exit 1
                fi
        fi
        iscsiadm -m node --logoutall=all
        /etc/init.d/iscsid stop
        success

        status iscsid > /dev/null 2>&1
        RETVAL=$?
        iscsiadm -m session -R > /dev/null 2>&1
        iscsiadmcheck=$?
        if [[ "$iscsiadmcheck" -ne 0 && $RETVAL -ne 0 ]]; then
          umount -a -O _netdev
        else
           action $"Stopping iscsid: " /bin/false
           echo "iSCSI did NOT logout successfully"
           RETVAL=1
        fi
}

case "$1" in
        start)
                start
                ;;
        stop)
                stop
                ;;
        restart)
                stop
                start
                ;;
        status)
                status iscsid
                RETVAL=$?
                ;;
        condrestart)
                [ -f /var/lock/subsys/iscsi ] && restart
                ;;
        *)
                echo $"Usage: $0 {start|stop|restart|status|condrestart}"
                exit 1
esac
exit $RETVAL

/etc/init.d/iscsid

#!/bin/sh
#
# chkconfig: 345 7 89
# description: Starts and stops the iSCSI daemon.
#
# processname: iscsid
# pidfile: /var/run/iscsid.pid
# config:  /etc/iscsi/iscsid.conf

# Source function library.
. /etc/init.d/functions

PATH=/sbin:/bin:/usr/sbin:/usr/bin

RETVAL=0

start()
{
        echo -n $"Turning off network shutdown. "
        # we do not want iscsi or network to run during system shutdown
        # incase there are RAID or multipath devices using
        # iscsi disks
        chkconfig --level 06 network off
        rm /etc/rc0.d/*network
        rm /etc/rc6.d/*network

        echo -n $"Starting iSCSI daemon: "
        modprobe -q iscsi_tcp
        modprobe -q ib_iser
        daemon iscsid
        RETVAL=$?
        echo
        [ $RETVAL -eq 0 ] || return

        touch /var/lock/subsys/iscsid

        success
        echo
        sleep 5
}

stop()
{
        rm -f /var/lock/subsys/iscsid

        # If this is a final shutdown/halt, do nothing since
        # we may need iscsid for as long as possible (halt script kills
        # us at the last second)
        if [ "$RUNLEVEL" = "6" -o "$RUNLEVEL" = "0" -o "$RUNLEVEL" = "1" ]; then
                success
                return
        fi

        # don't turn off iscsi if root is possibly on a iscsi disk
        rootopts=$(awk '{ if ($1 !~ /^[ \t]*#/ && $2 == "/") { print $4; }}' /etc/mtab)
        if [[ "$rootopts" =~ "_netdev" ]] ; then
                echo $"Can not shutdown iSCSI. Root is on a iSCSI disk."
                exit 1
        fi

        mysqlopts=$(awk '{  if ( $1 ~ /^datadir/ ) { gsub("datadir=", "", $1); print $1; } }' /etc/my.cnf)
        iscsiopts=$(awk -v mysqlopts=$mysqlopts '{ if ($1 !~ /^[ \t]*#/ && $2 != "/" && mysqlopts ~ $2 ) { print $4; }}' /etc/mtab)
        /etc/init.d/mysqld status > /dev/null 2>&1
        mysqlcheck=$?
        if [[ "$iscsiopts" = "rw,_netdev" ]] ; then
                if [ "$mysqlcheck" -eq 0 ]; then
                  echo "Can not shutdown iSCSI. MySQL is on a iSCSI disk."
                  exit 1
                fi
        fi


        echo -n $"Stopping iSCSI daemon: "

        # iscsid does not have a nice shutdown process.
        # It really should never be stopped
        #pkill -KILL iscsid
        killproc iscsid
        echo

        modprobe -r ib_iser 2>/dev/null
        modprobe -r iscsi_tcp 2>/dev/null
}

restart()
{
        stop
        start
}

case "$1" in
        start)
                start
                ;;
        stop)
                stop
                ;;
        restart)
                stop
                start
                ;;
        status)
                status iscsid
                RETVAL=$?
                ;;
        condrestart)
                [ -f /var/lock/subsys/iscsid ] && restart
                ;;
        *)
                echo $"Usage: $0 {start|stop|restart|status|condrestart}"
                exit 1
esac

exit $RETVAL

MySQL init script supporting iSCSI disk mount checking

Here is a MySQLd init script for CentOS 5.x. I presume it will work on all flavors of Linux, but haven’t tested. The init script is the original init script but modified to support checking for iSCSI disk support. Basically if the my.cnf specifies a directory that is on an iSCSI mounted disk it will make sure iscsi/iscsid are running and the mount is present in /etc/mtab. I also added a check to see if MySQL is already running and if so do NOT try to restart mysql when a user issues “/etc/init.d/mysqld start”. Basically The user must first issue “/etc/init.d/mysqld restart” or “/etc/init.d/mysqld stop” then “/etc/init.d/mysqld start”. I also added a chkconfig bit. MySQLd needs to start after iSCSI has started just in case it needs to the iSCSI disk. Multipath should be enabled with “/sbin/chkconfig multipathd on” which by default sets init levels 2, 3, 4, and 5 to on. So you would have the services start up as so: multipathd, iscsid, iscsi, mysqld. They will shut down in reverse order: mysqld, iscsi, iscsid, multipathd.


#!/bin/bash
#
# mysqld This shell script takes care of starting and stopping
# the MySQL subsystem (mysqld).
#
# chkconfig: 345 64 36
# description: MySQL database server.
# processname: mysqld
# config: /etc/my.cnf
# pidfile: /var/run/mysqld/mysqld.pid

# Source function library.
. /etc/rc.d/init.d/functions

# Source networking configuration.
. /etc/sysconfig/network

prog="MySQL"

# extract value of a MySQL option from config files
# Usage: get_mysql_option SECTION VARNAME DEFAULT
# result is returned in $result
# We use my_print_defaults which prints all options from multiple files,
# with the more specific ones later; hence take the last match.
get_mysql_option(){
result=`/usr/bin/my_print_defaults "$1" | sed -n "s/^--$2=//p" | tail -n 1`
if [ -z "$result" ]; then
# not found, use default
result="$3"
fi
}

get_mysql_option mysqld datadir "/var/lib/mysql"
datadir="$result"
get_mysql_option mysqld socket "$datadir/mysql.sock"
socketfile="$result"
get_mysql_option mysqld_safe log-error "/var/log/mysqld.log"
errlogfile="$result"
get_mysql_option mysqld_safe pid-file "/var/run/mysqld/mysqld.pid"
mypidfile="$result"

start(){
#
# iscsi checking. If we need mysql to create/load a DB that is one an iscsi
# disk we ned to make sure iscsi is running, we can successfully login,
# and verify the iscsi disk is moutned before starting mysql.
#
status mysqld > /dev/null 2>&1
mysqlcheck=$?
mysqlopts=$(awk '{ if ( $1 ~ /^datadir/ ) { gsub("datadir=", "", $1); print $1; } }' /etc/my.cnf)
iscsiopts=$(awk -v mysqlopts=$mysqlopts '{ if ($1 !~ /^[ \t]*#/ && $2 != "/" && mysqlopts ~ $2 ) { print $4; }}' /etc/mtab)
status iscsid > /dev/null 2>&1
iscsicheck=$?
if [ "$mysqlcheck" -eq 0 ]; then
echo "MySQL [ already running ]"
exit 1
else
if [ "$iscsicheck" -ne 0 ] ; then
if [[ "$iscsiopts" =~ "_netdev" ]]; then
action $"Checking iscsid: " /bin/true
action $"Starting $prog: " /bin/false
echo "Can not start MySQL, iSCSI disk is not mounted. MySQL is on a iSCSI disk."
exit 1
fi
elif [ "$iscsicheck" -eq 0 ]; then
if [[ "$iscsiopts" =~ "_netdev" ]]; then
iscsiadm -m session -R > /dev/null 2>&1
iscsiadmcheck=$?
if [ "$iscsiadmcheck" -ne 0 ]; then
action $"Checking iscsid: " /bin/true
action $"Starting $prog: " /bin/false
echo "iSCSI login check failed. QUITING!!!"
exit 1
fi
fi
else
action $"Checking iscsid: " /bin/false
action $"Starting $prog: " /bin/false
echo "Can not start MySQL, iSCSI is not running. MySQL is on a iSCSI disk."
exit 1
fi
fi
touch "$errlogfile"
chown mysql:mysql "$errlogfile"
chmod 0640 "$errlogfile"
[ -x /sbin/restorecon ] && /sbin/restorecon "$errlogfile"
if [ ! -d "$datadir/mysql" ] ; then
action $"Initializing MySQL database: " /usr/bin/mysql_install_db
ret=$?
chown -R mysql:mysql "$datadir"
if [ $ret -ne 0 ] ; then
return $ret
fi
fi
chown mysql:mysql "$datadir"
chmod 0755 "$datadir"
# Pass all the options determined above, to ensure consistent behavior.
# In many cases mysqld_safe would arrive at the same conclusions anyway
# but we need to be sure.
/usr/bin/mysqld_safe --datadir="$datadir" --socket="$socketfile" \
--log-error="$errlogfile" --pid-file="$mypidfile" \
>/dev/null 2>&1 &
ret=$?
# Spin for a maximum of N seconds waiting for the server to come up.
# Rather than assuming we know a valid username, accept an "access
# denied" response as meaning the server is functioning.
if [ $ret -eq 0 ]; then
STARTTIMEOUT=30
while [ $STARTTIMEOUT -gt 0 ]; do
RESPONSE=`/usr/bin/mysqladmin -uUNKNOWN_MYSQL_USER ping 2>&1` && break
echo "$RESPONSE" | grep -q "Access denied for user" && break
sleep 1
let STARTTIMEOUT=${STARTTIMEOUT}-1
done
if [ $STARTTIMEOUT -eq 0 ]; then
echo "Timeout error occurred trying to start MySQL Daemon."
action $"Starting $prog: " /bin/false
ret=1
else
action $"Starting $prog: " /bin/true
fi
else
action $"Starting $prog: " /bin/false
fi
[ $ret -eq 0 ] && touch /var/lock/subsys/mysqld
return $ret
}

stop(){
MYSQLPID=`cat "$mypidfile" 2>/dev/null `
if [ -n "$MYSQLPID" ]; then
/bin/kill "$MYSQLPID" >/dev/null 2>&1
ret=$?
if [ $ret -eq 0 ]; then
STOPTIMEOUT=60
while [ $STOPTIMEOUT -gt 0 ]; do
/bin/kill -0 "$MYSQLPID" >/dev/null 2>&1 || break
sleep 1
let STOPTIMEOUT=${STOPTIMEOUT}-1
done
if [ $STOPTIMEOUT -eq 0 ]; then
echo "Timeout error occurred trying to stop MySQL Daemon."
ret=1
action $"Stopping $prog: " /bin/false
else
rm -f /var/lock/subsys/mysqld
rm -f "$socketfile"
action $"Stopping $prog: " /bin/true
fi
else
action $"Stopping $prog: " /bin/false
fi
else
ret=1
action $"Stopping $prog: " /bin/false
fi
return $ret
}

restart(){
stop
start
}

condrestart(){
[ -e /var/lock/subsys/mysqld ] && restart || :
}

# See how we were called.
case "$1" in
start)
start
;;
stop)
stop
;;
status)
status mysqld
;;
restart)
restart
;;
condrestart)
condrestart
;;
*)
echo $"Usage: $0 {start|stop|status|condrestart|restart}"
exit 1
esac

exit $?