Linux下使用crontab实现MySQL数据库定时自动备份

执行MySQL手动备份与还原基础

在执行自动化任务前，理解单次手动操作有助于排查后续脚本可能遇到的权限或路径问题。

第1步：创建备份目录并执行导出

首先需要在服务器上规划存储备份文件的文件夹。通常选择根目录下的特定子目录以避免冲突，例如 /usr/local/mysql_dump。

1. 准备环境： 切换到root用户，创建存放备份的目录并进入该目录。此步骤确保后续脚本有权限写入文件。
2. 执行备份： 使用 mysqldump 命令将指定数据库导出为SQL文本文件。

# 创建并进入备份目录
mkdir -p /usr/local/mysql_dump
cd /usr/local/mysql_dump

# 执行全库热备
# 格式: mysqldump -u用户名 -p密码 数据库名 > 文件名.sql
mysqldump -u root -pmypasssecret cncounter > cncounter_dump.sql.20140414_1333

第2步：测试SQL文件还原流程

备份的价值在于恢复，建议定期验证备份文件的可用性。恢复过程主要涉及登录数据库并通过SQL命令加载备份文件。

1. 登录数据库： 进入MySQL客户端交互界面。
2. 加载备份： 使用 source 指令指向之前生成的 .sql 文件路径。

# 登录MySQL
mysql -u root -pmypasssecret

-- 进入目标数据库（需确保库存在）
use cncounter;

-- 执行源文件导入
source /usr/local/mysql_dump/cncounter_dump.sql.20140414_1333;
exit;

构建自动化备份Shell脚本

为了实现非人工干预的定时备份，需要将上述命令行操作封装为可执行的Shell脚本。脚本的核心在于动态生成带有时间戳的文件名，防止新备份覆盖旧数据。

第3步：编写备份脚本逻辑

新建一个执行文件（如 mysql_back.sh），并在其中定义数据库连接信息和导出命令。

• 变量定义： 明确数据库名(DB_NAME)、用户(DB_USER)、密码(DB_PASS)以及备份存放路径(BCK_DIR)。
• 日期格式化： 使用 Date 命令生成 YYYYMMDD_HHMMSS 格式的字符串，作为文件名后缀，确保每次备份的唯一性。
• 执行导出： 调用 /usr/bin/mysqldump 执行导出，推荐使用 --opt 参数以获得最佳性能。

#!/bin/sh
# File: /root/mysql_dump/mysql_back.sh
# Database info
DB_NAME="cncounter"
DB_USER="root"
DB_PASS="mypasssecret"

# Others vars
BIN_DIR="/usr/bin"
BCK_DIR="/root/mysql_dump/data"
DATE=`date +%Y%m%d_%H%M%S`

# 确保备份目录存在
mkdir -p $BCK_DIR

# 执行备份导出
$BIN_DIR/mysqldump --opt -u$DB_USER -p$DB_PASS $DB_NAME > $BCK_DIR/$DB_NAME.dump_$DATE.sql

进阶优化：开启Gzip压缩
对于大型数据库，原始SQL文件体积庞大，占用大量磁盘空间。可以通过管道操作符 | 将输出传递给 gzip 进行压缩，通常能实现约10倍的体积缩减。

/usr/bin/mysqldump --opt -uroot -pmypasssecret cncounter | gzip > /root/mysql_dump/data/cncounter.dump_`date +%Y%m%d_%H%M%S`.sql.gz

第4步：赋予脚本执行权限

创建完脚本后，必须将其标记为可执行文件，否则Cron任务无法启动它。

# 创建并进入目录
mkdir -p /root/mysql_dump/data
cd /root/mysql_dump
touch mysql_back.sh

# 修改权限为755 (所有者可读写执行，其他可读可执行)
chmod 755 mysql_back.sh

配置Crontab定时任务

一切就绪后，需要通过Linux的Crontab服务定期唤醒上述脚本。

第5步：添加并验证调度规则

编辑当前用户（通常为root）的Cron列表：

1. 打开编辑器： 运行 crontab -e。
2. 写入规则： 添加一行Cron表达式，指向刚才创建的绝对路径脚本。

# 示例：每天凌晨 1 点 1 分执行一次备份
1 1 * * * /root/mysql_dump/mysql_back.sh

Cron表达式详解： Cron的时间字段由5个星号组成，分别代表：

• 分钟 (0-59)
• 小时 (0-23)
• 日期 (1-31)
• 月份 (1-12)
• 星期 (0-6, 0代表周日)

当某位填写为 * 时，表示“每”的意思。上述示例意为“每天的01:01执行”。此外，若直接在root环境下编辑Crontab，无需在行末再次指定用户名；若在 /etc/crontab 中编辑，则必须在命令前加入执行用户名称（如 root），否则可能导致日志显示执行但实际不运行。

注意事项与排错建议

• 备份目录权限： 务必确认 mkdir 指定的路径存在且对运行脚本的用户（此处为root）具有写入权限。
• 日志监控： 建议在脚本末尾追加输出重定向，记录备份成功与否的信息，方便日后追踪异常。例如：> /var/log/mysql_backup.log 2>&1。
• Gzip依赖： 若使用gzip压缩模式，请确认服务器已安装 gzip 组件，大多数Linux发行版默认自带。
• 密码安全： 脚本中包含明文密码，建议将权限严格控制在 700 或 600，仅允许所有者读取和运行。