Mysql – best practice for fixing thesql slow query log error 13

best practicesloggingMySQLquery

CentOS 5.5
MySQL 5.5 installed via a yum repository

[ERROR] Could not use /var/log/mysqld.slow.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.

Using a standard install of MySQL 5.5 (specifically from the webtatic repository), the slow query log cannot start actually recording due to a permissions issue. If I pre-create a copy and chown it to belong to user:mysql and group:mysql, then it works fine.

In the very same directory (/var/log), it has no problems creating and logging to mysql.log and mysql.error.log.

Obviously I have a hackish fix for it, but I'd like to be able to use logrotate on it, without additionally requiring logrotate to repeat the hackery too. (The only thing worse than hackery is having to repeat hackery.)

Does anyone know what the best practice is for fixing this?

Best Answer

It sounds like the mysql process (mysqld_safe, I believe) is unable to create the log file after you use logrotate to move it out of the way and send a HUP? If I'm way off here, let me know.

Assuming I'm not way off, there are a few options here:

  • Move the log to a directory owned by the same user as the mysqld_safe process. For example, create a /var/log/mysqld/ directory and save the logfiles there. If the directory is mysql:mysql 700 then new files can be created without an issue.
  • Use the copy/truncate logrotate method instead of the move/SIGHUP method. The copy/truncate method will copy the current log files (mysqld.slow.log) to a new file (mysqld.slow.log.1) then truncate the original file to zero bytes. This is useful if you don't want to interrupt the process that is writing to the log for some reason. The downside, of course, is that there is additional disk overhead with copying the original file to a new file before wiping the original file back to zero bytes. This is done by adding the copytruncate option to the logrotate stanza for that file and removing the no-longer-required postrotate section.
Related Topic