Rotating MySQL logs is important because logs can take up a lot of disk space if left unmanaged. For example, an untuned database can lead to a lot of slow queries being logged. The error log can become large if it includes notes and warnings. Additionally, binlogs track every data modification statement like INSERT, DELETE, and UPDATE. If you are dropping a lot of tables, expect the binlogs to take up a lot of disk space, sometimes very quickly. The good news is that rotating MySQL logs can be automated with a system utility tool that is likely to already be installed on your operating system (OS), called logrotate.
A quick and easy way to check if logrotate is already installed is by using the following command, knowing the version is also useful since there are subtle differences in versions.
$ logrotate –-versionDespite the differences, configuration is pretty straightforward and easy in any version.
Configuring Logrotate
The logrotate script is installed into the /etc/cron.daily directory and will run daily as root user so no need to create a cronjob. I recommend leaving the script in this directory and modifying the configuration file if you want to rotate the logs weekly or monthly.
There are two logrotate configuration files. The main configuration file is located at /etc/logrotate.conf. The sample below, from v. 3.8.6, shows how intuitive it is to set up. On line 3, the log rotation is set to once a week, or weekly. This can be changed to daily or monthly. For binlogs, the value for expire_days will override this entry if the value is fewer days. Uncomment line 15 if you want the log files to be compressed upon rotation.
# see "man logrotate" for details
# rotate log files weekly
weekly
# keep 4 weeks worth of backlogs
rotate 4
# create new (empty) log files after rotating old ones
create
# use date as a suffix of the rotated file
dateext
# uncomment this if you want your log files compressed
#compress
# RPM packages drop log rotation information into this directory
include /etc/logrotate.d
# no packages own wtmp and btmp -- we'll rotate them here
/var/log/wtmp {
monthly
create 0664 root utmp
minsize 1M
rotate 1
}
/var/log/btmp {
missingok
monthly
create 0600 root utmp
rotate 1
}
# system-specific logs may also be configured here.Line 18 includes other configuration files found in the /etc/logrotate.d directory, which includes mysql-server (v 3.14.0 example below).
# - I put everything in one block and added sharedscripts, so that mysql gets
# flush-logs'd only once.
# Else the binary logs would automatically increase by n times every day.
# - The error log is obsolete, messages go to syslog now.
/var/log/mysql.log /var/log/mysql/*log {
daily
rotate 7
missingok
create 640 mysql adm
compress
sharedscripts
postrotate
test -x /usr/bin/mysqladmin || exit 0
# If this fails, check debian.conf!
MYADMIN="/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf"
if [ -z "`$MYADMIN ping 2>/dev/null`" ]; then
# Really no mysqld or rather a missing debian-sys-maint user?
# If this occurs and is not a error please report a bug.
#if ps cax | grep -q mysqld; then
if killall -q -s0 -umysql mysqld; then
exit 1
fi
else
$MYADMIN flush-logs
fi
endscript
}The first uncommented line lists the log files that will be rotated. In between the brackets are the directives, also known as options, that control the log rotation. The table below defines the directives included above. Another useful directive is minsize <size>, which will rotate the logs when it grows larger than the specified size. A full list of directives can be found here.
| Directive | Function | Other Options |
daily | Rotates the logs daily. | hourly weekly monthlyyearly |
rotate 7 | Keeps 7 days worth of backlogs. | Number of days can be changed. |
missingok | Will not throw an error message if a log file is missing. | nomissingok – will throw an error if a log file is missing. |
create 640 mysql admin | Specifies the mode, owner, and group of the log file created. | nocreate – disables or overrides the create option. |
compress | Backlogs are compressed using gzip. | compresscmd used to change the default (gzip) command to another command to compress log files.compressext – if compression is enabled, specifies an extension to use on compressed log files.compressoptions – if compression is enabled, specifies the compression options that are passed by logrotate on the command line. |
sharedscripts | Runs the script and its options once for all logs that require rotating. Will override nosharescripts option and implies create option. | nosharescripts – runs prerotate and postrotate scripts for every log file. |
postrotate/endscript | After the log file is rotated, any command between postrotate and endscript will be run using /bin/sh. | prerotate/endscript – before the log file is rotated, any command between pretrotate and endscript will be run using /bin/sh. |
Prior to logrotate version 3.14.0, this configuration file will look different. For instance, the paths for the log files are based on earlier MySQL versions. Additionally, a hidden file needs to be created if the MySQL root user has a password, which it definitely should. Handy instructions are included at the top of the configuration file (example below).
# If the root user has a password you have to create a
# /root/.my.cnf configuration file with the following
# content:
#
# [mysqladmin]
# password = <secret>
# user= root
#
# where "<secret>" is the password.
#
# ATTENTION: This /root/.my.cnf should be readable ONLY
# for root !Debugging
Even though configuration is easy, its technology. Things can still not work on the first try. If this is the case, logrotate can be executed on the command line with the --debug option (syntax below).
$ sudo logrotate /etc/logrotate.conf --debugI am a big fan of using the --verbose option. Similar to --debug, it can be run on the command line. The difference is that it prints everything that the logrotate is doing and not doing.
$ sudo logrotate /etc/logrotate.conf --verboseLast but not least, the manual can be accessed using the following syntax.
$ man logrotatePlease be sure to contact us for any questions or more information!