Database Backup & Restore

数据库备份与恢复

Overview

概述

Implement comprehensive backup and disaster recovery strategies. Covers backup types, retention policies, restore testing, and recovery time objectives (RTO/RPO).

实施全面的备份与灾难恢复策略。涵盖备份类型、保留策略、恢复测试以及恢复时间目标（RTO/RPO）。

When to Use

适用场景

Backup automation setup
Disaster recovery planning
Recovery testing procedures
Backup retention policies
Point-in-time recovery (PITR)
Cross-region backup replication
Compliance and audit requirements

自动备份设置
灾难恢复规划
恢复测试流程
备份保留策略
时间点恢复（PITR）
跨区域备份复制
合规与审计要求

PostgreSQL Backup Strategies

PostgreSQL备份策略

Full Database Backup

全量数据库备份

pg_dump - Text Format:

bash

undefined

pg_dump - 文本格式：

bash

undefined

Simple full backup

pg_dump -h localhost -U postgres -F p database_name > backup.sql

With compression

pg_dump -h localhost -U postgres -F p database_name | gzip > backup.sql.gz

Backup with verbose output

pg_dump -h localhost -U postgres -F p -v database_name > backup.sql 2>&1

Exclude specific tables

pg_dump -h localhost -U postgres database_name
--exclude-table=temp_* --exclude-table=logs > backup.sql


**pg_dump - Custom Binary Format:**

```bash

pg_dump -h localhost -U postgres database_name
--exclude-table=temp_* --exclude-table=logs > backup.sql


**pg_dump - 自定义二进制格式：**

```bash

Custom binary format (better for large databases)

pg_dump -h localhost -U postgres -F c database_name > backup.dump

Parallel jobs for faster backup (PostgreSQL 9.3+)

pg_dump -h localhost -U postgres -F c -j 4
--load-via-partition-root database_name > backup.dump

Backup specific schema

pg_dump -h localhost -U postgres -n public database_name > backup.dump

Get backup info

pg_dump_all -h localhost -U postgres > all_databases.sql


**pg_basebackup - Physical Backup:**

```bash

pg_dump_all -h localhost -U postgres > all_databases.sql


**pg_basebackup - 物理备份：**

```bash

Take base backup for streaming replication

pg_basebackup -h localhost -D ./backup_data -U replication_user -v -P

Label backup for archival

pg_basebackup -h localhost -D ./backup_data
-U replication_user -l "backup_$(date +%Y%m%d)" -v -P

Tar format with compression

pg_basebackup -h localhost -D - -U replication_user
-Ft -z -l "backup_$(date +%s)" | tar -xz -C ./backups/

undefined

pg_basebackup -h localhost -D - -U replication_user
-Ft -z -l "backup_$(date +%s)" | tar -xz -C ./backups/

undefined

Incremental & Differential Backups

增量与差异备份

WAL Archiving Setup:

sql

-- postgresql.conf configuration
-- wal_level = replica
-- archive_mode = on
-- archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'
-- archive_timeout = 300

-- Monitor WAL archiving
SELECT
  name,
  setting
FROM pg_settings
WHERE name LIKE 'archive%';

-- Check WAL directory
-- ls -lh $PGDATA/pg_wal/

-- List archived WALs
-- ls -lh /archive/

Continuous WAL Backup:

bash

#!/bin/bash

WAL归档设置：

sql

-- postgresql.conf configuration
-- wal_level = replica
-- archive_mode = on
-- archive_command = 'test ! -f /archive/%f && cp %p /archive/%f'
-- archive_timeout = 300

-- Monitor WAL archiving
SELECT
  name,
  setting
FROM pg_settings
WHERE name LIKE 'archive%';

-- Check WAL directory
-- ls -lh $PGDATA/pg_wal/

-- List archived WALs
-- ls -lh /archive/

持续WAL备份：

bash

#!/bin/bash

Backup script with WAL archiving

BACKUP_DIR="/backups" DB_NAME="production" TIMESTAMP=$(date +%Y%m%d_%H%M%S)

Create base backup

pg_basebackup -h localhost -D $BACKUP_DIR/base_$TIMESTAMP
-U backup_user -v

Archive WAL files

WAL_DIR=$BACKUP_DIR/wal_$TIMESTAMP mkdir -p $WAL_DIR cp /var/lib/postgresql/14/main/pg_wal/* $WAL_DIR/

Compress backup

tar -czf $BACKUP_DIR/backup_$TIMESTAMP.tar.gz
$BACKUP_DIR/base_$TIMESTAMP $BACKUP_DIR/wal_$TIMESTAMP

Verify backup

pg_basebackup -h localhost -U backup_user --analyze

Upload to S3

aws s3 cp $BACKUP_DIR/backup_$TIMESTAMP.tar.gz
s3://backup-bucket/postgres/

undefined

aws s3 cp $BACKUP_DIR/backup_$TIMESTAMP.tar.gz
s3://backup-bucket/postgres/

undefined

MySQL Backup Strategies

MySQL备份策略

Full Database Backup

全量数据库备份

mysqldump - Text Format:

bash

undefined

mysqldump - 文本格式：

bash

undefined

Simple full backup

mysqldump -h localhost -u root -p database_name > backup.sql

All databases

mysqldump -h localhost -u root -p --all-databases > all_databases.sql

With flush privileges and triggers

mysqldump -h localhost -u root -p
--flush-privileges --triggers --routines
database_name > backup.sql

Parallel backup (MySQL 5.7.11+)

mydumper -h localhost -u root -p password
-o ./backup_dir --threads 4 --compress


**Backup Specific Tables:**

```bash

mydumper -h localhost -u root -p password
-o ./backup_dir --threads 4 --compress


**备份指定表：**

```bash

Backup specific tables

mysqldump -h localhost -u root -p database_name table1 table2 > tables.sql

Exclude tables

mysqldump -h localhost -u root -p database_name
--ignore-table=database_name.temp_table
--ignore-table=database_name.logs > backup.sql

undefined

mysqldump -h localhost -u root -p database_name
--ignore-table=database_name.temp_table
--ignore-table=database_name.logs > backup.sql

undefined

Binary Log Backups

二进制日志备份

Enable Binary Logging:

sql

-- Check binary logging status
SHOW VARIABLES LIKE 'log_bin%';

-- Configure in my.cnf
-- [mysqld]
-- log-bin = mysql-bin
-- binlog_format = ROW

-- View binary logs
SHOW BINARY LOGS;

-- Get current position
SHOW MASTER STATUS;

Binary Log Backup:

bash

undefined

启用二进制日志：

sql

-- Check binary logging status
SHOW VARIABLES LIKE 'log_bin%';

-- Configure in my.cnf
-- [mysqld]
-- log-bin = mysql-bin
-- binlog_format = ROW

-- View binary logs
SHOW BINARY LOGS;

-- Get current position
SHOW MASTER STATUS;

二进制日志备份：

bash

undefined

Backup binary logs

MYSQL_PWD="password" mysqldump -h localhost -u root
--single-transaction --flush-logs --all-databases > backup.sql

Copy binary logs

cp /var/log/mysql/mysql-bin.* /backup/binlogs/

Backup incremental changes

mysqlbinlog /var/log/mysql/mysql-bin.000001 > binlog_backup.sql

undefined

mysqlbinlog /var/log/mysql/mysql-bin.000001 > binlog_backup.sql

undefined

Restore Procedures

恢复流程

PostgreSQL Restore

PostgreSQL恢复

Restore from Text Backup:

bash

undefined

从文本备份恢复：

bash

undefined

Drop and recreate database

psql -h localhost -U postgres -c "DROP DATABASE IF EXISTS database_name;" psql -h localhost -U postgres -c "CREATE DATABASE database_name;"

Restore from text backup

psql -h localhost -U postgres database_name < backup.sql

Restore with verbose output

psql -h localhost -U postgres -1 database_name < backup.sql 2>&1 | tee restore.log


**Restore from Binary Backup:**

```bash

psql -h localhost -U postgres -1 database_name < backup.sql 2>&1 | tee restore.log


**从二进制备份恢复：**

```bash

Restore from custom format

pg_restore -h localhost -U postgres -d database_name
-v backup.dump

Parallel restore (faster)

pg_restore -h localhost -U postgres -d database_name
-j 4 -v backup.dump

Dry run (test restore without committing)

pg_restore --list backup.dump > restore_plan.txt


**Point-in-Time Recovery (PITR):**

```bash

pg_restore --list backup.dump > restore_plan.txt


**时间点恢复（PITR）：**

```bash

List available backups and WAL archives

ls -lh /archive/

Restore to specific point in time

pg_basebackup -h localhost -D ./recovery_data
-U replication_user -c fast

Create recovery.conf

cat > ./recovery_data/recovery.conf << EOF recovery_target_timeline = 'latest' recovery_target_xid = '1000000' recovery_target_time = '2024-01-15 14:30:00' recovery_target_name = 'before_bad_update' EOF

Start PostgreSQL with recovery

pg_ctl -D ./recovery_data start

undefined

pg_ctl -D ./recovery_data start

undefined

MySQL Restore

MySQL恢复

Restore from SQL Backup:

bash

undefined

从SQL备份恢复：

bash

undefined

Restore full database

mysql -h localhost -u root -p < backup.sql

Restore specific database

mysql -h localhost -u root -p database_name < database_backup.sql

Restore with progress

pv backup.sql | mysql -h localhost -u root -p database_name


**Restore with Binary Logs:**

```bash

pv backup.sql | mysql -h localhost -u root -p database_name


**结合二进制日志恢复：**

```bash

Restore from backup then apply binary logs

mysql -h localhost -u root -p < backup.sql

Get starting binary log position from backup

grep "SET @@GLOBAL.GTID_PURGED=" backup.sql

Apply binary logs after backup

mysqlbinlog /var/log/mysql/mysql-bin.000005
--start-position=12345 |
mysql -h localhost -u root -p database_name


**Point-in-Time Recovery:**

```bash

mysqlbinlog /var/log/mysql/mysql-bin.000005
--start-position=12345 |
mysql -h localhost -u root -p database_name


**时间点恢复：**

```bash

Restore base backup

mysql -h localhost -u root -p database_name < base_backup.sql

Apply binary logs up to specific time

mysqlbinlog /var/log/mysql/mysql-bin.000005
--stop-datetime='2024-01-15 14:30:00' |
mysql -h localhost -u root -p database_name

undefined

mysqlbinlog /var/log/mysql/mysql-bin.000005
--stop-datetime='2024-01-15 14:30:00' |
mysql -h localhost -u root -p database_name

undefined

Backup Validation

备份验证

PostgreSQL - Backup Integrity Check:

bash

undefined

PostgreSQL - 备份完整性检查：

bash

undefined

Verify backup file

pg_dump --analyze --schema-only database_name > /dev/null && echo "Backup OK"

Test restore procedure

createdb test_restore pg_restore -d test_restore backup.dump psql -d test_restore -c "SELECT COUNT(*) FROM information_schema.tables;" dropdb test_restore


**MySQL - Backup Integrity:**

```bash

createdb test_restore pg_restore -d test_restore backup.dump psql -d test_restore -c "SELECT COUNT(*) FROM information_schema.tables;" dropdb test_restore


**MySQL - 备份完整性：**

```bash

Check backup file syntax

mysql -h localhost -u root -p < backup.sql --dry-run

Verify checksum

md5sum backup.sql

Save checksum: echo "abc123def456 backup.sql" > backup.sql.md5

md5sum -c backup.sql.md5

undefined

md5sum -c backup.sql.md5

undefined

Automated Backup Schedule

自动备份计划

PostgreSQL - Cron Backup:

bash

#!/bin/bash

PostgreSQL - Cron备份：

bash

#!/bin/bash

backup.sh - Daily backup script

BACKUP_DIR="/backups/postgresql" RETENTION_DAYS=30 TIMESTAMP=$(date +%Y%m%d_%H%M%S)

Create backup

pg_dump -h localhost -U postgres mydb | gzip >
$BACKUP_DIR/backup_$TIMESTAMP.sql.gz

Delete old backups

find $BACKUP_DIR -name "backup_*.sql.gz" -mtime +$RETENTION_DAYS -delete

Upload to S3

aws s3 cp $BACKUP_DIR/backup_$TIMESTAMP.sql.gz
s3://backup-bucket/postgresql/

Log backup

echo "$TIMESTAMP: Backup completed" >> /var/log/db_backup.log


**Crontab Entry:**

```bash

echo "$TIMESTAMP: Backup completed" >> /var/log/db_backup.log


**Crontab配置：**

```bash

Daily backup at 2 AM

0 2 * * * /scripts/backup.sh

Hourly backup

0 * * * * /scripts/hourly_backup.sh

Weekly full backup

0 3 0 * * /scripts/weekly_backup.sh

undefined

0 3 0 * * /scripts/weekly_backup.sh

undefined

Backup Retention Policy

备份保留策略

PostgreSQL - Retention Strategy:

sql

-- Create retention tracking
CREATE TABLE backup_retention_policy (
  backup_id UUID PRIMARY KEY,
  database_name VARCHAR(255),
  backup_date TIMESTAMP,
  backup_type VARCHAR(20),  -- 'full', 'incremental', 'wal'
  retention_days INT,
  expires_at TIMESTAMP GENERATED ALWAYS AS
    (backup_date + INTERVAL '1 day' * retention_days) STORED
);

-- Example retention periods
INSERT INTO backup_retention_policy VALUES
('backup-001', 'production', NOW(), 'full', 30),
('backup-002', 'production', NOW(), 'incremental', 7),
('backup-003', 'staging', NOW(), 'full', 7);

-- Query expiring backups
SELECT backup_id, expires_at
FROM backup_retention_policy
WHERE expires_at < NOW();

PostgreSQL - 保留策略：

sql

-- Create retention tracking
CREATE TABLE backup_retention_policy (
  backup_id UUID PRIMARY KEY,
  database_name VARCHAR(255),
  backup_date TIMESTAMP,
  backup_type VARCHAR(20),  -- 'full', 'incremental', 'wal'
  retention_days INT,
  expires_at TIMESTAMP GENERATED ALWAYS AS
    (backup_date + INTERVAL '1 day' * retention_days) STORED
);

-- Example retention periods
INSERT INTO backup_retention_policy VALUES
('backup-001', 'production', NOW(), 'full', 30),
('backup-002', 'production', NOW(), 'incremental', 7),
('backup-003', 'staging', NOW(), 'full', 7);

-- Query expiring backups
SELECT backup_id, expires_at
FROM backup_retention_policy
WHERE expires_at < NOW();

RTO/RPO Planning

RTO/RPO规划

Recovery Time Objective (RTO): How quickly must the system recover
Recovery Point Objective (RPO): How much data loss is acceptable

Example:
- RTO: 1 hour (system must be recovered within 1 hour)
- RPO: 15 minutes (no more than 15 minutes of data loss acceptable)

Backup frequency: Every 15 minutes (to meet RPO)
Replication lag: < 5 minutes (for RTO)

Recovery Time Objective (RTO): How quickly must the system recover
Recovery Point Objective (RPO): How much data loss is acceptable

Example:
- RTO: 1 hour (system must be recovered within 1 hour)
- RPO: 15 minutes (no more than 15 minutes of data loss acceptable)

Backup frequency: Every 15 minutes (to meet RPO)
Replication lag: < 5 minutes (for RTO)

Best Practices Checklist

最佳实践清单

✅ DO test restore procedures regularly ✅ DO implement automated backups ✅ DO monitor backup success ✅ DO encrypt backup files ✅ DO store backups offsite ✅ DO document recovery procedures ✅ DO track backup retention policies ✅ DO monitor backup performance

❌ DON'T rely on untested backups ❌ DON'T skip backup verification ❌ DON'T store backups on same server ❌ DON'T use weak encryption ❌ DON'T forget backup retention limits

✅ 定期测试恢复流程 ✅ 实施自动备份 ✅ 监控备份成功状态 ✅ 加密备份文件 ✅ 异地存储备份 ✅ 记录恢复流程文档 ✅ 跟踪备份保留策略 ✅ 监控备份性能

❌ 不要依赖未测试的备份 ❌ 不要跳过备份验证 ❌ 不要将备份存储在同一服务器 ❌ 不要使用弱加密 ❌ 不要忘记备份保留限制

database-backup-restore

Original

Translation

Database Backup & Restore

数据库备份与恢复

Overview

概述

When to Use

适用场景

PostgreSQL Backup Strategies

PostgreSQL备份策略

Full Database Backup

全量数据库备份

Simple full backup

Simple full backup

With compression

With compression

Backup with verbose output

Backup with verbose output

Exclude specific tables

Exclude specific tables

Custom binary format (better for large databases)

Custom binary format (better for large databases)

Parallel jobs for faster backup (PostgreSQL 9.3+)

Parallel jobs for faster backup (PostgreSQL 9.3+)

Backup specific schema

Backup specific schema

Get backup info

Get backup info

Take base backup for streaming replication

Take base backup for streaming replication

Label backup for archival

Label backup for archival

Tar format with compression

Tar format with compression

Incremental & Differential Backups

增量与差异备份

Backup script with WAL archiving

Backup script with WAL archiving

Create base backup

Create base backup

Archive WAL files

Archive WAL files

Compress backup

Compress backup

Verify backup

Verify backup

Upload to S3

Upload to S3

MySQL Backup Strategies

MySQL备份策略

Full Database Backup

全量数据库备份

Simple full backup

Simple full backup

All databases

All databases

With flush privileges and triggers

With flush privileges and triggers

Parallel backup (MySQL 5.7.11+)

Parallel backup (MySQL 5.7.11+)

Backup specific tables

Backup specific tables

Exclude tables

Exclude tables

Binary Log Backups

二进制日志备份

Backup binary logs

Backup binary logs

Copy binary logs

Copy binary logs

Backup incremental changes

Backup incremental changes

Restore Procedures

恢复流程

PostgreSQL Restore

PostgreSQL恢复

Drop and recreate database

Drop and recreate database

Restore from text backup