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.
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.
6 thoughts on “MySQL query logging”
Thanks for sharing, this is very useful for recording changescripts for deploying to production environment!
Thanks for sharing.
Yes, this MySQL tip is very useful (especially in the early stage of the project) and it allows to watch how Web app sends requests to the database server. Very helpful.