MySQL Backup Script
From Nonecks Docs
#!/usr/bin/perl
# mysql_backup.pl
###################################################################################
# POD Documentation
=head1 PROGRAM NAME AND AUTHOR
MySQL Backup v3.3
by Peter Falkenberg Brown
peterbrown@worldcommunity.com
http://worldcommunity.com/opensource/
Build Date: September 14, 2008
=PURPOSE
Backs up mysql data safely, using
'mysqldump', 'select to outfile' or 'normal record selection'.
This is my attempt :-) to provide a reasonably
full featured MySQL backup script that can be
run from:
1. Linux Crontab or Windows Scheduler
2. the shell or command prompt
3. the web (with password protection)
(Note that I don't recommend running it from the web
because of permission issues - however, some users
don't have shell access. It depends upon the security
level of your data.
It now works from both Linux and Windows.
See URLs at the end about where to get
WINDOWS VERSION OF UNIX UTILITIES.
It provides options to backup all of the databases and tables
in one particular host, with exception lists.
It now has the ability to select a variety of options
for the tar and gzip functions, including using the
tar -z switch, using bzip2 and piping data through gzip/bzip2.
It also deletes text files in between tar and gzip, to save space.
It also works around the sql wildcard glitch with
underlines (_), in table names, by not using mysqlshow
to get table names. I did this, because mysqlshow db %
didn't work under MySQL v3.22.20a, and I wasn't able to
determine when the % method came into being (under which
version.) So, in order to make things work for earlier
versions, I used 'show tables'.
(I still use mysqlshow for database names.)
=COPYRIGHT
Copyright 2000-2008 Peter Falkenberg Brown
(WCP - The World Community Press)
This program complies with the GNU GENERAL PUBLIC LICENSE
and is released as "Open Source Software".
NO WARRANTY IS OFFERED FOR THE USE OF THIS SOFTWARE
=BUG REPORTS AND SUPPORT
Send bug reports to peterbrown@worldcommunity.com.
=OBTAINING THE LATEST VERSION
==> Get the most recent version of this program at:
http://worldcommunity.com/opensource/
=TODO
- adding multiple recipient support
Email your feedback and ideas to:
peterbrown@worldcommunity.com
=VERSION HISTORY
(See the bottom of the file, since the version notes are getting longer.)
=cut
###################################################################################
use DBI;
use POSIX qw(strftime);
use Time::Local;
use Cwd;
use File::Path;
no strict 'refs';
# MANDATORY VARIABLE SET UP SECTION
# ..................................
# Note: the file is ALWAYS save locally, whether or not
# you set ftp_backup and/or email_backup to 'yes'
$ftp_backup = 'no';
use Net::FTP;
# set $ftp_backup to 'yes' or 'no'.
# => NOTE
# If you set it to 'yes',
# you'll need to install Net::FTP
# If you don't install Net::FTP,
# you MUST place a comment (#) in front of
# the 'use Net::FTP' line above
# You'll also have to set the variables
# for ftp host, etc (below)
$email_backup = 'no';
use MIME::Lite;
# some mailing hosts choke if the attachment is
# too large; you may have to use the ftp method
# if that is true
# set $email_backup to 'yes' or 'no'
# => NOTE
# If you set it to 'yes'
# you'll need to install MIME::Lite
# If you don't install MIME::Lite,
# you MUST place a comment (#) in front of
# the 'use MIME::Lite' line above
# See Windows Users Note below
# about MIME::Lite (You need it.)
# Go to search.cpan.org to get the libs.
# Microsoft Windows options
# ..................................
use Net::SMTP;
# COMMENT out the use Net::SMTP line
# if you're not using smtp
# NOTE for Windows Users:
# - adjust the $find_commands section below
# (set $find_commands to no)
# - you'll need to install Windows versions
# of the utilities listed in the find_commands
# section - see the urls at the end of the file
# - note also that your windows system may require that
# libintl-2.dll and libiconv-2.dll be installed in
# your c:\windows\system directory (for tar, I
# believe) I downloaded "libintl-0.11.5-2-bin.exe",
# from gnuwin32.sourceforge.net/packages/libintl.htm
# and went through the installation, but then had to
# copy the two files by hand from their default
# installation directory, over to the
# \windows\system directory.
# -- don't bother installing them unless you receive
# an error message - I only tested this on Win98.
# - set $chmod_backup_file to 'no'
# - set the smtp items in this section
# - install MIME::Lite and uncomment the
# use MIME::Lite line above
# - the tar z switch and the tar/gzip pipe method
# don't work on Windows
# - the shell command length on Win98 is 127
# (although you can increase it by installing 4Dos)
# -- thus, for Win98, you may wish to install
# your utilities in a short dir like c:\bin
# and make your backup dir short also,
# like c:\data
# -- For Win98 (at least) use the $max_cmd
# variable to abort on commands that are
# longer.
$max_cmd = 0;
# (for Windows Users, especially Win98)
# (for Win98 set this to '127')
# set this to '0' if you don't need to check the
# length of your shell command strings
# (see the ` backtick commands)
# $mailprog = "/var/qmail/bin/qmail-inject -h";
# $mailprog = '/usr/lib/sendmail -t -oi';
$mailprog = '/usr/sbin/sendmail -t -oi';
# sendmail is more common
# but qmail (qmail.org) is (was?) better :-).
# but.. qmail doesn't work on windows
# I've had a good experience with Postfix (as of 2008).
$smtp_host = 'devmail.auctiva.com';
# set only if you set send_method to 'smtp'
# (useful for Windows)
$send_method = 'smtp';
# set $send_method to 'sendmail' or 'smtp';
# (often set to smtp for WinX)
$admin_email_to = 'wikiadmin@auctivaws.com';
# the email for error and notification
# messages, and the email recipient
# of the database files.
$admin_email_from = 'it@auctiva.com';
# database options
# ..................................
@selected_databases = qw[wikidb livechat cacti];
# place the names of your databases here,
# separated by spaces, or set
# process_all_databases to 'yes'
$process_all_databases = 'no';
# @selected_databases is ignored if you set
# process_all_databases to 'yes'
# Many servers with virtual hosts allow you
# to see all of the databases while only giving
# you access to your own database. In that case,
# place the name of your database in the
# @selected_databases array.
# Someone else might want to process all of the
# databases, with possible exceptions. If so,
# place the databases to skip in the
# skip_databases array below.
@skip_databases = qw[];
# Note: skip_databases is only parsed
# if process_all_databases is set to 'yes'
# Leave it blank if you don't want to use it., i.e:
# qw[];
@skip_tables = qw[];
# skip_tables is always parsed.
# Leave it blank if you don't want to use it., i.e:
# qw[];
# This may be an issue with duplicate table names
# in multiple databases -- it's on the todo list.
$chmod_backup_file = 'no';
# set to 'yes' if you want to use it
# (you DO NOT want to set the backup file to 600
# unless you can ftp in as the user that
# the script runs as. (see web use above)
#..............................................................................
# db host information
# set $db_host to a remote server if you need to access data
# on a different machine.
$db_host = '127.0.0.1';
# or, use a domain name or ip
# for databases on different machines
$db_port = '3306';
# database connection variables
$user = 'root';
$password = 'yourrootpassword';
# $ENV{'MYSQL_UNIX_PORT'} = '/var/lib/mysql/mysql.sock';
# use an absolute path; ~/ may not work
# especially with crontab.
# $ENV{'MYSQL_UNIX_PORT'}
# can be used under two circumstances:
# 1: When you have multiple instances of the
# MySQL daemon running on your host,
# where each instance of the daemon
# has its own mysql.sock file - Therefore
# the script needs to find the socket file.
# 2: If your MySQL socket file is NOT
# in a default directory, such as:
# /var/lib/mysql/mysql.sock
# then you may need to use the above
# environment command.
# If you use the normal MySQL
# installation, you can COMMENT OUT the above line
$site_name = 'from.myhost.com';
$subject = "MySQL Backup Done for $site_name";
# subject is the email subject
$mysql_backup_dir = '/backup';
# use an absolute path; ~/ may not work
# the backup dir should normally be
# OUTSIDE of your web document root
# this directory must be writable by the script.
# If you backup from the web, then this directory
# should be set to 777. (see web notes above)
# MANDATORY UTILITY PATH SETTINGS
# ..................................
$find_commands = 'no';
# Set $find_commands to 'yes' or 'no'
# depending upon whether you want to have the program
# search for the command line utilities.
# This is a weak attempt at a ./configure concept.
# Do we need it, since one can edit the lines below?
# Probably not. :-)
# WINDOWS USERS: NOTE:
# Set $find_commands to 'no' and edit the
# path vars directly -- whereis doesn't exist
# on WinX (it should :-).
# See the notes at the end of the script
# about where to download
# WINDOWS VERSION OF UNIX UTILITIES.
# After installing them, edit the paths in this
# section. Use / forward slashes.
# If you want to use different utilities, simply
# use this manual method of setting the paths (below)
# and also change the utility name. Note, however,
# that you should then check the parameters to the
# utilities used inside the script.
# THIS IS WHERE YOU MANUALLY SET THE PATHS TO
# THE UTILITIES
# ...............................................
if ( $find_commands eq 'no' )
{
$mysqlshow_cmd = '/usr/bin/mysqlshow';
$mysqldump_cmd = '/usr/bin/mysqldump';
$nice_cmd = '/usr/bin/nice';
$tar_cmd = '/bin/tar';
$gzip_cmd = '/bin/gzip';
$bzip2_cmd = '/bin/bzip2';
$ls_cmd = '/bin/ls';
}
# ...............................................
# the automated method is done in the main body of
# the script
$compress_method = ;
# (blank) , or 'z_switch' or 'pipe_method'
# NOTE!!! The z switch and the pipe method
# don't work on Windows
# use either 'z_switch' (for tar -z), or
# 'pipe_method' for piping through gzip or bzip2, or
# (blank) for the two step method
# (first tar, then gzip)
# Note that if you set $delete_text_files
# to 'yes' (below)
# then the text files will be deleted
# BETWEEN the tar and gzip creation, if the
# compress_method is set to .
# (thus saving disk space)
# Otherwise, the text files will be deleted
# after the gzip file is created, using the
# z switch or pipe method.
$delete_text_files = 'yes';
# set delete_text_files to 'yes' if you want to
# delete the intermediate data text files,
# and only keep the tar.gzip files.
# I recommend this, because the text files
# can be large.
$use_bzip2 = 'yes';
# If you set 'use_bzip2' to 'yes'
# then it will be used instead of gzip,
# UNLESS!!
# $compress_method equals 'z_switch'
# (because the -z switch uses gzip)
# If set to 'no', the script won't check
# for bzip2 at all (so you don't need it on
# your disk)
$gzip_args = '-9v';
# set gzip arguments if you like
# -1 --fast compress faster
# -9 --best compress better
$bzip_args = '-9v';
# set bzip2 arguments if you like
# -1 .. -9 set block size to 100k .. 900k
###################################################################################
# OPTIONAL VARIABLES SET UP SECTION
# (You may not need to change the vars below)
###################################################################################
$show_file_list_in_email = 'yes';
# for large directories this should be set to 'no'
$print_stdout = 'yes';
# set this to 'yes' if you want to print
# messages to the screen, set it to 'no' if
# you only want the message to go to email
# Note that the print statements are
# created with a subroutine call &message
# &message('s/e/b', 'some message text';
# S = stdout, E = email, B = both
# Thus, you can selectively modify each
# &message print statement if you like.
# (screen output can only be selectively
# modified if $print_stdout equals 'yes')
$date_text = strftime("%Y-%m-%d_%H.%M.%S", localtime);
# the date_text var becomes part of the backup file
# name. see notes about 'backup_date_string' at end
# of file
$increments_to_save = 5;
$seconds_multiplier = 86400;
$increment_type = "Day(s)";
$seconds_to_save = $increments_to_save * $seconds_multiplier;
# increment_type is used for the text output,
# and has no impact on the math.
# one could set increment type to "Minute(s)"
# or "Hour(s)" or "Day(s)" or "Week(s)", etc.
# Just set the seconds_to_save number to
# the correct number of seconds, i.e:
# minute: 60 / hour: 3600 / day: 86400
# week: 604800
# these variables control how many increments
# (e.g. 'days') worth of
# backup files to save. Files with
# timestamps older than this will be deleted each time
# the script is run. Note that the file modification
# time is used - NOT the file name.
# This may have to be modified on non-Linux boxes.
$space_line = " " x 5;
# variable used for spaces at the beginning
# of some lines of printed output
# if you aren't going to ftp your backup file to a second server,
# you don't need to set these variables.
#$ftp_host = 'ftp.your_offsite_domain.com';
#$ftp_port = '21';
#$ftp_passive = '1';
# set to '0' (false) or '1' (true)
# you may need to use passive ftp transfers if you
# use ssh tunneling for ftp.
# Normally, you have to connect with ssh first,
# to the remote ftp host, using an IP number and
# port 21 for the local and remote host.
# You then use 'localhost' in the ftp script
# (i.e. $ftp_host above) and set $ftp_passive to '1'.
# Note that this script would need an addition to
# fire up ssh first, and then disconnect ssh afterward.
# (perhaps in the next version).
# when I manually fired up ssh first, with
# tunneling set, the ftp passive function worked fine.
#$ftp_user = 'your_username';
#$ftp_password = 'your_password';
#$ftp_dir = '/your_offsite_domain_backup_dir/mysql_backup/';
# NOTE!!!!
## This should be set to the ABSOLUTE PATH
## so that the delete old files routine works.
## That is, you should use a beginning slash /.
# NOTE!!!!
## You must also use a trailing / because of
## the double check with pwd() before the old files
## are deleted.
# For WinX users:
# Note that the upload directory under Win98
# using the free Cerberus FTP server correctly used
# long directory names, using either \ or / for
# directory delimiters. Cerberus is at:
# http://www.greenepa.net/~averett/cerberus.htm
#$delete_old_ftp_files = 'yes';
# delete old ftp files or not
#$number_of_files_to_save = 5;
# Number of files to keep on remote ftp server.
# Note that I don't use date processing to keep
# files older than a certain time, because of potential
# differences with timezones and remote server time
# changes. Since we can't control what the remote time
# is (unlike the local server), I used the concept of
# keeping a certain number of files in the remote ftp
# directory, using the list of files sorted by most
# recently uploaded first. Thus, if you keep 3, the
# the 3 that will be saved are the most recent 3.
# This number can't be less than 1.
$tar_options = '-pv';
# hardcoded options include 'c, f'
# p = retain permissions
# v = verbose (can be set below)
if ( $show_file_list_in_email eq 'yes' )
{
$tar_options .= ' -v';
}
# backup file prefix
$file_prefix = 'bak.mysql';
# the file prefix is also used to match files
# for the deletion of old files. It's a real
# 'PREFIX', it will be placed at the front of
# each file name
# mysqldump variables
# ..................................
$mysql_dump_file_ext = 'txt';
$backup_type = 'mysqldump';
# $backup_type = 'outfile';
# $backup_type = 'normal_select';
# set $backup_type to one of these 3 types:
# 'mysqldump'
# 'outfile'
# 'normal_select'
# (mysqldump is the best choice, followed by outfile)
# ! NOTE: for the 'outfile' method,
# you must have MySQL file privilege
# status, or the file will not be written
# (it will be 0 bytes long)
# 'normal_select' uses a normal
# select/write process; it's clunky,
# but some hosts don't provide access to
# mysqldump or 'select into outfile'
# (sometimes mysqldump is on a different
# server, and sometimes a user doesn't have
# 'file_privileges' for mysql.)
# NOTE: for LARGE data sets, 'normal_select'
# may not work well, because of memory problems
$backup_field_terminate = '|';
$backup_field_enclosed_by = ;
$backup_line_terminate = ":!:\n";
# params for 'normal_select' file writing
# note that the "\n" must be interpolated
# via " double quotes or the qq method
# I use :!:\n in order to accomodate
# text or blob fields that have line feeds.
$outfile_params = qq~ fields terminated by '|' lines terminated by ':!:\n' ~;
# params for 'select * from $table_name
# into $outfile ($outfile is created in
# the backup routine)
# end of mysqldump variables
# ...........................
# END OF SETUP VARIABLES
###################################################################################
# YOU NORMALLY WON'T HAVE TO MODIFY ANYTHING BELOW THIS LINE
###################################################################################
# finish setup of email variables
#............................................
if ( $send_method eq 'sendmail' )
{
$mailprog_or_smtp_host = $mailprog;
}
elsif ( $send_method eq 'smtp' )
{
$mailprog_or_smtp_host = $smtp_host;
}
else
{
print qq~Error! You haven't setup your email parameters correctly.~;
exit;
}
# automatic utility setup
#................................
# CMD_ARRAY NOTE (below):
# Note that the automatic method of finding the commands that is used here
# creates variables names that match the commands. Since the script uses the
# default variable names listed in the array, you shouldn't edit the array
# unless you also change the var names in the script.
# DON'T EDIT THIS CMD_ARRAY unless you know what you're doing :-).
@cmd_array = qw[mysqlshow mysqldump nice tar gzip bzip2 ls];
if ( $find_commands eq 'yes' )
{
foreach $command ( @cmd_array )
{
if ( $command eq 'bzip2' and $use_bzip2 ne 'yes' ){next;}
$cmd_name = $command . '_cmd';
($name, $$cmd_name, $rest) = split / /, `whereis $command`, 3;
chomp $$cmd_name;
}
}
# zip variable setup
$gzip_file_type = 'x-gzip';
$bzip_file_type = 'x-bzip2';
$gzip_ext = '.gz';
$bzip_ext = '.bz2';
$gzip_type = 'GZip';
$bzip_type = 'BZip2';
if ( $use_bzip2 eq 'yes' and $compress_method ne 'z_switch' )
{
$gzip_cmd = $bzip2_cmd;
$gzip_args = $bzip_args;
$gzip_file_type = $bzip_file_type;
$gzip_ext = $bzip_ext;
$gzip_type = $bzip_type;
}
# check if each cmd file exists
#...................................
foreach $command ( @cmd_array )
{
if ( $command eq 'bzip2' and $use_bzip2 ne 'yes' ){next;}
$cmd_name = $command . '_cmd';
unless ( -e $$cmd_name ){&error_message(qq~Error! $$cmd_name wasn't found.~);}
}
# BEGIN BACKUP PROCESS
#....................................
$body_text = ;
unless ( -e "$mysql_backup_dir" )
{
&error_message(qq~Error! $mysql_backup_dir doesn't exist.~);
}
chdir ("$mysql_backup_dir");
# now make a tar sub directory for this backup
$tar_dir = $file_prefix . "." . $date_text;
mkdir $tar_dir, 0777;
# we chmod the directory to 777 since the umask
# may be set differently.
# The directory needs to be set to 777 so that
# mysql can perform a 'select into outfile' in that
# directory (since mysql runs as a different user)
chmod 0777, $tar_dir;
unless ( -e "$mysql_backup_dir/$tar_dir" )
{
&error_message(qq~Error! $mysql_backup_dir/$tar_dir wasn't created.~);
}
chdir ("$tar_dir");
$msg = "\nProcessing Backups Using " . uc($backup_type) .
" in\n$mysql_backup_dir/$tar_dir\n\n";
if ( $print_stdout eq 'no' )
{
$msg .= qq~Screen Output (STDOUT) is turned OFF,
so you won't see much until the script is done.\n\n
~;
}
# I use print here, instead of &message,
# so that when $print_stdout is set to 'no',
# the script shows that it's working.
print "$msg";
&message('b',"Databases / Tables:\n");
# test and create the initial database array
# first convert the exception database and table arrays
# to hashes for speed searching
#............................................................................
%skip_databases = ();
%skip_tables = ();
foreach my $database_name ( @skip_databases )
{
$skip_databases{$database_name} = $database_name;
}
foreach my $table_name ( @skip_tables )
{
$skip_tables{$table_name} = $table_name;
}
# test to see if we should process all databases
if ( $process_all_databases eq 'yes' )
{
$cmd = qq~$mysqlshow_cmd --host=$db_host --user=$user --password=$password~;
&cmd_length($cmd) if $max_cmd > 0;
@databases = `$cmd`;
chomp ( @databases );
}
else
{
@databases = @selected_databases;
}
# here's where the backup is actually done
#............................................................................
foreach $db_main ( @databases )
{
if ( $db_main =~ /Databases/ ) {next;}
if ( $db_main !~ /\w+/ ) {next;}
$db_main =~ s/\|//g;
$db_main =~ s/\s+//g;
if ( $process_all_databases eq 'yes' and exists $skip_databases{$db_main} )
{
&message('b',"\nSkipping: [$db_main\]\n");
next;
}
# connect to db
&connect_to_db($db_main);
&message('b',"\nDatabase: [$db_main\]\n");
# now grab table names for this databases
# we use 'show tables' to avoid problems with mysqlshow % with older versions
# ............................................................................
$sth = $dbh->prepare("show tables") or &error_message(qq~Error!\n
Can't execute the query: $DBI::errstr~);
$rv = $sth->execute or &error_message(qq~Error!\n
Can't execute the query: $DBI::errstr~);
while ( ( $table_name ) = $sth->fetchrow_array )
{
if ( exists $skip_tables{$table_name} )
{
&message('b',"\nSkipping: [$table_name\]\n");
next;
}
if ( $print_stdout eq 'yes' )
{
print "$space_line table: [$table_name\]\n";
}
if ( $show_file_list_in_email eq 'yes' )
{
$body_text .= "$space_line table: [$table_name\]\n";
}
# NOW DO THE BACKUP
#############################################################
$backup_text = &do_backup($db_main, $table_name);
if ( $print_stdout eq 'yes' )
{
print $backup_text;
}
if ( $show_file_list_in_email eq 'yes' )
{
$body_text .= $backup_text;
}
}
# disconnect from each database
&logout;
}
# now tar and compress
#............................................................................
chdir ("$mysql_backup_dir");
&message('b',qq~\nTarring and Zipping Files (using $gzip_cmd):\n~);
$backup_tar_file = $mysql_backup_dir . "/" .
$file_prefix . "." . $date_text . "_.tar";
$backup_gzip_file = $backup_tar_file . "$gzip_ext";
$upload_gzip_filename = $file_prefix . "." . $date_text . "_.tar" . "$gzip_ext";
$compress_output = ;
if ( $compress_method eq 'z_switch' )
{
# compress with tar z switch
&message('b',qq~\nNow Compressing with the Tar -z Switch ...\n~);
$cmd = qq~$nice_cmd $tar_cmd $tar_options -c -z -f $backup_gzip_file $tar_dir~;
&cmd_length($cmd) if $max_cmd > 0;
$compress_output = `$cmd`;
}
elsif ( $compress_method eq 'pipe_method' )
{
# pipe through gzip or bzip2
&message('b',qq~\nNow Compressing via a Tar / $gzip_type Pipe ...\n~);
$cmd = qq~$nice_cmd $tar_cmd $tar_options -c -f - $tar_dir | $gzip_cmd $gzip_args > $backup_gzip_file~;
&cmd_length($cmd) if $max_cmd > 0;
$compress_output = `$cmd`;
}
else
{
# use two step method
&message('b',qq~\nNow Compressing via Tar followed by $gzip_type ...\n~);
$cmd = qq~$nice_cmd $tar_cmd $tar_options -c -f $backup_tar_file $tar_dir~;
&cmd_length($cmd) if $max_cmd > 0;
$compress_output = `$cmd`;
# delete text files now, to save disk space
if ( $delete_text_files eq 'yes' )
{
&delete_text_files;
# set delete_text_files to 'no'
# so that the script doesn't try to do it again, below
$delete_text_files = 'no';
}
&message('b',qq~\nNow Compressing with $gzip_type ...\n~);
$cmd = qq~$nice_cmd $gzip_cmd $gzip_args $backup_tar_file~;
&cmd_length($cmd) if $max_cmd > 0;
$compress_output .= `$cmd`;
}
&message('b',$compress_output);
if ( $chmod_backup_file eq 'yes' )
{
chmod 0600, $backup_gzip_file;
}
&message('b',"\nCreated Tar $gzip_type File: $backup_gzip_file\n");
# now check option to delete text files
#............................................................................
if ( $delete_text_files eq 'yes' )
{
&delete_text_files;
}
else
{
if ( $chmod_backup_file eq 'yes' )
{
chmod 0700, $tar_dir;
}
else
{
chmod 0755, $tar_dir;
}
}
# now clean old files from main dir (gzip files)
# includes old tar_dirs
&clean_old_files("$mysql_backup_dir");
# now do ftp, if option is set
if ( $ftp_backup eq 'yes' )
{
# Connect to the server:
&message('b',"\nConnecting via FTP to $ftp_host\n");
$ftp = Net::FTP->new("$ftp_host",
Timeout => "30",
Port => "$ftp_port",
Passive => "$ftp_passive",
Debug => "0") or
&error_message(qq~Error! Net::FTP couldn't connect to $ftp_host : $@\n~);
# Login with the username and password
&message('b',"\nLogging in with FTP.\n");
$ftp->login("$ftp_user", "$ftp_password") or
&error_message(qq~Error! Net::FTP couldn't login to $ftp_host : $!\n~);
# set the type to binary
&message('b',"\nSetting FTP transfer to binary.\n");
$ftp->binary or
&error_message(qq~Error! Net::FTP couldn't set the type to binary for $ftp_host : $!\n~);
# Change to the right directory
&message('b',"\nChanging to FTP dir: $ftp_dir\n");
$ftp->cwd("$ftp_dir") or
&error_message(qq~Error! Net::FTP couldn't change to $ftp_dir at $ftp_host : $!\n~);
#......................................................................
if ( $delete_old_ftp_files eq 'yes' )
{
# First check to see if file already exists
# and process deletions of old files
&message('b',"\nChecking to see if file exists already: $upload_gzip_filename\n... and deleting old files.\n");
# used 'ls' here instead of 'dir' because I only wanted the filename
$check_pwd = $ftp->pwd() or &error_message(qq~Error!\nCouldn't check ftp dir with "pwd".~);;
if ( "$check_pwd" ne "$ftp_dir" )
{
&error_message(qq~
Error! Delete Old FTP Files function in WRONG Directory. : $!
Setup Dir: $ftp_dir
Current Dir: $check_pwd
Possibly the script's ftp directory was not specified with
BEGINNING AND TRAILING SLASHES - this is necessary to match the
"pwd" check before deleting old files.
The beginning slash is mandatory for the "ls" command to work correctly,
to get a list of files to delete.
\n~);
}
else
{
&message('b',"\nIn Correct FTP Directory to Delete Old Files: $check_pwd.\n");
}
@files = $ftp->ls("$ftp_dir");
@new_files = ();
foreach my $file ( @files )
{
next if "$file" eq '.' or "$file" eq "..";
push @new_files, $file;
}
$number_files = scalar(@new_files);
&message('b',"\nNumber of remote files: $number_files\n");
if ( $number_files > 0 )
{
if ( $number_of_files_to_save < 1 )
{
&error_message(qq~Error.\nThe variable 'Number of Files to Save' can't be less than 1.~);
}
# reverse list to process deletions
@files_reverse = reverse(@new_files);
my $deleted_files = 0;
my $file_count = 1;
# adjust starting number to include file uploaded after deletion process
# e.g: save: 1
# file_count (real and then including count of uploaded file):
# 1(2) $file_count <= $number_of_files_to_save / keep / else delete
# 2(3)
# 3(4)
# +1 (uploaded file increments start count)
foreach $file ( @files_reverse )
{
next if "$file" eq '.' or "$file" eq "..";
$file_count++;
# keep $number_of_files_to_save, delete older files
if ( $file_count <= $number_of_files_to_save )
{
# keep
&message('b', "- Keeping Remote File: $file\n");
}
else
{
# delete
$del_return = $ftp->delete("$file");
$del_size = $ftp->size("$file");
if ( $del_size < 1 )
{
$deleted_files++;
&message('b', "$del_return File Removed: ($file\)\n");
}
else
{
&error_message("\nProblem Removing File: $file\n");
}
}
# check if file exists
if ( $file =~ /$upload_gzip_filename/ )
{
&error_message(qq~Error! $upload_gzip_filename already exists at $ftp_host : $!\n~);
}
}
&message('b', "$deleted_files Remote File(s) Removed\n");
}
} # end of delete old files routine
#......................................................................
# Upload the file
&message('b',"\nUploading file: $backup_gzip_file\n");
$ftp->put("$backup_gzip_file","$upload_gzip_filename") or
&error_message(qq~Error! Net::FTP couldn't upload $backup_gzip_file at $ftp_host : $!\n~);
# Get file size to see if the file uploaded successfully
&message('b',"\nChecking File Size of Remote file $upload_gzip_filename at $ftp_host\n");
$uploaded_size = $ftp->size("$upload_gzip_filename") or
&error_message(qq~Error! Net::FTP couldn't get the size of $upload_gzip_filename at $ftp_host : $!\n~);
$gzip_filesize = -s $backup_gzip_file;
if ( $gzip_filesize == $uploaded_size )
{
&message('b', "\nUploaded File Size ($uploaded_size\) of $upload_gzip_filename (local size: $gzip_filesize) Matched at ftp site: $ftp_host\n");
}
else
{
&error_message(qq~Error! Uploaded File Size ($uploaded_size\) of $upload_gzip_filename (local size: $gzip_filesize) did NOT match at $ftp_host : $!\n~);
}
# Disconnect
&message('b', $dir_print_text);
&message('b',"\nDisconnecting from ftp site: $ftp_host\n");
$ftp->quit() or
&error_message(qq~Error! Net::FTP couldn't disconnect from $ftp_host : $!\n~);
}
# now email admin notification of backup, with attached file option
#............................................................................
if ( $email_backup eq 'yes' )
{
&message('b', "\nEmailing $gzip_type File.\n");
MIME::Lite->send("$send_method", "$mailprog_or_smtp_host", Timeout=>60);
# Create a new multipart message:
$msg = new MIME::Lite
From =>"$admin_email_from",
To =>"$admin_email_to",
Subject =>"$subject",
Type =>"multipart/mixed";
# Add parts
attach $msg
Type =>"TEXT",
Data =>"\nTar.$gzip_type File\n[$backup_gzip_file]\nAttached\n$body_text";
attach $msg
Type =>"$gzip_file_type",
Encoding =>"base64",
Path =>"$backup_gzip_file",
Filename =>"$upload_gzip_filename";
$msg->send || die print qq~Error!\n\nError in Mailing Program!~;
}
else
{
# just send notice, without attachment
if ( $send_method eq 'smtp' )
{
# use this for windows machines that don't have sendmail
MIME::Lite->send("$send_method", "$mailprog_or_smtp_host", Timeout=>60);
$msg = new MIME::Lite
From =>"$admin_email_from",
To =>"$admin_email_to",
Subject =>"$subject",
Type =>"TEXT",
Encoding =>"7bit",
Data =>"$body_text";
$msg->send || die print qq~Error!\n\nError in Mailing Program!~;
}
else
{
&mail_to($admin_email_to, $admin_email_from, $subject, $body_text, $admin_email_from);
}
}
# I don't use &message here since the email has already gone out,
# and because it's perhaps good to give even minimilistic final output,
# even when $print_stdout is set to 'no'
print "\n\nDone! Exiting from MySQL Backup Script.\n\n";
exit;
###################################################################################
# connect_to_db
sub connect_to_db
{
# &connect_to_db($db_main);
my ($db_main) = @_;
$dbh = DBI->connect("DBI:mysql:$db_main:$db_host:$db_port", $user, $password)
|| &error_message(qq~Error!\nYou were unable to connect to the database.\n$DBI::errstr~);
$dbh->{PrintError} = 1;
$dbh->{RaiseError} = 0;
}
###################################################################################
# logout
sub logout
{
warn $DBI::errstr if $DBI::err;
if ( $dbh ){$rcdb = $dbh->disconnect;}
}
###################################################################################
# error_message
sub error_message
{
# &error_message($error_text);
my ($error_text) = @_;
my $subject = "$site_name MySQL Backup Error";
print qq~\n$subject\n$error_text\n~;
if ( $send_method eq 'smtp' )
{
# use this for windows machines that don't have sendmail
MIME::Lite->send("$send_method", "$mailprog_or_smtp_host", Timeout=>60);
$msg = new MIME::Lite
From =>"$admin_email_from",
To =>"$admin_email_to",
Subject =>"$subject",
Type =>"TEXT",
Encoding =>"7bit",
Data =>"$error_text";
$msg->send || die print qq~Error!\n\nError in Mailing Program!~;
}
else
{
&mail_to($admin_email_to, $admin_email_from, $subject, $error_text, $admin_email_from);
}
exit;
}
###################################################################################
# message
sub message
{
# &message('s/e/b', $message);
# S = stdout
# E = email
# B = both
my ($output_method, $message) = @_;
if ( $print_stdout eq 'yes' )
{
if ( $output_method eq 's' or $output_method eq 'b' )
{
print $message;
}
}
if ( $output_method eq 'e' or $output_method eq 'b' )
{
if ( $body_text !~ /\w+/ )
{
$body_text = $message;
}
else
{
$body_text .= $message;
}
}
}
###################################################################################
# cmd_length
sub cmd_length
{
# &cmd_length($cmd);
my ($cmd) = @_;
if ( length($cmd) > $max_cmd )
{
&error_message(qq~
Error:<p>
The length of [$cmd\] is longer than $max_cmd\.
<p>
Check things like the length of the directory name
where you store your unix utilities.
You might like to shorten it to '/bin'.
~)
}
}
###################################################################################
# mail_to
sub mail_to
{
# &mail_to($email_to, $email_from, $subject, $mail_body, $reply_to);
my ($email_to, $email_from, $subject, $mail_body, $reply_to) = @_;
if ( $reply_to !~ /\@/ ){$reply_to = $email_from;}
open (MAIL, "|$mailprog") || die print qq~Error!\n\nCan't open $mailprog!~;
print MAIL "To: $email_to\n";
print MAIL "From: $email_from\n";
print MAIL "Subject: $subject\n";
print MAIL "Reply-To: $reply_to\n";
print MAIL "\n";
print MAIL "$mail_body";
print MAIL "\n";
close (MAIL);
}
###################################################################################
# do_backup
sub do_backup
{
# &do_backup($db_main, $table_name);
my ($db_main, $table_name) = @_;
my $response_text = ;
my $sth, $rv, $backup_file, $mysqldumpcommand;
my $backup_str, $row_string, $field_value;
my $len_field_terminate;
my @row;
$backup_file = $file_prefix . "." . $date_text . "_" . $db_main . "." . $table_name . "." . $mysql_dump_file_ext;
$full_file = "$mysql_backup_dir/$tar_dir/$backup_file";
if ( $backup_type eq 'mysqldump' )
{
system("$mysqldump_cmd", "--host=$db_host", "--user=$user", "--password=$password", "--quick", "--add-drop-table", "-c", "-l", "--result- file=$full_file", "$db_main", "$table_name");
}
elsif ( $backup_type eq 'outfile' )
{
$backup_str = qq~
select * into outfile
'$full_file'
$outfile_params
from $table_name
~;
$sth = $dbh->do("$backup_str")
or &error_message(qq~Error!\n
Can't backup data: $DBI::errstr~);
}
else
{
unless ( open(FILE, ">$full_file" ))
{
&error_message(qq~Error!\n
Can't open File $backup_file.~);
}
$sth = $dbh->prepare("select * from $table_name")
or &error_message(qq~Error!\n
Can't do select for backup: $DBI::errstr~);
$rv = $sth->execute
or &error_message(qq~Error!\n
Can't execute the query: $DBI::errstr~);
while ( @row = $sth->fetchrow_array )
{
$row_string = ;
foreach $field_value (@row)
{
$row_string .= $backup_field_enclosed_by .
$field_value .
$backup_field_enclosed_by .
$backup_field_terminate;
}
$len_field_terminate = length($backup_field_terminate);
if ( substr($row_string,-$len_field_terminate,$len_field_terminate) eq $backup_field_terminate)
{
substr($row_string, -$len_field_terminate,$len_field_terminate) = ;
}
$row_string .= $backup_line_terminate;
print FILE $row_string;
}
close(FILE);
}
if ( $chmod_backup_file eq 'yes' )
{
chmod 0600, $full_file;
}
$filesize = -s $full_file;
$response_text .= ' ' x 13 . "file: ($filesize bytes) $backup_file\n";
unless ( -e "$full_file" )
{
&error_message(qq~Error! "$full_file" wasn't created!~);
}
return ($response_text);
}
###################################################################################
# delete_text_files
sub delete_text_files
{
&message('b', qq~\nRemoving Directory: $mysql_backup_dir/$tar_dir\n~);
chdir ("$mysql_backup_dir");
# this requires File::Path
$removed_dir = rmtree($tar_dir,0,1);
if ( -e "$tar_dir" )
{
&error_message(qq~Error! Tar Dir: $tar_dir wasn't deleted!
Output results:
$removed_dir
~);
}
else
{
&message('b', "Removed temporary Tar Dir: $mysql_backup_dir/$tar_dir\n");
}
}
###################################################################################
# clean_old_files
sub clean_old_files
{
# $mysql_backup_dir
# $seconds_to_save = $increments_to_save * $seconds_multiplier;
# call this subroutine with the '$full_dir_name'
my ($full_dir_name) = @_;
unless ( -e $full_dir_name )
{
&message('b',"\nCould NOT Clean Old Files - $full_dir_name doesn't exist.\n");
return;
}
&message('b',"\nCleaning Old Files\n");
$save_time = time() - $seconds_to_save;
$deleted_files = 0;
&message('b', "\nRemoving Files Older than $increments_to_save $increment_type\n");
opendir (DIRHANDLE, $full_dir_name);
# we use $file_prefix to make it safer; we don't want to delete
# any files except those matching the file spec
@filelist = grep { /^$file_prefix\./ } readdir(DIRHANDLE);
closedir (DIRHANDLE);
@sortlist = sort(@filelist);
my $file_count = @sortlist;
my $file_msg = "File Count in Backup Dir: $file_count \n\n";
&message('b', $file_msg);
# loop through directory
foreach $infile (@sortlist)
{
$infile_str = $infile;
$infile = "$full_dir_name/$infile";
($modtime) = (stat($infile))[9];
if ( $modtime < $save_time )
{
# file is older, so delete it
# check if file is a directory
if ( -d $infile )
{
&message('b', "\n - Deleting Tar Subdir: $infile\n");
$deleted_dir = rmtree($tar_dir,0,1);
if ( -e "$infile" )
{
&error_message("\n - Problem Deleting Tar Subdir - $infile_str\.\n");
}
else
{
$deleted_files++;
&message('b', " - Deleted Tar Subdir Correctly - $infile_str\.\n\n");
}
}
else
{
$delete_count = unlink "$infile";
if ( ! -e $infile )
{
$deleted_files++;
&message('b', "$delete_count File Removed: ($infile_str\)\n");
}
else
{
&error_message("\nProblem Removing File: $infile_str\n");
}
}
}
else
{
&message('b', "- Keeping: $infile_str\n");
}
} # end of file loop
&message('b', "\nRemoved $deleted_files Files and/or Directories.\n");
}
###################################################################################
# we place a 1; on the last line, because this file can now be 'required' in
# so that the script can be run from the web.
1;