MySQL Logging – How to log all mysql queries, errors and slow queries on Cpanel Servers

January 22, 2013

Logging slow queries and mysql errors is important for ease of troubleshooting mysql issues but if you also need to log every single mysql query executed by the server for troubleshooting purposes then you need to add an extra line to your mysql configuration file.

First and foremost, for this you will need to have root ssh access to your server.

In addition to the regular mysql configurations, this is how I do my mysql logging.

1. Once logged into the server run the command below to edit the main mysql config file.
# pico /etc/my.cnf

2. Add the following lines to the [mysqld] section.
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 1
log = /var/log/mysql/mysql-general.log

3. Add the following lines to the [mysqld_safe] section

4. Now save the file and run the following commands one after the other.
touch /var/log/mysql/mysql-slow.log
touch /var/log/mysql/mysqld-errors.log
touch /var/log/mysql/mysql-general.log

chown mysql:mysql /var/log/mysql/mysql-slow.log
chown mysql:mysql /var/log/mysql/mysqld-errors.log
chown mysql:mysql /var/log/mysql/mysql-general.log

5. Restart Mysql
# service mysql restart

NICE! You will now have comprehensive logging of all your mysql activities giving you some extra tools in case there is issues with your mysql server.

To view the content of the general log (for eg) run this command
# cat /var/log/mysql/mysql-general.log

To view the queries as they are being executed run this command
# tail -f /var/log/mysql/mysql-general.log

NOTE: on a production server, the general log can get big fast, so only enable that during development or troubleshooting. You can always open my.cnf and comment out the general log line using #. Make sure you restart the mysql service after making changes to my.cnf file.