Re: [edlug] MySql database backup

["David R. Baird" <dave@xxx.xxx.xxx>]

[Just joined the list myself].

Hi Paul, 

I recently configured various backups on my web server, including 
the MySQL database. Here's the Perl script I use to backup the 
db. 

I run this from a cron, so the home directory of the cron user 
should have a MySQL config file (.my.cnf) to supply the username 
and password for the mysql and mysqldump programs. It should look 
like this: 

# cat .my.cnf 
[mysql]
user=root
password=xxxxx

[mysqldump]
user=root
password=xxxxx



Note that this script writes every table of every db to a 
separate file. This is because my main backup script uses rdiff-
backup, which only sends changed files to my backup server. Most 
of the tables in my db don't change most days, so it saves me a 
bit of bandwidth. You may wish to get the output all in one file 
- see the mysqldump docs, it's just a small change to the 
mysqldump options. 

Anyway, if the script is doing more than you need, the thing you 
want is mysqldump. 

------------------8<--------------------------

#!/usr/bin/perl

# backup_mysql
# Send every table in every database to a file in the db backup
# directory. 

use strict;
use warnings;

my $backup_dir = '/misc/dbbackup/mysql';

chomp(my @databases = `/usr/bin/mysql -e 'show databases'`);

shift @databases; # remove title

my %db_tables = map { $_ => 1 } @databases;

foreach my $db (keys %db_tables) {
    chomp(my @tables = `/usr/bin/mysql -e 'use $db; 
                                           show tables'`);   
    shift @tables; # lose the title
    $db_tables{$db} = [ @tables ];
    delete $db_tables{$db} unless @tables;
}

print "BACKUP DIRECTORY: $backup_dir\n";
print "-------------------------------------\n\n";
    
foreach my $db (keys %db_tables) {
    
    eval "mkdir '$backup_dir/$db'";
    if ($@) {
        die "Couldn't mkdir [$backup_dir/$db]: $@" 
                unless $@ =~ 'File exists';
    }
    
    dump_db($db);
}

sub dump_db {
    my ($db) = @_;
     
    foreach my $table ( @{ $db_tables{$db} } ) {
        my $backup_file = "$backup_dir/$db/$table.sql";
        print "$db.$table -> $db/$table.sql\n";
        print `/usr/bin/mysqldump --all --opt --quote-names \
                        $db $table > $backup_file`;
    }
}

------------------8<--------------------------

HTH,

d.

On 4 Mar 2004 at 10:32, Paul McClung wrote:

> 
> 
> 
> ----------  Forwarded Message  ----------
> 
> Subject: MySql database backup
> Date: Wednesday 03 Mar 2004 10:21 pm
> From: Paul McClung
> To: edlug@xxx.xxx.xxx
> Cc: paul@xxx.xxx.xxx
> 
> Hello all before i start askin my little question let me introduce my
> self my name is Paul McClung and im a computing student who has been
> usin linux on and off for a few years now but am not very good at it.
> Anyway im wandering if anyone can help me with backing up several sql
> database that i created on my server for several websites. Im new to
> sql and only managed to set them up using commands from a redhat book
> however i now need to change my distro and want to make a copy of
> these to go with the websites that i have already backed up but have
> no idea where to start. I realise i am probably being a bit to general
> here but if you tell me what info you need i will provide it. Thanks
> and any help is much appreciated.
> 
> -------------------------------------------------------
> 
> -
> ----------------------------------------------------------------------
> You can find the EdLUG mailing list FAQ list at:
> http://www.edlug.org.uk/list_faq.html



-- 
Dr. David R. Baird
ZeroFive Web Design
dave@xxx.xxx.xxx
http://www.zerofive.co.uk
-
----------------------------------------------------------------------
You can find the EdLUG mailing list FAQ list at:
http://www.edlug.org.uk/list_faq.html