List:General Discussion« Previous MessageNext Message »
From:Mark Steele Date:July 9 2004 3:56pm
Subject:RE: Script to purge
View as plain text  
Hi Luis,

I've made a small script to do this, it's in the 'backing up mysql
databases' on http://www.control-alt-del.org/code

Basically, you probably want to save the binary logs by archiving
them (in case of a database crash). On a binary log I get about
60-80% compression ratios, so it's worth just archiving them.

Here's the script (see the web site for explanations)

#!/usr/bin/perl
## Binary log backup utility
## Author: Mark Steele <mark@stripped>
## Bug: This script will fail when you hit binlog #999 and roll over to
1
##      I'm too lazy to fix this just now, you've been warned.
use DBI;
use strict;

my $DBUSER = 'user';
my $DBPASSWORD = 'password';

## where the binlogs are eg: /usr/local/mysql/data
my $PATH_TO_DATA = '/path/to/mysql/data/files'; 

my $HOSTNAME = `hostname -s`;

## Which server to copy binary logs to
my $REMOTE_SERVER = 'backupserver.yourdomain.com'; 

## Path on remote machine where you want the backups to go
my $REMOTE_PATH = '/tmp'; 

my $dbh = DBI->connect("DBI:mysql:database=mysql;host=127.0.0.1",
                         $DBUSER,$DBPASSWORD) || die;

## Figure out the current binary log file
my $sth = $dbh->prepare("SHOW MASTER STATUS");
$sth->execute();

my $lastfile = $sth->fetchrow_hashref()->{File};

## Get log list from server
$sth = $dbh->prepare("SHOW MASTER LOGS");
$sth->execute();

my @files;
while (my $ref = $sth->fetchrow_hashref()) {
        last if ($ref->{Log_name} eq $lastfile);
        push(@files,$ref->{Log_name});
}

## Figure out first and last binlog numbers
$lastfile =~ /\.(\d+)$/;
my $lastno = $1 - 1;
$files[0] =~ /\.(\d+)$/;
my $firstno = $1;

## Make a list of the files to backup
my $a = join(" ",@files);
chdir($PATH_TO_DATA);

## Backup the binary logs, and remove them once they are backed up
`tar cvfj $PATH_TO_DATA/$HOSTNAME-binlogs-$firstno-$lastno.tar.bz2 $a`;
$dbh->do("PURGE MASTER LOGS TO '$lastfile'");
$dbh->disconnect;

## Copy to the remote machine, comment this out if you don't want to 
## backup your binary logs to a remote machine
`/usr/bin/scp -i /root/.ssh/backup-key
$PATH_TO_DATA/$HOSTNAME-binlogs-$firstno-$lastno.tar.bz2
$REMOTE_SERVER:$REMOTE_PATH`;

## Remove the backup (comment this out if you aren't 
## backing up to a remote server)
unlink("$PATH_TO_DATA/$HOSTNAME-binlogs-$firstno-$lastno.tar.gz"); 
			  

Cheers,

Mark Steele
Implementation Director
CDT Inc.

-----Original Message-----
From: Luis Mediero [mailto:woodito@stripped] 
Sent: July 7, 2004 1:43 PM
To: mysql@stripped
Subject: Script to purge

Hi,

	I would like write a script to purge every nigth the master log
with a cron
process. I need do it every nigth after load a lot of data into the
master.
I know if i do 'show master status' i can see the file_name of the last
log
file and then do -purge master logs to 'file_name'-. Is possible put the
file name into a variable and then do - purge master logs to '$variable'
-,
into a script?. Someone have a example?.

	I can't use 'PURGE MASTER LOGS BEFORE ....' because I use the
4.0.20
version. :-(


TIA
	
Luis



Thread
Script to purgeLuis Mediero7 Jul
  • Re: Script to purgegerald_clark7 Jul
  • Re: Script to purgeJeremy Zawodny7 Jul
  • RE: Script to purgeMark Steele9 Jul