价值3K的Percona XtraBackup全备与增量备份脚本

##

Percona XtraBackup全备与增量备份脚本

黑心商人，当时给客户3K，
这个案例是每天一次全备，每小时一次增备，保留7天

1、全备脚本

[root@mydb backup]# cat fullbackup.sh
#!/bin/bash

BAK_DIR_ROOT="/mysql/backup"
MYSQL_USERNAME="root"
MYSQL_PASSWORD="root"
MYSQL_CNF="/mysql/data/3306/my.cnf"

BAK_FULL_DIR=$BAK_DIR_ROOT/mysqlbackup`(date +%Y%m%d)`/mysqlfullbackup
BAK_LOG="$BAK_DIR_ROOT/mysqlbackup`(date +%Y%m%d)`/backup.log"

if [[ ! -d ${BAK_FULL_DIR} ]]; then
    mkdir -p ${BAK_FULL_DIR}
fi

xtrabackup --defaults-file=${MYSQL_CNF} --user=${MYSQL_USERNAME} --password=${MYSQL_PASSWORD} --backup --stream=xbstream --extra-lsndir="${BAK_FULL_DIR}/LSN_INFO" --target-dir=${BAK_FULL_DIR} --parallel=2 1> "${BAK_FULL_DIR}/mysql_backup_full.xbstream" 2>> ${BAK_LOG}
[root@mydb backup]#
复制

2、增量脚本

[root@mydb backup]# cat incrbackup.sh
#!/bin/bash

BAK_DIR_ROOT="/mysql/backup"
MYSQL_USERNAME="root"
MYSQL_PASSWORD="root"
MYSQL_CNF="/mysql/data/3306/my.cnf"

BAK_FULL_DIR=$BAK_DIR_ROOT/mysqlbackup`(date +%Y%m%d)`/mysqlfullbackup
BAK_LOG="$BAK_DIR_ROOT/mysqlbackup`(date +%Y%m%d)`/backup.log"

ti=`(date +%H)`
ti1=$((10#$ti - 0))
ti2=$((ti1 - 1))

#BASE_DIR=$BAK_DIR_ROOT/mysqlbackup`(date +%Y%m%d)`/mysqlincbackup${ti2}
INC_DIR=$BAK_DIR_ROOT/mysqlbackup`(date +%Y%m%d)`/mysqlincbackup${ti1}


if [[ ! -d ${INC_DIR} ]]; then
    mkdir -p ${INC_DIR}
fi

if [[ ${ti2} -eq 0 ]];then
    BASE_DIR=${BAK_FULL_DIR}
else
    BASE_DIR=$BAK_DIR_ROOT/mysqlbackup`(date +%Y%m%d)`/mysqlincbackup${ti2}
fi

xtrabackup --defaults-file=${MYSQL_CNF} \
           --user=${MYSQL_USERNAME} \
           --password=${MYSQL_PASSWORD} \
           --backup --stream=xbstream \
           --target-dir=${INC_DIR} \
           --incremental-basedir="${BASE_DIR}/LSN_INFO" \
           --extra-lsndir="${INC_DIR}/LSN_INFO" > "${INC_DIR}/mysqlincbackup${ti1}.xbstream" 2>> ${BAK_LOG}
[root@mydb backup]#
复制

3、清理脚本

[root@mydb backup]# cat cleanbackup.sh
#!/bin/bash

BAK_DIR_ROOT="/mysql/backup"

find ${BAK_DIR_ROOT} -type d -mtime +7 -name "mysqlbackup*" -exec rm -rf {} \;
复制

4、crontab任务

[root@mydb backup]# crontab -l
5 0 * * * source ~/.bash_profile;/usr/bin/sh /mysql/backup/fullbackup.sh
5 1-23 * * * source ~/.bash_profile;/usr/bin/sh /mysql/backup/incrbackup.sh
30 0 * * * source ~/.bash_profile;/usr/bin/sh /mysql/backup/cleanbackup.sh

[root@mydb backup]# cat /etc/crontab
SHELL=/bin/bash
PATH=/sbin:/bin:/usr/sbin:/usr/bin:/mysql/app/xtrabackup/bin
MAILTO=root
复制

这里是二进制安装的xtrabackup，所以环境变量需要设置，且xtrabackup的执行路径已经加入到root的~/.bash_profile中了

5、测试

先设置时间

[root@mydb backup]# date -s "Fri Jul 28 00:04:45 CST 2023"
Fri Jul 28 00:04:45 CST 2023
复制

再重启和加载crond

[root@mydb backup]# systemctl restart crond
[root@mydb backup]# systemctl reload crond
复制

检查crond的日志

tail -100f /var/log/cron
复制

6、其他crontab工具

https://tooltt.com/crontab/c/102.html

7、脚本问题

（上面的已经改正确了）

脚本报告错误：

2023-08-08T00:05:29.327767+08:00 0 [ERROR] [MY-011825] [Xtrabackup] cannot open /mysql/backup/mysqlbackup20230808/mysqlincbackup-1/LSN_INFO//xtrabackup_checkpoints
2023-08-08T00:05:29.327787+08:00 0 [ERROR] [MY-011825] [Xtrabackup] failed to read metadata from /mysql/backup/mysqlbackup20230808/mysqlincbackup-1/LSN_INFO//xtrabackup_checkpoints
复制

原因是在增量备份脚本中，

[root@mydb01 backup]# cat date.sh
#!/bin/bash

ti=`(date +%H)`
ti1=$((ti - 0))
ti2=$((ti1 - 1))

echo ${ti}
echo ${ti1}
echo ${ti2}
复制

测试

[root@mydb01 backup]# date -s "Tue Aug 8 8:16:56 CST 2023"
Tue Aug  8 08:16:56 CST 2023
[root@mydb01 backup]# ./date.sh
./date.sh: line 4: 08: value too great for base (error token is "08")
08

-1
复制

在8点的时候，就会出问题，
而0x对于shell来说，会解析成8进制，08超过了8进制的表示范围，所以报错：value too great for base；

解决方案：
指定其为10进制即可，在结果前加上10#
ti1=((10#ti - 0))

8、恢复脚本

#!/bin/bash

#xbstream fullbackup
cd mysqlfullbackup
/mysql/app/percona-xtrabackup-8.0.33-28-Linux-x86_64.glibc2.17/bin/xbstream -x < mysql_backup_full.xbstream
cd ..

#xbstream incbackup
numdirall=`ls -l |grep -c "^d"`
ti1=$((10#$numdirall - 0))
numdir=$((ti1 - 1))
#echo ${numdir}

for ((i=1;i<=${numdir};i++))
 do
   cd mysqlincbackup${i}
   /mysql/app/percona-xtrabackup-8.0.33-28-Linux-x86_64.glibc2.17/bin/xbstream -x < mysqlincbackup${i}.xbstream
   sleep 2
   cd ..
 done

##prepare##
#1.prepare fullbackup
/mysql/app/percona-xtrabackup-8.0.33-28-Linux-x86_64.glibc2.17/bin/xtrabackup --prepare --apply-log-only --target-dir=mysqlfullbackup

#2.prepare incbackup
for ((j=1;j<${numdir};j++))
 do
  /mysql/app/percona-xtrabackup-8.0.33-28-Linux-x86_64.glibc2.17/bin/xtrabackup --prepare --apply-log-only --target-dir=mysqlfullbackup --incremental-dir=mysqlincbackup${j}
   sleep 2
 done

#3.prepare last incbackup
/mysql/app/percona-xtrabackup-8.0.33-28-Linux-x86_64.glibc2.17/bin/xtrabackup --prepare --target-dir=mysqlfullbackup --incremental-dir=mysqlincbackup${numdir}

#4.prepare all data fullbackup
/mysql/app/percona-xtrabackup-8.0.33-28-Linux-x86_64.glibc2.17/bin/xtrabackup --prepare --target-dir=mysqlfullbackup

##rsync data
rsync -avrP mysqlfullbackup/* --exclude='xtrabackup_*' /mysql/data/3306/data

#edit privileges
chown -R mysql:mysql /mysql/data/3306/data/*
chmod -R 775 /mysql/data/3306/data/*
复制

如何使用
拷贝脚本到备份目录，然后更改数据目录和xtrabackup的目录，执行脚本./ircrestore.sh > ircrestore.log 2>&1