Shulou(Shulou.com)06/01 Report--

The MySQL of the production environment schedules hot backup scripts to back up data regularly through crontab. At present, hot standby is realized through XtraBackup software. For hot backup scripts, please check out my previous blog, "using shell to achieve mysql automatic complete, add & Log backup": http://linzhijian.blog.51cto.com/1047212/1891745, which is not explained here.

Backup storage: through the streaming backup of XtraBackup, the backup is stored on the backup server.

Backup strategy: full on Sunday and additional from Monday to Saturday.

Currently missing: these backup data can not achieve validity check, can not find out whether these backups have availability, need to achieve effectiveness testing through a certain mechanism.

The list of backup files on the backup machine is as follows:

Drwxr-xr-x 18 mysql mysql 4096 Apr 9 03:28 mysql01_20170409_023001_fulldrwxr-xr-x 18 mysql mysql 4096 Apr 10 03:25 mysql01_20170410_023001_incrdrwxr-xr-x 18 mysql mysql 4096 Apr 11 03:26 mysql01_20170411_023001_incrdrwxr-xr-x 18 mysql mysql 4096 Apr 12 03:25 mysql01_20170412_023001_incrdrwxr-xr-x 18 mysql mysql 4096 Apr 13 03:26 mysql01_20170413_023001_incrdrwxr-xr-x 18 mysql mysql 4096 Apr 14 03:26 mysql01_20170414_023001 _ incrdrwxr-xr-x 18 mysql mysql 4096 Apr 15 03:27 mysql01_20170415_023001_incrdrwxr-xr-x 18 mysql mysql 4096 Apr 16 03:29 mysql01_20170416_023001_fulldrwxr-xr-x 18 mysql mysql 4096 Apr 17 03:26 mysql01_20170417_023001_incr

Script implementation logic: automatically recover the complete data, and then restore the rest of the additional data to the complete data in turn. Finally, pull the recovered complete data with mysqld, and check whether there are any abnormal errors in the error log of MySQL to determine whether the recovery is normal.

Vim dbrecover.sh

#! / bin/shif [$#-ne 1] then echo "usage: `basename $0` [mysql01 | mysql02]" exit 1fihostnamekeeper exit 1today= `date +% Y% m% d`sh / home/mysql/shell/mysql_recover.sh $hostname $today

Vim mysql_recover.sh

#! / bin/shif [$#-ne 2] then echo "usage: `date $0` [mysql01 | mysql02 | mysql03] 20170501" exit 1fihostnamekeeper: "mysql02" # today= `date +% Y% m% d`date = $2birthday = `date +% w`week = `date-d $today +% w`time1 = `date +% s`timestamp = `date +% Y% m% d% H% M% S`logdir = "/ home/mysql/log/mysqlrecoverlog/$hostname/$timestamp" dir= "/ mysqlbackup/databak/$hostname/" fullname= "$dir/full_backup_file.txt" incrname= "$dir/incr_backup_file.txt" Datadir= `grep datadir / etc/my.cnf | awk-F\ ='{print $NF} '`errlog= `grep log-error / etc/my.cnf | awk-F\ =' {print $NF} '`n1 = "0" # what day of the week It is 1: 6 from Monday to Saturday and 0 on Sunday. N2 = "6" # the last day of the week, 1: 6 from Monday to Saturday and 0 on Sunday. Mkdir $logdir # # create a log directory function getdir () {if [$week-eq "$N1"] then fulldir= `find / mysqlbackup/databak/$ {hostname} /-type d-name "* ${today} * full" `# / mysqlbackup/databak/mysql01/mysql01_20170430_023001_full if [!-n "$fulldir"] then echo "the fulldir not logging directory!" > $logdir/recover_$ {timestamp} .log exit 1 fi num= `find / mysqlbackup/databak/$ {hostname} /-type d-name "* ${today} * full" | wc-l` # / mysqlbackup/databak/mysql01/mysql01_20170429_023001_incr if [$num-eq "1"] then echo $fulldir > $fullname else echo "there are not only full dbbackup in $today Please checkboxes! "> $logdir/recover_$ {timestamp} .log exit 1 fi else incrdir= `find / mysqlbackup/databak/$ {hostname} /-type d-name" * ${today} * incr "`if [!-n" $incrdir "] then echo" the incrdir not checklists found! "> $logdir/recover_$ {timestamp} .log fi num= `find / mysqlbackup/databak/$ {hostname} /-type d-name" * ${today} * incr "| wc-l` if [$num-eq" 1 "] then echo $incrdir > $incrname else echo" there are not only incr dbbackup in $today Please checklists created! "> $logdir/recover_$ {timestamp} .log exit 1 fi fi} function uncompress () {dir=$1 / usr/bin/innobackupex-- decompress-- parallel=8$ dir > > $logdir/uncompress_$ {timestamp} .log 2 > & 1 success_flag= `cat $logdir/uncompress_$ {timestamp} .log | grep" completed OK "`if [- n success_flag] then echo" $dir decompress sucessfully! "> $logdir/recover_$ {timestamp} .log else Echo "$dir decompress failed" > > $logdir/recover_$ {timestamp} .log exit 1 fi} function full_recover () {fullbakdir=$1 uncompress $fullbakdir / usr/bin/innobackupex-- use-memory=2G-- apply-log-- redo-only $fullbakdir > > $logdir/full_recover_$ {timestamp} .log 2 > & 1 success_flag= `cat $logdir/full_recover_$ {timestamp} .log | grep "innobackupex: completed OK" `if [- n "$success_flag"] Then echo "the full dbbackup $fullbakdir recovery is success!" > > $logdir/recover_$ {timestamp} .log else echo "the full dbbackup $fullbakdir recovery is fail!" > > $logdir/recover_$ {timestamp} .log exit 1 fi} function incr_recover () {incrbakdir=$1 fullbakdir=$2 uncompress $incrbakdir uncompress $fullbakdir if [$week-ne "$N2"] then / usr/bin/ Innobackupex-- use-memory=2G-- apply-log-- redo-only-- incremental-dir=$incrbakdir $fullbakdir > > $logdir/incr_recover_$ {timestamp} .log 2 > & 1 else / usr/bin/innobackupex-- use-memory=2G-- incremental-dir=$incrbakdir $fullbakdir > > $logdir/incr_recover_$ {timestamp} .log 2 > & 1 fi success_flag= `cat $logdir/incr_recover_$ {timestamp} .log | grep "innobackupex: completed OK" `if [- n "$success_flag"] Then echo "the incr dbbackup $incrbakdir recovery is success!" > > $logdir/recover_$ {timestamp} .log else echo "the incr dbbackup $incrbakdir recovery is fail!" > > $logdir/recover_$ {timestamp} .log exit 1 fi} function mysqlrecover () {fullbakdir=$1 # uncompress $fullbakdir rm-fr $datadir / bin/ln-s $fullbakdir $datadir chown-R mysql:mysql $datadir Chown-R mysql:mysql $fullbakdir / sbin/service mysqld start error_flag= `grep-I error $errlog` if [- z "$error_flag"] then echo "the mysqld don't report error Mysql recover is success! "> > $logdir/recover_$ {timestamp} .log else echo" the mysqld report error, mysql recover is fail Please check! "> $logdir/recover_$ {timestamp} .log exit 1 fi / sbin/service mysqld stop} getdirif [$week-eq" $N1 "] then full= `cat $fullname`full_recover $fullelse incr= `cat $fullname`full= `cat $fullname`incr_recover $incr $full if [$week-eq" $N2 "] then mysqlrecover $full fifitime2= `date +% s`times = $((${time2}-${time1}) echo" it takes $times seconds to finish the recoverables! "> $logdir/recover_$ {timestamp} .log

The backup machine currently installs MySQL in a binary package, uploads it directly to the / usr/local/mysql directory, and configures the corresponding / etc/profile and / etc/my.cnf.

Cat / etc/my.cnf

[mysqld] datadir=/mysqlbackup/mysql_testsocket=/mysqlbackup/mysql_test/mysql.sockuser=mysql# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links= 0[mysqld _ safe] log-error=/mysqlbackup/mysql_test/mysqld.logpid-file=/mysqlbackup/mysql_test/mysqld.pid

The generated log file is as follows:

-rw-r--r-- 1 root root 613918 May 409: 34 incr_recover_20170504092501.log-rw-r--r-- 1 root root 307 May 409: 34 recover_20170504092501.log-rw-r--r-- 1 root root 259310 May 409: 26 uncompress_20170504092501.log

Where:

Uncompress_20170504092501.log: log information generated when unzipping backup files

Log information generated when incr_recover_20170504092501.log:XtraBackup applies backup files

Recover_20170504092501.log: restore the brief log information recorded by the backup file at this time

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.