当前位置：首页 > news >正文

MySQL的对表对整库备份脚本

news 2025/6/14 8:24:36

1.对表的备份

1）全量备份

首先对表进行全量备份，对表定期全量
七天执行一次全量备份，备份完删除上一次全量备份SQL文件
创建计划任务crontab定时执行脚本进行备份

#!/bin/bash
# 文件名: full_backup.sh
# 优化说明：标准化 binlog 记录格式，增强错误处理# ===== 配置区 =====
DB_USER="root"
DB_PASSWORD="1q!1q!"
SOCKET_PATH="/usr/local/mysql/mysql.sock"
BACKUP_DIR="/home/mysql/backups"  # 改为您实际使用的目录
DATABASE="check_v1"
TABLES=("production_data")  # 根据您的实际需要调整# 确保备份目录存在
mkdir -p "$BACKUP_DIR"# ===== 执行全量备份 =====
TIMESTAMP=$(date +"%Y年 %m月 %d日 %A %H:%M:%S CST")
BACKUP_FILE="$BACKUP_DIR/full_backup_${DATABASE}_$(date +%Y%m%d-%H%M%S).sql"echo "[$TIMESTAMP] 开始全量备份数据库: $DATABASE"
mysqldump -u"$DB_USER" -p"$DB_PASSWORD" -S "$SOCKET_PATH" \--single-transaction --master-data=2 --triggers --routines --events \--hex-blob "$DATABASE" "${TABLES[@]}" > "$BACKUP_FILE"if [ $? -eq 0 ]; thenecho "[$TIMESTAMP] 全量备份成功！文件: $BACKUP_FILE"# 标准化 binlog 记录格式（关键修改）MASTER_STATUS_FILE="$BACKUP_DIR/last_binlog_pos.txt"mysql -u"$DB_USER" -p"$DB_PASSWORD" -S "$SOCKET_PATH" -N -e \"SHOW MASTER STATUS" | awk '{print "File:",$1,"\nPosition:",$2}' > "$MASTER_STATUS_FILE"echo "[$TIMESTAMP] 记录 binlog 位置:"cat "$MASTER_STATUS_FILE"
elseecho "[$TIMESTAMP] 备份失败！"rm -f "$BACKUP_FILE"exit 1
fi

2）增量备份

对表进行增量备份，每天将表中新增的内容增量备份
每天凌晨对表进行增量备份，每天的增量会形成一个文件，超过七天的文件sql将进行删除（因为七天进行一次全量，超过七天的文件已然不需要保留）
这样达到的效果就是不需要每天都要进行大量的数据备份，每天只要备份增加的数据
创建计划任务每天凌晨备份一次表中的增加数据

#!/bin/bash
# 文件名: incremental_backup.sh
# 优化说明：改进 binlog 解析逻辑，支持 GTID 和多语句过滤# ===== 配置区 =====
DB_USER="root"
DB_PASSWORD="1q!1q!"
SOCKET_PATH="/usr/local/mysql/mysql.sock"
BACKUP_DIR="/home/mysql/backups"
DATABASE="check_v1"
TABLE="production_data"
BINLOG_DIR="/usr/local/mysql/data"
MYSQL_PATH="/usr/local/mysql/bin"  # 根据实际路径调整# ===== 获取上次备份的 binlog 位置 =====
LAST_STATUS_FILE="$BACKUP_DIR/last_binlog_pos.txt"if [ ! -f "$LAST_STATUS_FILE" ]; thenecho "[$(date +"%Y年 %m月 %d日 %A %H:%M:%S CST")] 错误：找不到 binlog 记录文件！"exit 1
fi# 更安全的变量获取方式
LAST_LOG=$(grep "File:" "$LAST_STATUS_FILE" | awk '{print $2}')
LAST_POS=$(grep "Position:" "$LAST_STATUS_FILE" | awk '{print $2}')if [ -z "$LAST_LOG" ] || [ -z "$LAST_POS" ]; thenecho "[$(date)] 错误：无法解析 binlog 位置！"exit 1
fiTIMESTAMP=$(date +"%Y年 %m月 %d日 %A %H:%M:%S CST")
BACKUP_FILE="$BACKUP_DIR/incremental_${DATABASE}.${TABLE}_$(date +%Y%m%d-%H%M%S).sql"echo "[$TIMESTAMP] 开始增量备份表: $DATABASE.$TABLE，从 binlog: $LAST_LOG, 位置: $LAST_POS"# ===== 关键改进：使用 mysqlbinlog 专业过滤 =====
"$MYSQL_PATH"/mysqlbinlog \--start-position="$LAST_POS" \--database="$DATABASE" \"$BINLOG_DIR/$LAST_LOG" | \awk '/^# at .*/{print}\/^INSERT INTO `'$TABLE'`/{print}\/^UPDATE `'$TABLE'`/{print}\/^DELETE FROM `'$TABLE'`/{print}' \> "$BACKUP_FILE"if [ $? -eq 0 ] && [ -s "$BACKUP_FILE" ]; thenecho "[$TIMESTAMP] 增量备份成功！文件: $BACKUP_FILE"# 更新 binlog 位置（保持格式一致）mysql -u"$DB_USER" -p"$DB_PASSWORD" -S "$SOCKET_PATH" -N -e \"SHOW MASTER STATUS" | awk '{print "File:",$1,"\nPosition:",$2}' > "$LAST_STATUS_FILE"echo "[$TIMESTAMP] 更新 binlog 位置:"cat "$LAST_STATUS_FILE"
elseecho "[$TIMESTAMP] 增量备份失败或未发现新变更！"[ -f "$BACKUP_FILE" ] && rm -f "$BACKUP_FILE"exit 1
fi

2.对整库备份（除系统库之外）

1）全量备份

#!/bin/bash
# 文件名: full_backup.sh
# 优化说明：标准化整库备份流程，增强错误处理# ===== 配置区 =====
DB_USER="root"
DB_PASSWORD="1q!1q!"
SOCKET_PATH="/home/ing-sys/mysql/mysql.sock"
BACKUP_DIR="/home/path/to/backup"  # 改为您实际使用的目录
DATABASES=("check_v1" "other_db")  # 需要备份的数据库列表
IGNORED_DATABASES=("mysql" "performance_schema" "information_schema" "sys")# ===== 初始化检查 =====
TIMESTAMP=$(date +"%Y年 %m月 %d日 %A %H:%M:%S CST")
mkdir -p "$BACKUP_DIR"
LOG_FILE="$BACKUP_DIR/backup.log"log() {echo "[$(date +'%Y-%m-%d %H:%M:%S')] $1" >> "$LOG_FILE"
}log "====== 开始全量备份 ======"# ===== 获取需要备份的数据库 =====
if [ ${#DATABASES[@]} -eq 0 ]; then# 如果未指定数据库，则自动获取所有非系统库DATABASES=$(mysql -u"$DB_USER" -p"$DB_PASSWORD" -S "$SOCKET_PATH" -N -e "SHOW DATABASES;" | grep -Ev "^($(IFS=\|; echo "${IGNORED_DATABASES[*]}|Database"))$")
filog "将备份数据库: ${DATABASES[*]}"# ===== 执行全量备份 =====
BACKUP_FILE="$BACKUP_DIR/full_$(date +%Y%m%d-%H%M%S).sql"
mysqldump -u"$DB_USER" -p"$DB_PASSWORD" -S "$SOCKET_PATH" \--databases "${DATABASES[@]}" \--single-transaction --master-data=2 --triggers --routines --events \--hex-blob > "$BACKUP_FILE"if [ $? -eq 0 ]; thenlog "全量备份成功: $BACKUP_FILE"# 标准化 binlog 记录格式MASTER_STATUS_FILE="$BACKUP_DIR/last_binlog_pos.txt"mysql -u"$DB_USER" -p"$DB_PASSWORD" -S "$SOCKET_PATH" -N -e \"SHOW MASTER STATUS" | awk '{print "File:",$1,"\nPosition:",$2}' > "$MASTER_STATUS_FILE"log "记录 binlog 位置:"cat "$MASTER_STATUS_FILE" >> "$LOG_FILE"# 清理旧备份（保留7天）find "$BACKUP_DIR" -name "full_*.sql" -mtime +7 -deletelog "已清理超过7天的旧备份"
elselog "全量备份失败！"exit 1
fi

2）增量备份

#!/bin/bash
# 文件名: incremental_backup.sh
# 优化说明：改进整库binlog备份逻辑# ===== 配置区 =====
DB_USER="root"
DB_PASSWORD="1q!1q!"
SOCKET_PATH="/home/ing-sys/mysql/mysql.sock"
BACKUP_DIR="/home/path/to/backup"
BINLOG_DIR="/home/ing-sys/mysql/data"# ===== 初始化检查 =====
TIMESTAMP=$(date +"%Y年 %m月 %d日 %A %H:%M:%S CST")
LOG_FILE="$BACKUP_DIR/backup.log"log() {echo "[$(date +'%Y-%m-%d %H:%M:%S')] $1" >> "$LOG_FILE"
}log "====== 开始增量备份 ======"# ===== 获取上次备份的 binlog 位置 =====
LAST_STATUS_FILE="$BACKUP_DIR/last_binlog_pos.txt"if [ ! -f "$LAST_STATUS_FILE" ]; thenlog "错误：找不到 binlog 记录文件！请先执行全量备份"exit 1
fiLAST_LOG=$(grep "File:" "$LAST_STATUS_FILE" | awk '{print $2}')
LAST_POS=$(grep "Position:" "$LAST_STATUS_FILE" | awk '{print $2}')if [ -z "$LAST_LOG" ] || [ -z "$LAST_POS" ]; thenlog "错误：无法解析 binlog 位置！"exit 1
filog "从 binlog: $LAST_LOG, 位置: $LAST_POS 开始增量备份"# ===== 执行增量备份 =====
BACKUP_FILE="$BACKUP_DIR/incremental_$(date +%Y%m%d-%H%M%S).sql"mysqlbinlog --read-from-remote-server \-u"$DB_USER" -p"$DB_PASSWORD" \--start-position="$LAST_POS" \--host=localhost \"$BINLOG_DIR/$LAST_LOG" > "$BACKUP_FILE"if [ $? -eq 0 ] && [ -s "$BACKUP_FILE" ]; thenlog "增量备份成功: $BACKUP_FILE"# 更新 binlog 位置mysql -u"$DB_USER" -p"$DB_PASSWORD" -S "$SOCKET_PATH" -N -e \"SHOW MASTER STATUS" | awk '{print "File:",$1,"\nPosition:",$2}' > "$LAST_STATUS_FILE"log "更新 binlog 位置:"cat "$LAST_STATUS_FILE" >> "$LOG_FILE"
elselog "增量备份失败或未发现新变更！"[ -f "$BACKUP_FILE" ] && rm -f "$BACKUP_FILE"exit 1
fi

3.定时计划任务crontab

每周日凌晨 3 点全量备份
0 3 * * 0 /bin/bash /path/to/full_backup.sh

每天凌晨 2 点增量备份
0 2 * * * /bin/bash /path/to/incremental_backup.sh

4.恢复流程

全量恢复
./mysql -u root -p < full_20250612030000.sql

增量恢复
./mysqlbinlog incremental_20250612020000.sql | ./mysql -u root -p

5.定时删除超过七天的备份文件

#!/bin/bash
# 文件名: clean_backup.sh
# 功能：清理/home/mysql/backups目录下超过7天的SQL备份文件# ===== 配置区 =====
BACKUP_DIR="/home/mysql/backups"  # 备份目录
LOG_FILE="$BACKUP_DIR/clean_backup.log"  # 日志文件
KEEP_DAYS=7                       # 文件保留天数(您要求的7天)# ===== 初始化 =====
TIMESTAMP=$(date +"%Y-%m-%d %H:%M:%S")
mkdir -p "$BACKUP_DIR"echo "======= $TIMESTAMP 清理开始 =======" | tee -a "$LOG_FILE"
echo "备份目录: $BACKUP_DIR" | tee -a "$LOG_FILE"
echo "清理策略: 超过 $KEEP_DAYS 天的所有SQL备份" | tee -a "$LOG_FILE"# ===== 统一清理所有SQL备份(全量+增量) =====
OLD_SQL_FILES=$(find "$BACKUP_DIR" -type f \( -name "full_backup_check_v1_*.sql" -o -name "incremental_check_v1.production_data_*.sql" \) -mtime +$KEEP_DAYS)if [ -n "$OLD_SQL_FILES" ]; thenecho "发现以下过期备份文件:" | tee -a "$LOG_FILE"echo "$OLD_SQL_FILES" | tee -a "$LOG_FILE"# 安全删除(先打印确认)echo "$OLD_SQL_FILES" | xargs -I {} echo "即将删除: {}" | tee -a "$LOG_FILE"echo "$OLD_SQL_FILES" | xargs rm -fDELETED_COUNT=$(echo "$OLD_SQL_FILES" | wc -l)echo "已清理 $DELETED_COUNT 个SQL备份文件" | tee -a "$LOG_FILE"
elseecho "未发现需要清理的SQL备份文件" | tee -a "$LOG_FILE"
fi# ===== 清理过期状态文件 =====
OLD_STATUS_FILES=$(find "$BACKUP_DIR" -name "last_binlog_pos.txt" -mtime +$KEEP_DAYS)
if [ -n "$OLD_STATUS_FILES" ]; thenecho "清理过期状态文件:" | tee -a "$LOG_FILE"echo "$OLD_STATUS_FILES" | xargs rm -f
fi# ===== 最终统计 =====
REMAIN_FULL=$(find "$BACKUP_DIR" -name "full_backup_check_v1_*.sql" | wc -l)
REMAIN_INC=$(find "$BACKUP_DIR" -name "incremental_check_v1.production_data_*.sql" | wc -l)echo "清理完成，当前保留:" | tee -a "$LOG_FILE"
echo "全量备份: $REMAIN_FULL 个" | tee -a "$LOG_FILE"
echo "增量备份: $REMAIN_INC 个" | tee -a "$LOG_FILE"
echo "磁盘使用情况:" | tee -a "$LOG_FILE"
du -sh "$BACKUP_DIR" | tee -a "$LOG_FILE"
echo "======= 清理结束 =======" | tee -a "$LOG_FILE"

查看全文

http://www.xdnf.cn/news/1023589.html