Tailing the MySQL query log in real time can be a lifesaver for any developer, and it’s pretty easy to do.
1. Make the logfile
Create the logfile that the mysqld process will write to.
If you’re using MacPorts or the MySQL package, run this on the terminal:
sudo touch /var/log/mysql-query.log
sudo chown _mysql /var/log/mysql-query.log
If you’re using homebrew, MySQL will be running with your user id, so you’ll want to:
sudo touch /var/log/mysql-query.log
sudo chown `whoami` /var/log/mysql-query.log
2. Tell MySQL to write to the logfile
If you’ve installed MySQL 5.1.x from the Mac .pkg, you won’t have an /etc/my.cnf, but it just needs to have these two lines:
[mysqld]
log=/var/log/mysql-query.log
3. Restart mysqld
If you have the MySQL preference pane, open that, click stop, then start. (It turns out that when the preference pane is open, it pings the database every 2 seconds, so it can detect if the db is alive. If you mangle the my.cnf, you’ll find the start button seems to not respond to clicks.)
If you installed MySQL with homebrew or MacPorts, and installed a launchd script to start MySQL on startup, you can run mysqladmin shutdown and launchd will restart MySQL for you.
4. Finally: watch the query log
Run tail -f /var/log/mysql-query.log