Well you can do it a few ways. One way is to just backup the physical directory that olds all the databases (each database has its own directory within the main one). This is how mysql stores data, so if it became courpted or delete, you could literally just move the old directories into the place and everything would work fine again. Anther way is to pipe a myslq dump into a compression program to archive it.
I've done both ways before, but I find that just tar-ing up the mysql directory is the easiest way.
(note, this script tars the directory, ftp's it to a remote site and delete's the local copy)
Code:
#!/usr/bin/perl
# Backup stuff to a remote server
use Net::FTP;
# Create tmp tar file of the $ARGV[0] directory
my $dir = shift;
my $host = shift || 'ftp.remoteserver.com';
my $user = shift || 'username';
my $pass = shift || 'password';
my $remote_dir = shift || 'backups';
die "No directory to backup! - $dir\n" unless ($dir && -d $dir);
# Times
# We need this so we can allow a weeks worth of backups
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time());
my $filename;
if($dir =~ m|([^/]+)$|) {
$filename = $1 . '.' . $wday . '.tar.gz';
} else {
$filename = 'tmp.'.$wday.'.tar.gz';
}
# To the tar
system("tar zcf /tmp/$filename $dir");
my $ftp = Net::FTP->new($host, Debug => 0);
$ftp->login($user, $pass);
$ftp->cwd($remote_dir);
$ftp->delete($filename);
$ftp->binary;
$ftp->put('/tmp/'.$filename);
$ftp->quit;
unlink('/tmp/'.$filename);
Usage: /path/to/backup.pl /path/to/mysql/data/dir [ftpserver] [username] [password] [remote_dir] 2>/dev/null
the [ftpserver] [username] [password] and [remote] are optional (if you setup the script correctly.)
For example, this is what I have setup in the crontab (backs up 2 directories):
0 * * * * /usr/bin/perl /home/ledjon/crons/backupdir.pl /var/www 2>/dev/null
1 * * * * /usr/bin/perl /home/ledjon/crons/backupdir.pl /var/lib/mysql 2>/dev/null
the 2>/dev/null with put all the errors it might get (tar likes to throw errors on pointless stuff). This keeps you from getting crontab e-mails everyday with the tar messages.
Also note that this will keep backups for 7 days (7 days in a week!)
Dev Shed Forums Sponsor:
March 29th, 2001, 10:04 AM
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
