[Fife Linux Users Group] Full Data Dump

[Gilbert]

Chase. here is the Perl script I use to backup database dumps through
ssh connection, I think you might find it useful.

cheers.

B

#!/usr/bin/perl
# Little script that dumps the sql databases and rsync them to the
# backup server through an ssh connection, a rolling backup of seven
# days is kept.
# Use the following in your daily cron. (/etc/cron.daily) providing
# that this file is saved in /root/
#
#   #!/bin/bash
#   /root/sql_dump.pl | mail -s "Daily MySQL Backup"
admin at sharpnet.co.uk
#
# Written by G.Serex Sharpnet Internet Solutions Ltd. (c) 2003

# var definitions

# root username
$username = "root";

# root password
$password = "hehe";

# where the list of dbs will go.
$dbfile = "mysql_dbs";

# the dumped files path .  (absolute path + trailing / please)

$dumped_dbs_path = "/tmp/";

# the dumped files tar archive name

$mysql_dumped = "mysql_dumped_archive.tgz";

# the remote host name

$remotehost = "backups.box.com";

# The remote path   (absolute path + trailing / please)

$remotepath = "/backup-space/mysql/";

#________________ E N D _ V A R _ D E F S. ____________________________

# first check and optimise the lot.

system("/usr/bin/mysqlcheck --optimize --all-databases --auto-repair -u
$username -p$password");

# Dump the datbase list into $dbfile

system("/usr/bin/mysql -u $username -p$password -e \"show databases\" >
$dumped_dbs_path$dbfile");

# open the $outfile again

open (MSQL_DB_LIST, "$dumped_dbs_path$dbfile") or die "Cannot open
$dumped_dbs_path$dbfile: $!";

while (<MSQL_DB_LIST>) {
        chop $_;
        #ignore the db list header ( Database )
        next if ($_ =~ /^Database$/);
        print "Now writing $_ database...\n";
        # dump the content of the  database into a file
        system("/usr/bin/mysqldump -u $username -p$password --flush-logs
--opt $_ > $dumped_dbs_path$_.dump");

}
close (MSQL_DB_LIST);

# delete the file
unlink "$dumped_dbs_path$dbfile";

#get the day:

$today = (sun,mon,tue,wed,thu,fri,sat)[(localtime)[6]];

# compress the datas in a tgz with the day at the beginning
system("cd $dumped_dbs_path; /bin/tar -zcf $dumped_dbs_path$today\_
$mysql_dumped *.dump");

# rsync them to the backup server

system("/usr/bin/rsync -e ssh -avz $dumped_dbs_path$today\_$mysql_dumped
$remotehost:$remotepath");

#remove the dumped file.
unlink <$dumped_dbs_path\*.dump>;

# remove the backup file locally

unlink "$dumped_dbs_path$today\_$mysql_dumped";

print "database backup complete\n";
exit;


On Mon, 2005-10-10 at 13:21 +0100, Chase wrote:

> Hay, all
>    Im trying to get a nice easy backup script working. Zeedo talked about  
> somthing at the meeting but ken now i gone and forgot! So i started  
> writing my own. Does anyone know how to get a MySQL dump of a database,  
> without having to type and thing in to any prompts? its gona be for a  
> automated script, so i can have it asking me for a users password!
>    Cheers all
> 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mailman.lug.org.uk/pipermail/standrews/attachments/20051011/9d9287ed/attachment.html