SQLのリカバリ（データ復旧）を完全ガイド！初心者でもわかるデータ損失対策
新人
「先輩！間違えてデータを削除してしまいました…どうすればいいですか？」
先輩
「落ち着いて！SQLにはデータ復旧（リカバリ）の仕組みがあるから、状況によって適切な方法を使えば復旧できる可能性があるよ。」
新人
「本当に復旧できるんですか？」
先輩
「大丈夫。データベースにはトランザクションログやバックアップを利用して復旧する方法があるんだ。順番に説明していくね。」
1. データベースのリカバリとは？
SQLのリカバリ（データ復旧）とは、誤操作やシステム障害によって失われたデータを元に戻す手法のことです。
リカバリが必要なケース
- 誤ってデータを削除してしまった（DELETEやDROP TABLEの実行）
- 更新ミスをしてしまった（間違った値にUPDATE）
- データベースサーバーがクラッシュした（障害発生）
リカバリの方法
リカバリには主に以下の方法があります。
- トランザクションログを利用する（ROLLBACK）
- バックアップデータから復元する（リストア）
- ポイントインタイムリカバリ（特定の時点のデータに戻す）
まずは、トランザクションログを利用したリカバリについて詳しく見ていきましょう。
2. トランザクションログを利用したリカバリ（COMMIT, ROLLBACKの仕組み）
データベースでは、変更を即座に確定するのではなく、一時的に保留するトランザクションという仕組みを使うことで、誤操作を防ぐことができます。
1. トランザクションとは？
トランザクションとは、データベースの処理を一連の操作としてまとめて管理する仕組みのことです。トランザクションを利用することで、データの整合性を保ちつつ、誤った操作を元に戻す（ROLLBACK）ことができます。
2. COMMIT（確定）と ROLLBACK（取り消し）
トランザクションには以下の2つの重要なコマンドがあります。
- COMMIT： 変更を確定してデータベースに適用する
- ROLLBACK： 変更を取り消して元の状態に戻す
COMMITとROLLBACKの使い方
START TRANSACTION;
UPDATE employees SET salary = salary * 1.1 WHERE department = 'Sales';
COMMIT;
このSQLでは、Sales部門の給料を10%増加させた後、COMMITを実行することで変更を確定させています。
START TRANSACTION;
UPDATE employees SET salary = salary * 1.1 WHERE department = 'Sales';
ROLLBACK;
この場合、ROLLBACKを実行することで、変更が適用されず元のデータに戻ります。
3. 誤ったDELETEのリカバリ
誤ってデータを削除してしまった場合、DELETEの前にトランザクションを開始していれば、ROLLBACKで復旧できます。
START TRANSACTION;
DELETE FROM employees WHERE id = 5;
ROLLBACK;
このように、トランザクションを活用することで、誤った操作を取り消すことができます。
4. 自動コミットの無効化
通常のSQLでは、変更は自動的に確定（コミット）されることがあります。そのため、大事なデータを扱う場合は、以下のようにAUTOCOMMITを無効にするのがおすすめです。
SET AUTOCOMMIT = 0;
START TRANSACTION;
UPDATE employees SET salary = salary * 1.1 WHERE department = 'Sales';
COMMIT;
この設定を有効にすることで、明示的にCOMMITしない限り、変更は適用されません。
トランザクションを利用することで、データの安全性を高めることができます。
次のセクションでは、バックアップとリストアを利用したリカバリ方法について詳しく解説します。
4. バックアップとリストア（mysqldump, pg_dump, RMAN）
データの復旧を確実に行うためには、事前にバックアップを取得しておくことが重要です。データベースのバックアップを定期的に取得しておくことで、障害や誤操作が発生した際に、迅速にリカバリを行うことができます。
1. MySQLのバックアップ（mysqldump）
MySQLでは、mysqldumpコマンドを使用してデータベースのバックアップを取得できます。
データベース全体をバックアップ
mysqldump -u root -p database_name > backup.sql
特定のテーブルのみバックアップ
mysqldump -u root -p database_name employees > employees_backup.sql
バックアップデータのリストア
mysql -u root -p database_name < backup.sql
2. PostgreSQLのバックアップ（pg_dump）
PostgreSQLでは、pg_dumpを使用してバックアップを取得できます。
pg_dump -U postgres -W -F c -b -v -f "backup.dump" database_name
バックアップデータのリストア
pg_restore -U postgres -d database_name "backup.dump"
3. Oracleのバックアップ（RMAN）
Oracleでは、RMAN（Recovery Manager）を使用してバックアップを取得します。
RMAN> BACKUP DATABASE FORMAT '/backup/db_backup.bak';
バックアップデータのリストア
RMAN> RESTORE DATABASE;
このように、データベースのバックアップを取得し、必要に応じてリストアできるように準備しておきましょう。
5. ポイントインタイムリカバリ（特定の時点に戻す方法）
データベースでは、特定の時点にデータを戻すことができるポイントインタイムリカバリ（PITR）という機能があります。誤操作や障害が発生した場合に役立ちます。
1. MySQLでのポイントインタイムリカバリ
MySQLのバイナリログ（binlog）を利用すれば、特定の時点のデータに戻すことが可能です。
バイナリログを確認する
SHOW BINARY LOGS;
特定の時点までリストアする
mysqlbinlog --stop-datetime="2024-02-12 10:00:00" binlog.000001 | mysql -u root -p database_name
2. PostgreSQLでのポイントインタイムリカバリ
PostgreSQLでは、WAL（Write-Ahead Logging）を利用してPITRを実行できます。
リカバリ用のWAL設定
restore_command = 'cp /backup/wal/%f %p'
recovery_target_time = '2024-02-12 10:00:00'
これにより、指定した時間の直前までデータを復旧できます。
6. 誤って削除したデータの復旧方法（UNDOログの活用）
データを誤って削除した場合でも、データベースのUNDOログを利用すれば復元できる可能性があります。
1. MySQLのUNDOログを利用する
MySQLのInnoDBストレージエンジンでは、UNDOログを利用してデータを復元できます。
トランザクション内で削除したデータを復元
START TRANSACTION;
DELETE FROM employees WHERE id = 10;
ROLLBACK;
この場合、ROLLBACKを実行すれば削除前のデータが復元されます。
2. PostgreSQLのUNDOログを利用する
PostgreSQLでは、削除したデータを復元するにはpg_stat_activityを確認し、トランザクションを取り消すことができます。
実行中のトランザクションを確認
SELECT * FROM pg_stat_activity;
削除されたデータの復旧
ROLLBACK;
3. Oracleのフラッシュバック機能を利用する
Oracleデータベースでは、フラッシュバッククエリを利用して削除したデータを復元できます。
フラッシュバッククエリで削除前のデータを取得
SELECT * FROM employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '5' MINUTE);
削除したデータを復元
INSERT INTO employees SELECT * FROM employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '5' MINUTE);
このように、UNDOログを活用すれば、誤って削除したデータを復元できる可能性があります。
次のセクションでは、クラッシュリカバリやリカバリのパフォーマンス最適化について詳しく解説します。
7. クラッシュリカバリ（システム障害からの復旧）
データベースのクラッシュリカバリとは、システム障害や電源断などの予期せぬトラブルが発生した際に、データの整合性を確保しつつ復旧することを指します。
1. MySQLのクラッシュリカバリ
MySQLのInnoDBストレージエンジンでは、自動的にクラッシュリカバリが行われます。もし、リカバリが必要な場合は、次の手順で行います。
リカバリプロセスを確認
SHOW ENGINE INNODB STATUS;
強制リカバリを実行
リカバリに失敗した場合、my.cnfの設定を変更し、強制リカバリを試みます。
[mysqld]
innodb_force_recovery = 4
設定変更後、データベースを再起動することでリカバリを試みます。
2. PostgreSQLのクラッシュリカバリ
PostgreSQLは、WAL（Write-Ahead Logging）を利用してクラッシュリカバリを行います。
WALログを使用してリカバリを実行
pg_ctl start -D /var/lib/postgresql/data -r
クラッシュ後のリカバリモードでデータを復旧できます。
3. Oracleのクラッシュリカバリ
Oracleでは、RMAN（Recovery Manager）を使ってクラッシュリカバリを実行できます。
データベースの整合性を確認
RMAN> RECOVER DATABASE;
このコマンドを実行することで、クラッシュしたデータを復元することができます。
8. リカバリのパフォーマンス最適化（ログ管理とインデックスの影響）
リカバリを迅速に行うためには、ログ管理とインデックスの適切な設定が重要です。
1. ログ管理の最適化
MySQLやPostgreSQLでは、適切にログを設定することでリカバリの速度を向上させることができます。
MySQLのバイナリログサイズを最適化
[mysqld]
expire_logs_days = 7
max_binlog_size = 100M
古いログを削除することで、ログ管理の負担を軽減できます。
2. インデックスの管理
リカバリ時には、インデックスの作成や削除が影響を及ぼすことがあります。
リカバリ後にインデックスを再作成
ALTER TABLE employees DROP INDEX idx_salary;
ALTER TABLE employees ADD INDEX idx_salary (salary);
リカバリ後にインデックスを再作成することで、データベースのパフォーマンスを最適化できます。
9. 実践！データ復旧のベストプラクティス（復旧計画の作成とテスト）
最後に、リカバリを確実に行うための復旧計画の作成とテスト手順について解説します。
1. バックアップ戦略を立てる
- 毎日フルバックアップを取得する
- トランザクションログを適切に保存する
- 障害発生時の手順を明確にしておく
2. 定期的にリカバリテストを実施
バックアップが正しく機能するか定期的にテストを行うことが重要です。
リストアテストの実行
mysql -u root -p test_database < backup.sql
3. 事前に復旧手順を文書化
復旧手順を文書化しておくことで、障害発生時に迅速に対応できます。
これで、SQLのリカバリに関する一連の手順を理解できました。定期的なバックアップとテストを行い、万が一の事態に備えましょう！
