阮胜昌的技术记录站-LinuxMySQL数据库运维

功能说明：

整套功能需要 三个脚本 innobackupex_all.sh、innobackupex_increment.sh、innobackupex_restore.sh
我们知道xtrabackup可以第一次备份全量数据，之后设置增量备份。Linux crontab以数字0-6表示”周日”-“周六”。所以，我以一周为周期：周日进行全备，存放于0目录；周一至周六增备，存放于1-6目录；下次周日清空目录重新全备。
上面以0-6这样的目录名存放备份数据，是为了让恢复脚本innobackupex_restore.sh处理起来更方便。无论在周几执行恢复脚本，都能正确的执行xtrabackup的恢复过程。
依percona官方文档。xtrabackup备份至少需要以下权限：GRANT RELOAD, SUPER, LOCK TABLES, REPLICATION CLIENT, CREATE TABLESPACE ON \*.\* TO 'xtrabackup'@'localhost' identified by xxxx

计划任务设置如下 

0 4 * * 0 /bin/bash /shell/innobackupex_all.sh

0 4 * * 1-6 /bin/bash innobackupex_increment.sh

innobackupex_all.sh周天全量备份脚本
innobackupex_increment.sh 周一到周六增量备份脚本
innobackupex_restore.sh  从星期天到星期六全量自动恢复脚本

物理备份目录结构截图
[root@node7 increment]# pwd
/data/backup/increment
[root@node7 increment]# ls
0  1  2  3  4  5  6  log
[root@node7 increment]#

 [root@node7 shell_scripts]# cat innobackupex_all.sh
#!/bin/sh
MYCNF=/etc/my.cnf
MYSQL=/usr/bin/mysql
SOCKET=/var/lib/mysql/mysql.sock
USEROPTIONS="--user=root --password=Rscpass123."
base_dir=/data/backup/increment
LOGDIR=$base_dir/log
for i in $base_dir  $LOGDIR
do
        if [ ! -d $i ]; then
                mkdir -pv $i
        fi      
done
#clean base_dir ready for all backup database
for i in {0..6}
        do
                rm -rf $base_dir/$i 2>&1 | tee -a $LOGDIR/full_back_`date +%F`.log
        done
#start to full backup database
/usr/bin/innobackupex --defaults-file=$MYCNF  $USEROPTIONS  --safe-slave-backup --slave_info  --socket=$SOCKET   $base_dir 2>&1 | tee -a $LOGDIR/full_back_`date +%F`.log


cd $base_dir
#move full backupdir to dir_name 0
mv 20* 0  2>&1 | tee -a $LOGDIR/full_back_`date +%F`.log

[root@node7 shell_scripts]# cat innobackupex_increment.sh
#!/bin/sh
MYSQL=/usr/bin/mysql
SOCKET=/var/lib/mysql/mysql.sock
USEROPTIONS="--user=root --password=Rscpass123."
base_dir=/data/backup/increment
LOGDIR=$base_dir/log
for i in $base_dir  $LOGDIR
do
        if [ ! -d $i ]; then
                mkdir -pv $i
        fi       
done


today=`date +%u`
yesterday=`expr $today - 1`


echo -e "\n============================\n"  2>&1 | tee -a $LOGDIR/increment_`date +%F`.log
echo -e "\n==Start to increment backup database==\n" 2>&1 | tee -a $LOGDIR/increment_`date +%F`.log


/usr/bin/innobackupex --defaults-file=$MYCNF  $USEROPTIONS  --safe-slave-backup --slave_info  --socket=$SOCKET --incremental $base_dir --incremental-basedir=$base_dir/$yesterday  2>&1 | tee -a $LOGDIR/increment_`date +%F`.log


cd $base_dir
mv 20* $today  2>&1 | tee -a $LOGDIR/increment_`date +%F`.log
echo -e "\n==Finished to  increment backup database Directory is $today \n"  2>&1 | tee -a $LOGDIR/increment_`date +%F`.log

[root@node7 shell_scripts]# cat innobackupex_restore.sh
#!/bin/bash
MYCNF=/etc/my.cnf
MYSQL=/usr/bin/mysql
SOCKET=/var/lib/mysql/mysql.sock
USEROPTIONS="--user=root --password=Rscpass123."
base_dir=/data/backup/increment
LOGDIR=$base_dir/log
for ii in $base_dir  $LOGDIR
do
        if [ ! -d $ii ]; then
                mkdir -pv $ii
        fi       
done


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
    else
        echo -e "$1\n" 2>&1 | tee -a $LOGDIR/innobackupex_restore_`date +%F`.log
   fi
}


backup_dir=`ls -l $base_dir|grep -e "^d.*"|grep -vE "log"|awk '{print $NF}'`
echo -e "the backup dir is:\n$backup_dir"
sleep 1


if [ "$backup_dir" != '0' ];then
    echo -e "------ prepare to restore status code 0 ----\n" 2>&1 | tee -a $LOGDIR/innobackupex_restore_`date +%F`.log
    sleep 1
    /usr/bin/innobackupex  --defaults-file=$MYCNF  $USEROPTIONS  --safe-slave-backup --slave_info  --socket=$SOCKET --apply-log --redo-only $base_dir/0 2>&1 | tee -a $LOGDIR/innobackupex_restore_`date +%F`.log
    check "-Finished to prepare status code 0 ----";
    sleep 1


    dir_num=`ls -l $base_dir|grep -e "^d.*"|wc -l`
    for i in `seq 1 $(($dir_num - 2))`;do
       echo -e "------prepare to restore status code $i------ \n" 2>&1 | tee -a $LOGDIR/innobackupex_restore_`date +%F`.log
       sleep 1
       /usr/bin/innobackupex --defaults-file=$MYCNF  $USEROPTIONS  --safe-slave-backup --slave_info  --socket=$SOCKET --apply-log --redo-only  $base_dir/0 --incremental-dir=$base_dir/$i 2>&1 | tee -a $LOGDIR/innobackupex_restore_`date +%F`.log
       check "-Finished to prepare status code $i-";
        sleep 1
    done


        echo -e "-Apply all change to $base_dir/0 ---\n" 2>&1 | tee -a $LOGDIR/innobackupex_restore_`date +%F`.log
        sleep 1
        /usr/bin/innobackupex --defaults-file=$MYCNF  $USEROPTIONS  --safe-slave-backup --slave_info  --socket=$SOCKE  --apply-log $base_dir/0 2>&1 | tee -a $LOGDIR/innobackupex_restore_`date +%F`.log
        check "-finished to apply all change to $base_dir/0-\n"
        sleep 1




        echo -e "------copy back to mysql data dir -----\n" 2>&1 | tee -a $LOGDIR/innobackupex_restore_`date +%F`.log
        sleep 1
        /usr/bin/innobackupex --defaults-file=$MYCNF  $USEROPTIONS  --safe-slave-backup --slave_info  --socket=$SOCKE --copy-back $base_dir/0 2>&1 | tee -a $LOGDIR/innobackupex_restore_`date +%F`.log
        check "copy-back successfull";
        sleep 1
fi