バックアップ/リストア/ポイントインタイムリカバリ(PITR)の解説

2024.07.07
バックアップバックアップとは、データの破損に備えて、元データとは別にコピーを保存することです。
リストアリストアとは、バックアップを使ってデータを復元することです。
ポイントインタイムリカバリ (PITR)ポイントインタイムリカバリとは、バックアップ + ログである時点のデータに戻すことです
深夜 00:00 にバックアップを取得する場合
※ログ = バイナリログ (MySQL)、WAL ログ (PostgreSQL)、Redo ログ (Oracle)

初めに、バックアップ/リストア/ポイントインタイムリカバリ (PITR) のコマンドを記載します。

mysqldump -u root -p db durability > dump.sql
mysql -u root -p db < dump.sql
sudo mysqlbinlog /var/lib/mysql/binlog.000001 | mysql -u root -p
関連記事：データベースの基礎知識編
学習ロードマップ
おうちで学べるデータベースのきほん 第2版
おうちで学べるデータベースのきほん 第2版
翔泳社
Amazonの商品レビュー・口コミを見る
SQL 第2版: ゼロからはじめるデータベース操作
SQL 第2版: ゼロからはじめるデータベース操作
翔泳社
Amazonの商品レビュー・口コミを見る
スポンサーリンク

バックアップの種類

バックアップには、次の 3 つの方法があります。

  • ホットバックアップ/コールドバックアップ
  • 物理バックアップ/論理バックアップ
  • フルバックアップ/差分バックアップ/増分バックアップ

ホット/コールドバックアップ

ホットバックアップ (オンラインバックアップ)

ホットバックアップとは、データベースが稼働中にバックアップを取得する方法です。

コールドバックアップ (オフラインバックアップ)

コールドバックアップとは、データベースを停止してバックアップを取得する方法です。

RDBMS でホット/コールドバックアップする方法は、以下のとおりです。

MySQLPostgreSQL
ホットバックアップmysqldumppg_dump
コールドバックアップOS の cp, scp, rsync コマンドOS の cp, scp, rsync コマンド

物理/論理バックアップ

物理バックアップ

物理バックアップとは、実際のファイルをバックアップする方法です。

論理バックアップ

論理バックアップとは、データの復元手順 (SQL コマンド等) をバックアップする方法です。

「物理バックアップ」と「論理バックアップ」の性質の違いは、以下のとおりです。

性質物理バックアップ論理バックアップ
フォーマットバイナリ形式テキスト形式
バックアップ速度速い遅い
バイナリ⇔テキストの変換が必要
バックアップサイズ小さい大きい
データ書き換え困難容易 (SQL 文を書き換えるだけ)
移植性低い
他のバージョン/RDBMS は不可		高い
他のバージョン/RDBMS でも可
用途大規模リカバリ小規模リカバリ
別のマシンへ移植

RDBMS で物理/論理バックアップを取得する方法は、以下のとおりです。

MySQLPostgreSQL
物理バックアップMySQLEnterpriseBackup
OS の cp, scp, rsync コマンド		pg_basebackup
OS の cp, scp, rsync コマンド
論理バックアップmysqldump
mysqlpump		pg_dump

フル/差分/増分バックアップ

フルバックアップ

フルバックアップとは、すべてのデータをバックアップする方法です。

差分バックアップ

差分バックアップは、直近のフルバックアップ以降に更新したデータをバックアップする方法です。

増分バックアップ

増分バックアップは、直近のバックアップ以降に更新したデータをバックアップする方法です

「フルバックアップ」と「差分/増分バックアップ」の性質の違いは以下のとおりです。

性質フルバックアップ差分バックアップ増分バックアップ
リカバリ方法単純
フルバックアップだけ		複雑
フルと最後の差分		最も複雑
フルと全ての増分
リカバリ時間早い遅い最も遅い
バックアップ時間長い短い (変更したデータだけ)最も短い
バックアップサイズ大きい小さい (変更したデータだけ)最も小さい

RDBMS でフル/差分/増分バックアップを取得する方法は、以下のとおりです。

MySQLPostgreSQL
フルバックアップmysqldumppg_dump
差分/増分バックアップバイナリログ (後述)
MySQL Enterprise Backup		WAL ログ
※増分バックアップで使用
絵で見てわかるOS/ストレージ/ネットワーク 新装版
絵で見てわかるOS/ストレージ/ネットワーク 新装版
翔泳社
Amazonの商品レビュー・口コミを見る
達人に学ぶSQL徹底指南書 第2版 初級者で終わりたくないあなたへ
達人に学ぶSQL徹底指南書 第2版 初級者で終わりたくないあなたへ
翔泳社
Amazonの商品レビュー・口コミを見る
スポンサーリンク

バックアップ/リストアの手順,やり方

実際に mysqldump を利用してホット/論理/フルバックアップを取得してみます。

mysqldump でバックアップ

mysqldump -u ユーザー名 -p データベース名 テーブル名 > バックアップファイル名
mysqldump -u root -p db durability > dump.sql

mysqldump をリストア (復元)

mysql を利用して、mysqldump で取得したバックアップをリストアしてみます。

mysql -u ユーザー名 -p データベース名 < バックアップファイル名
select * from durability limit 3;
drop table durability;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
mysql -u root -p db < dump.sql
select * from durability limit 3;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
達人に学ぶDB設計 徹底指南書
達人に学ぶDB設計 徹底指南書
翔泳社
Amazonの商品レビュー・口コミを見る
SQLアンチパターン
SQLアンチパターン
オライリージャパン
Amazonの商品レビュー・口コミを見る
スポンサーリンク

ポイントインタイムリカバリ (PITR)

ポイントインタイムリカバリ (PITR)ポイントインタイムリカバリとは、バックアップ + ログである時点のデータに戻すことです。
深夜 00:00 にバックアップを取得する場合

適用するログにより、データを任意の時点に戻すことができます。

バックアップはデータベースに負荷をかけるため、高頻度で取得できませんが、ログは高頻度 (COMMIT する度) に取得できます。(つまり、バックアップより直近のデータまで復元できます)

※ログ = バイナリログ (MySQL)、WAL ログ (PostgreSQL)、Redo ログ (Oracle)

バイナリログ

バイナリログとは、テーブルやデータベースの変更が記録されるログです。

バイナリログの具体例は以下のとおりです。(binlog_format = ROW)

sudo od -ta /var/lib/mysql/binlog.000001
CREATE TABLE log (time date, request varchar(20));
BEGIN;
INSERT INTO log VALUES ( ‘2024/01/01’,‘GET /index’);

なお、Redo ログとの違いは以下で、物理的なページの内容に依存せずにデータを復元できます。

Redo ログバイナリログ
ログ形式物理ログ
MLOG_REC_UPDATE_IN_PLACE,
space:1, page_no:4...		論理ログ
UPDATE log SET id=100...
ログ出力InnoDB ストレージエンジン層MySQL サーバー層
利用用途クラッシュリカバリポイントインタイムリカバリ
レプリケーション

バイナリログの管理

SHOW MASTER STATUS\G
*************************** 1. row ***************************
             File: binlog.000003
         Position: 2212
     Binlog_Do_DB: 
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 12345678-1234-5678-9abc-123456789abc:1
SHOW MASTER LOGS;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |       501 | No        |
| binlog.000002 |       180 | No        |
| binlog.000003 |      2212 | No        |
+---------------+-----------+-----------+
FLUSH LOGS;
SHOW MASTER LOGS;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |       501 | No        |
| binlog.000002 |       180 | No        |
| binlog.000003 |      2212 | No        |
| binlog.000004 |       197 | No        |
+---------------+-----------+-----------+
RESET MASTER;
SHOW MASTER LOGS;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |       157 | No        |
+---------------+-----------+-----------+

MySQL でポイントインタイムリカバリ (PITR)

MySQL の mysqlbinlog を使って、実際にポイントインタイムリカバリを実施してみます。

事前準備

mysqldump -u root -p db durability > dump.sql
SELECT * FROM durability ORDER BY id ASC LIMIT 3;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
INSERT INTO durability VALUES(0);
SELECT * FROM durability ORDER BY id ASC LIMIT 3;
+------+
| id   |
+------+
|    0 |
|    1 |
|    2 |
+------+

リストアを PITR に含めないように、バイナリログを分けておきます。
(ログを分けずに、１つのログで時間を指定して PITR もできます。)

FLUSH BINARY LOGS;
SHOW BINARY LOGS;
+---------------+-----------+-----------+
| Log_name      | File_size | Encrypted |
+---------------+-----------+-----------+
| binlog.000001 |       478 | No        |
| binlog.000002 |       157 | No        |
+---------------+-----------+-----------+

id = 0 を INSERT した時点のバイナリログ (binlog.000001) をフラッシュし、新しいバイナリログ (binlog.000002) を作成しました。

ポイントインタイムリカバリ (PITR) を実施

以下の手順でポイントインタイムリカバリ (PITR) を実施します。

  1. バックアップをリストア
  2. mysqlbinlog でバイナリログを適用

バックアップをリストア

mysql -u root -p db < dump.sql
SELECT * FROM durability ORDER BY id ASC LIMIT 3;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+

id = 0 を INSERT する前に復元できていることが確認できます。

mysqlbinlog でバイナリログを適用

ここから、先ほど FLUSH した INSERT 時のバイナリログ (binlog.000001) を適用します。

リストア時の更新もバイナリログに残ります
sudo mysqlbinlog /var/lib/mysql/binlog.000001 | mysql -u root -p
SELECT * FROM durability ORDER BY id ASC LIMIT 3;
+------+
| id   |
+------+
|    0 |
|    1 |
|    2 |
+------+

バイナリログが適用できており、ポイントインタイムリカバリ (PITR) に成功しました。

失敗から学ぶRDBの正しい歩き方 (Software Design plus)
失敗から学ぶRDBの正しい歩き方 (Software Design plus)
技術評論社
Amazonの商品レビュー・口コミを見る
リレーショナルデータベース入門: データモデル・SQL・管理システム・NoSQL (Information&Computing 116)
リレーショナルデータベース入門: データモデル・SQL・管理システム・NoSQL (Information&Computing 116)
サイエンス社
Amazonの商品レビュー・口コミを見る

関連記事

学習ロードマップ
関連記事：データベースの基礎知識編
関連記事：データベース設計
データ指向アプリケーションデザイン ―信頼性、拡張性、保守性の高い分散システム設計の原理
データ指向アプリケーションデザイン ―信頼性、拡張性、保守性の高い分散システム設計の原理
オライリージャパン
Amazonの商品レビュー・口コミを見る
詳説 データベース ―ストレージエンジンと分散データシステムの仕組み
詳説 データベース ―ストレージエンジンと分散データシステムの仕組み
オライリージャパン
Amazonの商品レビュー・口コミを見る

参考記事

https://downloads.mysql.com/presentations/20151208_01_MySQL_Backup_for_Beginners.pdf
スポンサーリンク