sql-backup-database

SQL 数据库备份与恢复

数据库备份是灾难恢复、数据迁移和测试的关键过程。它涉及创建数据库副本，以便在数据丢失、损坏或系统故障时可以恢复。本教程涵盖了数据库备份的基本概念，并为现代数据库系统提供了实用示例。

数据库备份的核心概念

备份类型

• 全量备份（Full Backup）：整个数据库的完整副本。它包括所有数据、表结构、索引、存储过程和触发器。从全量备份恢复很简单，但可能很慢，并需要大量的存储空间。
• 差异备份（Differential Backup）：仅备份自上次全量备份以来发生更改的数据。恢复时需要上次全量备份加上最新的差异备份。它们比全量备份创建速度更快。
• 增量/事务日志备份（Incremental/Transaction Log Backup）：备份自上次任何类型备份以来发生的所有事务。这是最细粒度的备份形式，对于时间点恢复（Point-In-Time Recovery, PITR）至关重要。要恢复，你需要上次全量备份，可能还有上次差异备份，以及所有后续的事务日志备份。

逻辑备份与物理备份

大多数命令行工具，如 mysqldump 或 pg_dump，执行的是逻辑备份（logical backups）。它们将数据库内容导出为一组 SQL 语句（如 CREATE TABLE 和 INSERT）。物理备份（physical backups）则复制实际的数据文件，这通常更快，但在不同版本或操作系统之间灵活性较差。

按数据库系统划分的备份与恢复

备份操作的工具和语法特定于每个数据库管理系统（DBMS）。

PostgreSQL: 使用 pg_dump 和 pg_restore

pg_dump 是一个强大的命令行工具，用于创建 PostgreSQL 数据库的逻辑备份。

# 创建一个压缩的自定义格式备份：
# -U: 用户名, -W: 提示输入密码, -F c: 自定义格式, -d: 数据库名
pg_dump -U postgres -W -F c -d mydatabase > mydatabase.dump

# 从备份文件恢复：
# -d: 目标数据库（必须存在且为空）
createdb -U postgres new_database
pg_restore -U postgres -W -d new_database mydatabase.dump

MySQL/MariaDB: 使用 mysqldump

mysqldump 是备份 MySQL 或 MariaDB 数据库的标准工具。

# 创建包含存储过程和触发器的备份：
# --routines: 包含存储过程/函数
# --triggers: 包含触发器
mysqldump -u username -p --routines --triggers mydatabase > mydatabase_backup.sql

# 恢复数据库：
# 首先，如果数据库不存在则创建它
mysql -u username -p -e "CREATE DATABASE IF NOT EXISTS new_database;"

# 然后，导入转储文件
mysql -u username -p new_database < mydatabase_backup.sql

MS SQL Server: 使用 T-SQL 命令

SQL Server 在查询编辑器中使用 BACKUP DATABASE 和 RESTORE DATABASE 命令。

-- 创建一个全量备份
BACKUP DATABASE TestDB
TO DISK = 'D:\Backups\TestDB_Full.bak'
WITH NOFORMAT, INIT, NAME = N'TestDB-Full Database Backup', COMPRESSION;
GO

-- 恢复数据库，可能会覆盖现有数据库
RESTORE DATABASE TestDB
FROM DISK = 'D:\Backups\TestDB_Full.bak'
WITH REPLACE, RECOVERY;
GO

数据库备份的现代最佳实践

• 自动化：使用 cron 作业、Windows 任务计划程序或 CI/CD 流水线安排定期备份。云服务（如 AWS RDS、Azure SQL）提供自动快照功能。
• 测试备份：如果备份无法恢复，那么它是无用的。定期在非生产环境中测试你的恢复过程。
• 3-2-1 原则：至少保留三份数据副本，存储在两种不同类型的介质上，其中一份副本存储在异地（或不同的云区域）。
• 加密：加密你的备份文件以保护敏感数据，特别是当它们存储在云端或便携式介质上时。
• 监控：监控你的备份作业，确保它们成功完成，并在失败时收到警报。