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

2024年 1月 30日 73.6k 0

##

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

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论