Xtrabackup automatic backup script and xtrabackup script

Xtrabackup automatic backup script and xtrabackup script

# Backup. sh #! /Bin/sh # on xtrabackup 2.2.8 # When you execute it for the first time, it will check whether there is a full backup. Otherwise, create a full database backup first. # When you run it again, it will perform Incremental Backup Based on the settings in the script based on the previous full or Incremental Backup # ocpyang@126.comINNOBACKUPEX _ PATH = innobackupex # INNOBACKUPEX command INNOBACKUPEXFULL =/usr/local/xtrabackup/bin/$ INNOBACKUPEX_PATH # INNOBACKUPEX command path # mysql target server and username and password MYSQL_CMD = "-- host = 192.168.5.189 -- user = root -- password = password -- port = 3306" MYSQL_UP = "-- user = root -- password = 'Password' -- port = 3306" # Mysqladmin username and password TMPLOG = "/tmp/innobackupex. $. log "MY_CNF =/usr/local/mysql/my. cnf # mysql configuration file MYSQL =/usr/bin/mysqlMYSQL_ADMIN =/usr/bin/mysqladminBACKUP_DIR =/backup # backup home directory FULLBACKUP_DIR = $ BACKUP_DIR/full # full Database backup Directory INCRBACKUP_DIR = $ BACKUP_DIR/incret # Incremental Backup Directory FULLBACKUP_INTERVAL = 86400 # Full-Database Backup interval, time: seconds KEEP_FULLBACKUP = 1 # retain at least several full-Database backups logfiledate = backup. 'date every policyymm1_d1_h1_m'.txt # Start Time STARTED_TIME = 'date + % s '##### ######################################## ################################# Display an error and exit ### ######################################## ################################## error () {echo "$1" 1> & 2 exit 1} # Check the execution environment if [! -X $ INNOBACKUPEXFULL]; then error "$ INNOBACKUPEXFULL is not installed or is not linked to/usr/bin." fi if [! -D $ BACKUP_DIR]; then error "backup destination folder: $ BACKUP_DIR does not exist." fi mysql_status = 'netstat-nl | awk' NR> 2 {if ($4 ~ /. *: 3306/) {print "Yes"; exit 0} ''if [" $ mysql_status "! = "Yes"]; then error "MySQL does not start running." fi if! 'Echo 'exit '| $ MYSQL-s $ MYSQL_CMD'; the database username or password provided by then error is incorrect! "Fi # backup header information echo" ---------------------------- "echoecho" $0: MySQL backup script "echo" started: 'date + % f'' % t' % W' "echo # create the directory mkdir-p for full backup and differential backup $ FULLBACKUP_DIRmkdir-p $ INCRBACKUP_DIR # Find the latest full backup LATEST_FULL_BACKUP = 'Find $ FULLBACKUP_DIR-mindepth 1-maxdepth 1-type d-printf "% P \ n" | sort-nr | head-1 '# find the latest backup time LATEST_FULL_BACKUP_CREATED_TIME = 'stat-c % Y $ FULLBACKUP_DIR/$ LATEST_FULL_BACKUP '# If the full backup is valid for Incremental backup, otherwise Full row backup if ["$ LATEST_FULL_BACKUP"-a 'expr $ LATEST_FULL_BACKUP_CREATED_TIME + $ FULLBACKUP_INTERVAL + 5'-ge $ STARTED_TIME]; then # If the latest full backup has not expired, name the latest full backup file in the Incremental Backup Directory and create the echo-e directory. Full backup $ LATEST_FULL_BACKUP has not expired, use $ LATEST_FULL_BACKUP as the base directory name for Incremental Backup "echo" "NEW_INCRDIR = $ INCRBACKUP_DIR/$ LATEST_FULL_BACKUPmkdir-p $ NEW_INCRDIR # to check whether the latest Incremental Backup exists. specify a backup path as the basis for Incremental Backup LATEST_INCR_BACKUP = 'Find $ NEW_INCRDIR-mindepth 1-max Depth 1-type d-printf "% P \ n" | sort-nr | head-1 'if [! $ LATEST_INCR_BACKUP]; region = $ FULLBACKUP_DIR/$ region-e "Incremental Backup uses $ INCRBASEDIR as the backup base directory" echo "" region = $ INCRBACKUP_DIR/$ {LATEST_FULL_BACKUP}/$ {LATEST_INCR_BACKUP} echo- e "Incremental Backup uses $ INCRBASEDIR as the backup base directory" echo "" fiecho "and $ INCRBASEDIR as the base directory for this Incremental backup. "$ INNOBACKUPEXFULL -- defaults-file = $ MY_CNF -- use-memory = 4G $ MYSQL_CMD -- incremental $ NEW_INCRDIR -- incremental-basedir $ INCR BASEDIR> $ TMPLOG 2> & 1 # retain the Detailed backup log cat $ TMPLOG>/backup/$ logfiledateif [-z "'Tail-1 $ TMPLOG | grep' innobackupex: completed OK! ''"]; Then echo "$ INNOBACKUPEX command execution failed :"; echo-e "---------- $ INNOBACKUPEX_PATH error ----------" cat $ TMPLOG rm-f $ TMPLOG exit 1 fiTHISBACKUP = 'awk -- "/Backup created in directory/{split (\ $ 0, p, \ "'\"); print p [2]} "$ TMPLOG 'rm-f $ TMPLOGecho-n" the database is successfully backed up: $ THISBACKUP "echo # The backup file starting point LATEST_FULL_BACKUP = 'Find $ FULLBACKUP_DIR-mindepth 1-maxdepth 1-type d-printf" % P \ n "| sort-nr | hea D-1 'new _ INCRDIR = $ INCRBACKUP_DIR/$ LATEST_FULL_BACKUPLATEST_INCR_BACKUP = 'Find $ NEW_INCRDIR-mindepth 1-maxdepth 1-type d-printf "% P \ n" | sort-nr | head-1 'res _ FULL_BACKUP =$ {FULLBACKUP_DIR}/$ {LATEST_FULL_BACKUP} RES_INCRE_BACKUP = 'dirname $ {region}/$ {LATEST_FULL_BACKUP}/$ {region} 'echoecho-e' \ e [31 m NOTE: -------------------------------------------------------------------------- -------. \ E [m' # Red echo-e "must retain all incremental backups in the $ KEEP_FULLBACKUP full backup (full backup) $ {RES_FULL_BACKUP} and $ {RES_INCRE_BACKUP} directories. "echo-e '\ e [31 m NOTE :---------------------------------------------------------------------------------. \ e [m' # Red echoelseecho "******************************* ** "echo-e" is executing a completely new full backup... please wait... "echo" ******************************** "$ INNOBACKUPEXFULL -- defaults-file = $ MY_CNF -- use-memory = 4G $ MYSQL_CMD $ FULLBACKUP_DIR> $ TMPLOG 2> & 1 # reserve a copy of the Detailed backup log cat $ TMPLOG>/backup/$ logfiledateif [-z "'Tail-1 $ TMPLOG | grep 'innobackupex: completed OK! ''"]; Then echo "$ INNOBACKUPEX command execution failed :"; echo-e "---------- $ INNOBACKUPEX_PATH error ----------" cat $ TMPLOG rm-f $ TMPLOG exit 1fi THISBACKUP = 'awk -- "/Backup created in directory/{split (\ $ 0, p, \ "'\"); print p [2]} "$ TMPLOG 'rm-f $ TMPLOGecho-n" the database is successfully backed up: $ THISBACKUP "echo # The backup file starting point LATEST_FULL_BACKUP = 'Find $ FULLBACKUP_DIR-mindepth 1-maxdepth 1-type d-printf" % P \ n "| sort-nr | he Ad-1 'res _ FULL_BACKUP =$ {FULLBACKUP_DIR}/$ {LATEST_FULL_BACKUP} echoecho-e' \ e [31 m NOTE: success :---------------------------------------------------------------------------------. \ e [m' # Red echo-e "without Incremental backup, $ KEEP_FULLBACKUP must be retained for full backup $ {RES_FULL_BACKUP }. "echo-e '\ e [31 m NOTE :---------------------------------------------------------------------------------. \ e [m' # Red echofi # Delete expired full backup echo-e "find expire backup file .... ....... Waiting ......... "echo-e" Search for expired full-backup files and delete them ">/backup/$ logfiledatefor efile in $ (/usr/bin/find $ FULLBACKUP_DIR/-mtime + 6) doif [-d $ {efile}]; thenrm-rf "$ {efile}" echo-e "delete expired full backup files: $ {efile} ">>/ backup/$ logfiledateelif [-f $ {efile}]; thenrm-rf" $ {efile} "echo-e" delete expired full-backup files: $ {efile} ">>/ backup/$ logfiledatefi; doneif [$? -Eq "0"]; then echo-e "the expired full backup file" fiechoecho "cannot be deleted: 'date + % f'' % t' % W' "exit 0 --------------------------- the execution result is as follows: Warning: Using a password on the command line interface can be insecure. ----------------------------. /backup. sh: the MySQL backup script starts: 16:35:11 5 *********************************** a new full backup... please wait... * ********************************** the database is successfully backed up: /backup/full/2015-04-03_16-35-11 NOTE :---------------------------------------------------------------------------------. no Incremental backup. You must retain one full backup (full backup/full/2015-04-03_16-35-11. NOTE :---------------------------------------------------------------------------------. find expire backup file ........... waiting ......... the expired full backup file that can be deleted is not found at 16:35:25. /backup. sh: MySQL backup script started at: 16:35:50 5 full backup 2015-04-03_16-35-11 has not expired, according to the name of 2015-04-03_16-35-11 as the base Directory Name of Incremental Backup <span style = "white-space: pre "> </span> Incremental backup uses/backup/full/2015-04-03_16-35-11 as the backup base Directory <span style =" white-space: pre "> </span> Use/backup/full/2015-04-03_16-35-11 as the base directory for this Incremental backup. the database is successfully backed up to:/backup/incret/2015-04-03_16-35-11/2015-04-03_16-35-50 NOTE :---------------------------------------------------------------------------------. you must retain one full backup (full backup/full/2015-04-03_16-35-11) and all incremental backups in the/backup/incret/2015-04-03_16-35-11 directory. NOTE :---------------------------------------------------------------------------------. find expire backup file ........... waiting ......... no expired full backup files can be deleted.