August 11, 2011

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.

November 7, 2008

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.

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.