Rotating MySQL Slow Logs

While working with different clients I happen to run across very large slow log files from time to time.  While several opinions on how they should be rotated exist. Many of these opinions use log rotate and the flush logs command,  I prefer not to flush my binary logs though. This is why I agree with Ronald Bradford's blog post from years ago on  how to do this.

• http://ronaldbradford.com/blog/the-correct-approach-to-rolling-mysql-logs-2010-02-22/

I have taken it a little further and scripted the steps. The bash script is built with MySQL 5.6 and the mysql_config_editor in mind it can be used on older versions of MySQL as well.

The script will do the following:

• Gather current log file name
• Gather current long query time value
• Resets the long query time to a higher value
• Copies the log while truncating it as well (See Ronald's Post)
• Resets the long query time back to the original time
• Executes a simple slow query so you can check the new slow log if you wish
• Removes the older slow log so you can gain space back. 
• You can comment this command out if you wish to review the log instead.

So does it all work ?
Well let us use this example.

I am currently using the mysql_config_editor over a .my.cnf file so I updated the script accordingly.

#  mysql_config_editor print --all
[local]
user = root
password = *****
host = localhost


I can see that this slow query log is now 1G.

# ls -alh mysql-slow.log
-rw-rw---- 1 mysql mysql 1.1G Oct 11 16:08 mysql-slow.log


So I execute the script

 # /root/rotate_slow_logs.sh
 # ls -alh mysql-slow.log
-rw-rw---- 1 mysql mysql 5.8K Oct 11 16:11 mysql-slow.log


Ok good, it worked and I have a smaller log file now without flushing my bin logs or restarting MySQL.

This script can be added to a crontab so you can rotate as often as you would like.

Here is the script.

#!/bin/bash

# THIS IS BUILT WITH MYSQL 5.6 SECURITY IN MIND.
# SET THE LOGINPATHVALUE if you are using the mysql_config_editor
# IF YOU ARE NOT USING THE mysql_config_editor THEN IT IS ASSUMED YOU HAVE
# SET A .my.cnf FILE IN THE USER HOME DIR OR THIS USER HAS NO PASSWORD SET


# PLEASE SET THIS ACCORDINGLY TO YOUR SYSTEM.
LOGINPATHVALUE="local";

if [ -z "${LOGINPATHVALUE}" ]; then
LOGINPATH="";
fi

if [ -n "${LOGINPATHVALUE-unset}" ]; then
LOGINPATH="--login-path=$LOGINPATHVALUE "

fi

# GATHERS THE LOG FILE NAME
SLOWLOG=$(mysqladmin $LOGINPATH variables | grep slow | grep file |   awk '/[a-zA-Z]/ {print $4}' )

# GATHER CURRENT VALUE
LQT=$( mysqladmin $LOGINPATH variables | grep long_query_time |   awk '/[0-9]./ {print $4}'  )
LQTB=$(mysql $LOGINPATH -e " SELECT @@global.long_query_time *200 AS LQTB;" |   awk '/[0-9]./ {print $1}'  )
LQTC=$(mysql $LOGINPATH -e " SELECT @@global.long_query_time *2 AS LQTC;" |   awk '/[0-9]./ {print $1}'   )

# GATHER MARKER
DATE=`date +"%m%d%Y"`

# RESET SLOW QUERY TIME
# SET GLOBAL long_query_time=10;
mysql $LOGINPATH -e "SET GLOBAL long_query_time= $LQTB"

LQTD=$( mysqladmin $LOGINPATH variables | grep long_query_time |   awk '/[0-9]./ {print $4}'  )

#MOVE THE LOG OUT
cp $SLOWLOG $SLOWLOG.$DATE; > $SLOWLOG

#SET THE TIMEBACK
mysql $LOGINPATH -e "SET GLOBAL long_query_time= $LQT"

LQTD=$( mysqladmin $LOGINPATH variables | grep long_query_time |   awk '/[0-9]./ {print $4}'  )

#PLACE A Slow query for log
SLOWQUERY=$(mysql $LOGINPATH -e "SELECT sleep($LQTC) " )

# REMOVE OLD LOG
/bin/rm -f $SLOWLOG.$DATE;