服务器 mysql 数据库备份如何操作

学完这篇，你能在一台 Linux 服务器上手动备份 MySQL 数据库、把它做成每天自动跑的任务，并且验证备份真的能恢复回来。

第一步：确认 MySQL 版本和备份工具在哪

这一步是搞清楚你服务器上的环境，做完你能知道用哪个命令、配置文件在哪。

登录服务器后执行：

mysql --version
which mysqldump
rpm -qa | grep -i mysql     # CentOS/RHEL 系
dpkg -l | grep -i mysql     # Debian/Ubuntu 系

MySQL 5.7、8.0 以及 MariaDB 10.x 都自带 mysqldump，路径通常是 /usr/bin/mysqldump。如果你用的是 Docker 装的 MySQL，命令要进容器执行，形如 docker exec -it mysql8 mysqldump ...。

注意：mysqldump 是逻辑备份，导出的是 SQL 语句。数据量超过 50GB 时它可能跑几个小时，这种场景应该改用 XtraBackup 做物理备份，本文不展开。

第二步：备份单个数据库

这一步把指定的一个库导成一个 .sql 文件，做完你能拿到一份可读的备份。

mkdir -p /data/backup
mysqldump -u root -p \
  --single-transaction \
  --routines --triggers --events \
  --default-character-set=utf8mb4 \
  mydb > /data/backup/mydb_$(date +%F).sql

参数含义：

• --single-transaction：在 InnoDB 表上开启一致性快照，备份期间不锁表，业务可以继续写。
• --routines --triggers --events：把存储过程、触发器、定时事件一起导出，不加这三项这些对象会丢。
• --default-character-set=utf8mb4：防止中文变乱码。

注意：-p 后面直接跟密码（如 -p123456）会让密码出现在 ps 命令和历史记录里。正确做法是写配置文件：在 /root/.my.cnf 里写

[client]
user=root
password=你的密码

然后 chmod 600 /root/.my.cnf，之后命令里只写 mysqldump mydb > ... 即可，不用再输账号密码。

第三步：备份全部数据库，或者只备份表结构

这一步覆盖多库场景。备份所有库：

mysqldump --all-databases --single-transaction --routines --triggers --events \
  > /data/backup/all_$(date +%F).sql

只想留一份表结构（建表语句），方便对比变更：

mysqldump --no-data --all-databases > /data/backup/schema_$(date +%F).sql

第四步：写成脚本，压缩并自动清理旧文件

这一步把上面的命令固化成一个可执行脚本，做完你只需要跑一条命令就能完成备份+压缩+清理。

创建 /usr/local/bin/mysql_backup.sh：

#!/bin/bash
set -e
BACKUP_DIR=/data/backup
DATE=$(date +%F_%H%M)
KEEP_DAYS=7

mkdir -p "$BACKUP_DIR"
mysqldump --all-databases --single-transaction --routines --triggers --events \
  | gzip > "$BACKUP_DIR/all_$DATE.sql.gz"


find "$BACKUP_DIR" -name "all_*.sql.gz" -mtime +$KEEP_DAYS -delete
echo "backup done: $BACKUP_DIR/all_$DATE.sql.gz"

赋权并试跑：

chmod +x /usr/local/bin/mysql_backup.sh
/usr/local/bin/mysql_backup.sh
ls -lh /data/backup/

注意：set -e 很关键，让脚本在任何一步失败时立刻退出，避免生成一个空的、看起来正常的备份文件。另外把 > /data/backup/cron.log 2>&1 加进定时任务里，出问题才有日志可查。

第五步：加进 crontab，每天凌晨自动跑

这一步让备份自动发生。执行 crontab -e，加入一行：

0 2 * * * /usr/local/bin/mysql_backup.sh >> /data/backup/cron.log 2>&1

意思是每天 02:00 执行一次。保存后 crontab -l 确认已写入。CentOS 7/8 上还要确认 crond 服务在跑：systemctl status crond，没启动就 systemctl enable --now crond。

注意：crontab 里的环境变量和你登录 shell 的不一样（比如 PATH 可能没有 /usr/local/bin），所以脚本里用绝对路径最稳妥。

第六步：验证备份能恢复（这一步别省）

这一步确认备份文件不是废的。先建一个测试库：

mysql -e "CREATE DATABASE restore_test;"
gzip -dc /data/backup/all_2024-06-01_0200.sql.gz | mysql restore_test
mysql -e "SHOW TABLES FROM restore_test;" | head

能看到表名，说明备份可用。单库文件则是 mysql mydb_test < /data/backup/mydb_2024-06-01.sql。

注意：永远不要把备份和数据放在同一块磁盘或同一个云主机上，磁盘挂了两个一起没。建议用 scp、rsync 或者 rclone 同步到另一台机器或对象存储。

小结

• 逻辑备份用 mysqldump，关键参数是 --single-transaction --routines --triggers --events。
• 账号密码放 /root/.my.cnf 并设 600 权限，不要写在命令行里。
• 用 bash 脚本 + crontab 实现每天自动备份，脚本里加 set -e 和保留天数清理。
• 备份文件必须压缩、必须异地存放，否则等于没备份。
• 定期做一次恢复演练，能还原的备份才算数。
• 数据量特别大或要求秒级恢复时，改用 XtraBackup 等物理备份方案。