mysql自动备份

想在 CentOS 系统中实现 MySQL 自动备份，核心方案是 mysqldump 备份工具 + Shell 脚本 + crontab 定时任务，步骤清晰、可落地，且包含备份压缩、过期清理等优化，以下是完整实现流程：

一、前提准备

确认 MySQL 已安装 mysqldump 工具（MySQL 自带，通常在 /usr/bin/mysqldump），执行以下命令验证：
bash

运行
```
which mysqldump
```
若输出 /usr/bin/mysqldump，说明已存在；若未找到，需安装 MySQL 客户端（无需完整服务端）：
bash

运行
```
# CentOS 7/8 通用（根据MySQL版本调整）
yum install mysql-community-client -y
```

准备 MySQL 备份账号（推荐创建专用备份账号，最小权限原则）：

sql

-- 登录 MySQL 终端
mysql -u root -p

-- 创建备份账号（仅赋予查询、锁表权限，足够备份）
CREATE USER 'backup_user'@'localhost' IDENTIFIED BY '你的强密码';

-- 授予所有数据库备份权限（如需指定单个数据库，替换 *.* 为 db_name.*）
-- 授予全局 PROCESS 权限（必须全局，无法针对单个数据库）
-- 同时授予全局 EVENT 权限，以及的备份基础权限
GRANT SELECT, LOCK TABLES, SHOW VIEW, PROCESS, EVENT ON *.* TO 'backup_user'@'localhost';

-- 刷新权限
FLUSH PRIVILEGES;

-- 退出 MySQL
exit;

创建备份目录（用于存储备份文件，建议单独挂载磁盘或非系统目录）：

bash
运行

# 创建备份目录（示例：/data/mysql_backup）
mkdir -p /data/mysql_backup

# 赋予合适权限（避免权限不足无法写入，此处赋予当前用户，可根据实际调整）
chmod 755 /data/mysql_backup

二、编写 MySQL 自动备份 Shell 脚本

在备份目录下创建 Shell 脚本（如 mysql_auto_backup.sh），包含备份、压缩、过期清理核心逻辑，脚本可直接复制使用，仅需修改配置项。

步骤 1：创建并编辑脚本

bash
运行

vim /data/mysql_backup/mysql_auto_backup.sh

步骤 2：写入脚本内容（关键配置项标注清楚，按需修改）bash

运行

#!/bin/bash
# #########################################################
# 配置项（根据你的实际环境修改）
# #########################################################
MYSQL_USER="backup_user"          # MySQL 备份账号
MYSQL_PASS="你的备份账号密码"      # MySQL 备份账号密码
MYSQL_HOST="localhost"            # MySQL 主机（本地填 localhost，远程填 IP）
MYSQL_PORT="3306"                 # MySQL 端口
BACKUP_DIR="/data/mysql_backup"   # 备份文件存储目录
RETENTION_DAYS=7                  # 备份文件保留天数（超过自动删除，避免磁盘爆满）
DATABASES="all"                   # 要备份的数据库（all=所有数据库；指定单个：db1；多个：db1 db2 db3）

# #########################################################
# 无需修改以下内容（核心逻辑）
# #########################################################
# 1. 定义备份文件名（带时间戳，避免覆盖，格式：数据库名_YYYYMMDD_HHMMSS.sql.gz）
DATE=$(date +%Y%m%d_%H%M%S)
LOG_FILE="${BACKUP_DIR}/mysql_backup_${DATE}.log"

# 2. 检查备份目录是否存在，不存在则创建
if [ ! -d "${BACKUP_DIR}" ]; then
    mkdir -p "${BACKUP_DIR}"
    echo "$(date +'%Y-%m-%d %H:%M:%S') - 备份目录不存在，已创建：${BACKUP_DIR}" >> "${LOG_FILE}"
fi

# 3. 执行备份（分「所有数据库」和「指定数据库」两种场景）
echo "$(date +'%Y-%m-%d %H:%M:%S') - 开始执行 MySQL 备份..." >> "${LOG_FILE}"

if [ "${DATABASES}" = "all" ]; then
    # 备份所有数据库（包含数据库结构+数据，压缩输出）
    mysqldump -u"${MYSQL_USER}" -p"${MYSQL_PASS}" -h"${MYSQL_HOST}" -P"${MYSQL_PORT}" \
    --all-databases --single-transaction --routines --triggers --events \
    | gzip > "${BACKUP_DIR}/all_databases_${DATE}.sql.gz"
else
    # 备份指定数据库（循环备份多个数据库）
    for DB in ${DATABASES}; do
        mysqldump -u"${MYSQL_USER}" -p"${MYSQL_PASS}" -h"${MYSQL_HOST}" -P"${MYSQL_PORT}" \
        --databases "${DB}" --single-transaction --routines --triggers --events \
        | gzip > "${BACKUP_DIR}/${DB}_${DATE}.sql.gz"
        echo "$(date +'%Y-%m-%d %H:%M:%S') - 数据库 ${DB} 备份完成" >> "${LOG_FILE}"
    done
fi

# 4. 验证备份是否成功
if [ $? -eq 0 ]; then
    echo "$(date +'%Y-%m-%d %H:%M:%S') - MySQL 备份执行成功，备份文件存储于 ${BACKUP_DIR}" >> "${LOG_FILE}"
else
    echo "$(date +'%Y-%m-%d %H:%M:%S') - ERROR: MySQL 备份执行失败！" >> "${LOG_FILE}"
    exit 1
fi

# 5. 清理过期备份文件（删除超过 RETENTION_DAYS 天的 .sql.gz 备份文件）
echo "$(date +'%Y-%m-%d %H:%M:%S') - 开始清理 ${RETENTION_DAYS} 天前的过期备份..." >> "${LOG_FILE}"
find "${BACKUP_DIR}" -name "*.sql.gz" -type f -mtime +${RETENTION_DAYS} -delete
echo "$(date +'%Y-%m-%d %H:%M:%S') - 过期备份清理完成" >> "${LOG_FILE}"

# 6. 备份任务全部完成
echo "$(date +'%Y-%m-%d %H:%M:%S') - MySQL 自动备份任务全部执行完成" >> "${LOG_FILE}"
echo "================================================" >> "${LOG_FILE}"

步骤 3：赋予脚本执行权限

bash
运行

chmod +x /data/mysql_backup/mysql_auto_backup.sh

步骤 4：手动测试脚本（关键！确保脚本可正常运行）

bash
运行

# 执行脚本
/data/mysql_backup/mysql_auto_backup.sh

# 验证结果：查看备份目录是否生成 .sql.gz 压缩文件和日志文件
ls -lh /data/mysql_backup/

若生成 all_databases_YYYYMMDD_HHMMSS.sql.gz（或指定数据库备份文件）和 mysql_backup_YYYYMMDD_HHMMSS.log，且日志中无 ERROR，说明脚本正常。
若失败，查看日志文件排查问题（如密码错误、权限不足、目录不可写）。

三、配置 crontab 定时任务（实现自动执行）

CentOS 中通过 crontab 实现定时任务，将备份脚本添加到定时任务列表，实现每日 / 每周自动备份。

步骤 1：编辑 crontab 定时任务

bash
运行

# 编辑当前用户的定时任务（推荐使用 root 用户，确保有足够权限）
crontab -e

步骤 2：添加定时任务（示例：每天凌晨 2 点执行备份，服务器负载最低）

在文件末尾添加以下内容（根据需求调整执行时间）：

bash
运行

# 格式：分 时 日 月 周  脚本绝对路径  > /dev/null 2>&1（静默执行，不输出日志到终端）
0 2 * * * /data/mysql_backup/mysql_auto_backup.sh > /dev/null 2>&1

定时任务语法说明（按需调整）

字段	说明	示例（每天凌晨 2 点）
分	0-59	0（整点执行）
时	0-23	2（凌晨 2 点）
日	1-31	*（每天）
月	1-12	*（每月）
周	0-7（0/7 均为周日）	*（每周）

其他常用示例：

每周日凌晨 3 点执行：0 3 * * 0 /data/mysql_backup/mysql_auto_backup.sh > /dev/null 2>&1
每天凌晨 1 点 30 分执行：30 1 * * * /data/mysql_backup/mysql_auto_backup.sh > /dev/null 2>&1

步骤 3：保存并生效定时任务

vim 编辑器中，按 Esc → 输入 :wq 保存退出；

crontab 无需重启，添加后自动生效，若需重启 crond 服务（可选）：

bash
运行

# CentOS 7/8
systemctl restart crond

# 验证 crond 服务是否正常运行
systemctl status crond

四、关键优化与注意事项

密码安全优化：避免脚本中明文写密码，可通过 ~/.my.cnf 配置 MySQL 登录信息：

bash
运行

# 创建/编辑 MySQL 配置文件
vim ~/.my.cnf

# 写入以下内容（替换为你的备份账号和密码）
[mysqldump]
user=backup_user
password=你的强密码
host=localhost
port=3306

# 赋予文件只读权限（避免其他用户读取）
chmod 600 ~/.my.cnf

然后修改脚本，删除 MYSQL_USER、MYSQL_PASS、MYSQL_HOST、MYSQL_PORT 配置，mysqldump 会自动读取该配置文件。

备份文件存储优化：
- 避免将备份文件存储在系统盘（如 /root），优先使用独立数据盘；
- 定期将备份文件同步到异地存储（如阿里云 OSS、腾讯云 COS），防止本地磁盘损坏丢失备份。

备份验证：每周手动解压 1 个备份文件，验证是否可正常恢复：

bash
运行

# 解压备份文件
gzip -d all_databases_YYYYMMDD_HHMMSS.sql.gz

# 验证数据库结构（可选，登录 MySQL 执行恢复测试）
mysql -u root -p < all_databases_YYYYMMDD_HHMMSS.sql

 

日志监控：若需监控备份结果，可将脚本日志重定向到指定文件，而非 /dev/null：

bash
运行

# 修改 crontab 任务，保留执行日志
0 2 * * * /data/mysql_backup/mysql_auto_backup.sh >> /data/mysql_backup/crontab_backup.log 2>&1

大型数据库备份：mysqldump 适合中小型数据库（< 10GB），大型数据库推荐使用 Percona XtraBackup（支持增量备份、热备份，无锁）。

五、总结

核心流程：编写备份脚本（mysqldump+压缩+清理） → 赋予执行权限 → 手动测试 → crontab 定时任务；
关键保障：过期文件清理（避免磁盘爆满）、密码安全（避免明文）、定期验证备份（确保可恢复）；
定时任务：默认每天凌晨 2 点执行，可根据服务器负载调整执行时间。