#!/usr/bin/perl
#
# $Id: dbnightly,v 1.1 2007/05/10 18:33:50 cos Exp $
#
# Call with command line arguments for actions to be done in order:
#  maint: database sql maintenance
#  full: full mysqldump
#  partial: partial mysqldump
#  flush: flush binary logs
#  logs: copy logs to backup directory and gzip
#
# for example, dbnightly maint partial flush logs
#   sql maintenance, then partial dump, then flush logs, then backup logs
#
# timestamped output suitable for piping to email from a crontab run,
# and syslog to facility "user", level "notice"
#
# Contributions/suggestions/bugs to: Ofer Inbar <cos@aaaaa.org>
#
# Written at, and donated to the community by, Going.com
# All my scripts distributed under the terms of Larry Wall's excellent
# Artistic License:  http://language.perl.com/misc/Artistic.html


use POSIX qw(setsid);
use Sys::Syslog qw( :DEFAULT setlogsock);

my $script = $0;
   $script =~ s#^.*/##g;

setlogsock 'unix';
openlog $script, '', 'user';

# configure dbnightly by setting these variables to match your environment
my $dbuser = "username";  # mysql user used for partial dumps and maintenance
my $dbpass = "password";  # password for that user
my $rootpw = "password";  # password for the mysql root user
my $dbname = "database";  # the name of your database
my $dbdump = "/usr/bin/mysqldump";
my $sqldir = "/var/lib/mysql";
my $dumpdir = $ENV{HOME} . "/backups";  # where to store dumps and binary log backups
my $fulldump = "$dbdump --flush-logs --master-data=2";
my $binlogname = "binlogbasename";
my $flushlogs = "/usr/bin/mysqladmin -uroot -p$rootpw flush-logs";

sub say
{ my $string = shift;
  my ($sec,$min,$hour,$mday,$mon,$year) = localtime(time); $year+=1900; $mon++;
  printf "%04d-%02d-%02d %02d:%02d $script: %s\n", $year,$mon,$mday,$hour,$min,$string;
  syslog 'notice', $string;
}

sub errdie { my $string = shift; syslog 'error', $string; die "$0: $string\n" }
sub errwarn { my $string = shift; syslog 'warning', $string; warn "$0: $string\n" }

unless (@ARGV) { say "No command line arguments, nothing to do!" and exit }
chdir $dumpdir or errdie "$0: can't chdir $dumpdir: $!";

@tables = qw(list of table names for partial dump);

foreach $action (@ARGV)
{ if ($action =~ /^maint/) { &dbmaint }

  elsif ($action eq 'full')
  { sqldump('full', $dumpdir, $fulldump, $dbname, 'root', '$rootpw'); }
  elsif ($action eq 'partial')
  { sqldump('', $dumpdir, $dbdump, $dbname, $dbuser, $dbpass); }

  elsif ($action =~/^flush/)
  { say "Flushing binary logs";
    system($flushlogs) == 0  or errwarn "$0: $flushlogs failed: $?"; }

  elsif ($action =~/logs/) { &binlogs }
}

say "Done" and exit;


# ----- sql maintenance -----

sub dbmaint { say "Database maintenace";

  open MYSQL, "| mysql -u $dbuser -p$dbpass $dbname" or errdie "$0: can't run mysql: $!";

  print MYSQL <<DBMAINT;
# put SQL commands for periodic database maintenance here
# for example,
optimize table tablename;
purge master logs before date_sub(now(), interval 30 day);
DBMAINT

  close MYSQL or errwarn "$0: error from mysql: $!";
  say "Database maintenance done";
}

# ----- binary logs -----

sub binlogs
{ opendir SQLDIR, $sqldir or errdie "$0: can't open directory $sqldir: $!";

# first copy and gzip any new or modified logs

  foreach $binlog
    ( sort grep { /^$binlogname.\d+/ && -f "$sqldir/$_" } readdir(SQLDIR) )

  { next unless ((stat("$sqldir/$binlog"))[9] > (stat("$dumpdir/$binlog.gz"))[9]);
    say "Copying $sqldir/$binlog to $dumpdir";

    system("cp -p $sqldir/$binlog $dumpdir") == 0
      or errwarn "$0: cp -p $sqldir/$binlog $dumpdir failed: $?"
      and next;
    system("gzip -f $dumpdir/$binlog") == 0
      or errwarn "$0: gzip -f $dumpdir/$binlog failed: $?";
  }

# now delete from the backup dir any logs deleted from the sqldir

  opendir DUMPDIR, $dumpdir or errdie "$0: can't open directory $dumpdir: $!";

  foreach $savedlog
    ( sort grep { /^$binlogname/ && -f "$dumpdir/$_" } readdir(DUMPDIR) )
  { $savedlog =~ s/.gz$//;
    next if -f "$sqldir/$savedlog";

    say "Deleting $savedlog from $dumpdir";
    unlink $savedlog, "${savedlog}.gz"
        or errwarn "$0: error deleting $savedlog: $!";
  }
}

# ----------------------------------------------------------------------

sub sqldump
{ my ($full, $dir, $cmd, $dbname, $dbuser, $dbpass) = @_;

  my $dump .= "$cmd -u$dbuser -p$dbpass $dbname";
     $dump .= " @tables" unless $full;

  my ($sec,$min,$hour,$mday,$mon,$year) = localtime(time); $year+=1900; $mon++;
  my $date = sprintf "%04d%02d%02d%02d%02d",$year,$mon,$mday,$hour,$min;
  my $file = $full ? "${dbname}-$date.sql"
                   : "${dbname}-partial.sql";

  say ( ( $full ? "Full" : "Partial" ) . " dump of $dbname to $dir");

  open DUMPFILE, "> $file" or errdie "$0: can't write to $file: $!";
  open DUMP, "$dump |" or errdie "$0: can't run $cmd: $!";
  while (<DUMP>) { print DUMPFILE }
  close DUMP or errwarn "$0: error from $cmd: $!";
  close DUMPFILE or errwarn "$0: error closing $file: $!";

  say ( ( $full ? "Full" : "Partial" ) . " dump complete: $file");

  system("gzip -f $file") == 0
      or errdie "$0: gzip -f $file failed: $?";
}