Server Administration - MySQL - page 766

Problem
Available strategies include rotating a logfile through a set of names and expiring files
by age. But different strategies apply to different logs, so consider the log type before
choosing a strategy.
Discussion
Logfile rotation is a technique that renames a logfile through a series of one or more
names. This maintains the file for a certain number of rotations, at which point it reaches
the end of the sequence and its contents are discarded by being overwritten. Rotation
can be applied to the error log, general query log, or slow query log.
Logfile expiration removes files when they reach a certain age. This technique applies
to the binary log.
Both log management methods rely on log flushing to make sure that the current logfile
has been closed properly. When you flush the logs, the server closes and reopens
whichever of the files it is writing. If you rename the error, general query, or slow query
logfile first, the server closes the current file and reopens a new one using the original
name; this is what enables rotation of the current file while the server runs. The server
also closes the current binary logfile and opens a new one with the next number in the
sequence.
To flush the server logs, execute a FLUSH LOGS statement or use the mysqladmin flush-
logs command. (Log flushing requires the RELOAD privilege.) The following discussion
shows maintenance operations as performed at the command line, so it uses mysqlad‐
min . The examples use mv as the file renaming command, which is applicable on Unix.
On Windows, use rename instead.
Rotating the error, general query, or slow query log
To maintain a single file in a log rotation, rename the current logfile and flush the logs.
Suppose that the error logfile is named err.log in the data directory. To rotate it, change
location to the data directory, then execute these commands:
% mv err.log err.log.old
% mysqladmin flush-logs
When you flush the logs, the server opens a new err.log file. You can remove err.log.old
at your leisure. To maintain an archive copy, include it in your filesystem backups before
removing it.
To maintain a set of multiple rotated files, it's convenient to use a sequence of numbered
suffixes. For example, to maintain a set of three old general query logfiles, do this:
% mv query.log.2 query.log.3
% mv query.log.1 query.log.2