MySQL full-scale and incremental backup, based on Xtrabackup 2.3.5

Yesterday a PGP encryption disk broke, everything lost ... , My notes! How can I be 2?

Back up a script, convenient for later mobile phone view, saying I almost forgot the account password

Note: footstep recovery can only be restored to the state before the backup, if need to restore to the current state, but also to cooperate with Binlog, sleep like this

#!/bin/sh ##################################################### Author:jusonsummer # # VERS              ion:v1.0.0 # # date:2016-08-13 # # CORPORATION: Fiberhome ##################################################### #基于xtrabackup 2.3.5 #星期四, Sunday full, other workdays daily increments   Backup, manual execution can be set Week_num to determine whether it is incremental or full Innobackupex=innobackupex #INNOBACKUPEX的命令 #mysql目标服务器以及备份用户及密码 mysql_user=rootmysql_password=rootmysql_host=localhostmysql_port=3306 my_cnf=/etc/my.cnf MYSQL_CMD= "--user=$               Mysql_user--password= $MYSQL _password--host= $MYSQL _host--port= $MYSQL _port "tmp_log="/var/log/backup.$$.log "                # $$ Current Shell process number Backup_dir=/mysql_backup # Backup's home directory fullbackup_dir= $BACKUP _dir/full # Full Library backup directory incrbackup_dir= $BACKUP _dir/incre # Incremental backup directory backup_log_dir= $BACKUP _dir/log # Standby Log directory logfile_name=backup_ ' date +%y%m%d%h%m%s '. Log # BACKUP log file name # week week_num= ' date +%w ' #WEEK_NUM =0# new fully-prepared and differential backup directory mkdir-p $FULLBACKUP _dir mkdir-p $INCRBAC Kup_dir mkdir-p $BACKUP _log_dir #colour levelsetcolor_success= "Echo-en \\033[1;32m" setcolor_failure= "Echo-en \\033[1 31m "setcolor_warning=" Echo-en \\033[1;33m "setcolor_normal=" Echo-en \\033[0;39m "#日志输出函数LogMsg () {time= ' date" +%d %T "' echo" [$time]: info: $* "echo" [$time]: info: $* ">> $BACKUP _log_dir/$LOGFILE _name $SETCO        Lor_normal}logwarnmsg () {time= ' date "+%d%T" ' $SETCOLOR _warning echo "[$time]: WARN: $*" $SETCOLOR _normal}logsucmsg () {time= ' date "+%d%T" ' $SETCOLOR _success echo "[$time]: SUCCESS: $*" Ech O "[$time]: SUCCESS: $*" >> $BACKUP _log_dir/$LOGFILE _name $SETCOLOR _normal}logerrormsg () {time= ' date "+%d%T" ' $SETCOLOR _failure echo "[$time]: Error: $*" $SETCOLOR _normal} # Check Innobackupex error Output che    Ck_innobackupex_fail () {#保留一份备份的详细日志 cat $TMP _log>> $BACKUP _log_dir/$LOGFILE _name If [-z] ' Tail-2 $TMP _log | grep ' completed ok! ' `" ] ;  Then logerrormsg "$INNOBACKUPEX command execution failed" rm-f $TMP _loglogerrormsg "error log see $BACKUP _log_dir/$LOGFILE _name" Exit 1 FI} # Option detects $innobackupex-v if [' echo $? '-ne 0]; Then logerrormsg "$INNOBACKUPEX command does not exist, confirm whether the installation of Xtrabackup or environment variable is configured" Exit 1 fi portnum00= ' Netstat-lnt|grep    ${mysql_port}|wc-l ' if [$PORTNUM = 0];then logerrormsg "MySQL is turned off, please open MySQL." Exit 1fi if! ' Echo ' exit ' | Mysql-s $MYSQL _cmd--show-warnings=false ';    Then logerrormsg "The database has an incorrect configuration in the user name, password, hostname, or port!" Exit 1 Fi # Backup header information logmsg "----------------------------------------------" logmsg "$0:mysql backup Script" logmsg "----- -----------------------------------------"Logmsg" begins with: ' Date +%f ' '%T ' "#查找最新的完全备份文件夹 (excluding parent directory) Latest_full_b Ackup= ' Find $FULLBACKUP _dir-mindepth 1-maxdepth 1-type d-printf "%p\n" | Sort-nr | Head-1 ' # Find the most up-to-date full-Library backup time, dayPeriod format: 2016-07-20latest_full_backup_created_date= ' stat-c%y $FULLBACKUP _dir/$LATEST _full_backup | Cut-c 1-10 ' #星期一, Tuesday, Wednesday, Friday, Saturday perform an incremental backup if [$WEEK _num-eq 1-o $WEEK _num-eq 2-o $WEEK _num-eq 3-o $WEEK _num-eq 5-o $WEEK _num-eq 6]; Then Logmsg "************************************" logmsg "performing an incremental backup ...      Please wait a moment ... " Logmsg "************************************" #无全备时, you need to do a fully prepared if [-Z $LATEST _full_backup]; Then logerrormsg "The server has not yet done a full backup, please do a full preparation according to the installation documentation!" "Exit 1 Fi # New directory under incremental backup directory with latest full directory name logmsg" Incremental backup is placed in $incrbackup_dir/$LATEST _full_backup directory "new_in crdir= $INCRBACKUP _dir/$LATEST _full_backup mkdir-p $NEW _incrdir # Find out if the latest incremental backup exists. Specifies the path of a backup as the basis for an incremental backup LATEST _incr_backup= ' Find $NEW _incrdir-mindepth 1-maxdepth 1-type d-printf "%p\n" | Sort-nr | Head-1 ' if [! $LATEST _incr_backup];           Then incrbasedir= $FULLBACKUP _dir/$LATEST _full_backup logmsg "Incremental backup will be based on $incrbasedir backup set as backup base"   Else           incrbasedir= $INCRBACKUP _dir/$LATEST _full_backup/$LATEST _incr_backup logmsg "Incremental backup will be prepared with $incrbasedir backup set  Basis "fi $INNOBACKUPEX--defaults-file= $MY _cnf $MYSQL _cmd--incremental $NEW _incrdir--incremental-basedir $INCRBASEDIR > $TMP _log 2>&1 check_innobackupex_fail logmsg "database successfully incremental backup to: $NEW _incrdir" #星期四, Sunday performing full-scale backup Eli f [$WEEK _num-eq 0-o $WEEK _num-eq 4]; Then Logmsg "************************************" logmsg "performing a completely new full backup ...      Please wait a moment ... " Logmsg "************************************" $INNOBACKUPEX--defaults-file= $MY _cnf $MYSQL _cmd $FULLBACKUP _dir> $ Tmp_log 2>&1 check_innobackupex_fail logmsg "Database successful full backup to: $FULLBACKUP _dir" fi #删除过期的全量备份以及增量备份, expiry time For 7 days Logmsg "Look for outdated backup files ...   Please wait a moment ... " Expire_file= ' Find $FULLBACKUP _dir/-mindepth 1-maxdepth 1-type d-mtime +7-printf "%p\t" ' If [-Z ' $EXPIRE _file '];the N logmsg "could not find an expired backup file that can be deleted" Else for efile in ' Find $FULLBACKUP _dir/-mindepth 1-maxDepth 1-type d-mtime +7-printf "%p\n" ' do logmsg ' delete expired full file: $efile "Rm-rf $FULLBACKUP _dir/$EFILELOGMSG   "Delete outdated incremental backup files: $efile" Rm-rf $INCRBACKUP _dir/$efile done fi logsucmsg "Backup completed: ' Date +%f '%T '" Exit 0

　　

MySQL full-scale and incremental backup, based on Xtrabackup 2.3.5