【MySQL学習】バイナリログで増分バックアップ

はじめに

MySQLでのバイナリログを使用した増分バックアップについて学習したことを簡単にまとめます。

検証に使用した環境

DB：MySQL8.0

増分バックアップについて

MySQLではバイナリログを使って増分バックアップを実現します。
バイナリログにはMySQLに実行された変更イベントが記録されているため、記録された操作を再実行することでデータを再現させます。

そのため、MySQLではmysqldumpコマンド等で取得したフルバックアップからバイナリログによる増分バックアップを適用させることで特定の位置までの復元を実現させます。
mysqldumpコマンドについてはこちら

バイナリログの設定

バイナリログを有効にする

log_binシステム変数を確認します。
MySQL8.0からはデフォルトで有効になっているはずです。

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.08 sec)

もし無効になっていて有効にした場合、静的変数なのでMySQLを再起動が必要です。

バイナリログのフォーマットを確認する

バイナリログのフォーマットを確認します。
STATEMENT、ROW、MIXEDの3種類あり、以前はSTATEMENTがデフォルトでしたが、MySQL8.0からデフォルトはROWになっています。

mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.01 sec)

【注意】STATEMENT方式のバイナリログでの復元

STATEMENT方式の場合、ステートメントを再実行することでデータを復元するため、
sysdate()などの実行時点で結果が変わってしまうような関数が起因し、復元前と完全に一致しない場合があります。

そのため、特別な理由が無い限りはrowやmixed方式のバイナリログを使用することが良いとされます。

実際に使ってみる

サンプルデータを作成

サンプルデータを作成します。
sample_tableというテーブルを作成し、データを入れておきます。

mysql> create table sample_table(id int, name varchar(10));
mysql> insert into sample_table values(1, "hoge");

バイナリログに記録されているか確認

バイナリログの出力先を確認する

バイナリログの出力先を確認します。
システム変数のdatadirで確認できます。

mysql> show variables like 'datadir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.01 sec)

バイナリログを確認する

データディレクトリには多数のファイルがあるため、ファイル名を条件に一覧します。

sh-4.2# ls -al /var/lib/mysql/ | grep -- bin
-rw-r----- 1 mysql mysql      711 Jul 13 20:52 binlog.000002
-rw-r----- 1 mysql mysql      180 Jul 18 09:44 binlog.000003
-rw-r----- 1 mysql mysql      157 Jul 31 22:03 binlog.000004
-rw-r----- 1 mysql mysql     1142 Aug  6 04:00 binlog.000005
-rw-r----- 1 mysql mysql      157 Aug  7 06:34 binlog.000006
-rw-r----- 1 mysql mysql      180 Aug 12 04:50 binlog.000007
-rw-r----- 1 mysql mysql      157 Aug 12 04:50 binlog.000008
-rw-r----- 1 mysql mysql      112 Aug 12 04:50 binlog.index

バイナリログの内容を確認する

一覧したバイナリログの中から、最新だったbinlog.000008の中身を確認します。
バイナリログの内容を確認するにはmysqlbinlogコマンドを使用します。

verboseオプションを付与すると、各行イベントをSQLステートメントとして再構築して出力してくれます。

sh-4.2# mysqlbinlog --verbose /var/lib/mysql/binlog.index
・・・
# at 234
#220812  5:18:45 server id 1  end_log_pos 375 CRC32 0xd057dd41  Query   thread_id=13    exec_time=0     error_code=0    Xid = 1217
use `mysql`/*!*/;
SET TIMESTAMP=1660281525/*!*/;
SET @@session.pseudo_thread_id=13/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=255/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
create table sample_table(id int, name varchar(10))
/*!*/;
# at 375
・・・
# at 454
#220812  5:19:36 server id 1  end_log_pos 530 CRC32 0x56bb62ff 	Query	thread_id=13	exec_time=0	error_code=0
SET TIMESTAMP=1660281576/*!*/;
BEGIN
/*!*/;
# at 530
#220812  5:19:36 server id 1  end_log_pos 597 CRC32 0xaa9fc951 	Table_map: `mysql`.`sample_table` mapped to number 174
# at 597
#220812  5:19:36 server id 1  end_log_pos 642 CRC32 0x635b3d16 	Write_rows: table id 174 flags: STMT_END_F

BINLOG '
6OL1YhMBAAAAQwAAAFUCAAAAAK4AAAAAAAEABW15c3FsAAxzYW1wbGVfdGFibGUAAgMPAigAAwEB
AAID/P8AUcmfqg==
6OL1Yh4BAAAALQAAAIICAAAAAK4AAAAAAAEAAgAC/wABAAAABGhvZ2UWPVtj
'/*!*/;
### INSERT INTO `mysql`.`sample_table`
### SET
###   @1=1
###   @2='hoge'
# at 642
#220812  5:19:36 server id 1  end_log_pos 673 CRC32 0x44331178 	Xid = 1218
COMMIT/*!*/;
# at 673
#220812  5:39:00 server id 1  end_log_pos 717 CRC32 0xa0111396 	Rotate to binlog.000009  pos: 4
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

バイナリログにテーブルの作成とデータの挿入について記録されていることが確認できました。

サンプルデータを削除

次に復元を確認するため、サンプルデータを削除しておきます。

mysql> drop table sample_table;
Query OK, 0 rows affected (0.03 sec)

サンプルデータを復元

バイナリログを使用してサンプルデータを復元します。
バイナリログを見るとサンプルデータであるsample_tableにデータを挿入し、位置番号642でコミットされています。

sh-4.2# mysqlbinlog --verbose /var/lib/mysql/binlog.index
・・・
### INSERT INTO `mysql`.`sample_table`
### SET
###   @1=1
###   @2='hoge'
# at 642
#220812  5:19:36 server id 1  end_log_pos 673 CRC32 0x44331178 	Xid = 1218
COMMIT/*!*/;
# at 673

mysqlbinlogコマンドでは、--stop-positionオプションで復元を停止する位置番号を指定できますので、位置番号642の次にあたる673を指定して実行します。

sh-4.2# cd /var/lib/mysql/
sh-4.2# mysqlbinlog --stop-position=673 binlog.000008 | mysql -u root -p

その他

復元に使うバイナリファイルが複数ある

復元に使用するバイナリファイルが複数ある場合、古い順に指定する必要があります。

shell> mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p

停止位置を時間でも指定できる

--stop-datetimeオプションを使えば復元の停止位置を時間でも指定できます。

sh-4.2# mysqlbinlog --stop-datetime="2005-04-20 10:05:00" binlog.000008 | mysql -u root -p

停止位置だけでなく、開始位置も指定できる

--start-position、--start-datetimeオプションを使えばバイナリログの開始位置も指定できます。

復元後のサンプルデータを確認

ちゃんと復元出来ているか確認します。

mysql> select * from sample_table;
+------+------+
| id   | name |
+------+------+
|    1 | hoge |
+------+------+
1 row in set (0.00 sec)

データの復元が確認出来ました。

参考文献