<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 TRANSITIONAL//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=UTF-8">
<META NAME="GENERATOR" CONTENT="GtkHTML/3.6.2">
</HEAD>
<BODY>
Chase. here is the Perl script I use to backup database dumps through ssh connection, I think you might find it useful.<BR>
<BR>
cheers.<BR>
<BR>
B<BR>
<BR>
#!/usr/bin/perl<BR>
# Little script that dumps the sql databases and rsync them to the<BR>
# backup server through an ssh connection, a rolling backup of seven<BR>
# days is kept.<BR>
# Use the following in your daily cron. (/etc/cron.daily) providing<BR>
# that this file is saved in /root/<BR>
#<BR>
# #!/bin/bash<BR>
# /root/sql_dump.pl | mail -s "Daily MySQL Backup" <A HREF="mailto:admin@sharpnet.co.uk">admin@sharpnet.co.uk</A><BR>
#<BR>
# Written by G.Serex Sharpnet Internet Solutions Ltd. (c) 2003<BR>
<BR>
# var definitions<BR>
<BR>
# root username<BR>
$username = "root";<BR>
<BR>
# root password<BR>
$password = "hehe";<BR>
<BR>
# where the list of dbs will go.<BR>
$dbfile = "mysql_dbs";<BR>
<BR>
# the dumped files path . (absolute path + trailing / please)<BR>
<BR>
$dumped_dbs_path = "/tmp/";<BR>
<BR>
# the dumped files tar archive name<BR>
<BR>
$mysql_dumped = "mysql_dumped_archive.tgz";<BR>
<BR>
# the remote host name<BR>
<BR>
$remotehost = "backups.box.com";<BR>
<BR>
# The remote path (absolute path + trailing / please)<BR>
<BR>
$remotepath = "/backup-space/mysql/";<BR>
<BR>
#________________ E N D _ V A R _ D E F S. ____________________________<BR>
<BR>
# first check and optimise the lot.<BR>
<BR>
system("/usr/bin/mysqlcheck --optimize --all-databases --auto-repair -u $username -p$password");<BR>
<BR>
# Dump the datbase list into $dbfile<BR>
<BR>
system("/usr/bin/mysql -u $username -p$password -e \"show databases\" > $dumped_dbs_path$dbfile");<BR>
<BR>
# open the $outfile again<BR>
<BR>
open (MSQL_DB_LIST, "$dumped_dbs_path$dbfile") or die "Cannot open $dumped_dbs_path$dbfile: $!";<BR>
<BR>
while (<MSQL_DB_LIST>) {<BR>
chop $_;<BR>
#ignore the db list header ( Database )<BR>
next if ($_ =~ /^Database$/);<BR>
print "Now writing $_ database...\n";<BR>
# dump the content of the database into a file<BR>
system("/usr/bin/mysqldump -u $username -p$password --flush-logs --opt $_ > $dumped_dbs_path$_.dump");<BR>
<BR>
}<BR>
close (MSQL_DB_LIST);<BR>
<BR>
# delete the file<BR>
unlink "$dumped_dbs_path$dbfile";<BR>
<BR>
#get the day:<BR>
<BR>
$today = (sun,mon,tue,wed,thu,fri,sat)[(localtime)[6]];<BR>
<BR>
# compress the datas in a tgz with the day at the beginning<BR>
system("cd $dumped_dbs_path; /bin/tar -zcf $dumped_dbs_path$today\_$mysql_dumped *.dump");<BR>
<BR>
# rsync them to the backup server<BR>
<BR>
system("/usr/bin/rsync -e ssh -avz $dumped_dbs_path$today\_$mysql_dumped $remotehost:$remotepath");<BR>
<BR>
#remove the dumped file.<BR>
unlink <$dumped_dbs_path\*.dump>;<BR>
<BR>
# remove the backup file locally<BR>
<BR>
unlink "$dumped_dbs_path$today\_$mysql_dumped";<BR>
<BR>
print "database backup complete\n";<BR>
exit;<BR>
<BR>
<BR>
On Mon, 2005-10-10 at 13:21 +0100, Chase wrote:
<BLOCKQUOTE TYPE=CITE>
<PRE>
<FONT COLOR="#000000">Hay, all</FONT>
<FONT COLOR="#000000"> Im trying to get a nice easy backup script working. Zeedo talked about </FONT>
<FONT COLOR="#000000">somthing at the meeting but ken now i gone and forgot! So i started </FONT>
<FONT COLOR="#000000">writing my own. Does anyone know how to get a MySQL dump of a database, </FONT>
<FONT COLOR="#000000">without having to type and thing in to any prompts? its gona be for a </FONT>
<FONT COLOR="#000000">automated script, so i can have it asking me for a users password!</FONT>
<FONT COLOR="#000000"> Cheers all</FONT>
</PRE>
</BLOCKQUOTE>
</BODY>
</HTML>