Mysql automatic shell backup in linux

Mysql automatic shell backup in linux

#! /Bin/bash # mysql backup script # backup principle: #1 # list all databases; #2 # list each table using each database, in addition to the specified ignored database; #3 # Use mysqldump to export each table to a file: host name, year, month, day, database, or table. mysqldump. SQL #4 # verify whether the SQL file of each table contains the completion mark; #5 # compress each SQL file and delete this SQL file #6 # force delete all the files in the backup folder that has been used for more than x days #7 # Send processing logs to the specified email #8 # synchronize with each other multiple tools for server backup # mysql backup configuration information mysqlBackupUser = "backuper" # password does not contain "No. mysqlBackupPwd =" ** # & # ddddddddd ("# log file path/var/ log/file name. log, only records the logs of each operation # Name of the database not backed up, each name is wrapped with a () number, if abc is not backed up. d and abc. e. The two databases are spelled as "(AB C. d) (abc. e) ", the name is case insensitive notBackupDatabases =" (mysql) (information_schema) (performance_schema) "# Backup Directory, add/backupRoot = "/var/backup/hostname-mysql-data/" # Delete the backup Directory deleteRootOutDays = 30 # Must be the complete email address, because the front command to use smtpUser = "qidizi@qq.com" # smtp: // Protocol is required smtpHost = "smtp: // smtp.qq.com: 25 "# password cannot contain and quotes prevent shell errors smtpPwd =" pwd "smtpTo =" qq@qq.com "smtpSubject =" mysql automatic backup script execution information on host "# configure end line shName = $ (basename $0) shLogPath = "/Var/log/$ {shName}. log" ver = $ (realpath -- version 2> & 1) if ["$? "-Ne" 0 "]; then echo" An error occurred while testing the realpath -- version. Abort. error message: $ {ver} "exit 10 fiecho-e" $ (date "+ % Y-% m-% d % R: % S") By $ (realpath ${0 }) \ n "> $ shLogPathfunction myExit () {exitCode = $1 ver = $ (mailx-V 2> & 1) appendLog" Exit Time: $ (date + % Y-% m-% d/% R: % S) "appendLog" server information: \ n $ (ifconfig 2> & 1) "if [" $? "-Ne" 0 "]; then appendLog" An error occurred while testing the mailx command used to send emails. Please install it. For example, centos uses yum install mailx to ignore the steps for sending email notifications, error message: $ {ver} "else # Send 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> & 1) # The sending process log cannot be appended to the email notification. Therefore, it can only be stored in the log. If you need to know the email interaction process, please View appendLog in log file "Exit Time to email sending time: $ (date + % Y-% m-% d/% R: % S) \ n use mailx to send emial notification interaction as follows: \ n $ {mailInfo} "fi exit $ exitCode} # append log function appendLog () {echo-e "$ {1} \ n" >>$ shLogPath} if [! -E "$ {backupRoot}"]; then appendLog "backup root directory $ {backupRoot} does not exist." mkInfo =$ (mkdir-p $ backupRoot 2> & 1) has been created) if ["$? "-Ne" 0 "]; then appendLog" failed to create Backup directory $ {backupRoot}: $ {mkInfo} "myExit 1 fielif [! -D "$ {backupRoot}"]; then appendLog "the backup root directory path exists, but it is not a 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 appendLog" failed to create the backup directory $ {todayRoot} In this round. Abort: $ {mkInfo} "myExit 3 fifiappendLog" Backup Directory today: $ {todayRoot} "ver =$ (mysql -- version 2> & 1) if [" $? "-Ne" 0 "]; then appendLog" An error occurred while testing the mysql version. Abort. error message: $ {ver} "myExit 4 fiver =$ (mysqldump-V 2> & 1) if [" $? "-Ne" 0 "]; then appendLog" An error occurred while testing the mysqldump command. Please install and abort the command. error message: $ {ver} "myExit 5 fiver =$ (tail -- version 2> & 1) if [" $? "-Ne" 0 "]; then appendLog" An error occurred while testing the tail command version. Abort. error message: $ {ver} "myExit 41 fiver =$ (tar -- version 2> & 1) if [" $? "-Ne" 0 "]; then appendLog" An error occurred while testing the tar command version. Abort. error message: $ {ver} "myExit 42 fidatabases =$ (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 listing mysql database names using configuration information. Stop: $ {databases} "myExit 6 else appendLog" The list of all database names is as follows: \ n $ {databases} "databases or database in $ databases; do # case insensitive echo $ notBackupDatabases | grep-I "($ {database})">/dev/null # a database that does not need to be backed up if ["$? "-Eq" 0 "]; then appendLog" database $ {database} is specified without backup, skip "continue fi databaseRoot =" $ {todayRoot }$ {database}/"if [! -E "$ {databaseRoot}"]; then mkInfo =$ (mkdir $ databaseRoot 2> & 1) if ["$? "-Ne" 0 "]; then appendLog" failed to try to create the database $ {databaseRoot} directory. Abort: $ {mkInfo} "myExit 7 fi tables = $ (mysql -- host =" 127.0.0.1 "-- user =" $ {mysqlBackupUser} "-- password =" $ {mysqlBackupPwd} "-- execute = "show tables from \ '$ {database }\'; "-- silent -- skip-column-names -- unbuffered 2> & 1) if [" $? "-Ne" 0 "]; then appendLog" An error occurred while trying to use configuration information to list the $ {database} table of the mysql database. Stop: $ {tables} "myExit 8 else appendLog" $ {database} All database tables are listed as follows: \ n $ {tables} "fi for table in $ tables; do sqlPath = "$ {databaseRoot }$ {table }. SQL "timeStart =" Start dump time: $ (date + % Y-% m-% d/% R: % S) "dumpInfo = $ (mysqldump -- 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 = "dump completion time point: $ (date + % Y-% m-% d/% R: % S) "if [" $? "-Ne" 0 "]; then appendLog" An error occurred while trying to export the $ {table} table of the database $ {database}. Stop: $ {dumpInfo} "myExit 9 else appendLog" export the $ {database} table $ {table} to $ {sqlPath} success :$ {dumpInfo}; time consumed: from $ {timeStart} to $ {timeEnd} "tail -- lines = 10" $ {sqlPath} "| grep" \-Dump completed "2> & 1>/dev/ null if ["$? "-Ne" 0 "]; then appendLog" The 'dump completed' flag is not found. Check the Dump file $ {sqlPath }, log on to ssh to check whether the file is successfully backed up. "else appendLog" detects the 'dump completed' flag in the backup file content, dump File $ {sqlPath} should have been backed up successfully. "fi donedoneappendLog" \ n ------ database backup is complete ------ \ n "# Start compression, put compression at the end of the backup to prevent compression from taking too long. If the lock table appears, will affect the website running sqls =$ (ls -- almost-all -- ignore-backups -- indicator-style = slash-1 $ {todayRoot }*/*. SQL 2> & 1) for path in $ sqls; do # The path contains the absolute path. The tar command also requires To improve sqlDir =$ (dirname $ path) SQL =$ (basename $ path) tarInfo = $ (tar -- create -- remove-files -- bzip2 -- absolute-names -- directory = "$ {sqlDir}" -- add-file = "$ {SQL}" -- file = "zookeeper path).tar.bz2 ") if ["$? "-Ne" 0 "]; then appendLog" An error occurred while compressing and deleting the $ {path} file: \ n $ {tarInfo} "else appendLog" compressed and deleted $ {path} "fidoneappendLog" ------ compress completed ----- "# start to clean up backups larger than x days daysDir =$ (ls -- almost-all -- ignore-backups -- indicator-style = slash-1 "$ {backupRoot}" 2> & 1) for bkDir in $ daysDir; do bkDir = "$ {backupRoot }$ {bkDir}" if [! -D "$ {bkDir}"]; then appendLog ": when you are about to delete an expired backup, 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 about to delete the expired Backup directory, it is detected that the directory to be deleted is not a 10-digit number. skip this step: $ {bkDir} "continue fi outDay =$ (date -- date ="-$ {deleteRootOutDays} day "" + % Y % m % d00 ") # if the file time is earlier than the expiration time, force the entire directory to be deleted if ["$ {dirName}"-lt "$ {outDay}"]; then rmInfo = $ (rm -- force -- preserve-root -- recursive "$ {bkDir}" 2> & 1) appendLog "found an expired Backup directory $ {bkDir }, it has been more than $ {deleteRootOutDays} days, that is, less than $ {outDay} Will be deleted and forcibly deleted (0 is successful): $ {?} $ {RmInfo}; "fidoneappendLog" ------ clear the expired backup folder ---- "appendLog" space usage: \ n $ (df-h) "appendLog" current space occupied by backup files: \ n $ (du-hs $ {todayRoot}) "myExit 0