MySQL Shellのフルバックアップとmysqlbinlogを合わせてPITR

MySQL ShellのdumpInstanceとMySQL ShellのdumpBinlogs ではなく MySQL ShellのdumpInstanceとmysqlbinlogの組み合わせでのPITR

テスト用コンテナの起動とテスト用のデータ作成とハートビートの書き込み。GTIDは有効な状態。

$ yt-sandbox 8.0
[3254174] NOTE: Generate Sandbox directry into /home/yoku0825/yt-sandbox/bravo
[3254174] NOTE: Node1 Container Ipaddress: 172.17.0.2
Sandbox deployed into /home/yoku0825/yt-sandbox/bravo

$ cd /home/yoku0825/yt-sandbox/bravo
$ ./n1 -e "CREATE DATABASE sbtest"

$ sysbench --mysql-host=172.17.0.2 --mysql-user=root oltp_read_write prepare --table-size=100000 --tables=10
sysbench 1.0.20 (using system LuaJIT 2.1.0-beta3)

Creating table 'sbtest1'...
Inserting 100000 records into 'sbtest1'
Creating a secondary index on 'sbtest1'...
Creating table 'sbtest2'...
Inserting 100000 records into 'sbtest2'
Creating a secondary index on 'sbtest2'...
Creating table 'sbtest3'...
Inserting 100000 records into 'sbtest3'
Creating a secondary index on 'sbtest3'...
Creating table 'sbtest4'...
Inserting 100000 records into 'sbtest4'
Creating a secondary index on 'sbtest4'...
Creating table 'sbtest5'...
Inserting 100000 records into 'sbtest5'
Creating a secondary index on 'sbtest5'...
Creating table 'sbtest6'...
Inserting 100000 records into 'sbtest6'
Creating a secondary index on 'sbtest6'...
Creating table 'sbtest7'...
Inserting 100000 records into 'sbtest7'
Creating a secondary index on 'sbtest7'...
Creating table 'sbtest8'...
Inserting 100000 records into 'sbtest8'
Creating a secondary index on 'sbtest8'...
Creating table 'sbtest9'...
Inserting 100000 records into 'sbtest9'
Creating a secondary index on 'sbtest9'...
Creating table 'sbtest10'...
Inserting 100000 records into 'sbtest10'
Creating a secondary index on 'sbtest10'...

$ yt-heartbeat -h 172.17.0.2 -uroot -v

まずはMySQL Shellの util.dumpInstance でフルバックアップを取る。

$ date ; mysqlsh mysql://root@172.17.0.2 --js -- util dumpInstance '/tmp/test' ; date
Thu May  7 07:43:06 GMT 2026
Please provide the password for 'root@172.17.0.2':
Save password for 'root@172.17.0.2'? [Y]es/[N]o/Ne[v]er (default No):
Acquiring global read lock
Global read lock acquired

..
Uncompressed data size: 191.90 MB
Compressed data size: 87.48 MB
Compression ratio: 2.2
Rows written: 1000092
Bytes written: 87.48 MB
Average uncompressed throughput: 191.90 MB/s
Average compressed throughput: 87.48 MB/s
Thu May  7 07:43:10 GMT 2026

dumpInstanceは論理バックアップなので、 START TRANSACTION WITH CONSISTENT SNAPSHOT を使っている。よって、このバックアップをリストアした時に復旧できるタイムスライスは「バックアップを開始した時刻」になる。これはバックアップ先の @.json に入っていそう。

$ jq -r .begin /tmp/test/@.json
2026-05-07 07:43:09

もう1個サンドボックスを立ち上げてリストアしてみる。 updateGtidSet=replace にしないと新しいGTIDを払い出しちゃうので指定する。

$ yt-sandbox 8.0
[3255163] NOTE: Generate Sandbox directry into /home/yoku0825/yt-sandbox/charlie
[3255163] NOTE: Node1 Container Ipaddress: 172.17.0.3
Sandbox deployed into /home/yoku0825/yt-sandbox/charlie

$ cd /home/yoku0825/yt-sandbox/charlie
$ ./n1 -e "SET GLOBAL local_infile = ON"

$ date ; mysqlsh mysql://root@172.17.0.3 --js -- util loadDump '/tmp/test' { --updateGtidSet=replace } ; date
Thu May  7 07:49:37 GMT 2026
Please provide the password for 'root@172.17.0.3':
Save password for 'root@172.17.0.3'? [Y]es/[N]o/Ne[v]er (default No):
Loading DDL and Data from '/tmp/test' using 4 threads.

..
Resetting GTID_PURGED to dumped gtid set
11 chunks (1.00M rows, 191.90 MB) for 11 tables in 2 schemas were loaded in 23 sec (avg throughput 8.16 MB/s, 42.54K rows/s)
13 DDL files were executed in 0 sec.
Data load duration: 23 sec
Total duration: 23 sec
0 warnings were reported during the load.

Thu May  7 07:50:04 GMT 2026

$ ./n1 -e "SELECT hostname, server_time FROM ytkit.heartbeat ORDER BY server_time DESC LIMIT 1"  -- 07:43:09.546 のデータが手に入った
+----------+-------------------------+

| hostname | server_time             |
+----------+-------------------------+
| bravo-1  | 2026-05-07 07:43:09.546 |
+----------+-------------------------+

↑うーん、ミリ秒まで @.json に入っていてほしい気もする…。

あとはmysqldumpの時と同じく、「リストア後のGTIDが歯抜けにならないように」(= この場合は「少なくとも必ず b55fb915-49e7-11f1-87a5-0242ac110002:495 とそれ以降のGTID」を含む)バイナリログを適用すればいい。

rsyncか何かで定期的にバイナリログを他の場所に移しておいて（cpで代用）

$ mkdir work
$ sudo cp -ip /home/yoku0825/yt-sandbox/bravo/node1/datadir/binlog.00000* work/
$ ll work/
total 186720
-rw-r-----. 1 mysql mysql       180 May  7 07:38 binlog.000001
-rw-r-----. 1 mysql mysql       180 May  7 07:38 binlog.000002
-rw-r-----. 1 mysql mysql 191192963 May  7 07:54 binlog.000003

mysqlbinlogの —stop-datetime で着地したい時間を指定しつつ mysql コマンドラインクライアントに食わせる。

GTIDモードなので二重適用を恐れる必要はなく、邪魔にならない程度（無視されるとはいえ、GTIDをチェックして空振りさせるので多少の時間は必要で、ぴったり495から始める自信があるなら495から始めても良い）にgtid_executedがオーバーラップするように適用させる。

↑の例だと俺なら binlog.000001 から適用してしまう。000001と000002は実質空っぽだし、000003の07:43:09.546以前のイベントは単に読み捨てられるので。

$ sudo mysqlbinlog --stop-datetime="2026-05-07 07:50:03" work/* | mysql -h172.17.0.3 -uroot

$ ./n1 -e "SELECT hostname, server_time FROM ytkit.heartbeat ORDER BY server_time DESC LIMIT 1"
+----------+-------------------------+
| hostname | server_time             |
+----------+-------------------------+
| bravo-1  | 2026-05-07 07:50:02.848 |
+----------+-------------------------+

$ ./n1 -e "SHOW MASTER STATUS"
+---------------+-----------+--------------+------------------+---------------------------------------------------------------------------------------+
| File          | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                     |
+---------------+-----------+--------------+------------------+---------------------------------------------------------------------------------------+
| binlog.000001 | 190977368 |              |                  | b55fb915-49e7-11f1-87a5-0242ac110002:1-904,
ecc7abed-49e8-11f1-ad40-0242ac110003:1-24 |
+---------------+-----------+--------------+------------------+---------------------------------------------------------------------------------------+

mysqlbinlogの --stop-datetime は当該時刻「以上」のタイムスタンプ(秒まで)が現れた時点でbreakするので、7:50:03.855860のgitd=’b55fb915-49e7-11f1-87a5-0242ac110002:905’ は適用されない。

$ sudo mysqlbinlog -vv work/binlog.000003 | less
..
# at 191101220
#260507  7:50:03 server id 201  end_log_pos 191101299 CRC32 0xaa35a563  GTID    last_committed=904      sequence_number=905     rbr_only=yes    original_committed_timestamp=1778140204080496   immediate_commit_timestamp=1778140204080496     transaction_length=362
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1778140204080496 (2026-05-07 07:50:04.080496 GMT)
# immediate_commit_timestamp=1778140204080496 (2026-05-07 07:50:04.080496 GMT)
/*!80001 SET @@session.original_commit_timestamp=1778140204080496*//*!*/;
/*!80014 SET @@session.original_server_version=80046*//*!*/;
/*!80014 SET @@session.immediate_server_version=80046*//*!*/;
SET @@SESSION.GTID_NEXT= 'b55fb915-49e7-11f1-87a5-0242ac110002:905'/*!*/;
# at 191101299
#260507  7:50:03 server id 201  end_log_pos 191101382 CRC32 0x768811b1  Query   thread_id=13    exec_time=0     error_code=0
SET TIMESTAMP=1778140203.855860/*!*/;
BEGIN
/*!*/;
# at 191101382
#260507  7:50:03 server id 201  end_log_pos 191101448 CRC32 0x2ce58d7d  Table_map: `ytkit`.`heartbeat` mapped to number 145
# has_generated_invisible_primary_key=0
# at 191101448
#260507  7:50:03 server id 201  end_log_pos 191101551 CRC32 0xde01d932  Write_rows: table id 145 flags: STMT_END_F

BINLOG '
K0T8aRPJAAAAQgAAAAj6YwsAAJEAAAAAAAEABXl0a2l0AAloZWFydGJlYXQABA8SEvwF/AMDAwIA
AgP8/wB9jeUs
K0T8aR7JAAAAZwAAAG/6YwsAAJEAAAAAAAEAAgAE/wAHAGJyYXZvLTGZuc58gyFwmbnOfIMhZioA
YjU1ZmI5MTUtNDllNy0xMWYxLTg3YTUtMDI0MmFjMTEwMDAyOjEtOTA0MtkB3g==
'/*!*/;
### INSERT INTO `ytkit`.`heartbeat`
### SET
###   @1='bravo-1' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */
###   @2='2026-05-07 07:50:03.856' /* DATETIME(3) meta=3 nullable=0 is_null=0 */
###   @3='2026-05-07 07:50:03.855' /* DATETIME(3) meta=3 nullable=0 is_null=0 */
###   @4='b55fb915-49e7-11f1-87a5-0242ac110002:1-904' /* BLOB/TEXT meta=2 nullable=0 is_null=0 */
# at 191101551
#260507  7:50:03 server id 201  end_log_pos 191101582 CRC32 0xaa63b7d5  Xid = 16673
COMMIT/*!*/;

..

とこんな感じ。

​