- Documentation Library
- Table of Contents
- MySQL 6.0 Reference Manual
- MySQL 5.1 Reference Manual
- MySQL 5.0 Reference Manual
- MySQL 3.23/4.0/4.1 Manual
- Table of Contents
MySQL Server can create a number of different log files that make it easy to see what is going on. 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.1, “Database Backups”.
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.3, “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.
You can force MySQL to start using new log files by issuing a
FLUSH LOGS statement or executing
mysqladmin flush-logs or mysqladmin
refresh. See Section 12.5.7.3, “FLUSH Syntax”, and
Section 4.5.2, “mysqladmin — Client for Administering a MySQL Server”.
A log flushing operation does the following:
If general query logging (
--log) or slow query logging (--log-slow-queries) to a log file is enabled, the server closes and reopens the general query log file or slow query log file.If binary logging (
--log-bin) is used, the server closes the current log file and opens a new log file with the next sequence number.If the server was given an error log filename with the
--log-erroroption, it renames the error log with the suffix-oldand creates a new empty error 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 will open 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>cdshell>mysql-data-directorymv mysql.log mysql.oldshell>mv mysql-slow.log mysql-slow.oldshell>mysqladmin flush-logs
At this point, you can make a backup of
mysql.old and
mysql-slow.log and then remove them from
disk.
On Windows, you can rename the general query log or slow query
log while the server has it open. You cannot rename the error
log file while the server has it open. You must stop the server
and rename the file, and then restart the server to create a new
log file. However, a stop and restart can be avoided by using
FLUSH LOGS, which causes the server to rename
the error log with an -old suffix and open
a new error log.
You can disable the general query log or slow query log at runtime:
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.
User Comments
I didn't find a useful binary log rotation tool, so I made myself a perl script which I am sharing for those who may interest. The code follows:
#!/usr/bin/perl -w
#Copyright (c) 2003, Diego Antonio Grecco Ribeiro
#All rights reserved.
#Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:
#*Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.
#*Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/ or other materials provided with the distribution.
#*Neither the name of the Universidade Federal de Sao Paulo nor the names of its contributors may be used to endorse or promote products derived from this software without specific prior written permission.
#THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
# Number of binary log files left (not counting the one in use)
$num = 2;
# Mysql Server, User and Password
# (make sure the user can connect and has the "super" privilege)
$host = "";
$user = "";
$password = "";
$socket = ""; # Optional
# --- You don't need to edit below here ---
if ($socket!~//) {
$sock = ";mysql_socket=".$socket;
} else {
$sock = "";
}
use DBI;
$dsn = "DBI:mysql::".$host.$sock;
$dbh = DBI->connect($dsn, $user, $password);
if (!$dbh) {
print "\nERROR connecting database - " . $DBI::errstr . ".\n";
exit;
}
$cmd = "SHOW MASTER LOGS";
$sth = $dbh->prepare($cmd);
$sth->execute;
$res = $sth->fetch;
if(!$res){
print "Erro ao selecionar log binario";
} else {
$i = 0;
while ($$res[0]) {
$i++;
$value[$i] = $$res[0];
$res = $sth->fetch;
}
}
$sth->finish;
$val = $i-$num;
if ($value[$val]) {
$cmd2 = "PURGE MASTER LOGS TO '$value[$val]'";
$sth2 = $dbh->prepare($cmd2);
$sth2->execute;
$sth2->finish;
}
$dbh->disconnect;
Thanks for the script, but it should also be able to check one (or more?) slave servers to make sure that they don't still need any of the binary logs you are deleting! As a secondary measure, binary logs which the slave hasn't processed should be deleted only when disk space gets below X percentage.
Here is a PHP script I made after reading Diego's perl script above (posted as a user comment at the time this comment was posted). The purpose is to purge/rotate the binary logs, keeping only the most recent KEEPNUM # of log files. It uses the 'SHOW MASTER LOGS' and 'PURGE MASTER LOGS TO ... ' mechanism in MySQL. Though inspired by Diego's perl script, I don't consider this to be a derivative work. Therefore, the following is hereby public domain if you ask me:
#!/path/to/binary/php -n
<?
define('KEEPNUM', 4);
define('HOST', '');
define('USER', '');
define('PASS', '');
if(!$dbh = mysql_connect(HOST, USER, PASS))
die(mysql_error());
$query = 'SHOW MASTER LOGS';
if(!$rs = mysql_query($query, $dbh))
die(mysql_error());
$lognames = array();
while($row = mysql_fetch_array($rs))
$lognames[] = $row[0];
$oldest_kept_log = $lognames[count($lognames) - KEEPNUM];
$query = "PURGE MASTER LOGS TO '$oldest_kept_log'";
if(!mysql_query($query, $dbh))
die(mysql_error());
print "The MySQL binary logs have been rotated. The oldest log is $oldest_kept_log\n";
?>
php with mysqli extension:
--------------------------------------
#!/path/to/binary/php -n
<?php
define('KEEPNUM', 4);
define('HOST', 'localhost');
define('USER', 'root');
define('PASS', 'foobar');
$dbh = new mysqli(HOST, USER, PASS);
if(mysqli_connect_errno()) die(mysqli_connect_error());
if($result = $dbh->query("SHOW MASTER LOGS"))
{
$lognames = array();
while($row = $result->fetch_array()) $lognames[] = $row[0];
$oldest_kept_log = $lognames[count($lognames) - KEEPNUM];
if(!$dbh->query("PURGE MASTER LOGS TO '$oldest_kept_log'")) die($dbh->error);
print "The MySQL binary logs have been rotated. The oldest log is $oldest_k
ept_log\n";
}
$dbh->close();
?>
--------------------------------------
save as mysqlbinlog_rotate in /etc/cron.daily or whatever
-AA-
This a example script, based on Diegos script, which checks the replication status on the slave (its for a simple master/slave setup) and purges the logs on the master to this state. With $num you can skip the purging of the last $num files. Maybe it helps someone, comments and bugfixes welcome.
#!/usr/bin/perl
# Copyright (c) 2005, Christian Ermel
# Based on the code from Diego Antonio Grecco Ribeiro
# Use as you like. Since this code is based on Diegos code, you must accept
# his license stuff. NO WARRENTY FROM ME AT ALL, USE ON YOU OWN RISK.
# Copyright stuff from Diego:
#
# Copyright (c) 2003, Diego Antonio Grecco Ribeiro
# All rights reserved.
# Redistribution and use in source and binary forms, with or without modification,
# are permitted provided that the following conditions are met:
# *Redistributions of source code must retain the above copyright notice,
# this list of conditions and the following disclaimer.
# *Redistributions in binary form must reproduce the above copyright notice,
# this list of conditions and the following disclaimer in the documentation
# and/ or other materials provided with the distribution.
# *Neither the name of the Universidade Federal de Sao Paulo nor the names
# of its contributors may be used to endorse or promote products derived from
# this software without specific prior written permission.
# THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS"
# AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
# WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED.
# IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, i
# INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED
# TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS;
# OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER
# IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING
# IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
## logs to keep
$num = 2;
## MASTER data
$m_host = "";
$m_user = "";
$m_password = "";
$m_socket = "";
## SLAVE DATA
$s_host = "";
$s_user = "";
$s_password = "";
$s_socket = "";
#####################
use DBI;
if ($m_socket ne "") {
$m_sock = ";mysql_socket=" . $m_socket;
} else {
$m_sock = "";
}
if ($s_socket ne "") {
$s_sock = ";mysql_socket=" . $s_socket;
} else {
$s_sock = "";
}
## connect to master
$m_dsn = "DBI:mysql::" . $m_host . $m_sock;
$m_dbh = DBI->connect($m_dsn, $m_user, $m_password);
if (!$m_dbh) {
print "\nERROR connecting MASTER database - " . $DBI::errstr . ".\n";
exit;
}
## connect to slave
$s_dsn = "DBI:mysql::" . $s_host . $s_sock;
$s_dbh = DBI->connect($s_dsn, $s_user, $s_password);
if (!$s_dbh) {
print "\nERROR connecting SLAVE database - " . $DBI::errstr . ".\n";
exit;
}
## ask slave which is the last file he has
my $query = s_execsql("SHOW SLAVE STATUS");
my @array = $query->fetchrow_array;
my $last = $array[5];
## extract number out of filename
my @tmp = split(/\./, $last);
my $file = $tmp[0];
my $number = $tmp[1];
my $len = length($number);
## skip the last $num files
if (int($number) > $num) {
$number -= $num;
} else {
## too less files
exit;
}
## fill string with lots of zeros, just to make sure (the are surely better ways to do this, but it works (hopefully :))
for ($x = 0; $x < $len; $x++) {
$number = "0" . $number;
}
## cut out only the amount of zeros we want
$number = substr($number, (length($number) - $len), length($number));
## filename we want to purge too
my $purge = $file . "." . $number;
$sth->finish;
## purge on master
print "Purging master logs to $purge\n";
m_execsql("PURGE MASTER LOGS TO '$purge'");
$sth->finish;
$m_dbh->disconnect;
$s_dbh->disconnect;
exit;
## SUBROUTINES
sub m_execsql {
my $cmd = $_[0];
$sth = $m_dbh->prepare($cmd);
$sth->execute;
return $sth;
}
sub s_execsql {
my $cmd = $_[0];
$sth = $s_dbh->prepare($cmd);
$sth->execute;
return $sth;
}
Great scripts from the previous comments. However, I didn't like running them with full permissions to my db's so... after some (fruitless) searching of the online manuals, and trial and error I found that the only permission required to rotate the logs is: Manage Processes ("PROCESS") privilege.
Update:
I noticed that the logs weren't being rotated. The script was just deleting the oldest log files. To add log file rotation to the script I added these lines near the beginning of the php script:
$query = 'FLUSH LOGS';
if(!$rs = mysql_query($query,$dbh)){
die(mysql_error());
}
This also required further permissions, specifically "RELOAD".
based on Rick Hull ideas I created script to make and rotate binary and full mysql backups
my config binary log is stored in seperate folder. in that
folder I have "backup" folder. I store full and incremental backups there. each backup set(full dump + binary log) is stored in one separate folder(under backup folder).
in my case set_1, set_2, set_3, set_4
every time script is run - it rotates this folders smth like
logrotate.
<?php
/*
Copyright (c) 2006, Abdullaev Nariman based on Rick Hull ideas
THIS SOFTWARE IS PROVIDED "AS IS". SO NO WARRENTY FROM ME AT ALL, USE ON YOU OWN RISK
*/
//-------- config start ---------
define("BASE_DIR", "D:\\MySql5backup");
define("BACKUP_DIR", "backup");
define("FULL_BACKUP_NUMBER", 4);
define("BACKUPSET_FOLDER_NAME", "set_");
define("TMP_DIR", "tmp");
//------- config end ------------
$dbs= mysql_connect('localhost:3307', 'root', '123456');
if (!$dbs) {
die("cannot connect to mysql server!");
}
$cmd_dest_file = ' --result-file='.BASE_DIR.'\\'.BACKUP_DIR.'\\'.TMP_DIR.'\\full.sql';
$cmd_login = ' -u root -p123456';
$cmd_options = ' --flush-logs --lock-all-tables --all-databases';
$cmd_all = $cmd_login . $cmd_dest_file . $cmd_options.' 2>&1';
//print $tmp;
//print_r ($output);
//print $return_var;
$backupset_array = array();
//chdir(BASE_DIR);
if ($handle = opendir(BASE_DIR."//".BACKUP_DIR)) {
//echo "Directory handle: $handle\n";
//echo "Files:\n";
/* This is the correct way to loop over the directory. */
while (false !== ($file = readdir($handle))) {
//echo "$file\n";
if (is_dir(BASE_DIR."//".BACKUP_DIR."//".$file)){
$tmp_folder_name = substr($file, 0, strlen(BACKUPSET_FOLDER_NAME));
//echo $tmp_folder_name."\n";
if ($tmp_folder_name != BACKUPSET_FOLDER_NAME){ continue; }
$tmp_folder_index = substr($file, strlen(BACKUPSET_FOLDER_NAME), strlen($file));
//echo $tmp_folder_index."\n";
if (settype($tmp_folder_index, "integer") == true ){
//if ($tmp_folder_index > 0 and $tmp_folder_index < FULL_BACKUP_NUMBER){
//
//}
$backupset_array[$tmp_folder_index] = $tmp_folder_name;
} else {
continue;
}
} else {
continue;
}
}
closedir($handle);
} else {
die("cannot open BASE_DIR: ".BASE_DIR);
}
chdir(BASE_DIR."\\".BACKUP_DIR);
//print_r($backupset_array);
for ($i=1; $i <= FULL_BACKUP_NUMBER; $i++){
if (!isset($backupset_array[$i])){
if (mkdir(BACKUPSET_FOLDER_NAME.$i)){
print "create missing backup folder: ".BACKUPSET_FOLDER_NAME.$i."\n";
} else {
die ("cannot create missing backup folder: ".BACKUPSET_FOLDER_NAME.$i);
}
}
}
clearstatcache();
for ($i = FULL_BACKUP_NUMBER + 1; $i <= count($backupset_array); $i++){
if (isset($backupset_array[$i])){
if (is_dir(BACKUPSET_FOLDER_NAME.$i)){
if ($handle = opendir(BACKUPSET_FOLDER_NAME.$i)){
while (false !== ($file = readdir($handle))) {
if (is_file(BACKUPSET_FOLDER_NAME.$i."//".$file)){
if (unlink(BACKUPSET_FOLDER_NAME.$i."//".$file)){
print "delete file: ".$file." Ok\n";
} else {
die("cannot delete file: ".$file." Ok\n");
}
}
}
}
closedir($handle);
if (rmdir(BACKUPSET_FOLDER_NAME.$i)){
print "delete extra backup set: ".BACKUPSET_FOLDER_NAME.$i."\n";
} else {
die("cannot delete extra backup set: ".BACKUPSET_FOLDER_NAME.$i);
}
}
}
}
clearstatcache();
if (is_dir(TMP_DIR)){
if ($handle = opendir(TMP_DIR)){
while (false !== ($file = readdir($handle))) {
if (is_file(TMP_DIR."\\".$file)){
if (unlink(TMP_DIR."\\".$file)){
print "delete file: ".TMP_DIR."\\".$file." Ok\n";
} else {
die("cannot delete file: ".TMP_DIR."\\".$file." Ok\n");
}
}
}
}
} else {
if (mkdir(TMP_DIR)){
print "make temp dir: ".TMP_DIR." created Ok\n";
}
}
clearstatcache();
$tmp = exec('"D:\Program Files\MySQL\MySQL Server 5.0\bin\mysqldump"' . $cmd_all, $output, $return_var);
if ($return_var != 0){
print_r ($output);
die("error in mysqldump program");
}
$query = 'SHOW MASTER LOGS';
$rs = mysql_query($query, $dbs);
if(!$rs){
die(mysql_error());
}
$oldest_log_id = mysql_num_rows($rs);
$i = 1;
while($row = mysql_fetch_array($rs) and $i < $oldest_log_id){
if (copy(BASE_DIR."\\".$row[0], BASE_DIR."\\".BACKUP_DIR."\\".TMP_DIR."\\".$row[0])) {
print "copy bin_log file ".$row[0]." to ".BACKUP_DIR."\\".TMP_DIR."\\".$row[0]."\n";
} else {
print "cannot copy bin_log file ".$row[0]." to ".BACKUP_DIR."\\".TMP_DIR."\\".$row[0]."\n";
}
$i++;
}
mysql_fetch_array($rs);
$query = "PURGE MASTER LOGS TO '".$row[0]."'";
if(!mysql_query($query, $dbs)) die(mysql_error());
print "The MySQL binary logs have been rotated. The oldest log is ".$row[0]."\n";
if (is_dir(BACKUPSET_FOLDER_NAME.FULL_BACKUP_NUMBER)){
if ($handle = opendir(BACKUPSET_FOLDER_NAME.FULL_BACKUP_NUMBER)){
while (false !== ($file = readdir($handle))) {
if (is_file(BACKUPSET_FOLDER_NAME.FULL_BACKUP_NUMBER."\\".$file)){
if (unlink(BACKUPSET_FOLDER_NAME.FULL_BACKUP_NUMBER."\\".$file)){
print "delete file: ".BACKUPSET_FOLDER_NAME.FULL_BACKUP_NUMBER."\\".$file." Ok\n";
} else {
die("cannot delete file: ".BACKUPSET_FOLDER_NAME.FULL_BACKUP_NUMBER."\\".$file." Ok\n");
}
}
}
}
closedir($handle);
if (rmdir(BACKUPSET_FOLDER_NAME.FULL_BACKUP_NUMBER)){
print "last backup set: ".BACKUPSET_FOLDER_NAME.FULL_BACKUP_NUMBER." deleted Ok\n";
}
}
clearstatcache();
for ($i= FULL_BACKUP_NUMBER - 1; $i >= 1; $i--){
if (is_dir(BACKUPSET_FOLDER_NAME.$i)){
if (rename(BACKUPSET_FOLDER_NAME.$i, BACKUPSET_FOLDER_NAME.($i + 1))){
print "rename folder ".BACKUPSET_FOLDER_NAME.$i." to ".BACKUPSET_FOLDER_NAME.($i + 1)."\n";
}
}
}
if (rename(TMP_DIR, BACKUPSET_FOLDER_NAME.'1')){
print "rename folder ".TMP_DIR." to ".BACKUPSET_FOLDER_NAME."1"."\n";
}
print "Done!";
?>
I found that most of the log rotation scripts do not work in the case of 'highest load' (n+1 clients connected).
For example, lets say we have set max_connections=n and we have n clients with user privileges and the n+1th client with Superuser privilege (this is a feature supported by MySQL to assist the administrator to monitor the processes and preformance) connected to the server. Assume at this point the cron job for log rotation kicks in. My observation has been that the files are rotated (*.log moved to *.log.0 and *.log afresh is empty), but MySQL fails at restart. Hence, MySQL continues to log into *.log.0! 'ps -eaf | grep mysql' and 'lsof common.log.0' confirms this.
This is because during the restart process, the current MySQL process couldn't be killed. Most of the scripts use mysqladmin to stop the current process. 'mysqladmin' requires a connection to the server. In our example above, all connections are used up and hence, 'mysqladmin' will fail to connect. This scenario could be handled by checking for 'mysqladmin' statement execution failure and then killing the MySQL process by executing 'kill' on the pid.
if you set the expire_logs_days = x var in the [mysqld] section of your my.cnf it will automatically rotate your bin logs after x days.
If you delete all the logs in /var/log/mysql but leave the old mysql-bin.index pointing to them, mysqld will not start because it cannot find the last log file in the index.
Rename /var/log/mysql/mysql-bin.index to say mysql-bin.index.bak and retry and a new mysql-bin.index file and log file mysql-bin.000001 will be created.
To turn off log-bin, edit /etc/mysql/my.cnf and comment out log-bin:
#log-bin = /var/log/mysql/mysql-bin.log
This will save 100MBs of space if you have a fast insert going on.
You also have to comment out the following if you comment out log-bin or mysqld will not start:
#expire_logs_days = 10
Add your own comment.