MySQL query logging

Small tip of how to log all queries done by the MySQL server. It is very useful if you want to preview SQL requests from WordPress, Joomla or any other system or client. After logging is enabled, server will write information to the log file when clients connect or disconnect, and it will log each SQL statement.

To enable General query log add log line under the mysqld section in /etc/my.cnf configuration file and restart MySQL server.

[mysqld]
log=/var/log/mysqld.log

Many Linux distributions (like Fedora) come with created /var/log/mysqld.log file. Just be sure that mysqld.log file exists and mysql user has permissions to write. If you need to create a log file, here are instructions to follow:

# create mysqld.log file
bash> touch /var/log/mysqld.log
# set owner and group owner for the mysqld.log file
bash> chown mysql:mysql /var/log/mysqld.log

Now, you are ready to peek how MySQL server processes SQL statements and logs them to the /var/log/mysqld.log file.

bash> tail -f /var/log/mysqld.log

After MySQL debugging is finished, remove / comment log line because it could slow down your server or fill up the disk space. It’s certainly undesirable to have turned on logging on a production server.

Don’t forget to restart MySQL server each time you change the log setting.