Hi folks,
I have a question regarding backups taken from a running slave. I have a
slave
replicating to a master server, and do a hot backup using the following
script:
#!/usr/bin/perl
use strict;
use DBI;
use File::Copy;
use POSIX ":sys_wait_h";
my $dbh =
DBI->connect("DBI:mysql:dbname=mysql;mysql_socket=/tmp/mysql.sock","user
","password") || die DBI->errstr();
## Create the backup folder
my ($d,$m,$y) = (localtime)[3..5];
my $date = sprintf("%d-%02d-%02d",$y+1900,$m+1,$d);
my $ibbackup_pid;
print "Creating backup folder : /storage/backups/backup-$date\n";
mkdir("/storage/backups/backup-$date") || die "couldn't create backup
dir: $!";
mkdir("/storage/backups/backup-$date/sql") || die "couldn't create
backup dir: $!";
## Parse the /etc/my.cnf
print "Parsing my.cnf\n";
open(F,"/etc/my.cnf") || die;
my $found = 0;
my %cfg;
while(<F>) {
chomp;
## Junk until we have start of [mysqld] section
if (!$found) {
next if (!/\[mysqld\]/i);
$found = 1;
next;
}
## We aren't interested in anything not relating to the [mysqld]
section
## stop reading when it's finished.
last if ($found && /^\s*\[/);
## Skip comments and empty lines
next if (/^(?:(?:#\|;).*|\s*)$/);
## Get rid of trailing space or ;
s/(?:|\s*)$//;
## Handle the set-variable=var=something
if (/^set-variable=(.*)$/) {
my ($var,$val) = split(/\s*=\s*/,$1);
$cfg{'set-variable'}{$var} = $val;
next;
}
## Split name/value pairs
my ($var,$val) = split(/\s*=\s*/);
$cfg{$var} = $val;
}
## At this point %cfg contains the relevant [mysqld] configuration
information.
## Create the backup config file
print "Creating /storage/backups/backup-$date/my-backup.cnf\n";
open(FILE, ">/storage/backups/backup-$date/my-backup.cnf") || die
"Failed to open file /storage/backups/backup-$date/my-backup.cnf: $!";
print FILE <<"(EOF)";
# This MySQL options file was generated by Mark's backup script.
[mysqld]
datadir=/storage/backups/backup-$date/sql
innodb_data_home_dir=/storage/backups/backup-$date/sql
innodb_data_file_path=$cfg{'innodb_data_file_path'}
innodb_log_group_home_dir=/storage/backups/backup-$date/sql
innodb_log_files_in_group=$cfg{'set-variable'}{'innodb_log_files_in_grou
p'}
innodb_log_file_size=$cfg{'innodb_log_file_size'}
(EOF)
close(FILE);
## Start the backup...
my $pid = undef;
if (defined($pid = fork)) {
if ($pid) {
# parent process
$ibbackup_pid = $pid;
} else {
# child process
print "Child process started\n";
print "Command: ibbackup --suspend-at-end --compress
/etc/my.cnf /storage/backups/backup-$date/my-backup.cnf\n";
exec("ibbackup --suspend-at-end --compress /etc/my.cnf
/storage/backups/backup-$date/my-backup.cnf") || die "Failed to exec
ibbackup: $!";
}
} else {
die "failed to fork ibbackup child process: $!";
}
print "Waiting for child to create
/storage/backups/backup-$date/sql/ibbackup_suspended\n";
while(1) {
sleep 5;
$dbh->do("SELECT UNIX_TIMESTAMP()");
last if -e
"/storage/backups/backup-$date/sql/ibbackup_suspended";
}
print "Starting backup of MyISAM tables\n";
## ibbackup is waiting for us to get rid of the suspended file.
## start backing up the files.
print "Locking tables\n";
$dbh->do("DROP TABLE IF EXISTS ibbackup_binlog_marker");
$dbh->do("CREATE TABLE ibbackup_binlog_marker(a INT) TYPE=INNODB");
$dbh->{AutoCommit} = 0;
$dbh->do("INSERT INTO ibbackup_binlog_marker VALUES (1)");
$dbh->do("FLUSH TABLES WITH READ LOCK");
$dbh->commit();
opendir(DIR,$cfg{'datadir'}) || die "can't open directory: $!";
while (my $f = readdir(DIR)) {
next if (!-d "$cfg{'datadir'}/$f" || $f =~ /^\.{1,2}$/);
mkdir("/storage/backups/backup-$date/sql/$f") || die "couldn't
create folder: /storage/backups/backup-$date/$f : $!";
my @list = glob("$cfg{'datadir'}/$f/*.{frm,MYD}");
my @listidx = glob("$cfg{'datadir'}/$f/*.MYI");
for (@list) {
$dbh->do("SELECT UNIX_TIMESTAMP()");
print "Copying $_\n";
copy("$_","/storage/backups/backup-$date$_") || die
"couldn't copy file $_: $!";
}
for (@listidx) {
print "Copying INDEX $_\n";
$dbh->do("SELECT UNIX_TIMESTAMP()");
open(FIDX,"<$_") || die "couldn't open index... ack!";
my $buff;
my $length = read(FIDX,$buff,2048);
#die "Can't read index header from $_\n" if ($length <
1024);
close(FIDX);
open(FIDX,">/storage/backups/backup-$date$_") || die
"couldn't open file for copy: $_: $!";
if (syswrite(FIDX,$buff) != length($buff)) {
die "Error when writing data to
/storage/backups/backup-$date$_: $!\n";
}
close FIDX || die "Error on close of
/storage/backups/backup-$date$_: $!\n";
}
}
closedir(DIR);
print "Removing /storage/backups/backup-$date/sql/ibbackup_suspended\n";
unlink("/storage/backups/backup-$date/sql/ibbackup_suspended");
print "Waiting for ibbackup to finish\n";
waitpid($ibbackup_pid,0);
print "Unlocking tables\n";
$dbh->do("UNLOCK TABLES");
$dbh->do("DROP TABLE IF EXISTS ibbackup_binlog_marker");
$dbh->disconnect;
print "Compressing the backup\n";
## Tar the backup
`tar cfz /storage/backups/sql-backup-$date.tar.gz
/storage/backups/backup-$date`;
print "Removing the backup folder\n";
## Remove the folder
`rm -rf /storage/backups/backup-$date`;
print "Removing backup from 2 days ago\n";
## Remove the 2 day old backup
($d,$m,$y) = (localtime(time()-60*60*24*2))[3..5];
$date = sprintf("%d-%02d-%02d",$y+1900,$m+1,$d);
unlink("/storage/backups/sql-backup-$date.tar.gz");
## END
This script uses the ibbackup tool from innodb (non-free) to take a hot
backup
of the innodb tables, then near the end of the innodb backup,ibbackup
waits for a backup
to be taken of the MyISAM tables and table definitions (lock tables with
read lock, copy the
*.FRM, *.MYI, *.MYD files, unlock).
Once the MyISAM tables and table definitions are backed up, ibbackup
resumes and
theoretically I _should_ have a consistent backup of the whole database
server once ibbackup
is finished. (although I truncate the MyISAM index files, but they can
be reconstructed
with myisamchk -r)
My question is, suppose I would like to use one of these backups to
create a new
slave server, what additional information would I need to include in the
backup
to be able to start up a new slave properly? (relay logs/info files,
etc...)
Ideally, I would like these snapshots to be taken off the running server
while it
is in use, is there any way to do this?
Regards,
Mark Steele
Implementation Director
CDT Inc.
[EMAIL PROTECTED]
PS: the perl code is sloppy and needs more error checking,etc... use at
your own risk.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
