MySQL 5.5 Release Notes
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.
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.5, “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 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.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.
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-erroroption 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-logsOn 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:
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.
Note
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
#!/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;
#!/path/to/binary/php -n
--------------------------------------
#!/path/to/binary/php -n
--------------------------------------
save as mysqlbinlog_rotate in /etc/cron.daily or whatever
-AA-
#!/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;
}
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".
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.
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.
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
Notes: A comment above says that to rotate logs you need only the "PROCESS" privilege - I assume meaning Process_priv. When I try with that, in MySQL version: 5.0.67, I get the error "Access denied; you need the SUPER privilege for this operation." However, since the script is running on the same server as the database, I do not have a problem using a user with Super_priv. In fact, this also deals with another comment above about when there are max connections. This script will use the n+1 connection.
The script does two sanity checks, exiting if either fails:
1. Check that all logs in use by slave(s) exist on the master. If not, it prints the log file name(s), and the slave host using it.
2. Check that the oldest log on the server is older than the date you are purging to.
#!/usr/bin/perl
# This script is in the public domain. No Warranty. Use at your own risk.
use strict;
use DBI;
# For saving previous day's logs
use Date::Calc qw(Add_Delta_Days);
# Master connection
# master user requires Super_priv
my $mstr_dbuser = 'mstruser';
my $mstr_dbpassword = 'mstrpassword';
my $mstr_dbhost = 'mstrhost';
# Change if appropriate.
my $log_dir = '/var/lib/mysql';
# Name for binary logs, from my.cnf: log-bin=
my $log_bin = 'mysql-bin';
# For saving previous day's logs
# Days prior to currently used log file to not purge
my $savedays = 1;
# Slave connection(s)
# slave user requires Repl_client_priv
my @slv_dbusers = ('slvuser1','slvuser2','slvuser3');
my @slv_dbpasswords = ('slvpassword1','slvpassword2','slvpassword3');
my @slv_dbhosts = ('slvhost1','slvhost2','slvhost3');
### No changes needed below here. ##
$log_dir .= '/' unless $log_dir =~ m|/$|;
# Find the date of the oldest log in use by a slave. Use file mtime.
my $time = time;
# Add an hour to make sure $time is later than newest possible log mtime.
$time += 3600;
my ($no_connect,$not_exist);
for (my $i = 0; $i < @slv_dbhosts; $i++) {
my $slv_dbh = DBI->connect('dbi:mysql::'.$slv_dbhosts[$i],$slv_dbusers[$i],$slv_dbpasswords[$i]);
unless ($slv_dbh) {
$no_connect .= 'MySQL binary log purge: error connecting to slave database '.
$slv_dbhosts[$i].' - '.$DBI::errstr."\n";
next;
}
# Use field name to get current log name.
my $sth = $slv_dbh->prepare('show slave status');
$sth->execute;
my $rows = $sth->fetchall_arrayref({});
$sth->finish;
$slv_dbh->disconnect;
if (-f $log_dir.$rows->[0]->{Master_Log_File}) {
# Get mtime of this log file.
my $this_time = (stat($log_dir.$rows->[0]->{Master_Log_File}))[9];
$time = $this_time if $this_time < $time;
} else {
$not_exist .= 'MySQL binary log purge: major error - oldest log in use, by slave host '.
$slv_dbhosts[$i].', does not exist on master: '.
$log_dir.$rows->[0]->{Master_Log_File}."\n";
}
}
if ($no_connect) {
print $no_connect."\n";
exit;
}
if ($not_exist) {
print $not_exist."\n";
exit;
}
my ($day,$mon,$year) = (localtime($time))[3..5];
$year += 1900;
$mon++;
# For saving previous day's logs
$savedays *= -1 unless $savedays < 0;
($year,$mon,$day) = Add_Delta_Days($year,$mon,$day,$savedays);
$mon = '0'.$mon if $mon < 10;
$day = '0'.$day if $day < 10;
# Check if oldest existing log is on or before purge date
# Purge works okay if not, but best to warn in case it indicates problems
my $firstlog = `head -n 1 $log_dir$log_bin.index`;
chomp $firstlog;
my $logtime = (stat($log_dir.$firstlog))[9];
my ($Lsec,$Lmin,$Lhour,$Lday,$Lmon,$Lyear) = (localtime($logtime))[0..5];
$Lyear += 1900;
$Lmon++;
$Lmon = '0'.$Lmon if $Lmon < 10;
$Lday = '0'.$Lday if $Lday < 10;
$Lhour = '0'.$Lhour if $Lhour < 10;
$Lmin = '0'.$Lmin if $Lmin < 10;
$Lsec = '0'.$Lsec if $Lsec < 10;
if ($Lyear.$Lmon.$Lday.$Lhour.$Lmin.$Lsec >= $year.$mon.$day.'000000') {
print 'MySQL binary log purge: oldest log on master is later than, or equal to, '.
$year.'-'.$mon.'-'.$day.' 00:00:00'."\n";
exit;
}
my $mstr_dbh = DBI->connect('dbi:mysql::'.$mstr_dbhost,$mstr_dbuser,$mstr_dbpassword);
unless ($mstr_dbh) {
print 'MySQL binary log purge: error connecting to master database - '.$DBI::errstr."\n";
exit;
}
$mstr_dbh->do('purge binary logs before "'.$year.'-'.$mon.'-'.$day.'" 00:00:00');
$mstr_dbh->disconnect;
print 'MySQL binary log purge: purged before '.$year.'-'.$mon.'-'.$day."\n";
## end ##
$mstr_dbh->do('purge binary logs before "'.$year.'-'.$mon.'-'.$day.' 00:00:00"');
#!/usr/bin/php