#!/usr/bin/perl -w
#/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/#
#Marc Swanson             |      \|||/      #
#MSwanson Consulting      |      /o o\      #
#Phone:  (603)512-1267    |-ooo----O----ooo-#
#Fax:    (603)868-1730    |  Solutions in:  #
#mswanson@mswanson.com    |  'PHP    'Perl  #
#                         |  'SQL    'C++   #
#                         |  'HTML   'Sh/Csh#
# http://www.mswanson.com |  'Javascript    #
#/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/#

#mybackup - A utility using mysqldump to automatically backup a 
#mysql database in a nice directory tree.  run with --help for more details
#VERSION: 0.9.5c

#hey, its easier than doing it manually..
use Getopt::Long;
use DBI;
use File::Copy;
use strict;

my ($dirmode)  = oct('0764');
my ($filemode) = oct('0644');
my ($uid, $gid);

# set the generic umask
umask(0);

#DIST my($mysql_dump_args) = '--fields-terminated-by=, --fields-enclosed-by=\"';
my($mysql_dump_args) = '--fields-optionally-enclosed-by=\"';
my($mysql_data) = '/var/lib/mysql/';

my %table_hash;  #this will be used to store the list of db(s)/table(s) we need to back up
my @commands;  #this will be the list of mysqldump commands to execute
my ($dbname,$dsn,$dbh,$base_command,$command,$tmp);  #various vars we should scope.

# overwrite current gzip files
my ($gzip_args) = '-f';

# function prototypes
sub run_command(@);
sub get_tablenames($);
sub split_large_files();
sub split_file(@);
sub mysql2time($$);

my %options = (
    host        =>  'localhost',
    db          =>  '*',
    user        =>  'root',
    pass        =>  '',
    read_lock   =>  '0',
    overwrite   =>  '1',
    backup_dir  =>  '/var/lib/mysql-backup',
    split_large =>  '0',
    gzip        =>  '1',
    gzip_level  =>  '9',
    logfile     =>  '/var/log/mybackup.log',
    conf        =>  '1',
    verbose     =>  '0',
    mysqluser   =>  'mysql',
    fast        =>  '0',
    exclude     =>  ''
);

# NOTHING to change below here

#see if the user wants a list of their options.
if(($#ARGV >= 0) && $ARGV[0] eq "--help") {
    print   "mybackup   Ver 0.9.5c for most unix/linux flavors
By Marc Swanson, released under permission from
Sonitrol Communications, Hartford CT.  Modify it to your hearts content.
If you make a usefull addition, feel free to distribute it as long as this
help message remains intact.  This software comes with NO WARRANTY and if it blows up your
system, it's not my fault!!! :-)

mybackup is used to backup all the tables in one or all databases on a MySql server.
Usage:  mybackup [OPTIONS]

    -h, --host=        Host to connect to. Default=localhost
    -d, --database=    Database(s) to backup.  Default=*==All
    -u, --user=        User to conect as.  Default=root
    -p, --pass=        Password to connect with.  Default is ''
    -r, --read_lock    Issue a read lock.
    -o, --overwrite    Write over files in the directory with the same name before
                       Dumping.  Note that this actually removes EVERYTHING
                       In each 'table' subdirectory with extension .sql or .txt
    -b, --backup_dir=  Path to the base backup directory.
                       Sub directories for each database will be created
                       If they don't already exist.  Default='/var/lib/mysql-backup'
    -s, --split_large= Split files > 2 gigabytes into smaller pieces.  This feature
                       was added by request as it appears some commercial backup software products
                       do not propperly deal with files over 2 Gig in size even if the
                       OS supports LFS.  The resulting files will be broken up into
                       tablename_aa.txt, tablename_ab.txt, etc..
    -g, --gzip         Run gzip.
    -l, --level=(0-9)  Level 0 indicates no special options to pass to gzip
                       Default=9
    -f, --logfile=     Logfile to write to.  Blank will use STDOUT.
                       Default='/var/log/mybackup.log'
    -c, --config       Backup /etc/my.cnf
    --mysqluser=       The user MySQL is running as: only for root
                       Default=mysql
    -x, --exclude      Exclude tables with this pattern
    --fast             Only update the backup if the table has been changed
                       Compare the txt/sql file with the update stamp 
                       from 'SHOW TABLE STATUS <DATABASE>'
    -v, --verbose      Print lots of output\n\n";
    exit;
}


# Read in the options
GetOptions( 
    "host|h=s"          =>  \$options{host},
    "database|d=s"      =>  \$options{db},
    "user|u=s"          =>  \$options{user},
    "pass|p:s"          =>  \$options{pass},
    "read_lock|r!"      =>  \$options{read_lock},
    "overwrite|o!"      =>  \$options{overwrite},
    "backup_dir|b=s"    =>  \$options{backup_dir},
    "split_large|s!"    =>  \$options{split_large},
    "gzip|g!"           =>  \$options{gzip},
    "level|l=i"         =>  \$options{gzip_level},
    "logfile|f=s"       =>  \$options{logfile},
    "conf|c!"           =>  \$options{conf},
    "mysqluser=s"       =>  \$options{mysqluser},
    "verbose|v!"        =>  \$options{verbose},
    "fast!"             =>  \$options{fast},
    "exclude|x=s"       =>  \$options{exclude}
);

if ($options{verbose}) {
    print "create backup in $options{backup_dir} ...\n";
}

# check the current user
my($login) = (getpwuid($<))[0];
if ($options{mysqluser}) {
    if ($options{mysqluser} eq $login) {
        $options{mysqluser} = '';
    }
    elsif ($options{mysqluser} && $login eq 'root') {
        ($uid,$gid) = (getpwnam($options{mysqluser}))[2..3]
            or die "$options{mysqluser} not in passwd file";
    }
    else {
        # no privileges for this operation
        $options{mysqluser} = '';
    }
}

#get rid of a trailing slash if the user supplied one in the dirname
$options{backup_dir} =~ s/\/$//;

#if the user specified a logfile, then open it and send all prints to the log
if($options{logfile}) {
    open LOG, ">>$options{logfile}" or die "Can't open $options{logfile} for write: $!\n";
    select LOG;
    $| = 1;

    print "\n";
    print scalar(localtime(time())), ":\n";
    print "************starting mybackup************\n";
}
#connect to the db.. couldn't see how to use dbi without selecting a dbname so I used mysql..
$dsn = "DBI:mysql:host=$options{host};dbname=mysql";
# read default values from ~/.my.cnf: only works
# with DBI-mysql-1.2009 and up: 
#    http://dev.mysql.com/doc/mysql/de/perl-dbi-class.html
$dsn .= ";mysql_read_default_file=$ENV{HOME}/.my.cnf";
$dbh = DBI->connect($dsn, $options{user}, $options{pass}) ||
    die "Could not connect to database: $DBI::errstr";

#if the user specified just one database, use it
if($options{db} ne '*') {
    #get the table list for the specified db
    get_tablenames($options{db});
}

else { #the user specified to backup all the databases (default)
    
    #get a list of the available databases
    my @row;
    my $query = 'SHOW DATABASES';
    my $sth = $dbh->prepare($query);
    $sth->execute();
    while((@row) = $sth->fetchrow()) {
        #get a list of the tables in this database
        get_tablenames($row[0]);
    }
    if($sth) {
        $sth->finish();
    }
}

#we should be all done with the db at this point.
if($dbh) {
    $dbh->disconnect();
}

#set up the base mysqldump command

$base_command = "mysqldump -h $options{host} -u $options{user} $mysql_dump_args ";
if($options{pass}) {
    #bug found by Noel Clarkson (there used to be a space between -p and $options{pass}
    #which apparently could cause a problem

    #yet another issue related to passwords.  It is better 
    #(as pointed out by Ilja Slepnev) to quote the password field when
    #sending it to the shell.

    $base_command .= "-p\"$options{pass}\" ";
}

if($options{read_lock}) {
    $base_command .= "-l ";
}

$base_command .= "--tab=\"$options{backup_dir}\"";

#check to see if the backup directory specified exists.. if not, create it
if(!-e $options{backup_dir}) {
    print "$options{backup_dir} doesn't exist.. creating\n";
    
    mkdir($options{backup_dir}, $dirmode) or
        warn "Warning: mkdir $options{backup_dir} returned non-zero.  Error #$!\n";
    if ($options{mysqluser}) {
        chown($uid, $gid, $options{backup_dir}) or
            warn "Warning: chown $options{mysqluser} $options{backup_dir} returned non-zero.  Error #$!\n";
    }
}

# check to make a backup of /etc/my.cnf
if ($options{conf} && (-r '/etc/my.cnf')) {
    if ( !copy('/etc/my.cnf', $options{backup_dir}) ) {
        warn "Warning: could not backup '/etc/my.cnf': $!\n";
    }
}

#now that we've got a hash of the db(s)/table(s) that we need to backup, check to make sure we have directories 
#to put the data in

$gzip_args .= ' -'.$options{gzip_level} if ($options{gzip_level});
my($gz) = ($options{gzip} ? '.gz' : '');
my($tables);
foreach $dbname (keys %table_hash) {
    if(!-e "$options{backup_dir}/$dbname") {
        print "$options{backup_dir}/$dbname doesn't exist.. creating...\n";
        #the directory doesn't exist... create it!
        unless(mkdir("$options{backup_dir}/$dbname", $dirmode)) {
            warn "Warning: An error occured while attempting to create the directory $options{backup_dir}/$dbname.  Error #$!\n";
            next;
        }
        #now give mysql ownership of the directory
        if ($options{mysqluser}) {
            chown($uid, $gid, "$options{backup_dir}/$dbname") or
                warn "Warning: An error occured while attempting to chown the directory $options{backup_dir}/$dbname to $options{mysqluser}.  Error #$!\n";
        }
    }
    #indicate to the log/stdout that we're going to back up this database
    print "preparing to backup $dbname (", $#{$table_hash{$dbname}}, " tables) ...\n";

    #remove *.sql and *.txt from the data save directory
    if($options{overwrite}) {
        if($options{'fast'}) {
            foreach (@{$table_hash{$dbname}}) {
                unlink("$options{backup_dir}/$dbname/$_.sql$gz");
                unlink("$options{backup_dir}/$dbname/$_.txt$gz");
            }
        }
        else {
            if($options{verbose}) {
                print "removing old .sql and .txt files from $options{backup_dir}/$dbname\n";
            }
            unlink(glob("$options{backup_dir}/$dbname/*.sql$gz"));
              #NA warn "Warning: Error removing old .sql$gz files from $options{backup_dir}/$dbname: $!\n";
            unlink(glob("$options{backup_dir}/$dbname/*.txt$gz"));
              #NA warn "Warning: Error removing old .txt$gz files from $options{backup_dir}/$dbname: $!\n";
        }
    }
    
    # skip that table if requested
    $tables = '';
    if ($options{exclude}) {
        $tables = join('" "', grep( {!("$dbname.$_" =~ /$options{exclude}/)} @{$table_hash{$dbname}} ));
        $tables = "\"$tables\"" if ($tables);
        if ($options{verbose}) {
            $tmp = join('" "', @{$table_hash{$dbname}});
            print "excluding tables: $options{exclude}\n" if ($tmp ne $tables);
        }
    }

    #finish setting up the mysqldump commands and execute it
    run_command("$base_command/$dbname $dbname $tables >/dev/null", 0);
    
    #push the gzip command on if the user wants to gzip the backup
    if($options{gzip}) {
        # don't care if something goes wrong with gzip :o
        system("gzip $gzip_args \"$options{backup_dir}/$dbname\"/* >/dev/null 2>&1");
    }

    # set the right owner and mode for the files
    if ($options{mysqluser}) {
        my(@files) = glob("$options{backup_dir}/$dbname/*");
        chown($uid, $gid, @files) or
            warn "Warning: chown $options{mysqluser} $options{backup_dir}/$dbname/* returned non-zero.  Error #$!\n";
        chmod($filemode, @files) or
            warn "Warning: chmod $filemode $options{backup_dir}/$dbname/* returned non-zero.  Error #$!\n";
    }
}

#one last thing needs to be done.  If the user specified that they wanted
#to break up files > 2 Gigabytes, call a function to do the job

if($options{split_large}) {
    split_large_files();
}

#indicate that we're done
print "************    finished     ************\n";
#/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/#

#/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/#
sub run_command(@) {
    my($command) = shift;
    my($log) = shift;
    $log = 1 if (!defined($log));
    
    if($options{verbose} && $log) {
        print "issuing this command to the system:  $command\n";
    }
    my $rv = system("$command") ;
    if($rv != 0) {
        warn "this command returned non-zero on exit:  $command\n";
    }
}
#/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/#

#/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/#
sub get_tablenames($) {
    my ($dbname) = shift;
    my ($sth,$query,$rv,@row);  #scope local variables. 
    my ($filename, @fstat);
    my ($midx) = 11;

    $query = "USE $dbname";
    $rv = $dbh->do($query);
    if(!$rv) {
        print "Warning:  something went wrong with this query:  $query\n";
    }
    
    # get the table names with the current status
    $query = "SHOW TABLE STATUS FROM $dbname";
    $sth = $dbh->prepare($query);
    $sth->execute();
    while((@row) = $sth->fetchrow()) {
        if ($options{'fast'} && defined($row[$midx])) {
            # convert to unix time
            $filename  = "$options{backup_dir}/$dbname/$row[0].txt";
            $filename .= '.gz' if (! -r $filename);
            if (-r $filename) {
                @fstat = stat($filename) or warn "Couldn't stat $filename: $!\n";
                # skip table if up to date
                next if (mysql2time($row[$midx], $fstat[9]) <= 0);
            }
        }
        print "  $dbname.$row[0]: ctime($row[$midx-1]), mtime($row[$midx])\n" if ($options{verbose});
        #push the table name onto the hash using the dbname as the key
        push(@{$table_hash{$dbname}},$row[0]);
    }
    if($sth) {
        #call finish.. make perl happy..
        $sth->finish();
    }
}
#/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/#

#/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/#
sub split_large_files() {
    my (@fstats,$dbname,$tablename,$i,$filename);
    #open each directory that has database backup data in it and
    #split up any files which are larger than 2 gigabytes
    foreach $dbname (keys %table_hash) {
        foreach $tablename (@{$table_hash{$dbname}}) {
            $filename = $options{backup_dir} . "/" . $dbname . "/" . $tablename . ".txt";
            if($options{gzip}) {
                $filename .= ".gz";
            }

            @fstats = stat($filename)
                or warn "Couldn't stat $filename: $!\n";
            if($fstats[7] >= 2048000000) { #the file is larger than 2 gig.  handle it.
                split_file($filename, $dbname, $table_hash{$dbname}[$i]);
            }
        }
    }
}
#/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/#

#/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/#
sub split_file(@) {
    my ($filename) = shift;
    my ($dbname) = shift;
    my ($tablename) = shift;
    my($rv,$stripped_filename,$command, $directory, $dir_file, $pattern);
    

    $stripped_filename = $options{backup_dir} . "/" . $dbname . "/" . $tablename;
    #use the standard unix utility "split" to do the dirty work
    #in order to do this, move the file to the base tablename
    #so the prefix can be tacked on later.  admitadly this is a 
    #bit kludgy
    if( !rename($filename, $stripped_filename) ) {
        die "Could not move $filename to $stripped_filename\n";
    }
    $command = "split -b 2000m \"$stripped_filename\" \"$stripped_filename\_\"";
    $rv = system($command);
    if($rv != 0) {
        die "split returned an error when executed with: $command\n";
    }

    #finally, move each of the freshly split files so they have the appropriate file extension
    #by reading in the appropriate files from the directory

    $pattern = "^$tablename" . "_[ab]{2}\$";
    $directory = $options{backup_dir} . "/" . $dbname;
    opendir(DIR, $directory) or die "Could not open $directory: $!\n";
    while( defined ($dir_file = readdir(DIR))) {
        if($dir_file =~ /$pattern/) {
            $command = "mv \"$directory/$dir_file\" \"$directory/$dir_file\.txt";
            if($options{gzip}) {
                $command .= '.gz';
            }
            $command .= '"';
            $rv = system($command);
            if($rv != 0) {
                die "Could not execute $command!\n";
            }
        }
    }
    close(DIR);
}
#/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/#

#/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/#
sub mysql2time($$) {
    my($mytime) = shift;
    my($time)   = shift;

    # check mysql datetime value: YYYY-MM-DD HH:MM:SS
    if ($mytime =~ /^(\d\d\d\d)-(\d\d)-(\d\d) (\d\d):(\d\d):(\d\d)$/) {
        my($sec,$min,$hour,$mday,$mon,$year) = localtime($time);
        $year += 1900; $mon++;
        my($cmptime) = sprintf('%4d-%02d-%02d %02d:%02d:%02d', $year,$mon,$mday, $hour,$min,$sec);
        return 0 if ($mytime eq $cmptime);
        return 1 if ($mytime gt $cmptime);
    }
    return -1;
}
#/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/#