Hi,

I'm planing to do hot backup MySQL(innodb db) onto tape drive, and propose
the following solution/script:

# delete old mysql dumps
rm -r -r /backup/mysql
mkdir /backup/mysql

# Dump all mysql databases
mysqldump --all-databases -single-transaction --flush-logs >
all_databases.sql
mysqldump --database=mysql --lock-all-tables --flush-logs > system.sql

# Do backup
tar cvf /dev/rmt/0 /backup/mysql

My question is: is there any other better solutions(including commercial
solutions) to do hot MySQL backup to tape that can provide better
performance, or can backup directly to the tape(no need to dump to disk
first), etc. comparing to use mysqldump.

Thanks in advance for your information, or comment on the above solution.


Regards,
Jenny

Hi, Keith,

In my case, it is just single MySQL server(no replication).
Thanks for your info, I'll check with mk-parallel-dump tools, since
the speed of backup is important considering the database is locked for the
duration of the backup.


Regards,
Jenny


On 12/14/07, B. Keith Murphy <bmurphy@paragon-cs.com> wrote:
>
> Jenny Chen wrote:
> > Hi,
> >
> > I'm planing to do hot backup MySQL(innodb db) onto tape drive, and
> propose
> > the following solution/script:
> >
> > # delete old mysql dumps
> > rm -r -r /backup/mysql
> > mkdir /backup/mysql
> >
> > # Dump all mysql databases
> > mysqldump --all-databases -single-transaction --flush-logs >
> > all_databases.sql
> > mysqldump --database=mysql --lock-all-tables --flush-logs >
> system.sql
> >
> > # Do backup
> > tar cvf /dev/rmt/0 /backup/mysql
> >
> > My question is: is there any other better solutions(including commercial
> > solutions) to do hot MySQL backup to tape that can provide better
> > performance, or can backup directly to the tape(no need to dump to disk
> > first), etc. comparing to use mysqldump.
> >
> > Thanks in advance for your information, or comment on the above
> solution.
> >
> >
> > Regards,
> > Jenny
> >
> >
> You didn't specify if this was a master or slave. I certainly wouldn't
> dump off a master server. It will lock the database for the duration of
> the backup. One of the ways we do backups is to do an "lvmsnapshot" of
> the data partition on the slave and then just mount and rsync the
> snaphsot of the data off to the backup server. Inefficient for space,
> but would be vastly faster restoring a backup like this then running a
> restore from a mysldump. You will still need to tar this take it
> acceptable for tape.
>
> Another option would be to replace mysqldump with Baron Schwartz's
> mk-parallel-dump and mk-parallel-restore tools. They are faster than a
> traditional dump/restore and are much more compact. These tools (and a
> number of others) are located at maatkit.sourceforge.net.net.
>
> Hope that s.
>
> Keith
>
> --
> B. Keith Murphy
>
> Paragon Consulting Services
> http://www.paragon-cs.com
> 850-637-3877
>
>

Jenny Chen wrote:
> Hi, Keith,
>
> In my case, it is just single MySQL server(no replication).
> Thanks for your info, I'll check with mk-parallel-dump tools, since
> the speed of backup is important considering the database is locked for the
> duration of the backup.
>

Well, if you're saying that you're only using innodb then you don't need
to lock the server. I'm using the attached script for backups. We
configure our slaves to use the given $slave_socket (to prevent normal
programs from writing to the db by accident), that's the reason for the
optional --access-slave flag.

Christian.

#!/usr/bin/perl -w

# Fre Sep 30 12:46:38 MEST 2005
(my $email='christian%jaeger,mine,nu')=~ tr/%,/@./;

use strict;

my $slave_socket= "--socket=/var/run/mysqld/mysqld_safe.sock";

$0=~ /(.*?)([^\/]+)\z/s or die "?";
my ($mydir, $myname)=($1,$2);
sub usage {
print STDERR map{"$_\n"} @_ if @_;
print "$myname [${myname}_options] -- additional_mysqldump_options

call mysqldump with options to safely backup innodb databases.
warning: does not handle myisam tables safely!

options: only one:
--access-slave access socket '$slave_socket'

(Christian T+J <$email>)
";
exit @_ ? 1 : 0;
}

my @args;
my $DEBUG=0;
my ($opt_access_slave);

for (my $i=0; $i<=$#ARGV; $i++) {
local $_=$ARGV[$i];
if (/^--?h(elp)?$/) {
usage
} elsif ($_ eq '--') {
push @args, @ARGV[$i+1..$#ARGV];
last;
} elsif (/^--?d(ebug)?$/) {
$DEBUG=1;
} elsif (/^--access-slave$/) {
$opt_access_slave=1;
# } elsif (/^--?X(?:XXX(?:=(.*))?)?$/) {
# if (defined $1) {
# $XXX=$1
# } else {
# $XXX=$ARGV[++$i] or usage "missing argument for '$_' option";
# }
} elsif (/^-./) {
usage("Unknown option '$_'\n");
} else {
push @args, $_
}
}
usage unless @args;

if ($opt_access_slave) {
unshift @args, $slave_socket;
}

sub xexec {
if ($DEBUG) {
print join (" ¦ ",@_),"\n"
} else {
exec @_ or exit 127;
}
}

xexec
qw(mysqldump -O single-transaction=TRUE --skip-lock-tables --add-drop-table --all --extended-insert --quick --skip-add-locks ),@args;