sourcecode
penguinfuz
<code>
#! /usr/bin/perl -w
# db-backup.pl
# Dump and gzip each MySQL database.
use strict;
use DBI;
my $dbhost = "localhost";
my $dbuser = "root";
my $dbpass = "easyONE";
my $date = "`date +%Y-%m-%d`";
my $path = "/your/archive/db";
my $ext = "bz2";
flush_old($path,$ext);
my($dbh,$sth,$query);
my $dsn = "DBI:mysql:host=$dbhost";
$dbh = DBI->connect($dsn,$dbuser,$dbpass,{PrintError => 0, RaiseError => 1});
$query = qq^SHOW DATABASES^;
$sth = $dbh->prepare($query);
$sth->execute();
while(my $ref = $sth->fetchrow_array()) {
my @bp = `mysqldump --user=$dbuser --password=$dbpass --add-drop-table $ref | bzip2 -1 > $path/$ref.$date.$ext`;
}
$sth->finish();
$dbh->disconnect();
# - - - - - - - - - - - - - - - - -
sub flush_old {
my ($path,$ext) = @_;
opendir BP_DIR,"$path" or die "Cannot open $path: $!\n";
my @old_backups = grep { /\.$ext$/ } readdir BP_DIR;
closedir BP_DIR;
for (@old_backups) {
my @args = ("rm","-f","$path/$_");
system(@args);
}
}
</code>
This script backs up each MySQL database into individual gzip'd files; Useful in shared environments where many users have their own MySQL databases and wish to have daily backups of their own data.<br><br>
<strong>UPDATE: </strong>17/07/2003
<ul>
<li>Now using bzip2 for better compression</li>
<li>Removed connect() subroutine</li>
</ul>
<strong>TODO</strong>
<ul>
<li>Read db owners from a config file and automatically deliver
backups to the appropriate ~user dir.</li>
</ul>
Utility Scripts
penguinfuz at wonderwaylabs dot com