Skip navigation links

User Comments

Posted by Diego Ribeiro on December 12 2003 4:19am[Delete] [Edit]

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;

Posted by [name withheld] on December 24 2003 7:52am[Delete] [Edit]

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.

Posted by Rick Hull on September 28 2004 3:28pm[Delete] [Edit]

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(HOSTUSERPASS))
  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";
?>

Posted by aa on November 8 2004 12:38am[Delete] [Edit]

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(HOSTUSERPASS);
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-

Posted by Christian Ermel on February 28 2005 6:49pm[Delete] [Edit]

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;
}

Posted by Leonard on July 2 2005 6:48am[Delete] [Edit]

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".

Posted by Nariman on April 4 2006 6:59am[Delete] [Edit]

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 ------------

$dbsmysql_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($file0strlen(BACKUPSET_FOLDER_NAME));
            
//echo $tmp_folder_name."\n";
            
if ($tmp_folder_name != BACKUPSET_FOLDER_NAME){ continue; }
            
            
$tmp_folder_index substr($filestrlen(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 (
$iFULL_BACKUP_NUMBER 1$i >= 1$i--){
    if (
is_dir(BACKUPSET_FOLDER_NAME.$i)){
        
        if (
rename(BACKUPSET_FOLDER_NAME.$iBACKUPSET_FOLDER_NAME.($i 1))){
            print 
"rename folder ".BACKUPSET_FOLDER_NAME.$i." to ".BACKUPSET_FOLDER_NAME.($i 1)."\n";
        }
    }
}

if (
rename(TMP_DIRBACKUPSET_FOLDER_NAME.'1')){
    print 
"rename folder ".TMP_DIR." to ".BACKUPSET_FOLDER_NAME."1"."\n";
}



print 
"Done!";
?>

Posted by Girish Nair on April 25 2006 7:56pm[Delete] [Edit]

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.

Posted by Dwight Walker on September 30 2007 7:43am[Delete] [Edit]

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

Posted by Fred McIntyre on November 15 2008 5:44pm[Delete] [Edit]

A Perl script for a Master with one or more Slaves. It allows different user/passwords for each slave. This one finds the oldest log in use by a slave. It purges the logs up to, but not including, X days before the date of that log, according to $savedays. That is, if the oldest log in use is dated 2008-11-14 and you set $savedays = 1, it will purge all logs dated up to and including the last log from 2008-11-12, not purging those from 2008-11-13. It uses the Perl module Date::Calc, available at cpan.org, to find the previous day's date. I have commented the lines to leave out if you do not want install that module to save the previous day's logs. It will not purge logs from the same date as the oldest log in use. If run as a cron job, the print statements should cause an email to be sent to the cron job user.

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 ##

Posted by Alan Voker on February 23 2009 2:07am[Delete] [Edit]

The purge statement line in the above script should be:

$mstr_dbh->do('purge binary logs before "'.$year.'-'.$mon.'-'.$day.' 00:00:00"');

Posted by Virender Bhanot on April 6 2011 1:07pm[Delete] [Edit]

A PHP script to purge the master bin logs based on the position of slave. Can also be used to send alerts if slave is found to have a problem as mentioned below. Uncomment the line mentioned to actually purge logs.

<?
# Use as you like. No Warranties please. Use at your own risk.
# The script purges (deletes) the old log files based on the current file on which slave is acting.
# The script was tested by running the same on slave server. 
# It additionally sends an email/ sms if some of the parameters on slave are not found as required.

# define the number of logs to keep
define('KEEPNUM'5);
define('LINEBRK'"\n"); //make it <br> if running on browser

# connectivity settings for the slave (localhost)
define('HOST''localhost');
define('USER''user');
define('PASS''pass');

# connectivity settings for the master (remote)
define('HOSTM''x.x.x.x');
define('USERM''user');
define('PASSM''pass');

function 
send_email_sms($text

   
//Put here your code to send email and sms
   //return $retval; 


if(!
$dbh mysql_connect(HOSTUSERPASS)) die(mysql_error());
$query 'show slave status;';
if(!
$rs mysql_query($query$dbh)) die(mysql_error());
$row mysql_fetch_array($rs);

if (
strcmp($row['Slave_IO_Running'],"Yes") != 0send_email_sms("Alert. Slave IO not running.");
if (
strcmp($row['Slave_SQL_Running'],"Yes") != 0send_email_sms("Alert. Slave IO not running.");
if (
strcmp($row['Last_Error'],"") != 0send_email_sms("Alert. Error on Slave. ".$row['Last_Error']);

$x $row['Master_Log_File'];
echo 
"Current Master file on which Slave is acting: ".$x.LINEBRK;

if(!
$dbhm mysql_connect(HOSTMUSERMPASSM)) die(mysql_error());
$query1 'show master logs;';
if(!
$rs1 mysql_query($query1$dbhm)) die(mysql_error());
$lognames = array();

while(
$row1 mysql_fetch_array($rs1))
  
$lognames[] = $row1[0];

$marker array_search($x,$lognames);
$delmarker $marker KEEPNUM;

if(
$delmarker 2) {echo "Not enough logs to keep. Exiting..".LINEBRK;exit;}

echo 
"We are going to purge logs upto (not including) $lognames[$delmarker]".LINEBRK;

$del_query "PURGE MASTER LOGS TO '$lognames[$delmarker]'";
# Uncomment the following line to actually purge (i.e. delete) the logs
//if(!$rs2 = mysql_query($del_query, $dbhm)) die(mysql_error());
echo "The Master Server MySQL binary logs have been purged. The oldest log now available on the master is $lognames[$delmarker].".LINEBRK;
?> 

Posted by Matthieu Bienvenüe on October 12 2012 9:09am[Delete] [Edit]

Here is an upgraded version of the Virender Bhanot ones that could handle multiple slaves. This script could run on CLI or web browser.

#!/usr/bin/php
<?
// purge_master_logs.php - MB 08/10/2012
//
// Source inspired by Virender Bhanot comment's : http://dev.mysql.com/doc/refman/5.6/en/log-file-maintenance.html
//
// Use as you like. No Warranties please. Use at your own risk.
// The script purges (deletes) the old log files based on the current file on which Slaves are acting.
// It additionally sends an email/ sms if some of the parameters on slave are not found as required.
//
// Modified in order to support multiple slaves. Get the oldest log file used by all slaves in order to use it as the reference.
// The script could be executed on any host (even another one that don't have mysql installed; this is the case for host with only Mysql installed and no PHP).
// Remove nothing if one Slave (or Master) is down or has a problem.
//
// The script assumes that bin log filenames use the default Mysql pattern (e.g. : mysql-bin.000003).
//
// Settings are read from slaves.config and master.config files, one host per line with the following format: host:user:password; comments lines starting with # are ignored.
//
// To display debuging information use ./purge_master_logs.php debug on CLI or purge_master_logs.php?debug on a web browser, by default nothing is displayed in order to avoid cron jobs send e-mails.
//


define('KEEPNUM'5); // define the number of logs to keep
define('SLAVE_CONFIG_FILE''slaves.config');
define('MASTER_CONFIG_FILE''master.config');
//---- Nothing to be changed under this line

function send_email_sms($text)
{
   
//Put here your code to send email and sms
   //return $retval;
   
echo $text LINEBRK;
   exit(
1);
}

if( empty(
$_SERVER['HTTP_USER_AGENT']) ) // used on CLI
{
   
define('LINEBRK'"\n");
   
parse_str(implode('&'array_slice($argv1)), $_GET); // populate $_GET with CLI parameters
}
else
{
   
define('LINEBRK'"<br>\n"); // make it <br> if running on browser
}

if( isset(
$_GET['debug']) )
{
   
$debug true;
}
else
{
   
$debug false;
}

register_shutdown_function('shutdown'$debug); // display nothing (with echo, etc.) when in production (usefull for cron jobs)
ob_start();


//---- Read configs files
//---- Slaves
$slaves_lines file(SLAVE_CONFIG_FILEFILE_IGNORE_NEW_LINES FILE_SKIP_EMPTY_LINES);

if( 
$slaves_lines === false )
{
   
send_email_sms("Cannot read Slaves config file (" SLAVE_CONFIG_FILE ")");
}
$slaves_host = array();
$slaves_users = array();
$slaves_pass = array();
foreach( 
$slaves_lines as $line )
{
   if( 
substr($line0strlen('#')) !== '#' // lines starting with # are comments, ignore it
   
{
      list(
$host$user$password) = explode(':'$line3);
      
$slaves_host[] = $host;
      
$slaves_users[] = $user;
      
$slaves_pass[] = $password;
   }
}
unset(
$slaves_lines);
if( empty(
$slaves_host) || empty($slaves_users) || empty($slaves_pass) )
{
   
send_email_sms("No hosts defined in Slaves config file (" SLAVE_CONFIG_FILE ")");
}
echo 
"Slaves config : host|user|password" LINEBRK;
foreach( 
$slaves_host as $index => $host )
{
   if( empty(
$slaves_pass[$index]) )
   {
      
$password_txt 'NOT DEFINED';
   }
   else
   {
      
$password_txt 'SET BUT NOT DISPLAYED HERE';
   }
   echo 
$host '|' $slaves_users[$index] . '|' $password_txt LINEBRK;
}

//---- Master
$master_lines file(MASTER_CONFIG_FILEFILE_IGNORE_NEW_LINES FILE_SKIP_EMPTY_LINES);

if( 
$master_lines === false )
{
   
send_email_sms("Cannot read Master config file (" MASTER_CONFIG_FILE ")");
}
foreach( 
$master_lines as $line )
{
   if( 
substr($line0strlen('#')) !== '#' // lines starting with # are comments, ignore it
   
{
      list(
$master_host$master_user$master_password) = explode(':'$line3);
      break; 
// only one line of settings
   
}
}
unset(
$master_lines);
if( empty(
$master_host) || empty($master_user) || empty($master_password) )
{
   
send_email_sms("Problem when reading Master config file (" MASTER_CONFIG_FILE "); missing parameter");
}
echo 
"Master config : host|user|password" LINEBRK;
if( empty(
$master_password) )
{
   
$password_txt 'NOT DEFINED';
}
else
{
   
$password_txt 'SET BUT NOT DISPLAYED HERE';
}
echo 
$master_host '|' $master_user '|' $password_txt LINEBRK LINEBRK;


//---- get log files number on each slave
$file_numbers = array();
$min_file_number 999999;
foreach( 
$slaves_host as $index => $host )
{
   if( !
$dbh mysql_connect($host$slaves_users[$index], $slaves_pass[$index]) )
   {
      die(
mysql_error() . LINEBRK "line: " __LINE__ LINEBRK "Host: " $host LINEBRK);
   }
   
$query 'show slave status;';
   if( !
$rs mysql_query($query$dbh) )
   {
      die(
mysql_error() . LINEBRK "line: " __LINE__ LINEBRK "Host: " $host LINEBRK);
   }
   
$row mysql_fetch_array($rs);

   if( 
strcmp($row['Slave_IO_Running'], "Yes") != )
   {
      
send_email_sms("Alert. Slave IO not running on host: " $host);
   }
   if( 
strcmp($row['Slave_SQL_Running'], "Yes") != )
   {
      
send_email_sms("Alert. Slave SQL not running on host: " $host);
   }
   if( 
strcmp($row['Last_Error'], "") != )
   {
      
send_email_sms("Alert. Error on Slave on host: " $host LINEBRK $row['Last_Error']);
   }
   
mysql_close($dbh);

   
$log_file $row['Master_Log_File'];
   echo 
"Current Master file used on Slave host " $host ": " $log_file LINEBRK;

   
$ext pathinfo($log_filePATHINFO_EXTENSION);
   
$min_file_number min($min_file_numberintval($ext)); // the smallest number is the oldest log file
}

$file_name "mysql-bin." sprintf("%06d"$min_file_number);
echo 
LINEBRK "Current Master oldest file on which Slaves are acting: " $file_name LINEBRK;

if( !
$dbhm mysql_connect($master_host$master_user$master_password) )
{
   
send_email_sms(mysql_error() . LINEBRK "line: " __LINE__ LINEBRK);
}
$query1 'show master logs;';
if( !
$rs1 mysql_query($query1$dbhm) )
{
   die(
mysql_error() . LINEBRK "line: " __LINE__ LINEBRK);
}
$lognames = array();

while( 
$row1 mysql_fetch_array($rs1) )
{
   
$lognames[] = $row1[0];
}

$marker array_search($file_name$lognames);
$delmarker $marker KEEPNUM;

if( 
$delmarker )
{
   echo 
"Not enough logs to keep. Nothing to purge. Exiting." LINEBRK;
   exit(
0);
}

echo 
"We are going to purge logs up to (not including) $lognames[$delmarker]LINEBRK;

$del_query "PURGE MASTER LOGS TO '$lognames[$delmarker]'";
# Uncomment the following lines to actually purge (i.e. delete) the logs
//if( !$rs2 = mysql_query($del_query, $dbhm) )
//{
//   die(mysql_error() . LINEBRK . "line: " . __LINE__ . LINEBRK);
//}
echo "The Master Server MySQL binary logs have been purged. The oldest log now available on the master is $lognames[$delmarker]." LINEBRK;
exit(
0);
/*
 * shutdown(): used to display echo's only when debuging is activated
 */
function shutdown($debug)
{
   if( 
$debug )
   {
      
ob_end_flush();
   }
   else
   {
      
ob_end_clean();
   }
}


?>