Lessons

Database Backup Strategies

Level: Advanced Module: Database Services 13 min read Lesson 45 of 66

Overview

  • What you’ll learn: Logical and physical backup methods for MySQL and PostgreSQL, point-in-time recovery (PITR), automated backup scheduling with cron and systemd timers, backup verification and restoration testing, retention policies, and offsite backup strategies.
  • Prerequisites: Lessons 41-44 (MySQL and PostgreSQL installation, administration, and replication)
  • Estimated reading time: 25 minutes

Introduction

Backups are the last line of defense against data loss. Hardware failures, software bugs, human errors, and security breaches can all destroy or corrupt data. A robust backup strategy ensures that you can recover your databases to a known good state with minimal data loss and downtime.

Database backups fall into two broad categories: logical backups that export data as SQL statements or structured formats, and physical backups that copy the raw database files. Each approach has trade-offs in terms of speed, flexibility, and recovery time. A comprehensive backup strategy typically combines both methods and includes point-in-time recovery capabilities.

In this lesson you will learn the backup tools available for MySQL and PostgreSQL, configure automated backup schedules, implement point-in-time recovery, establish retention policies, and set up offsite backup storage for disaster recovery.

Backup Types

Logical Backups

Logical backups export the database as a series of SQL statements (CREATE TABLE, INSERT) or in a custom format that can be selectively restored. They are portable across versions and architectures but are slower to create and restore for large databases.

  • Advantages: Version-portable, selective restoration, human-readable (SQL format), can restore individual tables.
  • Disadvantages: Slower for large databases, does not capture physical storage optimizations, restoration rebuilds indexes from scratch.

Physical Backups

Physical backups copy the raw data files from the database’s data directory. They are much faster to create and restore but are tied to the specific database version and platform.

  • Advantages: Fast backup and restore, captures the exact state of the data directory, suitable for very large databases.
  • Disadvantages: Version- and platform-specific, requires the database to be stopped or use a consistent snapshot mechanism, larger backup size.

MySQL Backup Methods

mysqldump (Logical Backup)

# Backup a single database
$ mysqldump -u root -p --single-transaction --routines --triggers 
    myapp_db > /backup/mysql/myapp_db_$(date +%Y%m%d_%H%M%S).sql

# Backup all databases
$ mysqldump -u root -p --all-databases --single-transaction 
    --routines --triggers > /backup/mysql/all_dbs_$(date +%Y%m%d_%H%M%S).sql

# Backup specific tables
$ mysqldump -u root -p --single-transaction myapp_db users orders 
    > /backup/mysql/myapp_tables_$(date +%Y%m%d_%H%M%S).sql

# Compressed backup
$ mysqldump -u root -p --single-transaction myapp_db 
    | gzip > /backup/mysql/myapp_db_$(date +%Y%m%d_%H%M%S).sql.gz

The --single-transaction flag is essential for InnoDB tables. It starts a transaction before dumping, providing a consistent snapshot without locking tables.

Restoring from mysqldump

# Restore a database
$ mysql -u root -p myapp_db < /backup/mysql/myapp_db_20250101_020000.sql

# Restore from compressed backup
$ gunzip -c /backup/mysql/myapp_db_20250101_020000.sql.gz | mysql -u root -p myapp_db

# Restore all databases
$ mysql -u root -p < /backup/mysql/all_dbs_20250101_020000.sql

MySQL Physical Backup with Percona XtraBackup

# Install Percona XtraBackup
$ sudo apt install percona-xtrabackup-80 -y

# Create a full backup
$ sudo xtrabackup --backup --target-dir=/backup/mysql/full_$(date +%Y%m%d)

# Prepare the backup (apply WAL logs)
$ sudo xtrabackup --prepare --target-dir=/backup/mysql/full_20250101

# Restore: stop MySQL, replace data directory, start MySQL
$ sudo systemctl stop mysql
$ sudo rm -rf /var/lib/mysql/*
$ sudo xtrabackup --copy-back --target-dir=/backup/mysql/full_20250101
$ sudo chown -R mysql:mysql /var/lib/mysql
$ sudo systemctl start mysql

MySQL Point-in-Time Recovery

# Enable binary logging (should already be enabled for replication)
[mysqld]
log_bin = /var/log/mysql/mysql-bin
binlog_expire_logs_seconds = 604800

# Restore to a specific point in time
# 1. Restore the last full backup
$ mysql -u root -p myapp_db < /backup/mysql/myapp_db_20250101_020000.sql

# 2. Apply binary logs up to the desired point in time
$ mysqlbinlog --stop-datetime="2025-01-02 15:30:00" 
    /var/log/mysql/mysql-bin.000042 /var/log/mysql/mysql-bin.000043 
    | mysql -u root -p

# List binary log events to find the right stop point
$ mysqlbinlog --start-datetime="2025-01-02 14:00:00" 
    --stop-datetime="2025-01-02 16:00:00" 
    /var/log/mysql/mysql-bin.000043 | head -100

PostgreSQL Backup Methods

pg_dump (Logical Backup)

# Backup a single database (SQL format)
$ sudo -u postgres pg_dump myapp_db > /backup/pgsql/myapp_db_$(date +%Y%m%d_%H%M%S).sql

# Backup in custom format (allows selective restore)
$ sudo -u postgres pg_dump -Fc myapp_db > /backup/pgsql/myapp_db_$(date +%Y%m%d_%H%M%S).dump

# Backup all databases
$ sudo -u postgres pg_dumpall > /backup/pgsql/all_dbs_$(date +%Y%m%d_%H%M%S).sql

# Compressed SQL backup
$ sudo -u postgres pg_dump myapp_db | gzip > /backup/pgsql/myapp_db_$(date +%Y%m%d_%H%M%S).sql.gz

# Parallel backup (faster for large databases)
$ sudo -u postgres pg_dump -Fd -j 4 myapp_db -f /backup/pgsql/myapp_db_$(date +%Y%m%d)

Restoring from pg_dump

# Restore from SQL format
$ sudo -u postgres psql myapp_db < /backup/pgsql/myapp_db_20250101_020000.sql

# Restore from custom format (selective tables)
$ sudo -u postgres pg_restore -d myapp_db -t users /backup/pgsql/myapp_db_20250101_020000.dump

# Restore from custom format (full database)
$ sudo -u postgres pg_restore -d myapp_db --clean /backup/pgsql/myapp_db_20250101_020000.dump

# Parallel restore
$ sudo -u postgres pg_restore -d myapp_db -j 4 /backup/pgsql/myapp_db_20250101/

PostgreSQL Physical Backup with pg_basebackup

# Create a base backup
$ sudo -u postgres pg_basebackup 
    -D /backup/pgsql/base_$(date +%Y%m%d) 
    -Ft -z -P 
    -U repl_user -h localhost

# -Ft: tar format
# -z: gzip compression
# -P: show progress

PostgreSQL Point-in-Time Recovery

# Ensure WAL archiving is enabled
# /etc/postgresql/16/main/postgresql.conf
archive_mode = on
archive_command = 'cp %p /backup/pgsql/wal_archive/%f'
restore_command = 'cp /backup/pgsql/wal_archive/%f %p'

# Perform PITR
# 1. Stop PostgreSQL
$ sudo systemctl stop postgresql

# 2. Replace the data directory with the base backup
$ sudo rm -rf /var/lib/postgresql/16/main/*
$ sudo -u postgres tar xzf /backup/pgsql/base_20250101/base.tar.gz 
    -C /var/lib/postgresql/16/main/

# 3. Create recovery configuration
$ sudo -u postgres cat > /var/lib/postgresql/16/main/postgresql.auto.conf << 'EOF'
restore_command = 'cp /backup/pgsql/wal_archive/%f %p'
recovery_target_time = '2025-01-02 15:30:00'
recovery_target_action = 'promote'
EOF

# 4. Create the recovery signal file
$ sudo -u postgres touch /var/lib/postgresql/16/main/recovery.signal

# 5. Start PostgreSQL — it will recover to the specified time
$ sudo systemctl start postgresql

Automated Backup Scheduling

Using Cron

# Create the backup script
$ sudo nano /usr/local/bin/db-backup.sh
#!/bin/bash
# Database backup script
set -euo pipefail

BACKUP_DIR="/backup"
DATE=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=30
LOG="/var/log/db-backup.log"

echo "[$DATE] Starting database backups..." >> "$LOG"

# MySQL backup
mysqldump -u backup_user --single-transaction --routines --triggers 
    --all-databases 2>>"$LOG" | gzip > "$BACKUP_DIR/mysql/all_dbs_${DATE}.sql.gz"
echo "[$DATE] MySQL backup completed." >> "$LOG"

# PostgreSQL backup
sudo -u postgres pg_dumpall 2>>"$LOG" | gzip > "$BACKUP_DIR/pgsql/all_dbs_${DATE}.sql.gz"
echo "[$DATE] PostgreSQL backup completed." >> "$LOG"

# Remove old backups
find "$BACKUP_DIR/mysql" -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete
find "$BACKUP_DIR/pgsql" -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete
echo "[$DATE] Old backups cleaned up (retention: ${RETENTION_DAYS} days)." >> "$LOG"
# Make it executable
$ sudo chmod +x /usr/local/bin/db-backup.sh

# Schedule nightly backups at 2:00 AM
$ sudo crontab -e
0 2 * * * /usr/local/bin/db-backup.sh

Using systemd Timers

# /etc/systemd/system/db-backup.service
[Unit]
Description=Database Backup Service

[Service]
Type=oneshot
ExecStart=/usr/local/bin/db-backup.sh
User=root
# /etc/systemd/system/db-backup.timer
[Unit]
Description=Run database backup nightly

[Timer]
OnCalendar=*-*-* 02:00:00
Persistent=true

[Install]
WantedBy=timers.target
# Enable and start the timer
$ sudo systemctl enable db-backup.timer
$ sudo systemctl start db-backup.timer

# Check timer status
$ sudo systemctl list-timers db-backup.timer

Backup Verification

A backup that has never been tested is not a backup. Regularly verify your backups by restoring them to a test environment:

# Verify a MySQL backup
$ gunzip -c /backup/mysql/all_dbs_20250101_020000.sql.gz | mysql -u root -p test_restore_db

# Verify a PostgreSQL backup
$ sudo -u postgres createdb test_restore_db
$ gunzip -c /backup/pgsql/all_dbs_20250101_020000.sql.gz | sudo -u postgres psql test_restore_db

# Check row counts match production
$ mysql -u root -p -e "SELECT COUNT(*) FROM test_restore_db.users;"
$ sudo -u postgres psql test_restore_db -c "SELECT COUNT(*) FROM users;"

# Clean up test databases
$ mysql -u root -p -e "DROP DATABASE test_restore_db;"
$ sudo -u postgres dropdb test_restore_db

Offsite Backup Storage

# Sync backups to a remote server using rsync
$ rsync -avz --delete /backup/ backup-user@offsite-server:/backup/

# Upload to AWS S3
$ aws s3 sync /backup/ s3://company-db-backups/ --storage-class STANDARD_IA

# Upload to Google Cloud Storage
$ gsutil -m rsync -r /backup/ gs://company-db-backups/

# Encrypt backups before transfer
$ gpg --symmetric --cipher-algo AES256 
    /backup/mysql/all_dbs_20250101_020000.sql.gz

Backup Strategy Summary

A comprehensive backup strategy follows the 3-2-1 rule:

  • 3 copies of your data (production + 2 backups)
  • 2 different storage media (local disk + remote/cloud)
  • 1 copy offsite (in a different geographic location)

Combine logical and physical backups with WAL/binlog archiving for comprehensive coverage:

  • Nightly: Logical backup (mysqldump / pg_dump) with compression
  • Weekly: Physical backup (XtraBackup / pg_basebackup)
  • Continuous: WAL archiving (PostgreSQL) or binary log retention (MySQL) for point-in-time recovery
  • Monthly: Restoration test to verify backup integrity

Key Takeaways

  • Logical backups (mysqldump, pg_dump) are portable and allow selective restoration; physical backups (XtraBackup, pg_basebackup) are faster for large databases.
  • Always use --single-transaction with mysqldump for InnoDB tables to get a consistent snapshot without locking.
  • Point-in-time recovery requires WAL archiving (PostgreSQL) or binary log retention (MySQL) in addition to base backups.
  • Automate backups with cron or systemd timers and implement retention policies to manage disk space.
  • Follow the 3-2-1 rule: 3 copies, 2 media types, 1 offsite.
  • A backup is only as good as its last successful restore test — verify backups regularly.
  • Encrypt backups before transferring to offsite or cloud storage.

What's Next

Congratulations on completing Module 6: Database Services! You now have a solid foundation in relational database theory, MySQL and PostgreSQL installation and administration, replication and high availability, and backup strategies. In Module 7, you will move on to Security and Authentication, where you will learn about Linux security fundamentals, SSH hardening, firewall configuration, AppArmor, and certificate management.

繁體中文

概覽

  • 您將學到：MySQL 和 PostgreSQL 的邏輯和物理備份方法、時間點恢復（PITR）、使用 cron 和 systemd 計時器的自動備份排程、備份驗證和還原測試、保留策略和異地備份策略。
  • 先決條件：第 41-44 課（MySQL 和 PostgreSQL 安裝、管理和複寫）
  • 預估閱讀時間：25 分鐘

簡介

備份是防止資料遺失的最後一道防線。硬體故障、軟體錯誤、人為失誤和安全漏洞都可能破壞或損壞資料。穩健的備份策略確保您可以將資料庫恢復到已知的良好狀態，將資料遺失和停機時間降至最低。

資料庫備份分為兩大類：將資料匯出為 SQL 語句或結構化格式的邏輯備份，以及複製原始資料庫檔案的物理備份。每種方法在速度、靈活性和恢復時間方面都有權衡。全面的備份策略通常結合兩種方法，並包含時間點恢復能力。

備份類型

邏輯備份

邏輯備份將資料庫匯出為一系列 SQL 語句或可選擇性還原的自訂格式。它們可跨版本和架構移植，但對大型資料庫的建立和還原較慢。

物理備份

物理備份複製資料庫資料目錄的原始檔案。建立和還原速度更快，但與特定資料庫版本和平台綁定。

MySQL 備份方法

mysqldump（邏輯備份）

# 備份單一資料庫
$ mysqldump -u root -p --single-transaction --routines --triggers 
    myapp_db > /backup/mysql/myapp_db_$(date +%Y%m%d_%H%M%S).sql

# 備份所有資料庫
$ mysqldump -u root -p --all-databases --single-transaction 
    > /backup/mysql/all_dbs_$(date +%Y%m%d_%H%M%S).sql

# 壓縮備份
$ mysqldump -u root -p --single-transaction myapp_db 
    | gzip > /backup/mysql/myapp_db_$(date +%Y%m%d_%H%M%S).sql.gz

還原 mysqldump

# 還原資料庫
$ mysql -u root -p myapp_db < /backup/mysql/myapp_db_20250101_020000.sql

# 從壓縮備份還原
$ gunzip -c /backup/mysql/myapp_db_20250101_020000.sql.gz | mysql -u root -p myapp_db

使用 Percona XtraBackup 的物理備份

# 安裝 Percona XtraBackup
$ sudo apt install percona-xtrabackup-80 -y

# 建立完整備份
$ sudo xtrabackup --backup --target-dir=/backup/mysql/full_$(date +%Y%m%d)

# 準備備份
$ sudo xtrabackup --prepare --target-dir=/backup/mysql/full_20250101

# 還原
$ sudo systemctl stop mysql
$ sudo rm -rf /var/lib/mysql/*
$ sudo xtrabackup --copy-back --target-dir=/backup/mysql/full_20250101
$ sudo chown -R mysql:mysql /var/lib/mysql
$ sudo systemctl start mysql

MySQL 時間點恢復

# 1. 還原最後一次完整備份
$ mysql -u root -p myapp_db < /backup/mysql/myapp_db_20250101_020000.sql

# 2. 套用二進位日誌到所需時間點
$ mysqlbinlog --stop-datetime="2025-01-02 15:30:00" 
    /var/log/mysql/mysql-bin.000042 | mysql -u root -p

PostgreSQL 備份方法

pg_dump（邏輯備份）

# 備份單一資料庫（SQL 格式）
$ sudo -u postgres pg_dump myapp_db > /backup/pgsql/myapp_db_$(date +%Y%m%d_%H%M%S).sql

# 自訂格式備份（允許選擇性還原）
$ sudo -u postgres pg_dump -Fc myapp_db > /backup/pgsql/myapp_db_$(date +%Y%m%d_%H%M%S).dump

# 備份所有資料庫
$ sudo -u postgres pg_dumpall > /backup/pgsql/all_dbs_$(date +%Y%m%d_%H%M%S).sql

還原 pg_dump

# 從 SQL 格式還原
$ sudo -u postgres psql myapp_db < /backup/pgsql/myapp_db_20250101_020000.sql

# 從自訂格式還原（選擇性表）
$ sudo -u postgres pg_restore -d myapp_db -t users /backup/pgsql/myapp_db_20250101_020000.dump

PostgreSQL 時間點恢復

# 確保啟用 WAL 歸檔
archive_mode = on
archive_command = 'cp %p /backup/pgsql/wal_archive/%f'

# 1. 停止 PostgreSQL
$ sudo systemctl stop postgresql

# 2. 用基礎備份替換資料目錄
$ sudo rm -rf /var/lib/postgresql/16/main/*
$ sudo -u postgres tar xzf /backup/pgsql/base_20250101/base.tar.gz 
    -C /var/lib/postgresql/16/main/

# 3. 設定恢復目標時間
# postgresql.auto.conf
restore_command = 'cp /backup/pgsql/wal_archive/%f %p'
recovery_target_time = '2025-01-02 15:30:00'
recovery_target_action = 'promote'

# 4. 建立恢復信號檔
$ sudo -u postgres touch /var/lib/postgresql/16/main/recovery.signal

# 5. 啟動 PostgreSQL
$ sudo systemctl start postgresql

自動備份排程

使用 Cron

#!/bin/bash
# 資料庫備份腳本
set -euo pipefail

BACKUP_DIR="/backup"
DATE=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=30

# MySQL 備份
mysqldump -u backup_user --single-transaction --all-databases 
    | gzip > "$BACKUP_DIR/mysql/all_dbs_${DATE}.sql.gz"

# PostgreSQL 備份
sudo -u postgres pg_dumpall | gzip > "$BACKUP_DIR/pgsql/all_dbs_${DATE}.sql.gz"

# 清理舊備份
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete
# 排程每晚凌晨 2 點備份
$ sudo crontab -e
0 2 * * * /usr/local/bin/db-backup.sh

備份驗證

從未測試過的備份不算是備份。定期將備份還原到測試環境進行驗證：

# 驗證 MySQL 備份
$ gunzip -c /backup/mysql/all_dbs_20250101.sql.gz | mysql -u root -p test_restore_db

# 驗證 PostgreSQL 備份
$ sudo -u postgres createdb test_restore_db
$ gunzip -c /backup/pgsql/all_dbs_20250101.sql.gz | sudo -u postgres psql test_restore_db

異地備份儲存

# 使用 rsync 同步到遠端伺服器
$ rsync -avz --delete /backup/ backup-user@offsite-server:/backup/

# 上傳到 AWS S3
$ aws s3 sync /backup/ s3://company-db-backups/ --storage-class STANDARD_IA

# 傳輸前加密備份
$ gpg --symmetric --cipher-algo AES256 /backup/mysql/all_dbs_20250101.sql.gz

備份策略總結

全面的備份策略遵循 3-2-1 規則：

  • 3 份資料副本（生產環境 + 2 份備份）
  • 2 種不同的儲存媒體（本地磁碟 + 遠端/雲端）
  • 1 份異地副本（在不同的地理位置）

重點回顧

  • 邏輯備份（mysqldump、pg_dump）可移植且允許選擇性還原；物理備份（XtraBackup、pg_basebackup）對大型資料庫更快。
  • InnoDB 表的 mysqldump 始終使用 --single-transaction 取得一致性快照而不鎖定。
  • 時間點恢復需要 WAL 歸檔（PostgreSQL）或二進位日誌保留（MySQL），以及基礎備份。
  • 使用 cron 或 systemd 計時器自動化備份，實施保留策略管理磁碟空間。
  • 遵循 3-2-1 規則：3 份副本、2 種媒體類型、1 份異地。
  • 備份的價值取決於最後一次成功的還原測試——定期驗證備份。
  • 傳輸到異地或雲端儲存前加密備份。

下一步

恭喜您完成模組 6：資料庫服務！您現在擁有了關聯式資料庫理論、MySQL 和 PostgreSQL 安裝與管理、複寫和高可用性以及備份策略的紮實基礎。在模組 7 中，您將進入安全與認證，學習 Linux 安全基礎、SSH 強化、防火牆設定、AppArmor 和憑證管理。

日本語

概要

  • 学習内容：MySQL と PostgreSQL の論理的・物理的バックアップ方法、ポイントインタイムリカバリ（PITR）、cron と systemd タイマーによる自動バックアップスケジューリング、バックアップ検証とリストアテスト、保持ポリシー、オフサイトバックアップ戦略。
  • 前提条件：レッスン 41-44（MySQL と PostgreSQL のインストール、管理、レプリケーション）
  • 推定読了時間：25 分

はじめに

バックアップはデータ損失に対する最後の防衛線です。ハードウェア障害、ソフトウェアバグ、人的ミス、セキュリティ侵害はすべてデータを破壊または損傷させる可能性があります。堅牢なバックアップ戦略により、最小限のデータ損失とダウンタイムで既知の正常な状態にデータベースを復旧できることが保証されます。

データベースバックアップは2つの大きなカテゴリに分けられます：データを SQL 文や構造化フォーマットとしてエクスポートする論理バックアップと、生のデータベースファイルをコピーする物理バックアップです。各アプローチには速度、柔軟性、リカバリ時間の面でトレードオフがあります。

バックアップの種類

論理バックアップ

論理バックアップはデータベースを SQL 文のシリーズまたは選択的にリストアできるカスタムフォーマットとしてエクスポートします。バージョンとアーキテクチャ間で移植可能ですが、大規模データベースでは作成とリストアが遅くなります。

物理バックアップ

物理バックアップはデータベースのデータディレクトリから生のデータファイルをコピーします。作成とリストアがはるかに高速ですが、特定のデータベースバージョンとプラットフォームに紐付けられます。

MySQL バックアップ方法

mysqldump（論理バックアップ）

# 単一データベースのバックアップ
$ mysqldump -u root -p --single-transaction --routines --triggers 
    myapp_db > /backup/mysql/myapp_db_$(date +%Y%m%d_%H%M%S).sql

# 全データベースのバックアップ
$ mysqldump -u root -p --all-databases --single-transaction 
    > /backup/mysql/all_dbs_$(date +%Y%m%d_%H%M%S).sql

# 圧縮バックアップ
$ mysqldump -u root -p --single-transaction myapp_db 
    | gzip > /backup/mysql/myapp_db_$(date +%Y%m%d_%H%M%S).sql.gz

mysqldump からのリストア

# データベースのリストア
$ mysql -u root -p myapp_db < /backup/mysql/myapp_db_20250101_020000.sql

# 圧縮バックアップからのリストア
$ gunzip -c /backup/mysql/myapp_db_20250101_020000.sql.gz | mysql -u root -p myapp_db

Percona XtraBackup による物理バックアップ

# Percona XtraBackup のインストール
$ sudo apt install percona-xtrabackup-80 -y

# フルバックアップの作成
$ sudo xtrabackup --backup --target-dir=/backup/mysql/full_$(date +%Y%m%d)

# バックアップの準備
$ sudo xtrabackup --prepare --target-dir=/backup/mysql/full_20250101

# リストア
$ sudo systemctl stop mysql
$ sudo rm -rf /var/lib/mysql/*
$ sudo xtrabackup --copy-back --target-dir=/backup/mysql/full_20250101
$ sudo chown -R mysql:mysql /var/lib/mysql
$ sudo systemctl start mysql

MySQL ポイントインタイムリカバリ

# 1. 最後のフルバックアップをリストア
$ mysql -u root -p myapp_db < /backup/mysql/myapp_db_20250101_020000.sql

# 2. 目的の時点までバイナリログを適用
$ mysqlbinlog --stop-datetime="2025-01-02 15:30:00" 
    /var/log/mysql/mysql-bin.000042 | mysql -u root -p

PostgreSQL バックアップ方法

pg_dump（論理バックアップ）

# 単一データベースのバックアップ（SQL フォーマット）
$ sudo -u postgres pg_dump myapp_db > /backup/pgsql/myapp_db_$(date +%Y%m%d_%H%M%S).sql

# カスタムフォーマット（選択的リストア可能）
$ sudo -u postgres pg_dump -Fc myapp_db > /backup/pgsql/myapp_db_$(date +%Y%m%d_%H%M%S).dump

# 全データベースのバックアップ
$ sudo -u postgres pg_dumpall > /backup/pgsql/all_dbs_$(date +%Y%m%d_%H%M%S).sql

pg_dump からのリストア

# SQL フォーマットからのリストア
$ sudo -u postgres psql myapp_db < /backup/pgsql/myapp_db_20250101_020000.sql

# カスタムフォーマットからのリストア（選択的テーブル）
$ sudo -u postgres pg_restore -d myapp_db -t users /backup/pgsql/myapp_db_20250101_020000.dump

PostgreSQL ポイントインタイムリカバリ

# WAL アーカイブの有効化
archive_mode = on
archive_command = 'cp %p /backup/pgsql/wal_archive/%f'

# 1. PostgreSQL を停止
$ sudo systemctl stop postgresql

# 2. ベースバックアップでデータディレクトリを置換
$ sudo rm -rf /var/lib/postgresql/16/main/*
$ sudo -u postgres tar xzf /backup/pgsql/base_20250101/base.tar.gz 
    -C /var/lib/postgresql/16/main/

# 3. リカバリ設定
# postgresql.auto.conf
restore_command = 'cp /backup/pgsql/wal_archive/%f %p'
recovery_target_time = '2025-01-02 15:30:00'
recovery_target_action = 'promote'

# 4. リカバリシグナルファイルの作成
$ sudo -u postgres touch /var/lib/postgresql/16/main/recovery.signal

# 5. PostgreSQL を起動
$ sudo systemctl start postgresql

自動バックアップスケジューリング

Cron の使用

#!/bin/bash
# データベースバックアップスクリプト
set -euo pipefail

BACKUP_DIR="/backup"
DATE=$(date +%Y%m%d_%H%M%S)
RETENTION_DAYS=30

# MySQL バックアップ
mysqldump -u backup_user --single-transaction --all-databases 
    | gzip > "$BACKUP_DIR/mysql/all_dbs_${DATE}.sql.gz"

# PostgreSQL バックアップ
sudo -u postgres pg_dumpall | gzip > "$BACKUP_DIR/pgsql/all_dbs_${DATE}.sql.gz"

# 古いバックアップの削除
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete
# 毎晩午前2時にバックアップをスケジュール
$ sudo crontab -e
0 2 * * * /usr/local/bin/db-backup.sh

バックアップ検証

テストされていないバックアップはバックアップではありません。定期的にバックアップをテスト環境にリストアして検証してください：

# MySQL バックアップの検証
$ gunzip -c /backup/mysql/all_dbs_20250101.sql.gz | mysql -u root -p test_restore_db

# PostgreSQL バックアップの検証
$ sudo -u postgres createdb test_restore_db
$ gunzip -c /backup/pgsql/all_dbs_20250101.sql.gz | sudo -u postgres psql test_restore_db

オフサイトバックアップストレージ

# rsync でリモートサーバーに同期
$ rsync -avz --delete /backup/ backup-user@offsite-server:/backup/

# AWS S3 にアップロード
$ aws s3 sync /backup/ s3://company-db-backups/ --storage-class STANDARD_IA

# 転送前にバックアップを暗号化
$ gpg --symmetric --cipher-algo AES256 /backup/mysql/all_dbs_20250101.sql.gz

バックアップ戦略のまとめ

包括的なバックアップ戦略は 3-2-1 ルールに従います：

  • 3 つのデータコピー（本番環境 + 2つのバックアップ）
  • 2 つの異なるストレージメディア（ローカルディスク + リモート/クラウド）
  • 1 つのオフサイトコピー（異なる地理的場所に）

重要ポイント

  • 論理バックアップ（mysqldump、pg_dump）は移植可能で選択的リストアが可能。物理バックアップ（XtraBackup、pg_basebackup）は大規模データベースに高速。
  • InnoDB テーブルの mysqldump には常に --single-transaction を使用してロックなしの一貫性スナップショットを取得。
  • ポイントインタイムリカバリにはベースバックアップに加えて WAL アーカイブ（PostgreSQL）またはバイナリログ保持（MySQL）が必要。
  • cron または systemd タイマーでバックアップを自動化し、保持ポリシーでディスク容量を管理。
  • 3-2-1 ルールに従う：3つのコピー、2つのメディアタイプ、1つのオフサイト。
  • バックアップの価値は最後に成功したリストアテストで決まる——定期的にバックアップを検証すること。
  • オフサイトまたはクラウドストレージに転送する前にバックアップを暗号化すること。

次のステップ

モジュール6：データベースサービスの完了おめでとうございます！リレーショナルデータベース理論、MySQL と PostgreSQL のインストールと管理、レプリケーションと高可用性、バックアップ戦略の堅固な基盤が身につきました。モジュール7では、セキュリティと認証に進み、Linux セキュリティの基礎、SSH の強化、ファイアウォール設定、AppArmor、証明書管理を学びます。

You Missed