MySQL Backup问题怎么解决

这篇文章主要讲解了“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 dont recommend running it from the web         because of permission issues - however, some users         dont 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 %         didnt work under MySQL v3.22.20a, and I wasnt 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 youre not going to use the script from the web # you can comment out the next 3 CGI lines, if you dont 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,                              # youll need to install Net::FTP                              # If you dont install Net::FTP,                              # you MUST place a comment (#) in front of                              # the use Net::FTP line above                              # Youll 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                              # youll need to install MIME::Lite                              # If you dont 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 youre not using smtp                              # NOTE for Windows Users:                              # - adjust the $find_commands section below                              #   (set $find_commands to no)                              # - youll 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-/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.                              #   -- dont 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                              #   dont 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 dont 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 doesnt work on windows                              # Ive 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 dont want to use it., i.e:                              # qw[];@skip_tables                 =qw[];                              # skip_tables is always parsed.                              # Leave it blank if you dont want to use it., i.e:                              # qw[];                              # This may be an issue with duplicate table names                              # in multiple databases -- its 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.                              # Ive added functionality that allows the script                              # to be run from the web, because some users                              # dont 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 youll 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 dont 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 its 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 cant ftp                              #   outside their own home directory.)                              # - set $chmod_backup_file to no                              #   since files will be owned by web server                              #   and if theyre set to 600 you wont 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 youve got an ssl url, use it!                              #   Its 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 dont 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 dont have                              #   an ssl cert. What do you care :-)?                              #   (Youre 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 thats used                              # for web login -- either the real script,                              # or a stub script                              # If youre 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-f;                              # 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 its 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 didnt use the parsed data                              # in 2.7, but I hadnt 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 doesnt 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 eqno )       {       $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                              # dont 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 wont check                              # for bzip2 at all (so you dont 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 arent going to ftp your backup file to a second server, # you dont 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-/~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 dont use date processing to keep                              # files older than a certain time, because of potential                              # differences with timezones and remote server time                              # changes. Since we cant 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 cant 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 eqyes )       {       $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. Its 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, Ive hardcoded the above values                              # in the system call, in the subroutine                              # do_backup. Ive 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; its clunky,                              # but some hosts dont provide access to                              # mysqldump or select into outfile                              # (sometimes mysqldump is on a different                              # server, and sometimes a user doesnt 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 WONT 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 were authorized             print qq~

感谢各位的阅读，以上就是“MySQL Backup问题怎么解决”的内容了，经过本文的学习后，相信大家对MySQL Backup问题怎么解决这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是，小编将为大家推送更多相关知识点的文章，欢迎关注！