SQL 备份与恢复

学习备份 · 难度：高级 · +15XP

SQL 备份与恢复

数据是应用的核心资产，硬件故障、人为误操作、软件 Bug 都可能导致数据丢失。制定并执行定期备份策略是数据库管理中最重要的工作之一。备份让你在灾难发生后能够恢复到数据丢失前的状态，最大限度减少损失。

备份策略概述

常见的备份策略有三种：

策略	说明	优点	缺点
完整备份	备份整个数据库的所有数据	恢复最简单	耗时长、占用空间大
差异备份	只备份自上次完整备份以来变化的数据	比完整备份快	恢复需完整+差异
增量备份	只备份自上次任何备份以来变化的数据	最快、空间最小	恢复最复杂

各数据库的备份命令

不同数据库系统的备份方式各不相同：

-- ====== MySQL / MariaDB ======
-- 导出整个数据库（命令行）
mysqldump -u root -p 数据库名 > backup.sql
-- 导出指定表
mysqldump -u root -p 数据库名 表名 > table_backup.sql
-- 导出结构+数据，带删除表语句
mysqldump -u root -p --add-drop-table 数据库名 > backup.sql
-- ====== PostgreSQL ======
-- 导出数据库
pg_dump -U postgres 数据库名 > backup.sql
-- 自定义格式（可并行恢复）
pg_dump -U postgres -Fc 数据库名 > backup.dump
-- ====== SQLite ======
-- 使用 .backup 命令
.backup main backup.db
-- 或直接使用 .dump 导出SQL
.output backup.sql
.dump
.output stdout
-- ====== SQL Server ======
-- 完整备份
BACKUP DATABASE 数据库名 TO DISK = 'backup.bak';
-- 差异备份
BACKUP DATABASE 数据库名 TO DISK = 'backup.bak' WITH DIFFERENTIAL;

恢复数据

-- MySQL 恢复
mysql -u root -p 数据库名 < backup.sql
-- PostgreSQL 恢复
psql -U postgres 数据库名 < backup.sql
-- SQL Server 恢复
RESTORE DATABASE 数据库名 FROM DISK = 'backup.bak';
-- SQLite 恢复
.restore main backup.db

最佳实践

1. 定期备份：设置自动备份任务（cron/计划任务），建议每天一次完整备份
2. 异地存储：备份文件不要和数据库放在同一台服务器上
3. 验证备份：定期在测试环境恢复备份，确保备份文件可用
4. 保留多个版本：至少保留最近 7 天的备份，防止近期备份损坏
5. 备份前检查：确保数据库处于一致状态，必要时先锁表
6. 敏感数据加密：备份文件通常包含敏感信息，应加密存储

实战任务

1. 使用 mysqldump 导出一个测试数据库
2. 模拟误删数据后，使用备份文件恢复
3. 编写一个自动备份脚本，加入 crontab 定时执行