MySQL Auto-backup shell under Linux

#!/bin/bash# MySQL Backup script # Backup principle: # # Use to enumerate all the libraries; # use each library, list each table, in addition to specifying the ignored library; # 3# use mysqldump to export each table to a file: hostname/month day/library/table. MYSQ ldump.sql# 4# Verify that each table's SQL file contains the completion flag; # * * Zip each sql file and delete this SQL file # 6# Force delete the backup folder for more than x days all files # 7# send the processing log to the specified email# 8# please cooperate with sync Tool multiple server Backup # MySQL Backup configuration information mysqlbackupuser= "Backuper" # password do not include "number mysqlbackuppwd=" **#&&& #ddddddd ("# Log file path/var/log/ The file name. log, which records only the log # of each run # does not back up the database name, each name is wrapped with a () number, such as do not back up ABC.D and ABC.E two databases, write "(ABC.D) (ABC.E)", the name is not case-sensitive notbackupdatabases= "(MySQL) (information_schema) (Performance_schema)" #备份的目录, the following need to add/backuproot= "/var/backup/hostname-mysql-data/" # Delete a backup directory that exists greater than the following number of days deleterootoutdays=30# must be the full email address because the front command is used to smtpuser= "[email protected]" #smtp:// The protocol is required smtphost= "smtp://smtp.qq.com:25" #密码不能包含又引号防止shell出错smtpPwd = "pwd" smtpto= "[email protected]" smtpsubject= "MySQL automatic backup script execution information on host" # Configuration End Line shname=$ (basename $) shlogpath= "/var/log/${shname}.log" ver=$ (Realpath-- Version 2>&1) If ["$?"-ne "0"];then echo "test Realpath--version version error, abort, error message: ${ver}" Exit 10fiecho-E "$ (date" +%y-%m-%d%r:%s ") by $ (realpath ${0}) \ n" > $shLogPathfunction myexit () {exitcode=$1 ver=$ (mailx-v 2&  gt;&1) Appendlog "Exit Time: $ (date +%y-%m-%d/%r:%s)" Appendlog "Server information: \n$ (ifconfig 2>&1)" If ["$?"-ne "0"        ];then appendlog "Test the command used to send email MAILX error, please install, such as CentOS using yum install MAILX, ignore the steps to send an email notification, error message: ${ver}" Else  #发送email mailinfo=$ (Mailx-v-S "${smtpsubject}"-S from= "${smtpuser}"-S smtp-auth= "login"-S smtp= "${smtphost}" -S smtp-auth-user= "${smtpuser}"-S smtp-auth-password= "${smtppwd}"-S Ssl-verify=ignore "${smtpto}" < $shLogPath 2&G T;&AMP;1) # can not attach the sending process log to the email notification, so, can only be saved to the log, if you need to understand the interactive process of email, please check the log file Appendlog "Exit time to send email time: $ (Date +%  y-%m-%d/%r:%s) \ n send emial notification interaction using MAILX as follows: \n\n${mailinfo} "fi exit $exitCode}# append log function Appendlog () {ECHO-E" ${1} \ n ">> $shLogPath}if [!-e" ${backuproot} "];then appendlog" Backup root ${backuproot} does not exist, created "mkinfo=$ (mkdir-p $backupRoot 2>&1)   If ["$?"-ne "0"];then Appendlog "attempt to create backup directory ${backuproot} failed: ${mkinfo}" Myexit 1 fielif [!-d "${back Uproot} "];then Appendlog" Backup root path exists although it is not directory, abort: ${backuproot} "Myexit 2fi# today's backup directory todayroot=" ${backuproot}$ (date +% Y%M%D%H)/"if [!-e" ${todayroot} "];then mkinfo=$ (mkdir $todayRoot 2>&1) If [" $? "-ne" 0 "];then app Endlog "Attempt to create backup directory for this round ${todayroot} failed, aborted: ${mkinfo}" Myexit 3 Fifiappendlog "Today's backup directory: ${todayroot}" ver=$ (MySQL--ver Sion 2>&1) If ["$?"-ne "0"];then appendlog "error when testing MySQL version, abort, error message: ${ver}" Myexit 4fiver=$ (mysqldump-v 2&gt &1) If ["$?"-ne "0"];then appendlog "Test mysqldump command error, install, abort, error message: ${ver}" Myexit 5fiver=$ (tail--version 2> &1) If ["$?"-ne "0"];then appendlog "Test tail command version error, abort, error message: ${ver}" Myexit 41fiver=$ (Tar--version 2>& 1) If ["$?"-ne "0"];then appendlog "error when testing version of tar command, abort, error message: ${ver}" Myexit 42fidatabases=$ (mysql--host= "127.0.0.1" --user= "${mysqlbackupuseR} "--password=" ${mysqlbackuppwd} "--execute=" Show Databases; " --silent--skip-column-names--unbuffered 2>&1) If ["$?"-ne "0"]; Then Appendlog "An error occurred while attempting to enumerate MySQL database names using configuration information, abort: ${databases}" Myexit 6else appendlog "The list of all database names is as follows: \ n ${databases}" Fif or database in $databases; Do # matches case insensitive echo $notBackupDatabases |grep-i "(${database})" >/dev/null # belongs to a library that does not need to be backed up if ["$?"-eq "0"];  Then Appendlog "Database ${database} is specified that no backup is required, skip" continue fi databaseroot= "${todayroot}${database}/" if            [!-e "${databaseroot}"];then mkinfo=$ (mkdir $databaseRoot 2>&1) If ["$?"-ne "0"];then Appendlog "Attempt to create directory for database ${databaseroot} failed, aborted: ${mkinfo}" Myexit 7 fi fi tables=$ (MySQL--host= "12  7.0.0.1 "--user=" ${mysqlbackupuser} "--password=" ${mysqlbackuppwd} "--execute=" Show tables from \ ' ${database}\ '; " --silent--skip-column-names--unbuffered 2>&1) If ["$?"-ne "0"]; Then Appendlog "Try using configuration information to enumerate MysQL Database ${database} Table list error, abort: ${tables} "Myexit 8else appendlog" ${database} The full list of database tables is as follows: \ n ${tables} "fi for Table In $tables; Do sqlpath= "${databaseroot}${table}.sql" timestart= "Start dump time point: $ (date +%y-%m-%d/%r:%s)" dumpinfo=$ (my Sqldump--host= "127.0.0.1"--user= "${mysqlbackupuser}"--password= "${mysqlbackuppwd}"--dump-date--comments-- Quote-names--result-file= "${sqlpath}"--quick--databases "${database}"--tables "${table}" 2>&1) timeEnd  = "Completion dump time point: $ (date +%y-%m-%d/%r:%s)" If ["$?"-ne "0"];then Appendlog "attempt to export database ${database} table ${table} Failed, aborted: ${dumpinfo} "Myexit 9 else Appendlog" Export database ${database} table ${table} to ${sqlpath} success: ${d Umpinfo}; Time consuming: from ${timestart} to ${timeend} "tail--lines=10" ${sqlpath} "|grep" \-\-Dump completed "2>&1 >/dev /null if ["$?"-ne "0"];then Appendlog "not found in the contents of the ' Dump completed ' normal completion flag character, please check the dump file ${sqlpath }, please login to ssh to view this articleIf the backup was successful "else Appendlog" detects the ' dump completed ' flag character in the contents of the backup file, the dump file ${sqlpath} should be backed up successfully "fi Fi donedoneappendlog "\ n------Database backup complete------\ n" # Start compression, put compression to the end of the backup is to prevent the compression time is too long, if the lock table appears, will affect the site run sqls=$ (LS--almost-all --ignore-backups--indicator-style=slash-1 ${todayroot}*/*.sql 2>&1) for the path in $SQLS; Do #路径中包含了绝对路径, the tar command also needs to improve sqldir=$ (dirname $path) sql=$ (basename $path) tarinfo=$ (Tar--create--remove-files --BZIP2--absolute-names--directory= "${sqldir}"--add-file= "${sql}"--file= "${path}.tar.bz2") if ["$?"-ne "0"];th En appendlog "Compressed and deleted ${path} file: \n${tarinfo}" Else Appendlog "compressed and Deleted ${path}" Fidoneappendlog "------done Compress Operation-----"#开始清理大于x天的备份daysDir =$ (ls--almost-all--ignore-backups--indicator-style=slash-1" ${backuproot} "2> &1) for Bkdir in $daysDir;d o bkdir= "${backuproot}${bkdir}" if [!-D "${bkdir}"];then Appendlog "prepare to delete expired spare Operation, because ${bkdir} is not a directory, skip "continue fi dirname=$(basename $bkDir) #test echo $dirName | Grep-p "^\d{10}$" 2>&1 >/dev/null if ["$?"-ne "0"];then Appendlog "when you are ready to delete the expired backup directory, the name of the directory to be deleted is not 10 digits, skipping : ${bkdir} "continue fi outday=$ (date--date="-${deleterootoutdays}day "" +%y%m%d00 ") #如果文件时间小于这个过期时间那么就强制删除 Entire directory if ["${dirname}"-lt "${outday}"];then rminfo=$ (rm--force--preserve-root--recursive "${bkdir}" 2&GT;&A mp;1) Appendlog "found an outdated backup directory ${bkdir}, has been more than ${deleterootoutdays} days, that is, less than ${outday} will be deleted, is forced to delete the state (0 for success): ${?}    ${rminfo}; " Fidoneappendlog "------Complete cleanup of expired backup folder operations----" Appendlog "space usage is as follows: \ n $ (df-h)" Appendlog "Current backup file space consumption: \ n $ (du-hs ${ Todayroot}) "Myexit 0

MySQL Auto-backup shell under Linux