运维：CentOS 7.6 下 MySQL 5.7 自动备份与恢复完整指南

在数据库运维中，定期备份是保障数据安全的核心手段。今天给大家分享基于 CentOS 7.6 操作系统和 MySQL 5.7 数据库，提供一套完整、可直接投入生产的数据库自动备份方案。

一、备份脚本设计思路

备份脚本的核心是通过 mysqldump 工具导出数据库 SQL，并结合管道压缩。主要参数说明如下：

参数

作用

--single-transaction

针对 InnoDB 表开启一致性快照，备份过程中不锁表

--routines

导出存储过程和函数

--triggers

导出触发器

--events

导出事件调度器

--set-gtid-purged=OFF

关闭 GTID 信息导出，避免恢复时因 GTID 冲突报错

gzip

压缩输出为 .sql.gz 格式

脚本还集成了日志记录和过期文件自动清理功能，保证备份目录不会无限膨胀。

二、部署步骤

2.1 创建目录结构

mkdir -p /data/mysql/bak# 备份文件存放目录 mkdir -p /data/mysql/scripts # 脚本存放目录

2.2 编写备份脚本

创建脚本文件：

vim /data/scripts/mysql_backup.sh

将以下完整脚本内容复制进去（请替换其中的数据库用户名、密码和数据库名）：

#!/bin/bash # ==================================================== # MySQL 5.7 自动备份脚本 # 适用于 CentOS 7.6 环境 # 功能：自动备份数据库、压缩、删除过期备份、记录日志 # ==================================================== # ---------- 基础配置（请按实际修改） ---------- MYSQL_USER="root" # 数据库用户名 MYSQL_PASSWORD="your_password" # 数据库密码 MYSQL_HOST="localhost" # 数据库主机地址 # 要备份的数据库名（多个数据库用空格分隔，如 "db1 db2 db3"） # 留空表示备份所有数据库 DATABASE_NAME="your_database" # 修改为实际数据库名 BACKUP_DIR="/data/mysql/bak" # 备份存放路径 LOG_FILE="/var/log/mysql_backup.log" # 日志文件路径 RETENTION_DAYS=7 # 保留备份天数 # ---------- 以下为脚本逻辑，无需修改 ---------- DATE=$(date +%Y%m%d_%H%M%S) if [ ! -d "$BACKUP_DIR" ]; then mkdir -p "$BACKUP_DIR" fi echo "=========================================" >> "$LOG_FILE" echo "备份开始时间：$(date '+%Y-%m-%d %H:%M:%S')" >> "$LOG_FILE" if [ -n "$DATABASE_NAME" ]; then BACKUP_FILE="$BACKUP_DIR/${DATABASE_NAME}_${DATE}.sql.gz" echo "正在备份数据库：$DATABASE_NAME" >> "$LOG_FILE" mysqldump -h"$MYSQL_HOST" -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" --single-transaction --routines --triggers --events --set-gtid-purged=OFF --databases "$DATABASE_NAME" | gzip > "$BACKUP_FILE" else BACKUP_FILE="$BACKUP_DIR/all_databases_${DATE}.sql.gz" echo "正在备份所有数据库..." >> "$LOG_FILE" mysqldump -h"$MYSQL_HOST" -u"$MYSQL_USER" -p"$MYSQL_PASSWORD" --single-transaction --routines --triggers --events --set-gtid-purged=OFF --all-databases | gzip > "$BACKUP_FILE" fi if [ $? -eq 0 ]; then echo "备份成功！备份文件：$BACKUP_FILE" >> "$LOG_FILE" echo "备份文件大小：$(ls -lh "$BACKUP_FILE" | awk '{print $5}')" >> "$LOG_FILE" else echo "备份失败！请检查 MySQL 连接和权限配置。" >> "$LOG_FILE" exit 1 fi echo "正在清理 ${RETENTION_DAYS} 天前的旧备份文件..." >> "$LOG_FILE" find "$BACKUP_DIR" -type f -name "*.sql.gz" -mtime +"$RETENTION_DAYS" -exec rm -f {} ; DELETED_COUNT=$(find "$BACKUP_DIR" -type f -name "*.sql.gz" -mtime +"$RETENTION_DAYS" 2>/dev/null | wc -l) echo "已删除 $DELETED_COUNT 个过期备份文件" >> "$LOG_FILE" FILE_COUNT=$(ls -1 "$BACKUP_DIR"/*.sql.gz 2>/dev/null | wc -l) echo "当前备份目录中共有 $FILE_COUNT 个备份文件" >> "$LOG_FILE" echo "备份结束时间：$(date '+%Y-%m-%d %H:%M:%S')" >> "$LOG_FILE" echo "=========================================" >> "$LOG_FILE"

2.3 赋予备份脚本执行权限

chmod +x /data/mysql/scripts/mysql_backup.sh

2.4 手动测试

先手动执行一次，验证脚本是否正常工作：

/data/scripts/mysql_backup.sh

检查备份文件和日志：

ls -lh /data/backup/mysql/ cat /var/log/mysql_backup.log

如果使用上传解压包的方式安装mysql5.7，可能出现 mysqldump: command not found 错误，：

需要先查询nysqldump的安装目录

find / -name mysqldump -type f 2>/dev/null

通常目录为

/usr/local/mysql/bin/mysqldump

然后在备份脚本文件首行增加下面一行代码

export PATH=$PATH:/usr/local/mysql/bin

然后执行脚本文件就可以了。

若出现 Using a password on the command line 警告（不影响备份），可通过创建 ~/.my.cnf 文件消除。

三、配置定时任务（crontab）

3.1 确保 crond 服务运行

systemctl start crond systemctl enable crond systemctl status crond

3.2 添加定时任务

编辑当前用户的 crontab：

crontab -e

添加一行（每天凌晨3:00执行）：

0 3 * * * /bin/bash /data/mysql/scripts/mysql_backup.sh >> /var/log/mysql_backup_cron.log 2>&1

保存退出。可以通过 crontab -l 查看已添加的任务。

3.3 验证定时任务

tail -f /var/log/cron | grep mysql_backup

第二天检查备份目录和日志，确认自动备份已生效。

四、数据还原方法

备份的目的是恢复，下面针对生成的 .sql.gz 文件提供完整的还原逻辑。

4.1 全库还原

zcat /data/backup/mysql/all_databases_20260506_030001.sql.gz | mysql -uroot -p

注意：全库还原会覆盖现有所有数据库（包括 mysql 系统库中的用户、权限等），建议在新实例或测试环境中操作。

4.2 单库还原

# 若目标数据库已存在 zcat /data/mysql/bak/your_database_20260506_030001.sql.gz | mysql -uroot -p your_database # 若目标数据库不存在，先创建 mysql -uroot -p -e "CREATE DATABASE your_database;" zcat backup.sql.gz | mysql -uroot -p your_database

4.3 处理常见还原错误

ERROR 1419 (HY000) – DEFINER 权限问题

过滤掉 DEFINER 子句：

zcat backup.sql.gz | sed -e 's/DEFINER[ ]*=[ ]*[^*]**/*/' | mysql -uroot -p

外键约束失败

恢复前禁用外键检查：

zcat backup.sql.gz | mysql -uroot -p --init-command="SET FOREIGN_KEY_CHECKS=0;"

GTID 冲突

在恢复会话中关闭二进制日志：

mysql -uroot -p -e "SET SESSION SQL_LOG_BIN=0;" zcat backup.sql.gz | mysql -uroot -p

4.4 时间点恢复（利用 binlog）

如果需要恢复到某个时刻（例如误删数据后），步骤如下：

1. 恢复最近的全量备份

2. 应用全量备份之后的 binlog 到故障时间点

   1. mysqlbinlog --start-datetime="2026-05-06 02:00:00" --stop-datetime="2026-05-06 02:30:00" /var/lib/mysql/mysql-bin.000123 | mysql -uroot -p

建议在备份脚本中增加 mysqladmin flush-logs 命令，定期切割 binlog，方便时间点恢复。

五、常见问题与优化建议

5.1 安全性优化：消除密码明文警告

在用户家目录创建 ~/.my.cnf：

cat > ~/.my.cnf <

然后修改备份脚本，去掉 -u、-p、-h 参数，直接运行 mysqldump 即可。

5.2 磁盘空间不足预防

在脚本开头添加空间检查：

AVAIL=$(df -BG $BACKUP_DIR | awk 'NR==2 {print $4}' | sed 's/G//') if [ $AVAIL -lt 5 ]; then echo "磁盘剩余空间不足5GB，停止备份" >> $LOG_FILE exit 1 fi

5.3 定时任务未执行排查

检查 crond 服务状态：systemctl status crond

查看 cron 日志：tail -f /var/log/cron

确保脚本路径为绝对路径，并在脚本中设置 PATH：

export PATH=/usr/bin:/bin:/usr/local/bin:/usr/local/mysql/bin

5.4 权限不足（PROCESS 权限）

执行备份时报错 Access denied; you need the PROCESS privilege，登录 MySQL 授予相应权限：

GRANT PROCESS, SELECT, LOCK TABLES, RELOAD, SHOW VIEW ON *.* TO 'backup_user'@'localhost'; FLUSH PRIVILEGES;

5.5 异地备份（扩展）

在脚本末尾添加 rsync 同步命令，将备份文件推送到远程服务器：

rsync -avz --delete "$BACKUP_DIR/" backup_user@remote_host:/remote/backup/path/

六、总结

以上提供的备份脚本已在 CentOS 7.6 + MySQL 5.7 环境下验证通过，特点如下：

• 自动化：结合 crontab 实现无人值守

• 安全性：支持压缩、密码脱敏、过期清理

• 完整性：导出存储过程、触发器、事件等全部对象

• 可恢复性：配套详细的单库/全库/时间点恢复方法

建议在生产环境部署前先在测试环境验证备份和恢复流程，并定期进行恢复演练。数据本身才是最重要的。大家如果使用过程中有啥问题的话，欢迎评论区聊聊！

作者声明本文存在利益相关性，请大家尊重作者及分享的内容，友善沟通，理性决策~