这篇文章主要讲解了“MySQL Backup问题怎么解决”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“MySQL Backup问题怎么解决”吧！
#!/bin/bash
TARGET=/BACKUP
export DT=`date +"%Y%m%d_%H%M"`
export DESTDIR=${TARGET}/`date +"%Y%m%d/mysql"`
TMPNAME=_dump_sql_tmp_$$
function Backup()
{
local SERVER
SERVER=$1
BASE="$SERVER"_${DT}
CURDIR=`pwd`
echo " ${SERVER} to ${BASE}: "
DEST=${DESTDIR}/${SERVER}/${DT}
mkdir -p ${DEST}
cd ${DEST}
# getting list of databases...
echo "SHOW DATABASES;" | mysql -pPASSWORD -u USER -h ${SERVER} > ${TMPNAME}
while read DB
do
if [ $DB != "Database" ]; then
echo -n " ${DB}: "
mysqldump -Q --all --complete-insert --quote-names --add-drop-table -pPASSWORD -u USER -h ${SERVER} ${DB} >${BASE}_${DB}.mysql
echo -n "md5"
md5sum -b ${BASE}_${DB}.mysql >${BASE}_${DB}.md5sum
echo -n ", tar"
tar --remove-files -zcf ${BASE}_${DB}.tgz ${BASE}_${DB}.*
echo ", md5"
md5sum -b ${BASE}_${DB}.tgz >${BASE}_${DB}.md5sum
fi
done < ${TMPNAME}
rm -f ${TMPNAME}
cd ${CURDIR}
echo "Done."
}
# Call for all your servers
Backup localhost
Backup my.othersever.com
#!/usr/bin/perl
# mysql_backup.cgi
###################################################################################
# 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;
#.............................................................
# if you're not going to use the script from the web
# you can comment out the next 3 CGI lines, if you don't have
# CGI.pm installed.
use CGI qw(:all -private_tempfiles escapeHTML unescapeHTML);
use CGI::Carp qw(fatalsToBrowser cluck);
$q = new CGI;
#.............................................................
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:windowssystem 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
# windowssystem 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 = 'your.mailserver.domain.or.ip';
# set only if you set send_method to 'smtp'
# (useful for Windows)
$send_method = 'sendmail';
# set $send_method to 'sendmail' or 'smtp';
# (often set to smtp for WinX)
$admin_email_to = 'you@yourdomain.com';
# the email for error and notification
# messages, and the email recipient
# of the database files.
$admin_email_from = 'you@yourdomain.com';
# database options
# ..................................
$web_test_database = 'my_database';
# The name of your authorized database.
# See the notes below about logging in from the web.
#.....................................................
# Because the login must test the mysql authorization
# against a specific database, this new variable
# is required **ONLY** when logging in from the web.
# Use the name of ANY database for which your
# login username has authorized access.
# NOTE! This database name has NO relationship to the
# -- @selected_databases and
# --$process_all_databases variables
# You still MUST set those variables correctly.
# The $web_test_database var is ONLY used
# for an initial login test.
# NOTE! The user must be authorized for the
# test database AND the database that you place
# in the selected_database array
# If the user is NOT authorized for both,
# the script might pass the first login test,
# but fail on the specific database login,
# which would happen AFTER the creation of the
# backup directory (which is messy).
@selected_databases = qw[];
# place the names of your databases here,
# separated by spaces, or set
# process_all_databases to 'yes'
$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,
# 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.
$password_location = 'cnf';
# NEW OPTION FOR PASSWORD LOCATION:
# 'from_web' ALLOWS WEB LOGIN
#.................................................
# set to 'cnf' or 'this_file' or 'from_web'
#.................................................
# NOTE!! I have NOT tested cnf under Windows.
#.................................................
# the connection subroutine uses this
# to decide which method to use.
# I've added functionality that allows the script
# to be run from the web, because some users
# don't have shell access.
# if $password_location is set to 'from_web',
# then n chars are translated to
and also, a
# "Content-type: text/htmlnn"; is printed to sdtout.
# note that you'll have to either run the
# .cgi file from your web tree, or use a 'stub' file
# which I recommend. (see my web page for an example).
# NOTE!!! RUNNING FROM THE WEB is predicated
# on a number of issues:
# - web logins don't use a cnf file, since
# the cnf file would be required to be world
# readable, and because we have to check
# the user login anyway, against mysql.
# - the backup directory ($mysql_backup_dir)
# must be writeable by the web server
# (since it's outside the webtree and the
# the script requires a login, the risk
# is controlled -- however, if your server
# co-exists with other virtual servers,
# you should make sure that they can't ftp
# outside their own home directory.)
# - set $chmod_backup_file to 'no'
# since files will be owned by web server
# and if they're set to 600 you won't be able
# to download them
# - depending on which user you login as
# (super user or single user)
# you may not have permission to
# backup databases other than your own;
# thus, set
# -- @selected_databases and
# --$process_all_databases correctly.
# - mysql user permissions will also influence
# whether or not you can use
# the 'select into outfile' method of backup
# -- try myslqdump...
# -- if all else fails, use 'normal_select'
# - Note about SSL and Web logins:
# If you've got an ssl url, use it!
# It's worth it to encrypt your database
# username and password.
# (Just run the script prefixed by
# https://yourdomain.com/script_name.cgi)
# SSL Note2: Even if you don't OWN an SSL Cert,
# you can often use the https ssl syntax;
# the web server will encrypt it anyway;
# it simply warns you that you don't have
# an ssl cert. What do you care :-)?
# (You're the one running the script.)
# The Cert/Browser marriage is a SCAM
# in my opinion. Since the encryption
# happens anyway, the stupid warning box
# should be eliminated. Maybe a Congressional
# sub-committee should investigate it :-).
# If you want a CHEAP, FAST, cert, go to:
# http://instantssl.com (approx. $40!)
$login_script_name = 'mysql_backup_login.cgi';
# this is the name of the script that's used
# for web login -- either the real script,
# or a stub script
# If you're not using the web option,
# you can keep it blank, or whatever.
# See our website for an example stub script.
$chmod_backup_file = 'yes';
# 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 = 'localhost';
# or, use a domain name or ip
# for databases on different machines
$db_port = '3306';
# database connection variables
$cnf_file = '/home/yourdomain.com/.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.)
# NEW IN V3.0!
# I now longer parse the cnf file manually.
# (I actually didn't use the parsed data
# in 2.7, but I hadn't deleted the code.)
# If you select cnf_file, mysqlshow and mysqldump
# will use the cnf_group variable
# thus, your cnf file can include
# multiple groups if you wish.
# CNF FILE CONTENTS:
# [group_name]
# user=yourusername
# password=yourpassword
$user = '';
$password = '';
# $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 = 'YourDomain.com';
$subject = "MySQL Backup Done for $site_name";
# subject is the email subject
$mysql_backup_dir = '/home/yourdomain.com/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.
# If you backup from the web, then this directory
# should be set to 777. (see web notes above)
# MANDATORY UTILITY PATH SETTINGS
# ..................................
$find_commands = 'yes';
# 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 = '/bin/nice';
$tar_cmd = '/bin/tar';
$gzip_cmd = '/bin/gzip';
$bzip2_cmd = '/usr/bin/bzip2';
$ls_cmd = '/bin/ls';
# windows examples (use a short directory - see windows notes above)
# NOTE!! for Windows, you MUST include the .exe since the script
# checks to see if the file exists before running.
# $mysqlshow_cmd = '/bin/mysqlshow.exe';
# $mysqldump_cmd = '/bin/mysqldump.exe';
# $nice_cmd = '/bin/nice.exe';
# $tar_cmd = '/bin/tar.exe';
# $gzip_cmd = '/bin/gzip.exe';
# $bzip2_cmd = '/bin/bzip2.exe';
# $ls_cmd = '/bin/ls.exe';
# see windows notes above about libintl-2.dll and libiconv-2.dll
# you may need to place them in your windowssystem directory.
# (I needed to, under Win98.)
}
# ...............................................
# 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 = 'no';
# 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 = 'no';
# 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';
# NO LONGER USED!!
# $mysqldump_params = '--quick --add-drop-table -c -l';
# NO LONGER USED!!
# NOTE!!! The variable $mysqldump_params
# is NO LONGER USED!!!
# Because I run mysqldump from the system
# command, I've hardcoded the above values
# in the system call, in the subroutine
# do_backup. I've left them here so folks can
# see this note. If you want to change the
# above variables, then go to the system
# call under mysqldump in the do_backup subroutine.
$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
###################################################################################
# web login routine
#..........................................
if ( $password_location eq 'from_web' )
{
print "Content-type: text/htmlnn";
if ( param('w_user') =~ /w+/ and param('w_password') =~ /w+/ )
{
# test for authorized access
&connect_to_db($web_test_database);
# if we get to here, it means that we're authorized
print qq~感谢各位的阅读，以上就是“MySQL Backup问题怎么解决”的内容了，经过本文的学习后，相信大家对MySQL Backup问题怎么解决这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！
免责声明：本站发布的内容（图片、视频和文字）以原创、转载和分享为主，文章观点不代表本网站立场，如果涉及侵权请联系站长邮箱：is@yisu.com进行举报，并提供相关证据，一经查实，将立刻删除涉嫌侵权内容。
