📄 mysql_backup.cgi
字号:
#!/usr/bin/perl
# Last change: PFB 2002-06-11 11:34:21
# mysql_backup.cgi
###################################################################################
# POD Documentation
=head1 PROGRAM NAME AND AUTHOR
MySQL Backup v2.7
by Peter F. Brown
peterbrown@worldcommunity.com
Build Date: June 11, 2002
=PURPOSE
Backs up mysql data safely, using
'mysqldump', 'select to outfile' or 'normal record selection'.
Version 2 is a complete rewrite of Version 1,
which was done as a bash shell script.
This is my attempt :-) to provide a reasonably
full featured MySQL backup script that can be
run from cron or from the shell prompt.
It provides options to backup all of the databases and tables
in one particular host, with exception lists. It also
works around the sql wildcard glitch with _'s etc, in
database and table names, by not using mysqlshow for
table names. Instead I use 'show tables'. 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'.
=COPYRIGHT
Copyright 2002 Peter F. Brown (The World Community Network)
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.
Visit the author's web site at 'worldcommunity.com'
to view information about support, customer quotes,
a resume link, and fees for custom Perl/MySQL programming.
=OBTAINING THE LATEST VERSION
==> Get the most recent version of this program at:
http://worldcommunity.com/opensource
=VERSION HISTORY
TODO: (very soon :-)
- a version that works with WinNT's email system (NET::SMTP)
- a method to email each database to a different person
- a configuration option to allow running the script in different
subdirectories, with different config files, for different users,
all using the same copy of the master script.
- a configure option to combine tar and gz for drives with limited space
- deleting text files between tar and gz for drives with limited space
-----------------------------------------------------------------------------------
v2.7 - June 11, 2002 - Bug fix:
moved the two 'use' statements for
Net::FTP and MIME::Lite to the top of the file
with instructions to comment out those two lines
if the libraries aren't installed, since the
'use' lines do get parsed.
v2.6 - June 10, 2002 - added the ftp_option
The initial ftp code was contributed by
Gil Hildebrand, Jr. (root@moflava.net)
with additional code by myself.
(added error checking, extra params, etc.)
- added code to skip loading
Net::FTP and MIME::Lite if
$ftp_backup and $email_backup are set to 'no'
If you don't want to install them,
you no longer have to comment out code sections.
(see v2.7 above for bug fix)
(the main code doesn't have to be commented out,
but the 'use' lines do.)
- changed semi-colon ; to comma , after:
attach $msg ....
Path =>"$backup_gzip_file",
Thanks to Kenneth Martis for finding the bug.
- added a segment under $find_commands
to configure the paths for the command line utils
mysqldump, mysqlshow, nice, tar, gzip
Thanks to Luc Schiltz for the concept.
NOTE: If you use the automatic option, you
should of course check the results.
- added --host=$db_host to
mysqlshow and mysqldump commands
so one can use a remote host.
Thanks to Serge Colin and Gil Hildebrand, Jr.
for the suggestion.
- separated Mandatory Variables from Optional Variables
in the setup section, to make setup quicker.
v2.5 - March 1, 2002 - removed the password from the mysqldump and mysqlshow
lines if .cnf files are being used, since using the
user/pass on the command line shows up in 'ps'.
(Thus, it's highly advisable to use a .cnf file!)
- added $show_file_list_in_email var, to trim large emails.
The file list will not be included in the email unless
the var is set to 'yes'.
- Added functionality to backup LARGE systems, i.e:
- changed the tar method to tar a subdirectory with all
the files, so tar doesn't choke if there are too many
files. The subdir is removed once the tar file is made,
if $delete_text_files is set to yes. If not, the files
in old tar_dirs are cleaned out later by clean_old_files.
- modified 'ls -la' to use xargs so that large directories
don't choke.
v2.4 - June 20, 2001 - A bug fix of a bug fix. Oy.
Changed 'w_user' and 'w_password'
in the setup section back to 'user' and 'password'
to make it more consistent with .cnf files, and to
fix a bug I created when I changed it in v2.2
(with mysqldump looking for $user)
Now, we use $user and $password everywhere.
Thanks to our sharp eyed users!
v2.3 - June 5, 2001 - Changed the ~/ for the home directory in the vars
$cnf_file, $ENV{'MYSQL_UNIX_PORT'} and $mysql_backup_dir
to use absolute paths instead of the ~/. The ~/ didn't
work, and is a fine example of the need for testing :-)
I thought I was being clever and convenient, but I
actually didn't use it on my system (I used the absolute
paths instead.) Thanks to Rick Morbey in London.
v2.2 - May 25, 2001 - Bug fix; a typo. Changed 'user' and 'password'
in the setup section to 'w_user' and 'w_password'
so that the connect_to_db routine works.
Thanks to Glen Knoch.
v2.1 - May 23, 2001 - First public release of new Perl version
- bug fixes and added some options
. changed 'mysqlshow db %' for tables, to 'show tables'
. added prefixes to file deletes for safety
. fixed error in regex that checked for text file delete
. changed %e to %d in date string for file name
. added vars for nice, tar, gzip paths
. removed &error_message($error_text) from &mail_to,
to avoid recursive loop, since &error_message calls &mail_to
. fixed error in attached email file path
. commented out $dbh->{PrintError} = 1; and
$dbh->{RaiseError} = 1; so that the script
wouldn't die before emailing the error.
. made all vars in &do_backup 'my' to avoid conflicts
v2.0 - February 15, 2001 - completely rewritten as a Perl script
. added all core options
v1.0 - January 2, 2000 - written as a simple shell script
=cut
###################################################################################
use DBI;
use POSIX qw(strftime);
use Time::Local;
use Cwd;
no strict 'refs';
# MANDATORY VARIABLE SET UP SECTION
# ..................................
$ftp_backup = 'no';
# set $ftp_backup to 'yes' or 'no'.
# => NOTE
# If you set it to 'yes',
# you'll need to install Net::FTP
# You'll also have to set the variables
# for ftp host, etc (below)
$email_backup = 'no';
# set $email_backup to 'yes' or 'no'
# => NOTE
# If you set it to 'yes'
# you'll need to install MIME::Lite
# Load additional libraries:
# Go to search.cpan.org to get them.
# !NOTE! If $ftp_backup and $email_backup are set to 'no'
# and you haven't installed the Net::FTP and MIME::Lite libraries,
# then simply place a # comment mark in front of the two
# 'use' lines below. The rest of the related code in the script
# should be ignored.
# place a comment (#) in front of the line below if the lib isn't installed
#use Net::FTP;
# place a comment (#) in front of the line below if the lib isn't installed
#use MIME::Lite;
#..............................................................................
# db host information
# set $db_host to a remote server if you need to access data
# on a different machine.
$db_host = 'localhost';
$db_port = '3306';
# database connection variables
$cnf_file = '/myhomedir/.my.cnf';
# use an absolute path; ~/ may not work
$cnf_group = 'client';
# you can store your user name and
# password in your cnf file
# or.. you can place the username and
# password in this file,
# but you should set this to 700 (rwx------)
# so that it's more secure.
# we assume here that your user name status
# equals the functions needed.
# (for example, 'select to outfile'
# requires file privileges.)
# for the purposes of parsing the .cnf file
# to get the user and password for mysqlshow,
# the $cnf_group is ignored,
# so if you have more than one group, it won't work
# However, cnf_group is used by the dbh->connect
# method below.
# Bottom line? Use .my.cnf with only
# ONE user and password entry (which is the default)
# or set up a special .cnf file just for this program.
# the contents should look like this:
# CNF FILE CONTENTS:
# [group_name]
# user=yourusername
# password=yourpassword
$user = 'root';
$password = 'xxxxx';
$password_location = 'this_file';
# set to 'cnf' or 'this_file' - the
# connection subroutine uses this
# to decide which method to use.
# $ENV{'MYSQL_UNIX_PORT'} = "/home/mydomain/mysql/mysql.sock";
# use an absolute path; ~/ may not work
# $ENV{'MYSQL_UNIX_PORT'} is used because
# we have multiple instances of the MySQL
# daemon running on our host
# (worldcommunity.com) and each instance
# has its own mysql.sock file - Therefore
# the script needs to find it.
# !!! If you use the normal MySQL
# installation, then COMMENT OUT this line
# $mailprog = "/var/qmail/bin/qmail-inject -h";
# $mailprog = '/usr/lib/sendmail -t -oi';
$mailprog = '/usr/sbin/sendmail -t -oi';
# sendmail is more common
$admin_email_to = "webmaster\@chinaxp.org";
$admin_email_from = "webmaster\@chinaxp.org";
# the email for error messages, etc.
$site_name = 'ChinaXP';
$subject = "MySQL Backup Done for $site_name";
# subject is the email subject
#$mysql_backup_dir = '/yourhomedir.NOT.webdir/mysql_backup';
$mysql_backup_dir = '/home/jwtronics/mysql_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.
###################################################################################
# OPTIONAL VARIABLES SET UP SECTION
# (You may not need to change the vars below)
###################################################################################
$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.
$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.
$show_file_list_in_email = 'no';
# for large directories this should be set to 'no'
# if you aren't going to ftp your backup file to a second server,
# you don't need to set these variables.
$ftp_host = 'my_remote_server.com';
$ftp_port = '21';
$ftp_passive = '0';
# set to '0' (false) or '1' (true)
# you may need to use passive ftp transfers if you
# use ssh tunneling for ftp
# (I have NOT yet tested ssh tunneling with this
# script, although I use ssh tunneling for ftp
# with my normal ftp operations all the time.)
# 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).
$ftp_user = 'my_username';
$ftp_password = 'my_password';
$ftp_dir = '/my/upload/directory';
# 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
# mysqlshow and mysqldump variables
# ..................................
@selected_databases = qw[];
$process_all_databases = 'yes';
# @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,
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -