On Friday 11 February 2005 17:51, Joe Crnjanski wrote:
> Another simple question,
>
> HOW we can backup My SQL database.
>
> I have a customer with around 5GB MySQL database. Backup is over the
> internet, so dump file backup is out of question.
I don't know from where I got the original script, but this is working nice
for me:

#!/usr/bin/perl
# mysql-backup v1.0  (c) 26.5.2003 by Andreas Ley  (u) 27.5.2003
# MySQL Online TSM Backup

$mycnf = "/root/mysqladmin.cnf";
$dsmc = "/usr/bin/dsmc" ;
use DBI;
use Getopt::Std;

sub usage {
        my $image = $0;
        $image =~ s!.*/!!;
        print STDERR "Usage: $image [-v] [database [...]]\n";
        print STDERR "or:    $image [-v] -a\n";
        print STDERR "-v  Verbose mode\n";
        print STDERR "-a  Lock all databases during backup\n";
        print STDERR "Default is to lock databases one by one\n";
        exit(1);
}

getopts('aDhxv');

&usage if ($opt_h);

@opts = ("-verbose") if ($opt_v);

open (MYCNF,$mycnf) || die "Can't read $mycnf: $!\n";
while (<MYCNF>) {
        chomp;
        $user = $' if (/^\s*user\s*=\s*/);
        $password = $' if (/^\s*password\s*=\s*/);
}
close (MYCNF);

print STDERR "Connect to database...\n" if ($opt_v);
$dbh = DBI->connect("dbi:mysql:", $user, $password,
        { RaiseError => 1, PrintError => 0, AutoCommit => 1 });

($dummy,$datadir) = $dbh->selectrow_array("show variables like 'datadir'");

sub dsmc {
        unless ($opt_v) {
                open(OLDOUT,">&STDOUT");
                open(STDOUT,">/dev/null");
        }
        #print "@_\n" ;
        #map(s!/+$!!,@_);
        system($dsmc,"incremental","-subdir=yes",@opts,@_);
        unless ($opt_v) {
                open(STDOUT,">&OLDOUT");
        }
}

if ($opt_a) {
        print STDERR "Lock all tables...\n" if ($opt_v);
        $dbh->do("flush tables with read lock");
        &dsmc("$datadir//");
} else {
        if ($#ARGV >= 0) {
                $databases = \@ARGV;
        } else {
                print STDERR "Read databases...\n" if ($opt_v);
                $databases = $dbh->selectcol_arrayref("show databases");
        }

        for $database (@$databases) {
                print STDERR "Lock tables in $database...\n" if ($opt_v);
                $tables = $dbh->selectcol_arrayref("show tables from
$database");
                $temp = join(",", @$tables);
                if ( @$tables ) {
                          $dbh->do("lock tables ".join(",",map("$database.$_
read",@$tables)));
                        &dsmc($datadir.$database.'/');
                }
                print STDERR "Export tables in $database...\n" if ($opt_v);
                $output = $dbh->selectcol_arrayref("mysqldump
--tab=/backup/mysql --opt db_name");
        }
}

$dbh->disconnect;


/root/mysqladmin.cnf:
user=mysql-user
password=password



Stef