You can use this script to purge master logs older than the day of the current log. It checks the slaves are not using the older logs first.
Some prechecks:
1) create a repl_client user on each slave - i.e
GRANT REPLICATION CLIENT ON *.* TO 'repl_client'@'mk-dbxx-1' IDENTIFIED BY 'r3pl';
2) Put the master and slave user, pwd and hosts in the section below.
3) Set savedays to number of days of logs to keep if more than today+1.
4) Make sure perl DBD and CALC modules are installed.
perl –v ,
instmodsh (and enter l to list modules)
5) The purge statement will not work if the "show master logs;" doesn't match up with the actual log files on disk. This can happen if bin logs have been manually deleted, but the index file still thinks they are there. So to check it do
a) show master logs;
b) ls -l $DATADIR
c) if they are not in sync then remove all older ones
eg if 14 is the first one actually on the disk then run "purge master logs to "mysql-bin.000014";"
Crontab entry:
#HLakhan 25 Mar 09 - Purge master logs over a day old after checking slave
00 17 * * * /home/backup/purge.pl > /tmp/purge.log 2>&1
Script
- vi purge.pl
- cut and paste what is below & make necessary changes – pwds, server names
- chmod 755 purge.pl
- to run the script must do ./purge.pl - this tells it to use perl.
#!/usr/bin/perl
#
###########################################################################################################
# Script Name : purge.pl
# Usage : ./purge.pl
# Description :
#
# 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. Also DBD mysql.
# 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.
#
# You need the SUPER privilege for this operation.
#
# 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 slave host using it.
# 2. Check that the oldest log on the server is older than the date you are purging to.
#
# Also, the purge statement will not work if the "show master logs;" doesn't match up with the actual log files on disk.
# This can happen if bin logs have been manually deleted, but the index file still thinks they are there.
# So to check it do
# a) show master logs;
# b) ls -l $DATADIR
# c) if they are not in sync then remove all older ones
# eg if 14 is the first one actually on the disk then run "purge master logs to "mysql-bin.000014";"
#
#############################################################################################################
# Script Name : purge.pl
# Usage : ./purge.pl
# Prerequisites : 1) create a repl_client user on each slave - i.e
# GRANT REPLICATION CLIENT ON *.* TO 'repl_client'@'mk-dbmaster-1.portal.uk.intranet' IDENTIFIED BY 'r3pl_cl13nt';
# 2) Put the master and slave user, pwd and hosts in the section below.
# 3) Set savedays to number of days of logs to keep if more than today+1.
# 4) Make sure perl DBD and CALC modules are installed. I.e perl –v , instmodsh (and enter l to list modules)
#
# Author Version Date Comment
# -----------------------------------------------------------------------------------------------------------
# Fred McIntyre 1 15 Nov 08 Initial version from the web
# Harjit Lakhan 2 23 Mar 10 Made some changes to get it working
#
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 = 'root';
my $mstr_dbpassword = '**';
my $mstr_dbdsn = ('dbi:mysql:host=localhost');
#to specify the socket if its non-standard use following syntax
#my $mstr_dbdsn = ('dbi:mysql:host=localhost;mysql_socket=/tmp/mysql-master.sock');
# 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
#for more than one slave just comma separate eg ('repl_client','repl_client')
my @slv_dbusers = ('repl_client');
#for more than one slave just comma separate eg ('pwd','pwd2')
my @slv_dbpasswords = ('r3pl_cl13nt');
#for more than one slave just comma separate eg ('dbi..1','dbi...2')
my @slv_dbdsns = ('dbi:mysql:host=mk-dbslave-1.portal.uk.intranet');
#to specify the socket if its non-standard use following syntax
#my @slv_dbdsns = ('dbi:mysql:host=;mysql_socket=/tmp/mysql-slave.sock');
### 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_dbdsns; $i++) {
my $slv_dbdsn = $slv_dbdsns[$i];
my $slv_dbuser = $slv_dbusers[$i];
my $slv_dbpassword = $slv_dbpasswords[$i];
my $slv_dbh = DBI->connect($slv_dbdsn,$slv_dbuser,$slv_dbpassword);
print 'MySQL slave connect '.$slv_dbdsns[$i].$slv_dbusers[$i]."\n";
#print 'MySQL slave connect '.$slv_dbdsns[$i].$slv_dbusers[$i].$slv_dbpasswords[$i]."\n";
unless ($slv_dbh) {
$no_connect .= 'MySQL binary log purge: error connecting to slave database '.
$slv_dbdsns[$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_dbdsns[$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($mstr_dbdsn,$mstr_dbuser,$mstr_dbpassword);
#my $mstr_dbh = DBI->connect('dbi:mysql::'.$mstr_dbhost,$mstr_dbuser,$mstr_dbpassword);
#print 'MySQL master connect '.$mstr_dbdsn.$mstr_dbuser.$mstr_dbpassword."\n";
unless ($mstr_dbh) {
print 'MySQL binary log purge: error connecting to master database - '.$DBI::errstr."\n";
exit;
}
###comment out while testing
$mstr_dbh->do('purge master logs before "'.$year.'-'.$mon.'-'.$day.' 00:00:00"');
$mstr_dbh->disconnect;
print 'MySQL binary log purge: purged before '.$year.'-'.$mon.'-'.$day."\n";
## end ##
No comments:
Post a Comment