mysql定时备份脚本

概述

整理Mysql数据库备份脚本，用在生产环境数据库定时备份。

参考

链接: 安全管理MySQL凭证：使用mysql_config_editor设置login-path

创建MySQL凭证

创建凭证

1. mysql_config_editor设置凭证

./mysql_config_editor set --login-path=local --host=localhost --socket=/data/mysqldb/tmp/mysql.sock --user=root --password

2. 输入mysql 用户密码

Enter password: 

3. 查看创建的凭证

 ./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 - GPLCopyright (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> 

创建备份脚本

脚本工作流程

1. 初始化环境变量
2. 定义脚本参数
3. 根据参数，备份每个数据库并打包压缩备份文件
4. 根据参数，删除超出保存期限的数据库备份文件
5. 根据参数，删除超出保存期限的脚本执行日志

脚本内容

1. 创建目录和脚本文件

• 创建目录

mkdir -p /public/script/logs && cd  /public/script

• 创建脚本

vim mysql_backup.sh 

3. 输入以下内容

#!/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" ]; thenmkdir -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; doDATE_TIME=$(date +"%Y-%m-%d %H:%M:%S")echo -e "\n $DATE_TIME: #### Start backup $DBNAME ###" >> $LOG_FILEBACKUP_NAME=mysql_${DBNAME}_${CURRENT_DATE}## 备份单个数据库，使用--login-path 方式认证mysqldump --login-path=local --single-transaction $DBNAME > $BAK_HOME/${BACKUP_NAME}.sqlif [ $? -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_FILEbreakelseDATE_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}.sqlif [ $? -eq 0 ];thenrm -f ${BACKUP_NAME}.sqlDATE_TIME=$(date +"%Y-%m-%d %H:%M:%S")echo " $DATE_TIME: Compress ${BACKUP_NAME}.tar.gz sucess">>$LOG_FILEelseDATE_TIME=$(date +"%Y-%m-%d %H:%M:%S")echo " $DATE_TIME: Compress ${BACKUP_NAME}.tar.gz failed">>$LOG_FILEbreakfi## 如果存在上一步打包的压缩文件。查找保留天数-7天之间的旧备份压缩包并删除if [ -f "${BACKUP_NAME}.tar.gz" ];thenfor ((i=${RETAIN_DAY}; i<=7; i++)); doRETAIN_DATE=$(date -d "-$i day" +"%Y-%m-%d")DELETE_BACKUP_NAME=mysql_${DBNAME}_${RETAIN_DATE}if [ -f "${DELETE_BACKUP_NAME}.tar.gz" ]; thenrm -f ${DELETE_BACKUP_NAME}.tar.gzif [ $? -eq 0 ];thenDATE_TIME=$(date +"%Y-%m-%d %H:%M:%S")echo " $DATE_TIME: Delete ${DELETE_BACKUP_NAME}.tar.gz sucess">>$LOG_FILEelseDATE_TIME=$(date +"%Y-%m-%d %H:%M:%S")echo " $DATE_TIME: Delete ${DELETE_BACKUP_NAME}.tar.gz failed">>$LOG_FILEfifidone;fiDATE_TIME=$(date +"%Y-%m-%d %H:%M:%S")echo -e "\n $DATE_TIME: #### Finish backup $DBNAME ###" >> $LOG_FILEdone;## 删除脚本执行历史日志
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

2. 脚本文件添加执行权限

chmod +x mysql_backup.sh 

添加定时任务

1. 编辑crontab定时任务

crontab -e

2. 添加以下内容

10 0 * * * /public/script/mysql_backup.sh >> /public/script/logs/mysql_$(date +"\%Y-\%m-\%d").log 2>&1 & 

3. 手动执行验证

• 注意：手动执行时不需要 “\” 禁止转义

/public/script/mysql_backup.sh >> /public/script/logs/mysql_$(date +"%Y-%m-%d").log 2>&1 & 

• 如果脚本执行失败，可以取消脚本中 #set -x 的注释，在前台执行脚本进行调试

4. 查看执行结果

[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 sucess2024-08-01 11:12:26: Compress mysql_db1_2024-08-01.tar.gz sucess2024-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 sucess2024-08-01 11:14:11: Compress mysql_db2_2024-08-01.tar.gz sucess2024-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 sucess2024-08-01 11:22:29: Compress mysql_db3_2024-08-01.tar.gz sucess2024-08-01 11:22:29: Delete mysql_db3_2024-07-27.tar.gz sucess2024-08-01 11:22:29: Delete mysql_db3_2024-07-26.tar.gz sucess2024-08-01 11:22:29: #### Finish backup db3 ###2024-08-01 11:22:29: #### End backup ###