MySQL Server can create a number of different log files to help you see what activity is taking place. See Section 5.2, “MySQL Server Logs”. However, you must clean up these files regularly to ensure that the logs do not take up too much disk space.
When using MySQL with logging enabled, you may want to back up and remove old log files from time to time and tell MySQL to start logging to new files. See Section 6.2, “Database Backup Methods”.
On a Linux (Red Hat) installation, you can use the
mysql-log-rotate
script for this. If you
installed MySQL from an RPM distribution, this script should have
been installed automatically. You should be careful with this
script if you are using the binary log for replication. You should
not remove binary logs until you are certain that their contents
have been processed by all slaves.
On other systems, you must install a short script yourself that you start from cron (or its equivalent) for handling log files.
For the binary log, you can set the
expire_logs_days
system variable
to expire binary log files automatically after a given number of
days (see Section 5.1.4, “Server System Variables”). If you are
using replication, you should set the variable no lower than the
maximum number of days your slaves might lag behind the master. To
remove binary logs on demand, use the PURGE
BINARY LOGS
statement (see
Section 12.5.1.1, “PURGE BINARY LOGS
Syntax”).
You can force MySQL to start using new log files by flushing the
logs. Log flushing occurs when you issue a
FLUSH LOGS
statement or execute a mysqladmin flush-logs,
mysqladmin refresh, mysqldump
--flush-logs, or mysqldump
--master-data command. See Section 12.4.6.3, “FLUSH
Syntax”,
Section 4.5.2, “mysqladmin — Client for Administering a MySQL Server”, and Section 4.5.4, “mysqldump — A Database Backup Program”. In
addition, the binary log is flushed when its size reaches the
value of the max_binlog_size
system variable.
As of MySQL 5.5.3, FLUSH
LOGS
supports optional modifiers to enable selective
flushing of individual logs (for example,
FLUSH BINARY
LOGS
).
A log-flushing operation does the following:
If general query logging or slow query logging to a log file is enabled, the server closes and reopens the general query log file or slow query log file.
If binary logging is enabled, the server closes the current binary log file and opens a new log file with the next sequence number.
If the server was started with the
--log-error
option to cause the error log to be written to a file, the result of a log-flushing operation is version dependent:As of MySQL 5.5.7, the server closes and reopens the log file.
Prior to MySQL 5.5.7, the server renames the current log file with the suffix
-old
, then creates a new empty log file.
The server creates a new binary log file when you flush the logs.
However, it just closes and reopens the general and slow query log
files. To cause new files to be created on Unix, rename the
current logs before flushing them. At flush time, the server opens
new logs with the original names. For example, if the general and
slow query logs are named mysql.log
and
mysql-slow.log
, you can use a series of
commands like this:
shell>cd
shell>mysql-data-directory
mv mysql.log mysql.old
shell>mv mysql-slow.log mysql-slow.old
shell>mysqladmin flush-logs
On Windows, use rename rather than mv.
At this point, you can make a backup of
mysql.old
and
mysql-slow.old
and then remove them from
disk.
A similar strategy can be used to back up the error log file, if
there is one, except that, on Windows, you cannot rename the error
log file while the server has it open before MySQL 5.5.7. To
rename the error log file, a stop and restart can be avoided by
flushing the logs to cause the server to rename the current log
file with the suffix -old
and create a new
empty error log file. For further information, see
Section 5.2.2, “The Error Log”.
You can rename the general query log or slow query log at runtime by disabling the log:
SET GLOBAL general_log = 'OFF'; SET GLOBAL slow_query_log = 'OFF';
With the logs disabled, rename the log files externally; for example, from the command line. Then enable the logs again:
SET GLOBAL general_log = 'ON'; SET GLOBAL slow_query_log = 'ON';
This method works on any platform and does not require a server restart.