Truncating MySQL Log Files

Often I want to have MySQL logging turned on so that when there are problems I can quickly tail the log file to see what is causing the problem. The problem with leaving MySQL logging on for an extended period of time is that the log files can get big quickly. So I usually leave logging turned off and only turn it on when needed. Which works fine, but in order to turn off or on MySQL logging you have to restart the server. This is not ideal in most cases because a restart means that any clients trying to connect during the process of restarting will get an error.

Typical log rotation scripts won't work because when the file is rotated the MySQL server won't recreate the file or log to a new file even if you create it by hand.

Last week I thought of a solution that seems to be working well. I realized that I could leave logging turned on but have a nightly script that simply truncated the file with this command:

echo '' > /var/log/mysql.log

This will reduce the size of the file to zero bytes and at the same time MySQL will continue to log to the file without requiring a restart.

But this method doesn't allow you to actually rotate the log file in case you wanted to keep it around. So after researching a bit, I found this:

shell> mv host_name.log host_name-old.log
shell> mysqladmin flush-logs
shell> cp host_name-old.log backup-directory
shell> rm host_name-old.log

This would allow you to do an actual log rotation instead of just deleting the old file.

If you installed from RPM there is a log rotate script provided, and details can be found here:

http://dev.mysql.com/doc/refman/4.1/en/log-file-maintenance.html