##
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&1