Re: [SLUG] MySQL dumps per database, how 2 script ?

[Paul Robinson <paul@xxxxxxxxxxxxxxxx>]

Michael Chesterton wrote:

> "Voytek" <lists@xxxxxxxxxx> writes:
>
>  
>
> > ------
> > +-------------------+
> > |     Databases     |
> > +-------------------+
> > | atom              |
> > | commodore         |
> > ------
> >
> > perhaps some has such a script already ?
> >    
>
> mysqlshow|perl -e 'while(<>){if(/\|\s([^ ]+)/){print "$1\n"}}'
>
> which assumes no spaces in database names. 
>
> But I think there would be a more eloquent way.
>  
Wrote mine completely in perl. Excuse the formating - was mucked up in 
pasting it. It basically grabs a list of databases from the mysql db and 
proceeds to back it up while keeping a weekly rotation schedule. 
Probably other ways to do it but this met my needs.

HTH
Paul

#!/usr/bin/perl
use DBI;
use Mysql;
use Date::Pcalc qw(:all);
#use CGI;
#need username, password, name, email, url

$DB_Host = "localhost";
$DB_Name = "mysql";
$DB_User = "mysql_user_with_global_access";
$DB_Password = "password_here";
$backuppath = "/var/mysqlbackup";

my $dbh =
Mysql->Connect("$DB_Host;database=$DB_Name;",$DB_User,$DB_User,$DB_Password)
or die "$Mysql::db_errstr";

($year,$month,$day) = Today();
if (length($day)==1) {
       $day = "0$day";
}
if (length($month)==1) {
       $month = "0$month";
}

($oldyear, $oldmonth, $oldday) = Add_Delta_YMD($year, $month, $day, 0, 
0, -7);
if (length($oldday)==1) {
       $oldday = "0$oldday";
}
if (length($oldmonth)==1) {
       $oldmonth = "0$oldmonth";
}

$dbh->selectdb(mysql) or die "$Mysql::db_errstr";

my $sth = Query $dbh "SELECT * FROM db" or die "$Mysql::db_errstr";

while (my @arr = $sth->fetchrow) {

print "$arr[1]\n";
system("rm $backuppath/daily-$arr[1]-$oldyear$oldmonth$oldday.sql");
system("mysqldump --opt $arr[1] -u $DB_User --password=$DB_Password > 
$backuppath/daily-$arr[1]-$year$month$day.sql");
}

undef $sth;
undef $dbh;