Unobtrusive viewing of MySQL queries with tcpdump

words by Brian Racer

There are times when you need to monitor the queries coming in to MySQL, but turning on query logging would create too much of a disk I/O hit, or you can’t restart the server to setup MySQL Proxy. Instead we can just monitor the network traffic and extract data that might be interesting using tcpdump and an inline perl script:

sudo tcpdump -i lo -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.=" $_";
  }
}'

This will only work for clients communicating via TCP – if you are connecting through ‘localhost’ you will be going through a unix socket instead. If you switch ‘localhost’ to ‘127.0.0.1’ then your queries will go through the network stack.

If you just want to dump the traffic to a file for a little bit and analyze it later, do this instead:

sudo tcpdump -i lo port 3306 -s 65535 -x -n -q -tttt> tcpdump.out

You can then use mk-query-digest from Maatkit with–type=tcpdump. See more about this at the MySQL Performance Blog.


Installing MySQL Ruby Gem on CentOS 5 (and an explanation about getopt’s double hyphens)

words by Brian Racer

I had an issue trying to build the mysql gem on CentOS 5.x.

ERROR: While executing gem … (Gem::Installer::ExtensionBuildError)
ERROR: Failed to build gem native extension.
 
ruby extconf.rb update
checking for mysql_query() in -lmysqlclient… no
...

After much hairpulling, two hyphens solved my problem:

sudo gem install mysql -- \
--with-mysql-include=/usr/include/mysql \
--with-mysql-lib=/usr/lib/mysql

A blog comment I found explains the double hyphens:

The double hyphens (–) tells the option parser that there is no more options on the command line. This special syntax comes from GNU getopt. Everything after ‘–’ is treated as non-options. This is useful if you want to write something on the command line that looks like an option but is not, or if it should be parsed though as an option to another program called by the one you are calling.

The two hyphens in this particular command string is important since the gem binary must not confuse the ‘–with-mysql-dir’ option as an option for gem it self. Instead this option should be passed on to the make command called in the gem internals.

Thomas Watson

And if you are on a 64-bit system, be sure to use –-with-mysql-lib=/usr/lib64/mysql instead.


Bash script to create MySQL database and user

words by Brian Racer

Here is a little script I made to quickly and easily create users and databases for MySQL. I only use this for development, for actual deployed applications you would probably want to be more specific about the privileges given:

#!/bin/bash
 
EXPECTED_ARGS=3
E_BADARGS=65
MYSQL=`which mysql`
 
Q1="CREATE DATABASE IF NOT EXISTS $1;"
Q2="GRANT ALL ON *.* TO '$2'@'localhost' IDENTIFIED BY '$3';"
Q3="FLUSH PRIVILEGES;"
SQL="${Q1}${Q2}${Q3}"
 
if [ $# -ne $EXPECTED_ARGS ]
then
  echo "Usage: $0 dbname dbuser dbpass"
  exit $E_BADARGS
fi
 
$MYSQL -uroot -p -e "$SQL"

To use it, just run:

./createdb testdb testuser secretpass

That command would create a database named testdb, and user testuser with the password of secretpass.


How to fix Munin’s MySQL Graph on cPanel

words by Brian Racer

We have a few cPanel servers deployed and to visually monitor performance we use the Munin monitoring tool. We were having issues where the MySQL graphs would stop updating. The bug has to do with one of the perl libraries the script uses, which causes the MySQL plugin to have problems location the mysqladmin program.

To fix these, create or edit the following file:

vi /etc/munin/plugin-conf.d/cpanel.conf

It should contain the following:

[mysql*]
user root
group wheel
env.mysqladmin /usr/bin/mysqladmin
env.mysqlopts --defaults-extra-file=/root/.my.cnf

This would load the username and password from root’s mysql config. If that file doesn’t exist you can create it:

sudo vi /root/.my.cnf
[client]
user="root"
pass="secret!password"

Now just restart Munin, wait a a little bit, and the graphs should start populating again!

sudo /etc/init.d/munin-node restart

Using Maatkit to batch convert MySQL storage engines

words by Brian Racer

Recently I was working with a client who imported a couple databases, each that had thousands of InnoDB tables taking up tens of gigabytes of data to a new server. Unfortunately the InnoDB engine was misconfiguration and therefore not loaded at the time of import. MySQL silently created all these tables as MyISAM instead. Not wanting to wait hours for the import process to proceed again, I used a program from the excellent Maatkit package: mk-find. It works similar to the unix find command, except it works on the MySQL server.

The following command will find all MyISAM tables from a certain database and convert them to InnoDB:

mk-find <db_name> --engine MyISAM --exec "ALTER TABLE %D.%N ENGINE=INNODB" --print

You can download the latest version from Maatkit’s Google Code page.