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

[Paul Robinson]

Michael Chesterton wrote:
"Voytek" <[EMAIL PROTECTED]> 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;
--
SLUG - Sydney Linux User's Group Mailing List - http://slug.org.au/
Subscription info and FAQs: http://slug.org.au/faq/mailinglists.html