バックアップとポイントインタイムリカバリ (PITR) とは?

バックアップバックアップとは、データの破損に備えて、元データとは別にコピーを保存することです。
ポイントインタイムリカバリ (PITR)ポイントインタイムリカバリとは、バックアップ + ログである時点のデータに戻すことです。
深夜 00:00 にバックアップを取得する場合
データベースの基礎知識
スポンサーリンク

バックアップの種類

バックアップには、次の 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

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

フルバックアップ

フルバックアップとは、すべてのデータをバックアップする方法です。
https://downloads.mysql.com/presentations/20151208_01_MySQL_Backup_for_Beginners.pdf

増分バックアップ

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

差分バックアップ

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

増分バックアップは、直近のバックアップの種類を問いません (フルバックアップ以外も OK)

https://downloads.mysql.com/presentations/20151208_01_MySQL_Backup_for_Beginners.pdf

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

性質フルバックアップ差分/増分バックアップ
リストア単純
フルバックアップだけ
複雑
複数の部分バックアップを組み合わせる
バックアップ取得時間長い短い (変更したデータだけ)
バックアップサイズ大きい小さい (変更したデータだけ)

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

MySQLPostgreSQL
フルバックアップmysqldumppg_dump
差分/増分バックアップバイナリログ (後述)
MySQL Enterprise Backup
WAL ログ
※増分バックアップで使用
スポンサーリンク

MySQL でバックアップの方法

実際に 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 |
+------+
スポンサーリンク

ポイントインタイムリカバリ (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 に含めないように、バイナリログを分けておきます。
(ログを分けずに、1つのログで時間を指定して 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) に成功しました。

最後に

関連記事

データベースの基礎知識

参考記事

https://downloads.mysql.com/presentations/20151208_01_MySQL_Backup_for_Beginners.pdf