如何安全备份和恢复 MySQL 数据库？

需要备份生产数据库，或者从备份恢复数据，要求不影响线上业务

解决方案

mysqldump 逻辑备份（推荐中小库）推荐

# 完整备份（InnoDB 不锁表）
mysqldump -u root -p \
  --single-transaction \
  --routines \
  --triggers \
  --set-gtid-purged=OFF \
  ecommerce > ecommerce_$(date +%Y%m%d_%H%M%S).sql

# 压缩备份（节省 70% 空间）
mysqldump -u root -p --single-transaction ecommerce | gzip > ecommerce_$(date +%Y%m%d).sql.gz

# 恢复
mysql -u root -p ecommerce < ecommerce_20260523.sql

# 恢复压缩文件
gunzip < ecommerce_20260523.sql.gz | mysql -u root -p ecommerce

mysqldump 是最常用的备份方式。--single-transaction 保证 InnoDB 表备份一致性且不锁表，适合数据量在 50GB 以下的数据库。

适用场景：数据量 < 50GB 的日常备份

定时自动备份脚本推荐

#!/bin/bash
# /opt/scripts/mysql_backup.sh
set -e

DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR=/data/backup/mysql
DB_USER=backup_user
DB_PASS='BackupSecure2026!'
DB_NAME=ecommerce
KEEP_DAYS=7

# 创建备份目录
mkdir -p $BACKUP_DIR

# 执行备份
mysqldump -u $DB_USER -p"$DB_PASS" \
  --single-transaction --routines --triggers \
  $DB_NAME | gzip > $BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz

# 检查备份是否成功
if [ $? -eq 0 ] && [ -s $BACKUP_DIR/${DB_NAME}_${DATE}.sql.gz ]; then
  echo "[$(date)] 备份成功: ${DB_NAME}_${DATE}.sql.gz"
else
  echo "[$(date)] 备份失败!" >&2
  exit 1
fi

# 删除过期备份
find $BACKUP_DIR -name "*.sql.gz" -mtime +$KEEP_DAYS -delete

# crontab -e 添加：
# 0 2 * * * /opt/scripts/mysql_backup.sh >> /var/log/mysql_backup.log 2>&1

配合 crontab 实现每天凌晨自动备份，保留最近 7 天的备份文件，自动清理过期备份。

适用场景：需要自动化定时备份

备份单表或部分数据

# 备份指定表
mysqldump -u root -p --single-transaction ecommerce users orders > core_tables.sql

# 只备份表结构（不含数据）
mysqldump -u root -p --no-data ecommerce > schema_only.sql

# 按条件备份部分数据
mysqldump -u root -p --single-transaction \
  ecommerce orders --where="created_at >= '2026-01-01'" > orders_2026.sql

# 导出为 CSV
SELECT * FROM users
INTO OUTFILE '/tmp/users_export.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

不需要全库备份时，可以只备份关键表或按条件导出部分数据，减少备份时间和存储空间。

适用场景：只需要备份部分数据或迁移特定表

xtrabackup 物理备份（大库推荐）

# 安装 Percona XtraBackup
# apt install percona-xtrabackup-80

# 全量备份
xtrabackup --backup --target-dir=/data/backup/full \
  --user=root --password='pass'

# 准备备份（应用 redo log）
xtrabackup --prepare --target-dir=/data/backup/full

# 恢复（需要先停止 MySQL）
systemctl stop mysql
xtrabackup --copy-back --target-dir=/data/backup/full
chown -R mysql:mysql /var/lib/mysql
systemctl start mysql

xtrabackup 是物理备份工具，直接复制数据文件，备份和恢复速度远快于 mysqldump，适合大数据量场景。

适用场景：数据量 > 50GB 或需要快速恢复

注意事项

备份完成后务必验证备份文件可以正常恢复

不要把备份文件和数据库放在同一台服务器

恢复操作在生产环境执行前先在测试环境验证