概述
整理Mysql数据库备份脚本,用在生产环境数据库定时备份。
参考
链接: 安全管理MySQL凭证:使用mysql_config_editor设置login-path
创建MySQL凭证
创建凭证
- mysql_config_editor设置凭证
./mysql_config_editor set --login-path=local --host=localhost --socket=/data/mysqldb/tmp/mysql.sock --user=root --password
- 输入mysql 用户密码
Enter password:
- 查看创建的凭证
./mysql_config_editor print --all
[local]
user = "root"
password = *****
host = "localhost"
socket = "/public/mysqldb/tmp/mysql.sock"
验证凭证
./mysql --login-path=local
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.4.1 MySQL Community Server - GPL
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
创建备份脚本
脚本工作流程
- 初始化环境变量
- 定义脚本参数
- 根据参数,备份每个数据库并打包压缩备份文件
- 根据参数,删除超出保存期限的数据库备份文件
- 根据参数,删除超出保存期限的脚本执行日志
脚本内容
- 创建脚本文件
vim mysql_backup.sh
- 输入以下内容
#!/bin/bash
# 需要调试时,取消注释,前台运行脚本
#set -x
##################################################################################################################
#
# 备份mysql数据库脚本
# 1. 使用 mysql_config_editor定义mysql的user和password
# 2. 定义需要备份的数据库和保留时间
# 3. 备份定义的数据库并打包压缩
# 4. 备份成功后,删除旧备份文件和脚本日志
# 5. crontab -e 定时样例:
# 10 0 * * * /public/script/mysql_backup.sh >> /public/script/logs/mysql_$(date +"\%Y-\%m-\%d").log 2>&1 &
#
##################################################################################################################
# 定义环境变量,请确认使用路径正确
PATH=/usr/java/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin:/usr/local/mysql/bin:/usr/local/mysql/lib
export PATH
# 定义备份数据库名字的列表,多个数据库用空格分开。
DBS="db1 db2 db3"
# 定义脚本路径和备份存储路径
SCRIPT_HOME="/public/script"
BAK_HOME="/public/mysqlbackup"
# 检查是否存在日志目录
if [ ! -d "$SCRIPT_HOME/logs" ]; then
mkdir -p $SCRIPT_HOME/logs
fi
# 定义脚本变量
CURRENT_DATE=$(date +"%Y-%m-%d")
RETAIN_DAY=3 #保留最近几天的备份,最长不超过7天
LOG_RETAIN_DAY=14 # 脚本执行日志保留天数
LOG_FILE=$SCRIPT_HOME/logs/mysql_${CURRENT_DATE}.log
# 开始执行
DATE_TIME=$(date +"%Y-%m-%d %H:%M:%S")
echo -e "\n $DATE_TIME: #### Begin backup $DBS ###" >> $LOG_FILE
## 判断备份目录是否存在,不存时新建目录
[ ! -d $BAK_HOME ] && mkdir -p $BAK_HOME
cd $BAK_HOME #转到备份目录
## 循环备份DBS中定义的数据库
for DBNAME in $DBS; do
DATE_TIME=$(date +"%Y-%m-%d %H:%M:%S")
echo -e "\n $DATE_TIME: #### Start backup $DBNAME ###" >> $LOG_FILE
BACKUP_NAME=mysql_${DBNAME}_${CURRENT_DATE}
## 备份单个数据库,使用--login-path 方式认证
mysqldump --login-path=local --single-transaction $DBNAME > $BAK_HOME/${BACKUP_NAME}.sql
if [ $? -ne 0 ];then #$? 代表上一个shell命令的执行的返回值。0表示执行成功。其他表示错误。并将结果写入到日志中。
DATE_TIME=$(date +"%Y-%m-%d %H:%M:%S")
echo -e "\n $DATE_TIME: ${CURRENT_DATE} $DBNAME mysqldump failed">>$LOG_FILE
break
else
DATE_TIME=$(date +"%Y-%m-%d %H:%M:%S")
echo -e "\n $DATE_TIME: ${CURRENT_DATE} $DBNAME mysqldump sucess">>$LOG_FILE
fi
## 打包压缩备份文件(如果库大,压缩需要谨慎处理)
tar -zcf ${BACKUP_NAME}.tar.gz ${BACKUP_NAME}.sql
if [ $? -eq 0 ];then
rm -f ${BACKUP_NAME}.sql
DATE_TIME=$(date +"%Y-%m-%d %H:%M:%S")
echo " $DATE_TIME: Compress ${BACKUP_NAME}.tar.gz sucess">>$LOG_FILE
else
DATE_TIME=$(date +"%Y-%m-%d %H:%M:%S")
echo " $DATE_TIME: Compress ${BACKUP_NAME}.tar.gz failed">>$LOG_FILE
break
fi
## 如果存在上一步打包的压缩文件。查找保留天数-7天之间的旧备份压缩包并删除
if [ -f "${BACKUP_NAME}.tar.gz" ];then
for ((i=${RETAIN_DAY}; i<=7; i++)); do
RETAIN_DATE=$(date -d "-$i day" +"%Y-%m-%d")
DELETE_BACKUP_NAME=mysql_${DBNAME}_${RETAIN_DATE}
if [ -f "${DELETE_BACKUP_NAME}.tar.gz" ]; then
rm -f ${DELETE_BACKUP_NAME}.tar.gz
if [ $? -eq 0 ];then
DATE_TIME=$(date +"%Y-%m-%d %H:%M:%S")
echo " $DATE_TIME: Delete ${DELETE_BACKUP_NAME}.tar.gz sucess">>$LOG_FILE
else
DATE_TIME=$(date +"%Y-%m-%d %H:%M:%S")
echo " $DATE_TIME: Delete ${DELETE_BACKUP_NAME}.tar.gz failed">>$LOG_FILE
fi
fi
done;
fi
DATE_TIME=$(date +"%Y-%m-%d %H:%M:%S")
echo -e "\n $DATE_TIME: #### Finish backup $DBNAME ###" >> $LOG_FILE
done;
## 删除脚本执行历史日志
find $SCRIPT_HOME/logs -maxdepth 1 -mtime +$LOG_RETAIN_DAY -name "mysql_*.log" -execdir rm -rf {} \;
DATE_TIME=$(date +"%Y-%m-%d %H:%M:%S")
echo -e "\n $DATE_TIME: #### End backup ###" >> $LOG_FILE
- 脚本文件添加执行权限
chmod +x mysql_backup.sh
添加定时任务
- 编辑crontab定时任务
crontab -e
- 添加以下内容
10 0 * * * /public/script/mysql_backup.sh >> /public/script/logs/mysql_$(date +"\%Y-\%m-\%d").log 2>&1 &
- 手动执行验证
- 注意:手动执行时不需要 “\” 禁止转义
/public/script/mysql_backup.sh >> /public/script/logs/mysql_$(date +"%Y-%m-%d").log 2>&1 &
- 如果脚本执行失败,可以取消脚本中 #set -x 的注释,在前台执行脚本进行调试
- 查看执行结果
[root@localhost logs]# pwd
/public/script/logs
[root@localhost logs]# tail -n200 mysql_2024-08-01.log
2024-08-01 11:12:26: #### Begin backup db1 db2 db3 ###
2024-08-01 11:12:26: #### Start backup db1 ###
2024-08-01 11:12:26: 2024-08-01 db1 mysqldump sucess
2024-08-01 11:12:26: Compress mysql_db1_2024-08-01.tar.gz sucess
2024-08-01 11:12:26: #### Finish backup db1 ###
2024-08-01 11:12:26: #### Start backup db2 ###
2024-08-01 11:13:25: 2024-08-01 db2 mysqldump sucess
2024-08-01 11:14:11: Compress mysql_db2_2024-08-01.tar.gz sucess
2024-08-01 11:14:11: #### Finish backup db2 ###
2024-08-01 11:22:28: #### Start backup db3 ###
2024-08-01 11:22:29: 2024-08-01 db3 mysqldump sucess
2024-08-01 11:22:29: Compress mysql_db3_2024-08-01.tar.gz sucess
2024-08-01 11:22:29: Delete mysql_db3_2024-07-27.tar.gz sucess
2024-08-01 11:22:29: Delete mysql_db3_2024-07-26.tar.gz sucess
2024-08-01 11:22:29: #### Finish backup db3 ###
2024-08-01 11:22:29: #### End backup ###