MariaDBのPITR

PITRはフルバックアップからバイナリーログを指定してロールフォーワードすることにより実行するらしい。

MariaDB Point In Time Recovery | MariaDB

手順は以下の通り。

■バイナリログ有効化

my.cnfの[mysqld]セクションに以下を追記してmariadbサービスを再起動する。

# cat /etc/my.cnf.d/server.cnf
---中略---
[mysqld]

---中略---
log-bin = /var/lib/mysql_bin/mariadb-bin
binlog_format = ROW
server-id = 1
---中略---
※デフォルトでは、バイナリログ保存場所はmariadbのデータディレクトリになる

※PITRはフルバックアップとフルバックアップ取得時点から戻る日時までのバイナリログが必要で、mysqlデータディレクトリにバイナリログを書き込むとフルバックアップからリストアする際に一旦データディレクトリ内のバイナリログ以外のファイルを削除するプロセスがあるが、このときにバイナリログだけ残すのは煩雑なのであらかじめバイナリログの書き出し場所とデータディレクトリを別にした方が都合がいい。

# systemctl restart mariadb

デフォルトでは、バイナリログ保存場所はmariadbのデータディレクトリになる

# ll /var/lib/mysql_bin/mariadb-bin*
-rw-rw---- 1 mysql mysql 353  8月 15 06:54 /var/lib/mysql_bin/mariadb-bin.000001
-rw-rw---- 1 mysql mysql  42  8月 15 06:54 /var/lib/mysql_bin/mariadb-bin.index

■フルバックアップを取得

# mariabackup --backup -u root -pP@ssw0rd --target-dir /bk/mariabackup_$(date '+%Y%m%d')

# ll /bk

---中略---
drwx------  7 root root     270  8月 15 07:27 mariabackup_20250815

---中略---

■仕込み

・数分おきに適当なデータをデータベースに書き込む

フルバックアップ取得直後

# mariadb -Ae "select * from test_db.test_tb;" -uroot -pP@ssw0rd
+----+--------------+-------------------+---------------------+
| id | name         | comment           | created_at          |
+----+--------------+-------------------+---------------------+
|  2 | dagyah       | before ful_backup | 2025-08-12 05:44:28 |
|  4 | dagyah0      | after ful_backup  | 2025-08-13 05:29:19 |
|  5 | before_inc1  | after_full        | 2025-08-13 05:56:54 |
|  6 | after_full_2 | after full_2      | 2025-08-13 09:53:23 |
+----+--------------+-------------------+---------------------+

# mariadb -Ae "select * from test_db.test_tb;" -uroot -pP@ssw0rd
+----+--------------+-------------------+---------------------+
| id | name         | comment           | created_at          |
+----+--------------+-------------------+---------------------+
|  2 | dagyah       | before ful_backup | 2025-08-12 05:44:28 |
|  4 | dagyah0      | after ful_backup  | 2025-08-13 05:29:19 |
|  5 | before_inc1  | after_full        | 2025-08-13 05:56:54 |
|  6 | after_full_2 | after full_2      | 2025-08-13 09:53:23 |
|  8 | 1            | test of PITR      | 2025-08-15 07:30:29 |　　　　　　　←数分おきにデータを挿入
|  9 | 2            | test of PITR      | 2025-08-15 07:34:13 |　　　　　　　←数分おきにデータを挿入
| 10 | 3            | test of PITR      | 2025-08-15 07:37:18 |　　　　　　　←数分おきにデータを挿入
| 11 | 4            | test of PITR      | 2025-08-15 07:41:04 |　　　　　　　←数分おきにデータを挿入
+----+--------------+-------------------+---------------------+

■フルバックアップからリストア

# mariabackup --prepare --target-dir /bk/mariabackup_20250815

# systemctl stop mariadb

※上記の前にアプリケーションサーバ、他のDBとのレプリケーションをすべて停止しておく
# rm -rf /var/lib/mysql/*
# mariabackup --copy-back --target-dir /bk/mariabackup_20250815
# chown -R mysql:mysql /var/lib/mysql/*
# systemctl start mariadb

・確認

# mariadb -Ae "select * from test_db.test_tb;" -uroot -pP@ssw0rd

+----+--------------+-------------------+---------------------+
| id | name         | comment           | created_at          |
+----+--------------+-------------------+---------------------+
|  2 | dagyah       | before ful_backup | 2025-08-12 05:44:28 |
|  4 | dagyah0      | after ful_backup  | 2025-08-13 05:29:19 |
|  5 | before_inc1  | after_full        | 2025-08-13 05:56:54 |
|  6 | after_full_2 | after full_2      | 2025-08-13 09:53:23 |
+----+--------------+-------------------+---------------------+

→フルバックアップ直後に戻った

■PITR

・ロールフォワード

「2025-08-15 07:37:00」を指定してロールフォワード

# mysqlbinlog --stop-datetime="2025-08-15 07:37:00" /var/lib/mysql_bin/mariadb-bin.* | mysql -uroot -pP@ssw0rd
 ※２０２５年８月１５日７時３７分以降の日時を持つ最初のトランザクションで出力を停止

・確認

# mariadb -Ae "select * from test_db.test_tb;" -uroot -pP@ssw0rd
+----+--------------+-------------------+---------------------+
| id | name         | comment           | created_at          |
+----+--------------+-------------------+---------------------+
|  2 | dagyah       | before ful_backup | 2025-08-12 05:44:28 |
|  4 | dagyah0      | after ful_backup  | 2025-08-13 05:29:19 |
|  5 | before_inc1  | after_full        | 2025-08-13 05:56:54 |
|  6 | after_full_2 | after full_2      | 2025-08-13 09:53:23 |
|  8 | 1            | test of PITR      | 2025-08-15 07:30:29 |
|  9 | 2            | test of PITR      | 2025-08-15 07:34:13 |
+----+--------------+-------------------+---------------------+

■バックアップ取得日時とバイナリログのローテーションタイミングの不一致問題

バイナリログのローテーションタイミングは以下の３つのいずれからしい。

・max_binlog_sizeのサイズに達した時

・mariadbサービスをリスタートした時

・手動でFLUSH LOGSコマンドを実行した時

もし、バイナリログにdelete文でレコードを削除したことが書き込まれた後に、バイナリログがローテーションするまでの間にフルバックアップを取得して、そのフルバックアップをリストアしてmysqlbinlogコマンドでロールフォワードをしようとした場合は、対象のバイナリログを含めて実行しようとすると、フルバックアップ内にすでに含まれてないレコードに対してdelete文を適用として「そのようなレコードは無い」という内容のエラーになってmysqlbinlogコマンドは失敗する。

この場合には、バイナリログがローテーションされてフルバックアップを所得する時点までのすべてのdelete文,trancate文,drop文などを除外した位置（時刻）を指定してmysqlbinlogコマンドを実行すれば成功する。
【例】

# mysqlbinlog --start-datetime="2025-08-15 07:20:00" --stop-datetime="2025-08-15 07:37:00" /var/lib/mysql_bin/mariadb-bin.* | mysql -uroot -pP@ssw0rd

※上記の赤文字のオプションでエラーになるすべてのSQLが含まれない読み込み開始時刻を指定して青文字のオプションで時刻を指定したPITRを実行する。

ちなみに、mysqlbinlogコマンドでバイナリログを指定して実行したらバイナリログの内容が標準出力されるので、実行したSQLと実行時刻を確認することができる。（ただしSQLの数が膨大な場合は現実的ではない？）

上記のほかにバイナリログのローテーションタイミングとフルバックアップ取得タイミングのズレを防ぐ方法としてより直接的に、フルバックアップ取得時にフルバックアップの整合性が確認されたバイナリログファイルと位置をxtrabackup_binlog_infoファイルで確認して以下のコマンドでロールフォワードを実行する。

【例】

# mysqlbinlog --start-position=位置 --stop-datetime="2025-08-15 07:37:00" /var/lib/mysql_bin/{バイナリログファイル1,バイナリログファイル2,・・・,バイナリログファイルn} | mysql -uroot -pP@ssw0rd

 ※バイナリログファイルはxtrabackup_binlog_infoファイルで確認したファイルからPITRでロールフォワードしたい位置までを含むバイナリログファイルをすべて併記する

■フルバックアップとバイナリログフラッシュ同期の必要性検証

・mariadbをリスタートしてバイナリログを強制的にローテ―ションする

リスタート前

# ls -lh
合計 20K
-rw-rw---- 1 mysql mysql 1.7K  8月 15 07:49 mariadb-bin.000001
-rw-rw---- 1 mysql mysql 1.2K  8月 15 08:08 mariadb-bin.000002
-rw-rw---- 1 mysql mysql  367  8月 15 08:13 mariadb-bin.000003
-rw-rw---- 1 mysql mysql 1.1K  8月 15 09:01 mariadb-bin.000004
-rw-rw---- 1 mysql mysql  152  8月 15 08:13 mariadb-bin.index
# systemctl restart mariadb
# ls -lh
合計 24K
-rw-rw---- 1 mysql mysql 1.7K  8月 15 07:49 mariadb-bin.000001
-rw-rw---- 1 mysql mysql 1.2K  8月 15 08:08 mariadb-bin.000002
-rw-rw---- 1 mysql mysql  367  8月 15 08:13 mariadb-bin.000003
-rw-rw---- 1 mysql mysql 1.2K  8月 15 11:02 mariadb-bin.000004
-rw-rw---- 1 mysql mysql  344  8月 15 11:02 mariadb-bin.000005　　　←ローテーションされた
-rw-rw---- 1 mysql mysql  190  8月 15 11:02 mariadb-bin.index

・現在のDBの状態

#  mariadb -Ae "select * from test_db.test_tb;" -uroot -pP@ssw0rd
+----+--------------+-------------------+---------------------+
| id | name         | comment           | created_at          |
+----+--------------+-------------------+---------------------+
|  2 | dagyah       | before ful_backup | 2025-08-12 05:44:28 |
|  4 | dagyah0      | after ful_backup  | 2025-08-13 05:29:19 |
|  5 | before_inc1  | after_full        | 2025-08-13 05:56:54 |
|  6 | after_full_2 | after full_2      | 2025-08-13 09:53:23 |
|  8 | 1            | test of PITR      | 2025-08-15 07:30:29 |
|  9 | 2            | test of PITR      | 2025-08-15 07:34:13 |
+----+--------------+-------------------+---------------------+

・delete文でレコードを削除

上記のid=8と9のレコードを削除する。

#  mariadb -Ae "DELETE FROM test_db.test_tb WHERE id = 8 OR id = 9;" -uroot -pP@ssw0rd
#  mariadb -Ae "select * from test_db.test_tb;" -uroot -pP@ssw0rd
+----+--------------+-------------------+---------------------+
| id | name         | comment           | created_at          |
+----+--------------+-------------------+---------------------+
|  2 | dagyah       | before ful_backup | 2025-08-12 05:44:28 |
|  4 | dagyah0      | after ful_backup  | 2025-08-13 05:29:19 |
|  5 | before_inc1  | after_full        | 2025-08-13 05:56:54 |
|  6 | after_full_2 | after full_2      | 2025-08-13 09:53:23 |
+----+--------------+-------------------+---------------------+

→削除された

・フルバックアップを取得

# mariabackup --backup -u root -pP@ssw0rd --target-dir /bk/mariabackup_20250815

・数分おきに適当なデータをデータベースに書き込む

#  mariadb -Ae "select * from test_db.test_tb;" -uroot -pP@ssw0rd
+----+--------------+-------------------+---------------------+
| id | name         | comment           | created_at          |
+----+--------------+-------------------+---------------------+
|  2 | dagyah       | before ful_backup | 2025-08-12 05:44:28 |
|  4 | dagyah0      | after ful_backup  | 2025-08-13 05:29:19 |
|  5 | before_inc1  | after_full        | 2025-08-13 05:56:54 |
|  6 | after_full_2 | after full_2      | 2025-08-13 09:53:23 |
| 10 | 1            | test of PITR      | 2025-08-15 11:47:44 |
| 11 | 2            | test of PITR      | 2025-08-15 11:48:30 |
+----+--------------+-------------------+---------------------+

・フルバックアップからリストア

# mariabackup --prepare --target-dir /bk/mariabackup_20250815
# systemctl stop mariadb
※上記の前にアプリケーションサーバ、他のDBとのレプリケーションをすべて停止しておく

# rm -rf /var/lib/mysql/*
# mariabackup --copy-back --target-dir /bk/mariabackup_20250815
# chown -R mysql:mysql /var/lib/mysql/*
# systemctl start mariadb

・確認

# mariadb -Ae "select * from test_db.test_tb;" -uroot -pP@ssw0rd
+----+--------------+-------------------+---------------------+
| id | name         | comment           | created_at          |
+----+--------------+-------------------+---------------------+
|  2 | dagyah       | before ful_backup | 2025-08-12 05:44:28 |
|  4 | dagyah0      | after ful_backup  | 2025-08-13 05:29:19 |
|  5 | before_inc1  | after_full        | 2025-08-13 05:56:54 |
|  6 | after_full_2 | after full_2      | 2025-08-13 09:53:23 |
+----+--------------+-------------------+---------------------+

・ロールフォワード

「2025-08-15 11:48:00」を指定してロールフォワード

※見込みではid=10のレコードだけがバイナリログからリカバリされる。

フルバックアップの整合性が確認されたバイナリログファイルと位置の確認

# cat /bk/mariabackup_20250815/xtrabackup_binlog_info
mariadb-bin.000005      632     0-1-4

上記のバイナリログファイルにはフルバックアップ取得以前に実行したdelete文に相当するSQLが書き込まれているのでこのバイナリログファイルを指定してロールフォワードを実行する。

# mysqlbinlog --stop-datetime="2025-08-15 11:48:00" /var/lib/mysql_bin/mariadb-bin.00000[56] | mysql -uroot -pP@ssw0rd
--------------
BINLOG '
OlSfaBMBAAAAPQAAAAgCAAAAABIAAAAAAAEAB3Rlc3RfZGIAB3Rlc3RfdGIABAMPDxEFMgBkAAAA
sK0hQQ==
OlSfaBkBAAAAUQAAAFkCAAAAABIAAAAAAAEABA/wCAAAAAExDHRlc3Qgb2YgUElUUmifGlXwCQAA
AAEyDHRlc3Qgb2YgUElUUmifGzWVWLoC
'
--------------
ERROR 1032 (HY000) at line 35: Can't find record in 'test_tb'

→のようにエラーになって失敗する。

# mariadb -Ae "select * from test_db.test_tb;" -uroot -pP@ssw0rd
+----+--------------+-------------------+---------------------+
| id | name         | comment           | created_at          |
+----+--------------+-------------------+---------------------+
|  2 | dagyah       | before ful_backup | 2025-08-12 05:44:28 |
|  4 | dagyah0      | after ful_backup  | 2025-08-13 05:29:19 |
|  5 | before_inc1  | after_full        | 2025-08-13 05:56:54 |
|  6 | after_full_2 | after full_2      | 2025-08-13 09:53:23 |
+----+--------------+-------------------+---------------------+

→ロールフォワードされてない。

・DELETE文を実行した時刻を調べる

# mysqlbinlog /var/lib/mysql_bin/mariadb-bin.000005 | fgrep -A2 -B2 "DELETE FROM test_db.test_tb WHERE id = 8 OR
 id = 9"
# at 459
#250815 11:37:30 server id 1  end_log_pos 459 CRC32 0xdc6a8411  Annotate_rows:
#Q> DELETE FROM test_db.test_tb WHERE id = 8 OR id = 9
#250815 11:37:30 server id 1  end_log_pos 520 CRC32 0x4121adb0  Table_map: `test_db`.`test_tb` mapped to number 18
# at 520

・上記のDELETE文実行時刻より後の時刻からPITRしたい時刻までを指定してロールフォワード

# mysqlbinlog --start-datetime="2025-08-15 11:38:00" --stop-datetime="2025-08-15 11:48:00" /var/lib/mysql_bin/ma
riadb-bin.000005 | mysql -uroot -pP@ssw0rd

# mariadb -Ae "select * from test_db.test_tb;" -uroot -pP@ssw0rd
+----+--------------+-------------------+---------------------+
| id | name         | comment           | created_at          |
+----+--------------+-------------------+---------------------+
|  2 | dagyah       | before ful_backup | 2025-08-12 05:44:28 |
|  4 | dagyah0      | after ful_backup  | 2025-08-13 05:29:19 |
|  5 | before_inc1  | after_full        | 2025-08-13 05:56:54 |
|  6 | after_full_2 | after full_2      | 2025-08-13 09:53:23 |
| 10 | 1            | test of PITR      | 2025-08-15 11:47:44 |
+----+--------------+-------------------+---------------------+

→id=10のレコードだけがバイナリログからリカバリされた。

■「xtrabackup_binlog_info」ファイルで確認したバイナリログファイルと位置を指定してPITR検証

・mariadbをリスタートしてバイナリログを強制的にローテ―ションする

# systemctl restart mariadb
# ls -lh /var/lib/mysql_bin
合計 44K
-rw-rw---- 1 mysql mysql 1.7K  8月 15 07:49 mariadb-bin.000001
-rw-rw---- 1 mysql mysql 1.2K  8月 15 08:08 mariadb-bin.000002
-rw-rw---- 1 mysql mysql  367  8月 15 08:13 mariadb-bin.000003
-rw-rw---- 1 mysql mysql 1.2K  8月 15 11:02 mariadb-bin.000004
-rw-rw---- 1 mysql mysql 1.2K  8月 15 11:52 mariadb-bin.000005
-rw-rw---- 1 mysql mysql  754  8月 15 12:37 mariadb-bin.000006
-rw-rw---- 1 mysql mysql  622  8月 15 12:53 mariadb-bin.000007
-rw-rw---- 1 mysql mysql  933  8月 15 15:00 mariadb-bin.000008
-rw-rw---- 1 mysql mysql  754  8月 15 15:22 mariadb-bin.000009
-rw-rw---- 1 mysql mysql  344  8月 15 15:22 mariadb-bin.000010
-rw-rw---- 1 mysql mysql  380  8月 15 15:22 mariadb-bin.index

・現在のDBの状態

#  mariadb -Ae "select * from test_db.test_tb;" -uroot -pP@ssw0rd

+----+--------------+-------------------+---------------------+
| id | name         | comment           | created_at          |
+----+--------------+-------------------+---------------------+
|  2 | dagyah       | before ful_backup | 2025-08-12 05:44:28 |
|  4 | dagyah0      | after ful_backup  | 2025-08-13 05:29:19 |
|  5 | before_inc1  | after_full        | 2025-08-13 05:56:54 |
|  6 | after_full_2 | after full_2      | 2025-08-13 09:53:23 |
| 11 | 1            | test of PITR      | 2025-08-15 14:56:35 |
+----+--------------+-------------------+---------------------+

・delete文でレコードを削除

上記のid=11のレコードを削除する。

#  mariadb -Ae "DELETE FROM test_db.test_tb WHERE id = 11;" -uroot -pP@ssw0rd
#  mariadb -Ae "select * from test_db.test_tb;" -uroot -pP@ssw0rd

+----+--------------+-------------------+---------------------+
| id | name         | comment           | created_at          |
+----+--------------+-------------------+---------------------+
|  2 | dagyah       | before ful_backup | 2025-08-12 05:44:28 |
|  4 | dagyah0      | after ful_backup  | 2025-08-13 05:29:19 |
|  5 | before_inc1  | after_full        | 2025-08-13 05:56:54 |
|  6 | after_full_2 | after full_2      | 2025-08-13 09:53:23 |
+----+--------------+-------------------+---------------------+

→削除された

・フルバックアップを取得

# mariabackup --backup -u root -pP@ssw0rd --target-dir /bk/mariabackup_20250815

・数分おきに適当なデータをデータベースにINSERT文で書き込む

# mariadb -Ae "INSERT INTO test_db.test_tb(name,comment) VALUES('1', 'test of PITR');" -uroot -pP@ssw0rd

# mariadb -Ae "INSERT INTO test_db.test_tb(name,comment) VALUES('2', 'test of PITR');" -uroot -pP@ssw0rd

# mariadb -Ae "select * from test_db.test_tb;" -uroot -pP@ssw0rd
+----+--------------+-------------------+---------------------+
| id | name         | comment           | created_at          |
+----+--------------+-------------------+---------------------+
|  2 | dagyah       | before ful_backup | 2025-08-12 05:44:28 |
|  4 | dagyah0      | after ful_backup  | 2025-08-13 05:29:19 |
|  5 | before_inc1  | after_full        | 2025-08-13 05:56:54 |
|  6 | after_full_2 | after full_2      | 2025-08-13 09:53:23 |
| 12 | 1            | test of PITR      | 2025-08-15 15:24:40 |
| 13 | 2            | test of PITR      | 2025-08-15 15:27:22 |
+----+--------------+-------------------+---------------------+

・フルバックアップからリストア

# mariabackup --prepare --target-dir /bk/mariabackup_20250815
# systemctl stop mariadb
※上記の前にアプリケーションサーバ、他のDBとのレプリケーションをすべて停止しておく

# rm -rf /var/lib/mysql/*
# mariabackup --copy-back --target-dir /bk/mariabackup_20250815
# chown -R mysql:mysql /var/lib/mysql/*
# systemctl start mariadb

・バイナリログと位置の確認

# cat /bk/mariabackup_20250815/xtrabackup_binlog_info
mariadb-bin.000010      599     0-1-8

・確認

# mariadb -Ae "select * from test_db.test_tb;" -uroot -pP@ssw0rd

+----+--------------+-------------------+---------------------+
| id | name         | comment           | created_at          |
+----+--------------+-------------------+---------------------+
|  2 | dagyah       | before ful_backup | 2025-08-12 05:44:28 |
|  4 | dagyah0      | after ful_backup  | 2025-08-13 05:29:19 |
|  5 | before_inc1  | after_full        | 2025-08-13 05:56:54 |
|  6 | after_full_2 | after full_2      | 2025-08-13 09:53:23 |
+----+--------------+-------------------+---------------------+

・バイナリログファイルの確認

# ll /var/lib/mysql_bin/
合計 40
-rw-rw---- 1 mysql mysql 1739  8月 15 07:49 mariadb-bin.000001
-rw-rw---- 1 mysql mysql 1141  8月 15 08:08 mariadb-bin.000002
-rw-rw---- 1 mysql mysql  367  8月 15 08:13 mariadb-bin.000003
-rw-rw---- 1 mysql mysql 1141  8月 15 11:02 mariadb-bin.000004
-rw-rw---- 1 mysql mysql 1221  8月 15 11:52 mariadb-bin.000005
-rw-rw---- 1 mysql mysql  754  8月 15 12:37 mariadb-bin.000006
-rw-rw---- 1 mysql mysql  622  8月 15 12:53 mariadb-bin.000007
-rw-rw---- 1 mysql mysql  933  8月 15 15:00 mariadb-bin.000008
-rw-rw---- 1 mysql mysql  344  8月 15 15:00 mariadb-bin.000009
-rw-rw---- 1 mysql mysql 1188  8月 15 15:29 mariadb-bin.000010　 ←このバイナリログにロールバックしたいSQLが含まれる
-rw-rw---- 1 mysql mysql  344  8月 15 15:30 mariadb-bin.000011        ←mariadb再起動時にローテーションされて現れた
-rw-rw---- 1 mysql mysql  418  8月 15 15:30 mariadb-bin.index

・ロールフォワード

2025-08-15 27:21より過去にPITRリカバリしたい（id=12を含んでid=13は含まない）

mariadb-bin.000010の最後のエントリを確認

# mysqlbinlog /var/lib/mysql_bin/mariadb-bin.000010 | tail
# at 1134
#250815 15:27:22 server id 1  end_log_pos 1165 CRC32 0x07d43a43         Xid = 30
COMMIT/*!*/;
# at 1165
#250815 15:29:50 server id 1  end_log_pos 1188 CRC32 0x510ec59d         Stop
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

上記より、PITRにはmariadb-bin.000010は必要だが、mariadb-bin.000011は不要であることがわかる。

# mysqlbinlog --start-position=599 --stop-datetime="2025-08-15 15:27:21" /var/lib/mysql_bin/mariadb-bin.000010

| mysql -uroot -pP@ssw0rd

# mariadb -Ae "select * from test_db.test_tb;" -uroot -pP@ssw0rd
+----+--------------+-------------------+---------------------+
| id | name         | comment           | created_at          |
+----+--------------+-------------------+---------------------+
|  2 | dagyah       | before ful_backup | 2025-08-12 05:44:28 |
|  4 | dagyah0      | after ful_backup  | 2025-08-13 05:29:19 |
|  5 | before_inc1  | after_full        | 2025-08-13 05:56:54 |
|  6 | after_full_2 | after full_2      | 2025-08-13 09:53:23 |
| 12 | 1            | test of PITR      | 2025-08-15 15:24:40 |
+----+--------------+-------------------+---------------------+

→無事PITRできた。

mariabackup

■フルバックアップとリストア

・必要パッケージのインストール

# yum install MariaDB-backup

・仕込み

バックアップ取得前に以下のデータを挿入する

MariaDB [test_db]> INSERT INTO test_tb(name,comment) VALUES('dagyah', 'before ful_backup');
MariaDB [test_db]> select * from test_tb;
+----+--------+-------------------+---------------------+
| id | name   | comment           | created_at          |
+----+--------+-------------------+---------------------+
|  2 | dagyah | before ful_backup | 2025-08-12 05:44:28 |
+----+--------+-------------------+---------------------+

・フルバックアップ

# mariabackup --backup -u root -pP@ssw0rd --target-dir /bk/mariabackup_$(date '+%Y%m%d')

# ll /bk
合計 0
drwx------. 7 root root 240  8月 12 05:51 mariabackup_20250812

・フルバックアップ後に以下のデータを挿入する

MariaDB [test_db]> INSERT INTO test_tb(name,comment) VALUES('dagyah2', 'after ful_backup');
MariaDB [test_db]> SELECT * FROM test_tb;
+----+---------+-------------------+---------------------+
| id | name    | comment           | created_at          |
+----+---------+-------------------+---------------------+
|  2 | dagyah  | before ful_backup | 2025-08-12 05:44:28 |
|  4 | dagyah2 | after ful_backup  | 2025-08-12 05:53:51 |
+----+---------+-------------------+---------------------+

・リストア

# mariabackup --prepare --target-dir /bk/mariabackup_$(date '+%Y%m%d')

# systemctl stop mariadb
# rm -rf /var/lib/mysql/*
# mariabackup --copy-back --target-dir /bk/mariabackup_20250812
# chown -R mysql:mysql /var/lib/mysql/*
# systemctl start mariadb

・バックアップ取得直前にリストアされたことの確認

MariaDB [test_db]> select * from test_tb;
+----+--------+-------------------+---------------------+
| id | name   | comment           | created_at          |
+----+--------+-------------------+---------------------+
|  2 | dagyah | before ful_backup | 2025-08-12 05:44:28 |
+----+--------+-------------------+---------------------+

■増分バックアップとリストア

・参照先

Incremental Backup and Restore with mariadb-backup | MariaDB Documentation

・初回フルバックアップ取得

# mariabackup --backup -u root -pP@ssw0rd --target-dir /bk/full/mariabackup_20250813

・初回フルバックアップ取得後に以下のデータを挿入する

MariaDB [test_db]> INSERT INTO test_tb(name,comment) VALUES('dagyah0', 'after ful_backup');

・第一回増分バックアップ取得前に以下のデータを挿入する

MariaDB [test_db]> INSERT INTO test_tb(name,comment) VALUES('before_inc1', 'after_full');

・第一回増分バックアップ取得

# mariadb-backup --backup --target-dir=/bk/inc1/mariabackup_20250813/  \

   --incremental-basedir=/bk/full/mariabackup_20250813/ -uroot -pP@ssw0rd

・第二回増分バックアップ取得前に以下のデータを挿入する

MariaDB [test_db]> INSERT INTO test_tb(name,comment) VALUES('before_inc2', 'after_inc1');

・第二回増分バックアップ取得

#  mariadb-backup --backup --target-dir=/bk/inc2/mariabackup_20250813/  \

   --incremental-basedir=/bk/inc1/mariabackup_20250813/ -uroot -pP@ssw0rd

・第二回増分バックアップ取得後に以下のデータを挿入する

MariaDB [test_db]> INSERT INTO test_tb(name,comment) VALUES('after_inc2', 'after_inc2');

MariaDB [test_db]> select * from test_tb;
+----+-------------+-------------------+---------------------+
| id | name        | comment           | created_at          |
+----+-------------+-------------------+---------------------+
|  2 | dagyah      | before ful_backup | 2025-08-12 05:44:28 |
|  4 | dagyah0     | after ful_backup  | 2025-08-13 05:29:19 |
|  5 | before_inc1 | after_full        | 2025-08-13 05:56:54 |
|  6 | before_inc2 | after_inc1        | 2025-08-13 06:07:55 |
|  7 | after_inc2  | after_inc2        | 2025-08-13 06:14:37 |
+----+-------------+-------------------+---------------------+

・リストア前にフルバックアップとすべての増分バックアップをバックアップしておく

# cd /bk

# cp -pr full/ full_bk/

# cp -pr inc1/ inc1_bk/

# cp -pr inc2/ inc2_bk/

・フルバックアップ直後にリストア

# mariabackup --prepare --target-dir=/bk/full/mariabackup_20250813

# systemctl stop mariadb
# rm -rf /var/lib/mysql/*
# mariabackup --copy-back --target-dir=/bk/full/mariabackup_20250813
# chown -R mysql:mysql /var/lib/mysql/*
# systemctl start mariadb

※リストアできているか確認

MariaDB [test_db]> SELECT * FROM test_tb;
+----+--------+-------------------+---------------------+
| id | name   | comment           | created_at          |
+----+--------+-------------------+---------------------+
|  2 | dagyah | before ful_backup | 2025-08-12 05:44:28 |
+----+--------+-------------------+---------------------+

※リストア後に新たにフルバックアップ取得に成功するか確認

# mariabackup --backup -u root -pP@ssw0rd --target-dir /tmp/mariabackup_test

ーーー中略ーーー

[00] 2025-08-13 06:48:25 Redo log (from LSN 52236 to 52248) was copied.
[00] 2025-08-13 06:48:25 completed OK!

・第二回増分バックアップ直後にリストア

# cd /bk
# rm -rf full
# cp -pr full_bk/ full/

# mariabackup --prepare --target-dir=/bk/full/mariabackup_20250813
# mariabackup --prepare --target-dir=/bk/full/mariabackup_20250813  \

  --incremental-dir=/bk/inc1/mariabackup_20250813
# mariabackup --prepare --target-dir=/bk/full/mariabackup_20250813  \

  --incremental-dir=/bk/inc2/mariabackup_20250813

# systemctl stop mariadb
# rm -rf /var/lib/mysql/*
# mariabackup --copy-back --target-dir=/bk/full/mariabackup_20250813
# chown -R mysql:mysql /var/lib/mysql/*
# systemctl start mariadb

※リストアできているか確認

MariaDB [test_db]> SELECT * FROM test_tb;
+----+-------------+-------------------+---------------------+
| id | name        | comment           | created_at          |
+----+-------------+-------------------+---------------------+
|  2 | dagyah      | before ful_backup | 2025-08-12 05:44:28 |
|  4 | dagyah0     | after ful_backup  | 2025-08-13 05:29:19 |
|  5 | before_inc1 | after_full        | 2025-08-13 05:56:54 |
|  6 | before_inc2 | after_inc1        | 2025-08-13 06:07:55 |
+----+-------------+-------------------+---------------------+

※リストア後に新たにフルバックアップ取得に成功するか確認

# mariabackup --backup -u root -pP@ssw0rd --target-dir /tmp/mariabackup_test2

---中略---

[00] 2025-08-13 07:02:41 Redo log (from LSN 55308 to 55320) was copied.
[00] 2025-08-13 07:02:41 completed OK!

・第一回増分バックアップ直後にリストア

# cd /bk
# rm -rf full
# cp -pr full_bk/ full/

# rm -rf inc1

# cp -pr inc1_bk/ inc1/

# mariabackup --prepare --target-dir=/bk/full/mariabackup_20250813
# mariabackup --prepare --target-dir=/bk/full/mariabackup_20250813 \

  --incremental-dir=/bk/inc1/mariabackup_20250813

# systemctl stop mariadb
# rm -rf /var/lib/mysql/*
# mariabackup --copy-back --target-dir=/bk/full/mariabackup_20250813
# chown -R mysql:mysql /var/lib/mysql/*
# systemctl start mariadb

※リストアできているか確認

MariaDB [test_db]> SELECT * FROM test_tb;

+----+-------------+-------------------+---------------------+
| id | name        | comment           | created_at          |
+----+-------------+-------------------+---------------------+
|  2 | dagyah      | before ful_backup | 2025-08-12 05:44:28 |
|  4 | dagyah0     | after ful_backup  | 2025-08-13 05:29:19 |
|  5 | before_inc1 | after_full        | 2025-08-13 05:56:54 |
+----+-------------+-------------------+---------------------+

※リストア後に新たにフルバックアップ取得に成功するか確認

# mariabackup --backup -u root -pP@ssw0rd --target-dir /tmp/mariabackup_test1

---中略---

[00] 2025-08-13 07:14:03 Redo log (from LSN 55308 to 55320) was copied.
[00] 2025-08-13 07:14:03 completed OK!

・第一回増分バックアップ直後の状態から再度フルバックアップ、新第１回増分バックアップ、新第２回増分バックアップを取得して新第一回増分バックアップ直後にリストア

新初回フルバックアップ取得

# mariabackup --backup -u root -pP@ssw0rd --target-dir /bk/full/mariabackup_20250813

新第一回増分バックアップ取得前に以下のデータを挿入する

MariaDB [test_db]> INSERT INTO test_tb(name,comment) VALUES('before_inc1', 'after_full');

新第一回増分バックアップ取得

# mariadb-backup --backup --target-dir=/bk/inc1/mariabackup_20250813/ --incremental-basedir=/bk/full/mariabackup_20250813/ -uroot -pP@ssw0rd

新第二回増分バックアップ取得前に以下のデータを挿入する

MariaDB [test_db]> INSERT INTO test_tb(name,comment) VALUES('before_inc2_2', 'after_inc1_2');

新第二回増分バックアップ取得

mariadb-backup --backup --target-dir=/bk/inc2/mariabackup_20250813/ --incremental-basedir=/bk/inc1/mariabackup_20250813/ -uroot -pP@ssw0rd

新第二回増分バックアップ取得後に以下のデータを挿入する

MariaDB [test_db]> INSERT INTO test_tb(name,comment) VALUES('after_inc2_2', 'after_inc2_2');

MariaDB [test_db]> SELECT * FROM test_tb;
+----+---------------+-------------------+---------------------+
| id | name          | comment           | created_at          |
+----+---------------+-------------------+---------------------+
|  2 | dagyah        | before ful_backup | 2025-08-12 05:44:28 |
|  4 | dagyah0       | after ful_backup  | 2025-08-13 05:29:19 |
|  5 | before_inc1   | after_full        | 2025-08-13 05:56:54 |
|  6 | before_inc1_2 | after full_2      | 2025-08-13 09:53:23 |
|  7 | before_inc2_2 | after_inc1_2      | 2025-08-13 09:55:09 |
|  8 | after_inc2_2  | after_inc2_2      | 2025-08-13 10:00:01 |
+----+---------------+-------------------+---------------------+

リストア前にフルバックアップとすべての増分バックアップをバックアップ

# cd /bk

# cp -pr full/ full_bk/

# cp -pr inc1/ inc1_bk/

# cp -pr inc2/ inc2_bk/

新第一回増分バックアップ直後にリストア

# mariabackup --prepare --target-dir=/bk/full/mariabackup_20250813
# mariabackup --prepare --target-dir=/bk/full/mariabackup_20250813 --incremental-dir=/bk/inc1/mariabackup_20250813
# systemctl stop mariadb
# rm -rf /var/lib/mysql/*
# mariabackup --copy-back --target-dir=/bk/full/mariabackup_20250813
# chown -R mysql:mysql /var/lib/mysql/*
# systemctl start mariadb

リストアできているか確認

MariaDB [test_db]> SELECT * FROM test_tb;
+----+---------------+-------------------+---------------------+
| id | name          | comment           | created_at          |
+----+---------------+-------------------+---------------------+
|  2 | dagyah        | before ful_backup | 2025-08-12 05:44:28 |
|  4 | dagyah0       | after ful_backup  | 2025-08-13 05:29:19 |
|  5 | before_inc1   | after_full        | 2025-08-13 05:56:54 |
|  6 | before_inc1_2 | after full_2      | 2025-08-13 09:53:23 |
+----+---------------+-------------------+---------------------+

リストア後に新たにフルバックアップ取得に成功するか確認

# mariabackup --backup -u root -pP@ssw0rd --target-dir /tmp/mariabackup_test1_2

----中略---

[00] 2025-08-13 10:19:07 Redo log (from LSN 56332 to 56344) was copied.
[00] 2025-08-13 10:19:07 completed OK!

※以下のURLにすべてのテーブルのレコード数，チェックサムを出力して、バックアップ・リストア前後でこれらを比較してバックアップ・リストアの正常性を評価する方法が載っている。

Mariabackup による MariaDB Server のバックアップ/リストア

MariaDBでSQL

■インストール

インストーラ（mariadb-10.6.23-rhel-8-x86_64-rpms.tar）はtarアーカイブなので展開する。

展開後mariadb-10.6.23-rhel-8-x86_64-rpmsのディレクトリ内に「README」ファイルがあるのでそれを読む。

READMEの記載通りmariadb-10.6.23-rhel-8-x86_64-rpms/setup_repositoryを実行する
→　/etc/yum.repos.d/mariadb.repo　が作成される。

あとはREADMEの記載通り、以下のコマンドを実行するとMariaDBはインストールされる。
# yum install -y MariaDB-server

※おいらの環境ではcentos8.5を使っているので、すでにCentOS.orgのリポジトリへアクセスできないので、これらのリポジトリはdefaultで「enabled=1」となっているとyumコマンドがエラーになるのえ「enabled=0」に変更しておく。

■初期設定

・とりあえずやっとく

# systemctl disable firewalld

#  systemctl enable mariadb

・データベースファイルの初期バックアップ

# cd /var/lib;tar czf mysql.tgz ./mysql

・rootユーザのパスワード設定

# systemctl start mariadb

# mysql -uroot

MariaDB [(none)]> ALTER USER 'root'@'localhost' IDENTIFIED BY 'P@ssw0rd';

■ユーザ作成

・ユーザ作成

MariaDB [(none)]> CREATE USER 'dagyah'@'localhost' IDENTIFIED BY 'P@ssw0rd';

・ユーザ一覧表示

MariaDB [(none)]> SELECT Host, User FROM mysql.user;
+-------------------+-------------+
| Host              | User        |
+-------------------+-------------+
| centos8\_20250812 |             |
| localhost         |             |
| localhost         | dagyah      |
| localhost         | mariadb.sys |
| localhost         | mysql       |
| localhost         | root        |
+-------------------+-------------+
6 rows in set (0.009 sec)

■データベース作成

・データベース作成

MariaDB [(none)]> CREATE DATABASE test_db;

・データベース一覧確認

MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
| test_db            |
+--------------------+
6 rows in set (0.002 sec)

■テーブル作成

・テーブルを作成するデータベースに接続

MariaDB [(none)]> use test_db;
Database changed
MariaDB [test_db]>

・現在接続中のデータベースの確認

MariaDB [test_db]> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| test_db    |
+------------+
1 row in set (0.001 sec)

・テーブル作成

MariaDB [test_db]> CREATE TABLE test_tb (
    ->     id INT AUTO_INCREMENT PRIMARY KEY,
    ->     name VARCHAR(50) NOT NULL UNIQUE,
    ->     comment VARCHAR(100) NOT NULL,
    ->     created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    -> );

・テーブル一覧確認

MariaDB [test_db]> SHOW TABLES;
+-------------------+
| Tables_in_test_db |
+-------------------+
| test_tb           |
+-------------------+
1 row in set (0.001 sec)

・テーブル定義の確認

MariaDB [test_db]> DESCRIBE test_tb;
+------------+--------------+------+-----+---------------------+----------------+
| Field      | Type         | Null | Key | Default             | Extra          |
+------------+--------------+------+-----+---------------------+----------------+
| id         | int(11)      | NO   | PRI | NULL                | auto_increment |
| name       | varchar(50)  | NO   | UNI | NULL                |                |
| comment    | varchar(100) | NO   |     | NULL                |                |
| created_at | timestamp    | NO   |     | current_timestamp() |                |
+------------+--------------+------+-----+---------------------+----------------+
4 rows in set (0.001 sec)

■ユーザにデータベースへの権限付与

・「dagyah@localhost」ユーザに、「test_db」データベースへのすべての権限を付与

GRANT ALL PRIVILEGES ON test_db.* TO 'dagyah'@'localhost';

・権限が付与されていることの確認

MariaDB [test_db]> SHOW GRANTS FOR 'dagyah'@'localhost';
+---------------------------------------------------------------------------------------------------------------+
| Grants for dagyah@localhost                                                                                   |
+---------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `dagyah`@`localhost` IDENTIFIED BY PASSWORD '*8232A1298A49F710DBEE0B330C42EEC825D4190A' |
| GRANT ALL PRIVILEGES ON `test_db`.* TO `dagyah`@`localhost`                                                   |
+---------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

■DML (Data Manipulation Language)

・INSERT文

MariaDB [test_db]> INSERT INTO test_tb(name,comment) VALUES('dagyah1', 'comment1');

・SELECT文

MariaDB [test_db]> SELECT * FROM test_tb;
+----+---------+----------+---------------------+
| id | name    | comment  | created_at          |
+----+---------+----------+---------------------+
|  1 | dagyah1 | comment1 | 2025-08-12 05:17:54 |
+----+---------+----------+---------------------+

・UPDATE文

MariaDB [test_db]> UPDATE test_tb SET name = 'dagyah2',comment = 'comment2' WHERE id = 1;
MariaDB [test_db]> SELECT * FROM test_tb;
+----+---------+----------+---------------------+
| id | name    | comment  | created_at          |
+----+---------+----------+---------------------+
|  1 | dagyah2 | comment2 | 2025-08-12 05:17:54 |
+----+---------+----------+---------------------+

・DELETE文

MariaDB [test_db]> DELETE FROM test_tb WHERE id = 1;
MariaDB [test_db]> SELECT * FROM test_tb;
Empty set (0.000 sec)