#!/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.5

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

#forward declare subroutines
sub get_tablenames;
sub split_large_files;
sub split_file;

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

#see if the user wants a list of their options.
if(($#ARGV >= 0) && $ARGV[0] eq "--help") {
	print 	"mybackup   Ver 0.9.5 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
	-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},
	"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..
$dsn = "DBI:mysql:host=$options{host};dbname=mysql";
$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} --fields-terminated-by=, --fields-enclosed-by=\\\" ";
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";
	
	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";
	}
}

# check to make a backup of /etc/my.cnf
if ($options{conf} && (-e '/etc/my.cnf')) {
	my $rv = system("cp -a -f /etc/my.cnf $options{backup_dir}");
	if ($rv != 0) {
		warn "Warning: cp -a -f /etc/my.cnf 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";
		}
		if($options{gzip}) { # When using gzip option delete .sql.gz and .txt.gz files
		       # this section of code submitted by Hans Rakers 3/16/2001
			my $rv = system("rm -f $options{backup_dir}/$dbname/*.sql.gz");
			if($rv != 0) {
				warn "Warning: An error occured while attempting to remove old .sql.gz files from $options{backup_dir}/$dbname.  Error #$rv\n";
			}
			$rv = system("rm -f $options{backup_dir}/$dbname/*.txt.gz");
			if($rv != 0) {
				warn "Warning: An error occured while attempting to remove old .txt.gz files from $options{backup_dir}/$dbname.  Error #$rv\n";
			}
		} 
		else {
			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";
	}
}

#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 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();
	}
}
#/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/#

#/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/#
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
	$rv = system("mv $filename $stripped_filename");
	if($rv != 0) {
		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";
			}
			$rv = system($command);
			if($rv != 0) {
				die "Could not execute $command!\n";
			}
		}
	}
	close(DIR);
}
#/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/#