Enable logging in MySQL

These are some haphazardly written notes on logging in MySQL. For consistency, create a new subdirectory in /var/log/ and assign ownership to the user under which your MySQL server is running - by default that would be the mysql user:

mkdir /var/log/mysqld/
chown mysql:mysql /var/log/mysqld

I like to name the directory mysqld instead of mysql, to clarify that the logs in that directory are those of the MySQL Server and not those of the MySQL Client.

Slow Query Log

The slow query log contains queries the execution of which took more than the value of the long_query_time variable, which by default is 10 seconds.

To enable the slow query log on the next start of mysqld, add the following line in your MySQL server's configuration file (/etc/my.cnf by default), in the [mysqld] section:

slow-query-log
slow_query_log_file=/var/log/mysqld/slow-queries.log

Create the file and assign ownership to the mysql user:

touch /var/log/mysqld/slow-queries.log
chown mysql:mysql /var/log/mysqld/slow-queries.log

Finally, either restart the server to enable the logging, or enable it on the already running server with:

mysql> SET GLOBAL slow_query_log_file='/var/log/mysqld/slow-queries.log';
mysql> SET GLOBAL slow_query_log=1;

In any of those cases, the values of the global variables slow_query_log and slow_query_log_file, should change from the default:

mysql> show variables like 'slow_query_log%';
+---------------------+----------------------------------+
| Variable_name       | Value                            |
+---------------------+----------------------------------+
| slow_query_log      | OFF                              |
| slow_query_log_file | /var/run/mysqld/mysqld-slow.log  |
+---------------------+----------------------------------+

To:

mysql> show variables like 'slow_query_log%';
+---------------------+----------------------------------+
| Variable_name       | Value                            |
+---------------------+----------------------------------+
| slow_query_log      | ON                               |
| slow_query_log_file | /var/log/mysqld/slow-queries.log |
+---------------------+----------------------------------+