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 <[EMAIL PROTECTED]>
## 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:[EMAIL PROTECTED]
Sent: July 7, 2004 1:43 PM
To: [EMAIL PROTECTED]
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
