#!/usr/bin/perl -w
#
#############################################################################
#                  ____   ___  _       _                _                
#  _ __ ___  _   _/ ___| / _ \| |     | |__   __ _  ___| | ___   _ _ __  
# | '_ ` _ \| | | \___ \| | | | |     | '_ \ / _` |/ __| |/ / | | | '_ \ 
# | | | | | | |_| |___) | |_| | |___  | |_) | (_| | (__|   <| |_| | |_) |
# |_| |_| |_|\__, |____/ \__\_\_____| |_.__/ \__,_|\___|_|\_\\__,_| .__/ 
#            |___/                                                |_|    
#
# File:      mySQLbackup.pl
# Author:    Philip Ronan (midori.co.uk)
# Created:   2006-02-10
# Last rev:  2006-02-11
# Backlink:  http://midori.co.uk/wordpress/2007/02/10/3/
#
# Setup:
# Before installing this script, make sure the variables listed below (database
# name, password, email addresses, etc.) have been set up correctly.
#
# Installation:
# Upload this script to your private directory and set its permissions to 700.
# Then set up a cron job to run this script every 24 hours.
#
# Usage:
# When this script is run, it creates a gzipped archive of your mySQL database
# inside your private folder and calculates the MD5 hash of this archive. If
# the value of this MD5 hash is not the same as the MD5 hash of the previous
# archive (i.e., if any changes have been made to your database in the last
# 24 hours), this archive will be sent to you by email. Error messages will
# be emailed by the cron daemon.
#
#############################################################################
#
# Before using this script, you need to set up the following variables:
#
# Your domain name (leave off the www at the beginning):
my $domain    = 'your.domain.com';
#
# Location of your private directory:
my $privDir   = '/usr/local/psa/home/vhosts/your.domain.com/private/';
#
# Name of your database:
my $dbName    = 'yourDB';
#
# Username and password for accessing this database:
my $dbUser    = 'user';
my $dbPass    = 'password';
#
# Email address where backups are to be sent:
my $admin     = 'Your Name Here <your.email.address@your.domain.com>';
#
# Email address of server admin (the email comes from this address):
my $from      = 'Database Admin <dbadmin@your.domain.com>';
#
# Location of the mysqldump utility on your server:
my $mysqldump = '/usr/local/psa/mysql/bin/mysqldump';
#
# Location of the sendmail utility on your server:
my $sendmail  = '/usr/sbin/sendmail -oi -t';
# (The -t switch forces sendmail to scan the message for recipients. The -oi
# switch [IgnoreDots] keeps message lines with a single dot (.) from ending
# the data transfer before the whole message has been sent. It's actually not
# necessary for this script, but avoids strange problems in other scripts.)
#
# Location of the md5 utility on your server:
my $md5       = '/sbin/md5 -q';
# (The -q switch witches off verbose output)
#
# Location of the gzip utility on your server:
my $gzip      = '/usr/bin/gzip -n';
# (The -n switch prevents gzip from saving the file modification date. Without
# this switch, the MD5 hash would end up being different every time)
#
# Location of the uuencode utility on your server:
my $uuencode  = '/usr/bin/uuencode -m';
# (The -m switch is required for base64 encoding)
#
#############################################################################
#
# You probably won't need to change anything below this line.
#
#############################################################################

my $gzipFile = "$dbName.sql.gz";
my $hashFile = "$gzipFile.md5";

my $sr;
my $oldHash  = '0000000000000000';
my $newHash;
my $li;

# Get previous MD5 hash, if available:
if (open HASH, "< $privDir$hashFile") {
  $oldHash = <HASH>;
  chomp($oldHash);
  close(HASH);
};

# Create new database backup and calculate MD5 hash
$sr = `$mysqldump -u $dbUser -p$dbPass $dbName | $gzip > $privDir$gzipFile`;
if ($sr ne '') {
  die("mySQLbackup.pl: $sr");
};
$sr = `chmod 600 $privDir$gzipFile`;
if ($sr ne '') {
  die("mySQLbackup.pl: $sr");
};
$sr = `$md5 $privDir$gzipFile > $privDir$hashFile`;
if ($sr ne '') {
  die("mySQLbackup.pl: $sr");
};
$sr = `chmod 600 $privDir$hashFile`;
if ($sr ne '') {
  die("mySQLbackup.pl: $sr");
};

# If the hash is identical, exit the script:
open HASH, "< $privDir$hashFile" or die("mySQLbackup.pl: can't read hash file\n");
$newHash = <HASH>;
chomp($newHash);
close HASH;

if ($newHash eq $oldHash) {
  # no changes detected
  exit;
};

# Otherwise mail the gzipped file as an attachment:
open(MAIL,"|$sendmail") or die("mySQLbackup.pl: can't open stream to sendmail\n");
print MAIL "To: $admin\n";
print MAIL "From: $from\n";
print MAIL "Subject: Database backup ($dbName)\n";
print MAIL "MIME-Version: 1.0\n";
print MAIL "Content-Type: multipart/mixed;\n";
print MAIL "\tboundary=\"--$newHash--\"\n";
print MAIL "\n";
print MAIL "This is a multi-part message in MIME format.\n";
print MAIL "\n";
print MAIL "----$newHash--\n";
print MAIL "Content-Type: text/plain; charset=us-ascii\n";
print MAIL "Content-Transfer-Encoding: 7bit\n";
print MAIL "\n";
print MAIL "The $dbName database at $domain has been updated. Here is the\n";
print MAIL "latest backup.\n";
print MAIL "\n";
print MAIL "----$newHash--\n";
print MAIL "Content-Type: application/x-gzip; name=\"$gzipFile\"\n";
print MAIL "Content-Transfer-Encoding: base64\n";
print MAIL "Content-Disposition: attachment; filename=\"$gzipFile\"\n";
print MAIL "\n";
open(FILE, "$uuencode $privDir$gzipFile $privDir$gzipFile|") or die("mySQLbackup.pl: can't access uuencoded data\n");
# Discard first line ("begin-base64...etc...")
$li = <FILE>;
# Send everything except the end marker
while($li = <FILE>) {
  if ($li ne "====\n") {
    print MAIL $li;
  }
};
close(FILE);
print MAIL "----$newHash----\n";
close MAIL;

exit;