#!/usr/bin/perl
#
# Christopher Lindsey --- Mallorn Computing, Inc.
# lindsey@mallorn.com
#
# Copyright (c) 1999 Christopher Lindsey -- All Rights Reserved

use DBI;
use DBD::mysql;
use Time::Local;
use File::Path;
$|++;
$days = 86400 * 1;
# CHANGE THESE SETTINGS!
$backupDir = "/mnt/Admin/backups/SQL/";
$hostname = "mysql.example.com";
$user = "root"; 
$password = "xxxxxx";

@months = qw(January, February, March, April, May, June, July, August,
             September, October, November, December);
($e,$m,$y,$w) = (localtime(time + $changedate))[3,4,5,6];
$d = sprintf ("%02d", $e);
$y = sprintf ("%02d", $y % 100);
$B = $months[$m]; $b = "\L$1" if $B =~ /^(...)/;
$date = "$d$b$y";
$backupDir .= "/$y/";

$driver = "mysql"; $database = "mysql";
$dsn = "DBI:$driver:database=$database;host=$hostname;port=3306";
$dbh = DBI->connect($dsn, $user, $password);

# GET LIST OF DATABASES
$sth = $dbh->prepare("SHOW databases");
$sth->execute();
push @databases, $$ref[0] while $ref = $sth->fetchrow_arrayref();

$dbh->do("flush logs");
foreach $i (@databases) {
   $dbh = DBI->connect($dsn, $user, $password) if ! $dbh->ping;
   $backup = 0;
   $sth = $dbh->prepare("SHOW TABLE STATUS FROM $i");
   $sth->execute();
   while ($ref = $sth->fetchrow_arrayref()) {
      ($name, $time) = ($$ref[0], $$ref[12]);
      if($time eq "" || $time eq "NULL"){
        $backup = 1;
        last;
      }else{
         ($year, $mon, $day, $hours, $min, $sec) = ($time =~ /^(\d\d\d\d)-(\d\d)-(\d\d) (\d\d):(\d\d):(\d\d)$/);
         $mon--; $year -= 1900;
         $time = timelocal($sec, $min, $hours, $day, $mon, $year);
         if ($time > time() - $days) {
            $backup = 1;
            last;
         }
      }
   }

   if ($backup) {
      mkpath("$backupDir", 0, 0755) if ! -d $backupDir;
      system ("/usr/local/mysql/bin/mysqldump --opt --lock-tables=FALSE -u $user -p'$password' -h $hostname $i | /usr/local/bin/bzip2 -9s > $backupDir/$i.$date.bz2");
   }
}