So, you’ve got a nice lean mysqld server running, when you get a page for a filesystem about to fill up:

root@WidgetMysqlDB001:/var/log/mysqld# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda1              12G   11G    1G  92% /

Hmm… what could possibly be doing that?

root@WidgetMysqlDB001:/var/log/mysqld# ls -al
-rw-rw---- 1 mysql root        12000 Jan 21 12:00 mysqld.err
-rw-rw---- 1 mysql mysql 11211160064 Jan 30 12:30 mysqld.slow.log

Wow, that’s alot of slow queries! (Or someone hasn’t been rotating logs!)

If you happen to have admin rights to the MySQL DB: (Taken from: MySQL 5.0 Reference Manual :: Server Log Maintenance

shell> cd mysql-data-directory
shell> mv mysql.log mysql.old
shell> mv mysql-slow.log mysql-slow.old
shell> mysqladmin flush-logs

However, this could screw up other log rotation bits, as ‘flush-logs’ flushes all logs. However, if you can, the above would be the preferred.

Let’s say you don’t have admin rights to the MySQL instance and just access to the files. One somewhat brute force way would be:

cp /var/log/mysqld/mysqld.slow.log /bigdisk/mysqld/mysqld.slow.log
echo "#`date`" > /var/log/mysqld/mysqld.slow.log

And call it a day… which would be fine, if the log wasn’t being updated frequently.

However, in my case, from the time a ‘cp’ started and the time the ‘cp’ or ‘rsync’ ended, the file had grown some 100KB in the process. So the copy would be out of date as soon as it was done. Not good. Well, the number of slow queries wasn’t good either… but let’s say you absolutely needed to preserve those slow query entries. I accomplished this as follows:

To warm up the file:

rsync /var/log/mysqld/mysqld.slow.log /bigdisk/mysql/mysqld.slow.log

In one window, let’s start capturing any delta(s). I prep’d a command, ready to be run:

tail -f /var/log/mysqld/mysqld.slow.log >> /bigdisk/mysql/mysqld.slow.log.catchup

In another window, I prep another command:

rsync --append --inplace -v /var/log/mysqld/mysqld.slow.log \\
/bigdisk/mysql/mysqld.slow.log \\
&& echo "# `date`" > /var/log/mysqld/mysqld.slow.log

I hit enter in the first window, to start capturing the delta, and immediately hit enter in the second window to start the final rsync/append, followed by a truncation of the log file that was being updated.

On completion of the command in window #2, I’ll get a truncation error in window #1 from the ‘tail -f’:

tail: /var/log/mysqld/mysqld.slow.log: file truncated

At which point, I CTRL-C to stop the ‘tail -f’ and append the contents of “/bigdisk/mysql/mysqld.slow.log.catchup” into “/bigdisk/mysql/mysqld.slow.log”:

cat /bigdisk/mysql/mysqld.slow.log.catchup >> /bigdisk/mysql/mysqld.slow.log

In this situation, you will end up with at least one repeat entry, if not many more, but you will not lose any entries. Ideally, you would have logrotation in place and are leveraging MySQL’s admin tools to flush log files so you won’t have to resort to this. However, if you’ve no other choice, this is a means by which you can rotate the log file without losing data and free up space.

Note, the above would work for other log files on a system, where the file has an open file handle. Tomcat shell wrapper dumping to output.log growing to epic sizes? Yep.

Of course, you should also consider why the MySQL thinks there are so many slow queries… but that’s another article.