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.
