データベース

データベースのバックアップとリカバリとは？データ保護の実践

公開日: 2024年2月10日著者: mogura

疑問

データベースのバックアップとリカバリは、どのように実装すればよいのでしょうか？データ保護の実践について一緒に学んでいきましょう。

導入

データベースのバックアップとリカバリは、データ損失を防ぐための重要な対策です。適切なバックアップ戦略により、システム障害や人的ミスからデータを保護し、迅速に復旧することができます。

本記事では、バックアップの種類から、実践的なバックアップ戦略、リカバリ手順、災害対策まで、詳しく解説していきます。

バックアップとリカバリのイメージ

解説

1. バックアップの重要性

データベースのバックアップは、ハードウェア障害、ソフトウェア障害、人的ミス、サイバー攻撃、災害など、様々なリスクからデータを保護するための重要な対策です。適切なバックアップ戦略により、データ損失を防ぎ、迅速に復旧することができます。

データ損失のリスク

ハードウェア障害: ディスクの故障、サーバーのクラッシュ、メモリエラーなど、ハードウェアの故障によりデータが失われる可能性があります。
ソフトウェア障害: データベースのバグ、OSのクラッシュ、アプリケーションのエラーなど、ソフトウェアの問題によりデータが破損する可能性があります。
人的ミス: 誤ったデータ削除、誤った更新、誤った設定変更など、人的ミスによりデータが失われる可能性があります。
サイバー攻撃: ランサムウェア、データ破壊、不正アクセスなど、サイバー攻撃によりデータが失われる可能性があります。
災害: 火災、地震、洪水、停電など、自然災害によりデータが失われる可能性があります。

参考リンク： Database Backup Strategies - バックアップ戦略の基本概念とベストプラクティスについての詳細な説明

2. バックアップの種類

バックアップには、フルバックアップ、差分バックアップ、増分バックアップなど、様々な種類があります。それぞれ異なる特性を持ち、用途に応じて適切な種類を選択することが重要です。

フルバックアップ

フルバックアップは、データベース全体をバックアップします。リカバリが最も簡単ですが、時間とストレージを多く消費します。通常、週次または月次で実行されます。

差分バックアップ

差分バックアップは、最後のフルバックアップ以降のすべての変更をバックアップします。フルバックアップと最新の差分バックアップのみでリカバリできるため、リカバリが比較的簡単ですが、時間が経つにつれてサイズが大きくなります。

増分バックアップ

増分バックアップは、最後のバックアップ（フルまたは増分）以降の変更をバックアップします。ストレージを最も効率的に使用できますが、リカバリにはフルバックアップとすべての増分バックアップが必要です。

バックアップの種類と実行例

これらの例では、フルバックアップ、差分バックアップ、増分バックアップの実行方法を示しています。それぞれ異なる頻度で実行し、ストレージとリカバリ時間のバランスを取ります。

# バックアップ戦略の例
# 1. フルバックアップ（毎週日曜日）
mysqldump --all-databases > full_backup_$(date +%Y%m%d).sql

# 2. 差分バックアップ（毎日）
# 最後のフルバックアップ以降の変更をバックアップ
mysqldump --all-databases --where="updated_at > '2024-02-10'" > diff_backup_$(date +%Y%m%d).sql

# 3. 増分バックアップ（毎時間）
# バイナリログを使用した増分バックアップ
mysqlbinlog --start-datetime="2024-02-10 10:00:00" \
  --stop-datetime="2024-02-10 11:00:00" \
  mysql-bin.000001 > incremental_backup_$(date +%Y%m%d_%H%M%S).sql

3. MySQLのバックアップ方法

MySQLでは、mysqldumpを使用して論理バックアップを取得できます。また、バイナリログを使用してポイントインタイムリカバリ（PITR）を実現できます。

mysqldumpによるバックアップ

mysqldumpは、データベースの構造とデータをSQL文として出力します。テーブル構造、データ、ストアドプロシージャ、トリガーなどをバックアップできます。

オプションの説明

--single-transactionオプションは、トランザクション内でバックアップを実行し、一貫性のあるバックアップを取得できます。--routinesオプションはストアドプロシージャと関数を、--triggersオプションはトリガーをバックアップします。

バイナリログのバックアップ

バイナリログを有効にすることで、ポイントインタイムリカバリ（PITR）が可能になります。バイナリログを定期的にバックアップすることで、特定の時点までデータを復旧できます。

MySQLのバックアップ方法

これらの例では、mysqldumpを使用した様々なバックアップ方法を示しています。単一データベース、すべてのデータベース、圧縮バックアップ、リモートバックアップなどの方法があります。

# 単一データベースのバックアップ
mysqldump -u root -p mydb > mydb_backup.sql

# すべてのデータベースのバックアップ
mysqldump -u root -p --all-databases > all_databases_backup.sql

# トランザクションを使用した一貫性のあるバックアップ
mysqldump -u root -p --single-transaction --routines --triggers mydb > mydb_backup.sql

# 圧縮してバックアップ
mysqldump -u root -p --all-databases | gzip > backup_$(date +%Y%m%d).sql.gz

# バイナリログのバックアップ
mysqlbinlog --start-datetime="2024-02-10 00:00:00" \
  --stop-datetime="2024-02-10 23:59:59" \
  mysql-bin.000001 mysql-bin.000002 > binlog_backup.sql

# リモートサーバーからバックアップ
mysqldump -h remote_host -u root -p mydb > mydb_backup.sql

参考リンク： MySQL公式ドキュメント - mysqldump - mysqldumpの詳細なドキュメントとオプション一覧

4. PostgreSQLのバックアップ方法

PostgreSQLでは、pg_dumpを使用して論理バックアップを、pg_basebackupを使用して物理バックアップを取得できます。それぞれ異なる特性を持ち、用途に応じて適切な方法を選択します。

pg_dumpによるバックアップ

pg_dumpは、データベースの構造とデータをSQL文またはカスタム形式で出力します。テーブル構造、データ、ストアドプロシージャ、トリガーなどをバックアップできます。カスタム形式（-Fc）を使用すると、圧縮や並列リストアが可能になります。

pg_basebackupによる物理バックアップ

pg_basebackupは、データベースクラスタ全体を物理的にコピーします。論理バックアップよりも高速で、大規模なデータベースに適しています。WAL（Write-Ahead Logging）アーカイブと組み合わせることで、ポイントインタイムリカバリ（PITR）が可能になります。

PostgreSQLのバックアップ方法

これらの例では、pg_dumpとpg_basebackupを使用した様々なバックアップ方法を示しています。論理バックアップと物理バックアップの両方の方法があります。

# pg_dumpによる論理バックアップ
# 単一データベースのバックアップ（プレーンテキスト形式）
pg_dump -U postgres mydb > mydb_backup.sql

# カスタム形式（圧縮、並列リストア可能）
pg_dump -U postgres -Fc mydb > mydb_backup.dump

# すべてのデータベースのバックアップ
pg_dumpall -U postgres > all_databases_backup.sql

# スキーマのみのバックアップ
pg_dump -U postgres --schema-only mydb > mydb_schema.sql

# データのみのバックアップ
pg_dump -U postgres --data-only mydb > mydb_data.sql

# pg_basebackupによる物理バックアップ
pg_basebackup -U postgres -D /backup/postgresql -Ft -z -P

# WALアーカイブの設定（postgresql.conf）
# wal_level = replica
# archive_mode = on
# archive_command = 'cp %p /backup/wal/%f'

参考リンク： PostgreSQL公式ドキュメント - バックアップとリストア - PostgreSQLのバックアップとリストアの詳細なドキュメント

5. 自動バックアップスクリプト

自動バックアップスクリプトを実装し、cronで定期実行することで、手動操作なしで定期的にバックアップを取得できます。エラーハンドリングとログ記録を適切に実装することが重要です。

MySQLの自動バックアップスクリプト

MySQLの自動バックアップスクリプトでは、mysqldumpを使用してバックアップを取得し、古いバックアップを削除します。エラーハンドリングとログ記録を実装し、バックアップの成功・失敗を通知します。

cronで定期実行

cronを使用して、毎日、毎週、毎月など、定期的にバックアップスクリプトを実行します。crontabにエントリを追加することで、自動実行を設定できます。

PostgreSQLの自動バックアップスクリプト

PostgreSQLの自動バックアップスクリプトでは、pg_dumpまたはpg_basebackupを使用してバックアップを取得します。MySQLと同様に、エラーハンドリングとログ記録を実装します。

自動バックアップスクリプトの例

これらの例では、MySQLとPostgreSQLの自動バックアップスクリプトを示しています。エラーハンドリング、ログ記録、古いバックアップの削除を実装しています。

#!/bin/bash
# MySQL自動バックアップスクリプト

# 設定
DB_USER="root"
DB_PASS="password"
DB_NAME="mydb"
BACKUP_DIR="/backup/mysql"
RETENTION_DAYS=7
LOG_FILE="/var/log/mysql_backup.log"

# バックアップディレクトリの作成
mkdir -p $BACKUP_DIR

# バックアップファイル名
BACKUP_FILE="${BACKUP_DIR}/backup_${DB_NAME}_$(date +%Y%m%d_%H%M%S).sql.gz"

# バックアップの実行
mysqldump -u $DB_USER -p$DB_PASS --single-transaction --routines --triggers $DB_NAME | gzip > $BACKUP_FILE

# バックアップの成功確認
if [ $? -eq 0 ]; then
    echo "$(date): Backup successful: $BACKUP_FILE" >> $LOG_FILE
    
    # 古いバックアップの削除
    find $BACKUP_DIR -name "backup_${DB_NAME}_*.sql.gz" -mtime +$RETENTION_DAYS -delete
    echo "$(date): Old backups cleaned up" >> $LOG_FILE
else
    echo "$(date): Backup failed!" >> $LOG_FILE
    # エラー通知（メール送信など）
    exit 1
fi

# cron設定の例（毎日午前2時に実行）
# 0 2 * * * /path/to/mysql_backup.sh

# PostgreSQL自動バックアップスクリプト
#!/bin/bash
# PostgreSQL自動バックアップスクリプト

DB_NAME="mydb"
DB_USER="postgres"
BACKUP_DIR="/backup/postgresql"
RETENTION_DAYS=7
LOG_FILE="/var/log/postgresql_backup.log"

mkdir -p $BACKUP_DIR
BACKUP_FILE="${BACKUP_DIR}/backup_${DB_NAME}_$(date +%Y%m%d_%H%M%S).dump"

# pg_dumpでバックアップ
pg_dump -U $DB_USER -Fc $DB_NAME > $BACKUP_FILE

if [ $? -eq 0 ]; then
    echo "$(date): Backup successful: $BACKUP_FILE" >> $LOG_FILE
    find $BACKUP_DIR -name "backup_${DB_NAME}_*.dump" -mtime +$RETENTION_DAYS -delete
else
    echo "$(date): Backup failed!" >> $LOG_FILE
    exit 1
fi

6. リカバリ手順

リカバリは、バックアップからデータベースを復元するプロセスです。フルバックアップからのリカバリ、差分バックアップや増分バックアップとの組み合わせなど、様々なリカバリ方法があります。

MySQLのリカバリ

MySQLのリカバリでは、mysqlコマンドを使用してSQLファイルを実行します。データベースを削除して再作成し、バックアップファイルをインポートします。

PostgreSQLのリカバリ

PostgreSQLのリカバリでは、pg_restoreコマンド（カスタム形式の場合）またはpsqlコマンド（プレーンテキスト形式の場合）を使用します。データベースを削除して再作成し、バックアップファイルをリストアします。

リカバリ手順の例

これらの例では、MySQLとPostgreSQLのリカバリ手順を示しています。データベースを削除して再作成し、バックアップファイルからデータを復元します。

# MySQLのリカバリ
# 1. データベースの削除と再作成
mysql -u root -p -e "DROP DATABASE IF EXISTS mydb;"
mysql -u root -p -e "CREATE DATABASE mydb;"

# 2. バックアップからのリカバリ
mysql -u root -p mydb < mydb_backup.sql

# 圧縮されたバックアップからのリカバリ
zcat mydb_backup.sql.gz | mysql -u root -p mydb

# PostgreSQLのリカバリ
# 1. データベースの削除と再作成
psql -U postgres -c "DROP DATABASE IF EXISTS mydb;"
psql -U postgres -c "CREATE DATABASE mydb;"

# 2. プレーンテキスト形式からのリカバリ
psql -U postgres mydb < mydb_backup.sql

# 3. カスタム形式からのリカバリ
pg_restore -U postgres -d mydb mydb_backup.dump

# 4. 並列リストア（カスタム形式）
pg_restore -U postgres -d mydb -j 4 mydb_backup.dump

7. ポイントインタイムリカバリ（PITR）

ポイントインタイムリカバリ（PITR）は、特定の時点までデータを復旧する機能です。フルバックアップとトランザクションログ（バイナリログやWAL）を組み合わせることで実現できます。

MySQLのPITR

MySQLのPITRでは、フルバックアップをリストアし、その後のバイナリログを適用することで、特定の時点までデータを復旧します。バイナリログを有効にし、定期的にバックアップすることが重要です。

PostgreSQLのPITR

PostgreSQLのPITRでは、pg_basebackupで取得した物理バックアップをリストアし、その後のWALアーカイブを適用することで、特定の時点までデータを復旧します。WALアーカイブを有効にし、定期的にバックアップすることが重要です。

ポイントインタイムリカバリの例

これらの例では、MySQLとPostgreSQLのPITR手順を示しています。フルバックアップをリストアし、トランザクションログを適用することで、特定の時点までデータを復旧します。

# MySQLのPITR
# 1. フルバックアップのリストア
mysql -u root -p mydb < full_backup.sql

# 2. バイナリログの適用（特定の時点まで）
mysqlbinlog --start-datetime="2024-02-10 10:00:00" \
  --stop-datetime="2024-02-10 11:00:00" \
  mysql-bin.000001 mysql-bin.000002 | mysql -u root -p mydb

# PostgreSQLのPITR
# 1. 物理バックアップのリストア
pg_basebackup -D /var/lib/postgresql/data -Ft -z -P

# 2. recovery.confの設定
# restore_command = 'cp /backup/wal/%f %p'
# recovery_target_time = '2024-02-10 11:00:00'

# 3. PostgreSQLの起動（自動的にWALを適用）
pg_ctl start -D /var/lib/postgresql/data

8. レプリケーションによる高可用性

レプリケーションは、複数のデータベースサーバー間でデータを複製する機能です。マスター・スレーブ構成により、読み取り負荷の分散や高可用性を実現できます。

MySQLのレプリケーション設定

MySQLのレプリケーションでは、マスターサーバーでバイナリログを有効にし、スレーブサーバーでレプリケーションを設定します。マスターサーバーで発生した変更が、スレーブサーバーに自動的に複製されます。

PostgreSQLのレプリケーション設定

PostgreSQLのレプリケーションでは、プライマリサーバーでWALアーカイブを有効にし、スタンバイサーバーでレプリケーションを設定します。プライマリサーバーで発生した変更が、スタンバイサーバーに自動的に複製されます。

レプリケーション設定の例

これらの例では、MySQLとPostgreSQLのレプリケーション設定を示しています。マスター・スレーブ構成により、高可用性と読み取り負荷の分散を実現します。

# MySQLのレプリケーション設定
# マスターサーバー（my.cnf）
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW

# スレーブサーバーでの設定
CHANGE MASTER TO
  MASTER_HOST='master_host',
  MASTER_USER='repl_user',
  MASTER_PASSWORD='repl_password',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=154;

START SLAVE;

# レプリケーション状態の確認
SHOW SLAVE STATUS\G

# PostgreSQLのレプリケーション設定
# プライマリサーバー（postgresql.conf）
wal_level = replica
max_wal_senders = 3

# スタンバイサーバーでの設定（recovery.conf）
standby_mode = 'on'
primary_conninfo = 'host=primary_host port=5432 user=repl_user'

9. バックアップの検証

バックアップの検証は、バックアップが正しく取得されているかを確認し、リカバリテストを実施することで、災害時に迅速に復旧できる体制を整えることが重要です。

バックアップの整合性確認

バックアップファイルのサイズ、チェックサム、ファイルの存在を確認します。定期的にバックアップファイルを検証し、破損や欠損がないかを確認します。

リカバリテスト

リカバリテストでは、実際にバックアップからデータベースを復元し、データの整合性を確認します。定期的にリカバリテストを実施することで、災害時に迅速に復旧できる体制を整えます。

バックアップ検証の例

これらの例では、バックアップファイルの整合性確認とリカバリテストの方法を示しています。定期的に検証を実施することで、バックアップの信頼性を確保します。

# バックアップファイルの整合性確認
# ファイルサイズの確認
ls -lh /backup/mysql/backup_*.sql.gz

# チェックサムの確認
md5sum /backup/mysql/backup_mydb_20240210.sql.gz
sha256sum /backup/mysql/backup_mydb_20240210.sql.gz

# バックアップファイルの内容確認（圧縮ファイルの場合）
zcat /backup/mysql/backup_mydb_20240210.sql.gz | head -n 100

# リカバリテストスクリプト
#!/bin/bash
TEST_DB="mydb_test"
BACKUP_FILE="/backup/mysql/backup_mydb_20240210.sql.gz"

# テストデータベースの作成
mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS $TEST_DB;"

# バックアップからのリストア
zcat $BACKUP_FILE | mysql -u root -p $TEST_DB

# データの整合性確認
mysql -u root -p -e "SELECT COUNT(*) FROM $TEST_DB.users;"
mysql -u root -p -e "SELECT COUNT(*) FROM $TEST_DB.orders;"

# テストデータベースの削除
mysql -u root -p -e "DROP DATABASE $TEST_DB;"

echo "Recovery test completed successfully"

10. クラウドバックアップ

クラウドデータベースサービスでは、自動バックアップ機能が提供されています。AWS RDS、Google Cloud SQL、Azure Databaseなど、各クラウドプロバイダーが自動バックアップとポイントインタイムリカバリをサポートしています。

AWS RDSの自動バックアップ

AWS RDSでは、自動バックアップが有効になっている場合、毎日自動的にバックアップが取得されます。バックアップ保持期間は1〜35日間で設定でき、ポイントインタイムリカバリ（PITR）も利用できます。

Google Cloud SQLのバックアップ

Google Cloud SQLでは、自動バックアップが有効になっている場合、定期的にバックアップが取得されます。バックアップ保持期間は1〜365日間で設定でき、ポイントインタイムリカバリ（PITR）も利用できます。

Azure Databaseのバックアップ

Azure Databaseでは、自動バックアップが有効になっている場合、定期的にバックアップが取得されます。バックアップ保持期間は7〜35日間で設定でき、長期保持オプションにより最大10年間保持することもできます。

クラウドバックアップの設定例

これらの例では、AWS RDSとGoogle Cloud SQLのバックアップ設定と操作を示しています。クラウドサービスでは、自動バックアップとオンデマンドバックアップの両方が利用できます。

# AWS RDSのバックアップ設定（AWS CLI）
# 自動バックアップの有効化
aws rds modify-db-instance \
  --db-instance-identifier mydb \
  --backup-retention-period 7 \
  --preferred-backup-window "03:00-04:00"

# オンデマンドバックアップの作成
aws rds create-db-snapshot \
  --db-instance-identifier mydb \
  --db-snapshot-identifier mydb-snapshot-$(date +%Y%m%d)

# スナップショットからのリストア
aws rds restore-db-instance-from-db-snapshot \
  --db-instance-identifier mydb-restored \
  --db-snapshot-identifier mydb-snapshot-20240210

# Google Cloud SQLのバックアップ設定（gcloud CLI）
# 自動バックアップの有効化
gcloud sql instances patch mydb \
  --backup-start-time=03:00 \
  --enable-bin-log

# オンデマンドバックアップの作成
gcloud sql backups create --instance=mydb

# バックアップからのリストア
gcloud sql backups restore BACKUP_ID \
  --backup-instance=mydb

11. バックアップ戦略のベストプラクティス

バックアップ戦略のベストプラクティスに従うことで、データ損失を防ぎ、迅速に復旧できる体制を整えることができます。3-2-1ルールや適切なバックアップスケジュールを実装することが重要です。

3-2-1ルール

3-2-1ルールは、3つのコピー（本番データ、ローカルバックアップ、リモートバックアップ）、2つの異なるメディア、1つのオフサイトバックアップを推奨します。これにより、様々な障害からデータを保護できます。

バックアップスケジュール

フルバックアップは週次または月次、差分バックアップは日次、増分バックアップは時間次で実行します。データの重要度と変更頻度に応じて、適切なスケジュールを設定します。

3-2-1ルールの実装: 3つのコピー（本番データ、ローカルバックアップ、リモートバックアップ）、2つの異なるメディア、1つのオフサイトバックアップを実装します。
自動バックアップの実装: 手動操作に依存せず、自動バックアップスクリプトとcronを使用して、定期的にバックアップを取得します。
バックアップの検証: 定期的にバックアップファイルの整合性を確認し、リカバリテストを実施して、バックアップの信頼性を確保します。
バックアップの暗号化: 機密データを含むバックアップは、暗号化して保存します。転送時も暗号化（TLS/SSL）を使用します。
バックアップの保持期間: データの重要度と法的要件に応じて、適切な保持期間を設定します。古いバックアップは自動的に削除します。
ドキュメント化: バックアップ手順、リカバリ手順、連絡先などをドキュメント化し、災害時に迅速に対応できるようにします。

12. 災害対策計画（DRP）

災害対策計画（DRP）は、災害発生時に迅速に復旧するための計画です。RTO（Recovery Time Objective）とRPO（Recovery Point Objective）を定義し、適切なバックアップ戦略を実装することが重要です。

RTOとRPO

RTO（Recovery Time Objective）: RTOは、災害発生後、システムを復旧するまでの許容時間です。RTOが短いほど、迅速な復旧が必要で、レプリケーションや高可用性構成が必要になります。
RPO（Recovery Point Objective）: RPOは、災害発生時、どの時点までデータを復旧するかの許容範囲です。RPOが短いほど、頻繁なバックアップやレプリケーションが必要になります。

災害対策のチェックリスト

災害対策のチェックリストには、バックアップの確認、リカバリ手順の確認、連絡先の確認、リカバリテストの実施などが含まれます。定期的にチェックリストを確認し、更新します。

RTOとRPOの定義: ビジネス要件に基づいて、RTOとRPOを定義します。RTOが短い場合はレプリケーション、RPOが短い場合は頻繁なバックアップが必要です。
バックアップ戦略の実装: RTOとRPOに基づいて、適切なバックアップ戦略を実装します。フルバックアップ、差分バックアップ、増分バックアップを組み合わせます。
リカバリ手順のドキュメント化: リカバリ手順を詳細にドキュメント化し、誰でも実行できるようにします。定期的に手順を確認し、更新します。
リカバリテストの実施: 定期的にリカバリテストを実施し、リカバリ手順の有効性を確認します。テスト結果を記録し、改善点を特定します。
連絡先の確認: 災害発生時の連絡先（管理者、ベンダー、クラウドプロバイダーなど）を確認し、最新の状態を保ちます。
オフサイトバックアップ: オフサイトバックアップを実装し、災害時にデータを保護します。クラウドストレージやリモートサーバーを活用します。

まとめ

データベースのバックアップとリカバリは、データ損失を防ぐための重要な対策です。適切なバックアップ戦略（フル、差分、増分）を組み合わせ、自動化されたバックアップスクリプトを実装することで、定期的にバックアップを取得できます。

ポイントインタイムリカバリ（PITR）により、特定の時点までデータを復旧でき、レプリケーションによる高可用性構成により、システムの可用性を向上させることができます。定期的なリカバリテストとバックアップの検証により、災害時に迅速に復旧できる体制を整えることが重要です。

実践的なプロジェクトでバックアップとリカバリを実装し、継続的に改善していくことで、より堅牢なデータベースシステムを構築できます。