MySQL 备份与恢复实战指南

没有备份的数据库，就像没有降落伞的飞机。 这不是夸张——每年都有无数公司因为数据库损坏或误操作导致数据丢失。

一、备份的重要性

什么时候需要备份？

二、mysqldump 备份工具

基本语法

# 备份单个数据库
mysqldump -u root -p database_name > backup.sql

# 备份多个数据库
mysqldump -u root -p --databases db1 db2 > backup.sql

# 备份所有数据库
mysqldump -u root -p --all-databases > all_backup.sql

常用参数

最佳实践

# InnoDB 热备（推荐，不影响业务）
mysqldump -u root -p 
  --single-transaction 
  --routines 
  --triggers 
  --databases www_wxdh_com 
  > /backup/www_wxdh_com_$(date +%Y%m%d).sql

三、数据恢复

基本恢复

# 方式一：命令行导入
mysql -u root -p database_name < backup.sql

# 方式二：MySQL 命令行内
mysql> source /path/to/backup.sql;

部分恢复

# 恢复单个表
mysqldump -u root -p database_name table_name > table_backup.sql
mysql -u root -p database_name < table_backup.sql

# 恢复部分数据
mysql -u root -p database_name -e "
  INSERT INTO users SELECT * FROM backup_db.users WHERE id = 1;
"

四、自动化备份脚本

Shell 脚本

#!/bin/bash
# /usr/local/bin/mysql_backup.sh

DB_USER="root"
DB_PASS="your_password"
DB_NAME="www_wxdh_com"
BACKUP_DIR="/backup"
RETENTION_DAYS=30
DATE=$(date +%Y%m%d_%H%M%S)

# 创建备份目录
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

# 删除30天前的备份
find $BACKUP_DIR -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete

echo "备份完成: ${DB_NAME}_${DATE}.sql.gz"

定时任务（crontab）

# 每天凌晨3点执行备份
crontab -e
0 3 * * * /usr/local/bin/mysql_backup.sh

五、物理备份（XtraBackup）

适用场景

安装 XtraBackup

# CentOS/Rocky
yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm
yum install -y percona-xtrabackup-80

# Ubuntu
sudo apt install percona-xtrabackup-80

全量备份

# 创建备份
xtrabackup --backup 
  --user=root 
  --password=your_password 
  --target-dir=/backup/full

# 准备恢复
xtrabackup --prepare 
  --target-dir=/backup/full

# 恢复
xtrabackup --copy-back 
  --target-dir=/backup/full 
  --datadir=/var/lib/mysql

六、误操作恢复实战

场景1：误删数据

-- 1. 如果开启了 binlog
-- 先恢复备份
mysql -u root -p db < backup.sql

-- 再恢复 binlog（从备份时间点到误操作前）
mysqlbinlog --start-datetime="2026-06-17 10:00:00" 
            --stop-datetime="2026-06-17 11:30:00" 
            /var/lib/mysql/binlog.000001 | mysql -u root -p db

场景2：误删表

# 从备份中恢复单个表
mysqldump -u root -p db_name dropped_table < backup.sql
mysql -u root -p db_name < dropped_table.sql

场景3：误修改数据

-- 开启事务，查询旧值
BEGIN;
SELECT * FROM users WHERE id = 1;  -- 记录旧值
-- 回滚
ROLLBACK;

-- 或者使用时间点恢复

七、验证备份

没验证过的备份，等于没有备份！

# 1. 检查备份文件完整性
gzip -t backup.sql.gz
echo $?  # 返回 0 表示正常

# 2. 检查 SQL 文件是否完整
tail -n 5 backup.sql
# 应该以 "-- Dump completed" 结尾

# 3. 定期做恢复演练
# 在测试环境执行恢复
mysql -u root -p test_db < backup.sql

# 4. 检查行数是否一致
mysql -u root -p test_db -e "SELECT COUNT(*) FROM users;"
mysql -u root -p production_db -e "SELECT COUNT(*) FROM users;"

八、备份检查清单

□ 数据库是否每天自动备份？
□ 备份文件是否存储在远程服务器？
□ 最近一次备份是否成功验证？
□ 是否知道恢复流程？
□ 是否有权限执行恢复？
□ 能否在 1 小时内完成全量恢复？

如果以上任何一项回答 否，请立即修复！

本篇小结

√ 掌握了 mysqldump 的完整用法
√ 学会了 InnoDB 热备技巧
√ 搭建了自动化备份脚本
√ 知道了 XtraBackup 物理备份方案
√ 实战了 3 种误操作恢复场景
√ 学会了如何验证备份有效性

这是 MySQL 教程系列的完结篇，恭喜你完成了从入门到实战的完整学习！