Two sets of MySQL backup scripts

Data backup its importance needless to say, we need at least two kinds of backup: A logical backup (mysqldump generated SQL file), a physical backup (Xtrabackup can be done well). Logical backups provide finer-grained recovery and contrast in the case of problems, and physical backups are much more efficient at restoring the entire library range or increasing the amount of data from the library.

First look at the logical backup script, relatively simple

#!/bin/sh## #每天运行一次 # # #定义用户   Password   backup directory information user=mysqldumppsd=mysqldumpbackup_base=/data/mysql_backupdate= ' date +%y%m%d ' old_date= ' date +%y%m%d -d -30days '     ## #保存的天数 # # #获取库名, Exclude libraries that do not need to be backed up for db in  ' mysql -u$user -p$psd -s -n -e  ' show  Databases "|grep -v -e " (Test|percona|information_schema|performance_schema) "' do  ### Create backup directory   if [ ! -d  $backup _base/$db  ];then    mkdir   $backup _base/$db   fi   cd  $backup _base/$db   chattr -i  ./*      rm -f  $old _date.sql* &>/dev/null       #删除30天以前的   mysqldump -u$user -p$psd --events --routines    $db  |gzip >  $date. sql.gz    ## #备份 & Compression   chattr +i  ./*    ## #这里加了个防误删的属性done 

Next look at the physical (whole library) backup script based on Xtrabackup. Compared to the above script, this is interesting (mainly in the generation of backups followed by a rule, and then in the recovery script in the logic to determine the implementation of any time can be a key recovery)

Three scripts innobackupex_all.sh, innobackupex_increment.sh, innobackupex_restore.sh for the complete set of functions

We know that Xtrabackup can back up the full amount of data for the first time and then set up an incremental backup. The Linux crontab with the number 0-6 represents "Sunday"-"Saturday". So, I have a week-long cycle: Sunday is fully prepared, stored in 0 directories, from Monday to Saturday to be added, stored in 1-6 directories; the next Sunday empty directory is fully prepared.

The backup data is stored in 0-6 such directory names to make the recovery script innobackupex_restore.sh easier to handle. The xtrabackup recovery process can be performed correctly, regardless of the weeks of execution of the recovery script .

According to Percona official documentation. Xtrabackup backup requires at least the following permissions:GRANT RELOAD, SUPER, LOCK TABLES, REPLICATION CLIENT, CREATE tablespace on *. * to ' Xtrabackup ' @ ' localhost ' identified by xxxx

The scheduled tasks are set up as follows

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/74/9C/wKiom1YjllLjfwWHAABUb2Zxa3Q422.jpg "title=" 456. PNG "alt=" wkiom1yjllljfwwhaabub2zxa3q422.jpg "/>

Fully prepared script innobackupex_all.sh

#!/bin/sh## #每周日凌晨4点, perform full backup BASE_DIR=/LJK/DATARM-RF $base _dir/* 2>> $base _dir/all.log # # #清空base_dir, execute full standby # # # Back up and log records under Base_dir all.log/usr/bin/innobackupex--user=xtrabackup--password=xtrabackup $base _dir 2>> $base _ DIR/ALL.LOGCD $base _dir# will be renamed to ' 0 ' mv 20* 0 2>> $base _dir/all.log with ' current-time-named fully-prepared directory '

Add Script innobackupex_increment.sh

#!/bin/sh## #周1--Week 6 4 o'clock in the morning incremental backup base_dir=/ljk/datatoday= ' date +%u ' yesterday= ' expr $today-1 ' echo-e ' \n================== =================\n ">> $base _dir/increment.log # # #增备日志记录于 $base _dir/increment.log/usr/bin/innobackupex-- User=xtrabackup--password=xtrabackup--incremental $base _dir--incremental-basedir= $base _dir/$yesterday 2>> $ BASE_DIR/INCREMENT.LOGCD $base _dir# Rename the backup file to 0 1 2 3 4 5 6 for Sunday to Saturday MV 20* $today 2>> $base _dir/increment.log

Recovery Script innobackupex_restore.sh

#!/bin/sh## #检查上次命令执行是否成功的函数function  check {   if [ $? -ne 0  ];then        echo -e  "\e[1;31m\n $1 exec  Failed,please check it !\e[0m \n "        echo   "$1 failed,please check it !"         sleep 1         Exit -1   fi}base_dir=/ljk/databackup_dir= ' ls -l  $base _dir|grep -e  "^d . * "|awk  ' {print  $NF} ' echo -e " the backup dir is:  $backup _dir \n " sleep 1## #开始恢复cd   $base _direcho -e  "------  Prep stage  0 ------\ n" sleep 1/usr/ bin/innobackupex --apply-log --redo-only  $base _dir/0check  "preparation Stage  0"; # # #执行除去  0   and   Recovery preparation for directories other than the last directory   dir_num= ' ls -l  $base _dir|grep -e  "^d.*" |wc -l '        #取出有几个备份目录for  i in  ' seq 1  $ ($dir _num - 2) '   do    echo -e  '------  preparation stage   $i  ------ \n "    sleep 1    /usr/bin/innobackupex  --apply-log --redo-only  $base _dir/0 --incremental-dir= $base _dir/$i      check  Preparation stage   $i;d one## #最后一个增量备份echo  -e ------  Prep Stage  $ ($dir _num -  1)  ------ \n "sleep 1/usr/bin/innobackupex --apply-log  $base _dir/0 -- Incremental-dir= $base _dir/$ (($dir _num - 1)) check  "Preparation stage  $ (($dir _num - 1)"; # # # The above steps apply the changes recorded in all incremental backups to the original full-scale backup echo -e  "------  apply all changes to $base_dir/0 ------\ n"/usr/bin/ innobackupex --apply-log  $base _dir/0check  "Apply all changes to $base_dir/0"; # # #将数据考回数据目录echo  -e  "- -----  Get the processed data back to the data Catalog  ------\N "/usr/bin/innobackupex --copy-back  $base _dir/0check " Copy-back "; 

Physical backup directory Structure

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/74/9E/wKiom1YkT2Hy8NGHAACa4O-rEnA967.jpg "title=" 123. PNG "alt=" wkiom1ykt2hy8nghaaca4o-rena967.jpg "/>

This article is from "Endeavor K" blog, please be sure to keep this source http://kaifly.blog.51cto.com/3209616/1704105

Two sets of MySQL backup scripts