#!/usr/bin/perl -w
#/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/#
#Marc Swanson             |      \|||/      #
#MSwanson Consulting      |      /o o\      #
#Phone:  (603)868-1721    |-ooo----O----ooo-#
#Fax:    (603)868-1730    |  Solutions in:  #
#marcswanson@mediaone.net |  '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


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


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,$dbh,$base_command,$command);  #various vars we should scope.

my %options = (
	host		=>	'localhost',
	db		=>	'*',
	user		=>	'root',
	pass		=>	'',
	read_lock	=>	'0',
	overwrite	=>	'0',
	backup_dir	=>	'/var/lib/mysql_backup',
	gzip		=>	'0',
	gzip_level	=>	'9',
	logfile		=>	'/var/log/mybackup.log',
	verbose		=>	'0'
);

#see if the user wants a list of their options.
if($ARGV[0] eq "--help") {
	print 	"mybackup   Ver 0.1 for most unix/linux flavors\n",
		"By Marc Swanson, released under permission from\n",
		"Sonitrol Communications, Hartford CT.  Modify it to your hearts content.\n",
		"If you make a usefull addition, feel free to distribute it as long as this\n",
		"help message remains intact.  This software comes with NO WARRANTY and if it blows up your\n",
		"system, it's not my fault!!! :-)\n",
		"\n",
		"mybackup is used to backup all the tables in one or all databases on a MySql server.\n",
		"Usage:  mybackup [OPTIONS]\n",
		"\n",
			"\t-h, --host=\t\tHost to connect to. Default=localhost\n",
			"\t-d, --database=\t\tDatabase(s) to backup.  Default=*==All\n",
			"\t-u, --user=\t\tUser to conect as.  Default=root\n",
			"\t-p, --pass=\t\tPassword to connect with.  Default is ''\n",
			"\t-r, --read_lock\t\tIssue a read lock.\n",
			"\t-o, --overwrite\t\tWrite over files in the directory with the same name before\n",
					"\t\t\tDumping.  Note that this actually removes EVERYTHING\n",
					"\t\t\tIn each 'table' subdirectory with extension .sql or .txt\n",
			"\t-b, --backup_dir=\t\tPath to the base backup directory.\n",
					"\t\t\tSub directories for each database will be created\n",
					"\t\t\tIf they don't already exist.  Default='/var/lib/mysql_backup'\n",
			"\t-g, --gzip\t\tRun gzip.\n",
			"\t-l, --level=(0-9)\t\tLevel 0 indicates no special options to pass to gzip\n",
					"\t\t\tDefault=9\n",
			"\t-L, --logfile=\t\tLogfile to write to.  Blank will use STDOUT.\n",
					"\t\t\tDefault='/var/log/mybackup.log'\n",
			"\t-v, --verbose\t\tPrint 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},
	"gzip|g!"		=>	\$options{gzip},
	"level|l=i"		=>	\$options{gzip_level},
	"logfile|L=s"		=>	\$options{logfile},
	"verbose|v!"		=>	\$options{verbose},
);
#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..
$dbh = DBI->connect("dbi:mysql:dbname=mysql", $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} ";
if($options{pass}) {
	$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";
	
	my $rv = system("mkdir $options{backup_dir}");
	if($rv != 0) {
		warn "Warning: mkdir $options{backup_dir} returned non-zero.  Error #$rv\n";
	}
	$rv = system("chown mysql $options{backup_dir}");
	if($rv != 0) {
		warn "Warning: chown mysql $options{backup_dir} returned non-zero.  Error #$rv\n";
	}
	$rv = system("chmod 764 $options{backup_dir}");
	if($rv != 0) {
		warn "Warning: chmod 764 $options{backup_dir} returned non-zero.  Error #$rv\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

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!
		my $rv = system("mkdir $options{backup_dir}/$dbname");
		if($rv != 0) {
			warn "Warning: An error occured while attempting to create the directory $options{backup_dir}/$dbname.  Error #$rv\n";
		}
		#now give mysql ownership of the directory
		$rv = system("chown mysql $options{backup_dir}/$dbname");
		if($rv != 0) {
			warn "Warning: An error occured while attempting to chown the directory $options{backup_dir}/$dbname to mysql.  Error #$rv\n";
		}
		#finally, mod the directory to 764
		$rv = system("chmod 764 $options{backup_dir}/$dbname");
		if($rv != 0) {
			warn "Warning: An error occured while attempting to chmod the directory $options{backup_dir}/$dbname to 764.  Error #$rv\n";
		}
	}
	#indicate to the log/stdout that we're going to back up this database
	print "preparing to backup $dbname...\n";

	if($options{overwrite}) { #remove *.sql and *.txt from the data save directory
		if($options{verbose}) {
			print "removing old .sql and .txt files from $options{backup_dir}/$dbname\n";
		}
		my $rv = system("rm -f $options{backup_dir}/$dbname/*.sql");
		if($rv != 0) {
			warn "Warning: An error occured while attempting to remove old .sql files from $options{backup_dir}/$dbname.  Error #$rv\n";
		}
		$rv = system("rm -f $options{backup_dir}/$dbname/*.txt");
		if($rv != 0) {
			warn "Warning: An error occured while attempting to remove old .txt files from $options{backup_dir}/$dbname.  Error #$rv\n";
		}
	}
	
	#finish setting up the mysqldump commands
	push(@commands,"$base_command/$dbname $dbname");
	
	#push the gzip command on if the user wants to gzip the backup
	if($options{gzip}) {
		if($options{gzip_level}) { #as long as it isn't zero..
			push(@commands,"gzip -$options{gzip_level} $options{backup_dir}/$dbname/*");
		}
		else {
			push(@commands,"gzip $options{backup_dir}/$dbname/*");
		}
	}

}

#finally, run all the commands in the "commands" array

foreach $command (@commands) {
	if($options{verbose}) {
		print "issuing this command to the system:  $command\n";
	}
	my $rv = system("$command >/dev/null") ;
	if($rv != 0) {
		warn "this command returned non-zero on exit:  $command\n";
	}
}
#indicate that we're done
print "************    finished     ************\n";
#/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/#


#/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/#
sub get_tablenames() {
	my ($dbname) = shift;
	my ($sth,$query,$rv,@row);  #scope local variables. 
		
	$query = "use $dbname";
	$rv = $dbh->do($query);
	if(!$rv) {
		print "Warning:  something went wrong with this query:  $query\n";
	}
	
	#get the table names
	
	$query = "show tables";
	$sth = $dbh->prepare($query);
	$sth->execute();
	while((@row) = $sth->fetchrow()) {
		#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();
	}
}
#/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/#