Rechercher dans le manuel MySQL
5.4.7 Server Log Maintenance
As described in Section 5.4, “MySQL Server Logs”, MySQL Server can create several different log files to help you see what activity is taking place. 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 7.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. 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.
Binary log files are automatically removed after the server's
binary log expiration period. Removal of the files can take place
at startup and when the binary log is flushed. The default binary
log expiration period is 30 days. You can specify an alternative
expiration period using the
binlog_expire_logs_seconds
system
variable. If you are using replication, you should specify an
expiration period that is no lower than the maximum amount of time
your slaves might lag behind the master. To remove binary logs on
demand, use the PURGE BINARY LOGS
statement (see Section 13.4.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 13.7.7.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.
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 server closes and reopens the 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 log files before flushing them. At flush time, the server
opens new log files with the original names. For example, if the
general and slow query log files are named
mysql.log
and
mysql-slow.log
, you can use a series of
commands like this:
shell> cd mysql-data-directory
shell> 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.
You can rename the general query log or slow query log at runtime by disabling the log:
With the logs disabled, rename the log files externally (for example, from the command line). Then enable the logs again:
This method works on any platform and does not require a server restart.
For the server to recreate a given log file after you have
renamed the file externally, the file location must be writable
by the server. This may not always be the case. For example, on
Linux, the server might write the error log as
/var/log/mysqld.log
, where
/var/log
is owned by
root
and not writable by
mysqld. In this case, the log-flushing
operation will fail to create a new log file.
To handle this situation, you must manually create the new log
file with the proper ownershiop after renaming the original log
file. For example, execute these commands as
root
:
shell> mv /var/log/mysqld.log /var/log/mysqld.log.old
shell> install -omysql -gmysql -m0644 /dev/null /var/log/mysqld.log
Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-log-file-maintenance.html
The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.
References
These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.