Command Center

One of the issues facing log rotation in mySQL is that mySQL doesn’t seem to have the ability to perform a “reload”.

Meaning standard methods of rotating logs using logrotate leave mySQL logging an error in the syslog saying that the log file could not be found, and refusing to to any logging until the server is restarted, not fun if like me you manage high availability solutions and restarting a service is never the best option.

As such I have written this log rotate script, this does however make some assumptions.

1. Your mysql user is “mysql”
2. Your mysql slow query log is /var/log/mysql-slow.log

The script is written to perform the following actions:

1. Rotate weekly
2. Retain 3 rotations (3 files + live log)
3. Compress on rotate (gzip)
4. Create new logfile with 660 permissions chowned to mysql:mysql
5. Run: mysqladmin flush-logs

Please be aware that the flush logs command will also rotate any binary logging currently in place, please ensure this will not adversely affect your deployment prior to use

Please ensure you carry out your own testing prior to deploying this script into a live environment.

http://svn.saiweb.co.uk/branches/linux-the-sysadmin-script/branches/logrotate.d/mysql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

#
# mySQL slow log rotation script by D.Busby
# place this script in /etc/logrotate.d/ or your appropriate logrotate dir.
# http://creativecommons.org/licenses/by-sa/2.0/uk/ CC BY-SA
#
# NOTE: if you are reliant on binlogs i.e. for replication, 'flush logs' closes the current binlog and moves to the next itteration of log files
# You should test this does not cause an issue with your deployment before using this script
#
/var/log/mysql-slow.log {
weekly
rotate 3
compress
missingok
notifempty
sharedscripts
create 660 mysql mysql
postrotate
/usr/bin/mysqladmin flush-logs
endscript
}
Tags: log rotation, mySQL, slow query


Reference: http://www.saiweb.co.uk/mysql/mysql-slow-query-log-rotation