Logging queries with MySQL

It is possible to log all queries done by the MySQL server. To enable MySQL logging, the MySQL manual indicates you should add --log[=file_name] when starting mysqld (the manual page is here). This isn't really the best options, as most Linux distributions use init scripts to start up MySQL, and this means either modifying them, or calling mysqld from the command line, which isn't really the best way of doing this when the init scripts do the job of starting and stopping services so nicely.

So instead, it is easier to modify the my.cnf file (located at /etc/my.cnf on many Linux distros) and add the logging option there. This is as simple as adding the following line under the [mysqld] section, creating a new [mysqld] section if one isn't present:

[mysqld]
log=/tmp/mysql.log

You can put the log file anywhere you want, but it must be in a location that the mysql daemon can write to, and the file must be writeable by mysqld. When doing this myself I initially put the file at /var/log/mysql.log but got the following error message in the mysqld.log logfile:

071023 17:11:03 mysqld started
/usr/libexec/mysqld: File '/var/log/mysql.log' not found (Errcode: 13)
071023 17:11:03 [ERROR] Could not use /var/log/mysql.log for logging (error 13). Turning logging off for the whole duration of the MySQL server process. To turn it on again: fix the cause, shutdown the MySQL server and restart it.

I then did touch /var/log/mysql.log and restarted MySQL but got the same error message. So then chown mysql:mysql /var/log/mysql.log but still got the error message. As soon as I relocated the file to /tmp it worked fine.

As far as security is concerned, you should make sure the permissions for this file are 0700, so that only the mysql daemon can read the file, and no one else can. This is because every single query will be logged, and you don't want ordinary users to view the log file because queries may contain sensitive information, such as logins and passwords.

Another thing to remember is that every single query is logged (yes, this is the second time I've mentioned this). So you really only want to be logging MySQL queries when you are doing debugging, as the file will quickly become very large on a production server. You also have to restart MySQL each time you want to change the setting, so on a production server you would need to adjust these sorts of settings only during quiet periods.