日々の覚書: 最新のMySQL ShellだけでMySQLのPITR可能なバックアップを設定する(増分バックアップ編) の更に続き。
フェイルオーバー、ソース切り替えが起こって gtid_executed
が複数行になった時にどうなるのかを調べる。
昨日リストアした172.17.0.3 に更にsysbenchでちょっと更新をかけながらハートビートテーブルにも書かせていた。
mysql> SHOW BINARY LOG STATUS;
+---------------+----------+--------------+------------------+-------------------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------------------------------------------------------------------------------+
| binlog.000003 | 48101470 | | | 3b5d02fb-56e7-11f0-8f01-0242ac110002:1-87887,
8e7dfcc0-57b7-11f0-a722-0242ac110003:1-1686 |
+---------------+----------+--------------+------------------+-------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
切り替わりが発生すると、それまで積み重ねてきたバイナリログのバックアップに追加することはできなくなる。おそらく切り替わったらフルバックアップを取り直してそこからまたバイナリログを積み上げるのが良さそう。
$ mysqlsh -h 172.17.0.3 -uroot --no-password --js -- util dumpBinlogs /path/to/binlog_backup
WARNING: Using a password on the command line interface can be insecure.
ERROR: ArgumentError: The source instance is different from the one used in the previous dump and it's not part of an InnoDB Cluster group.
gtid_mode=ON
なら切り替わりをまたぐPITRでも他のノードで log_replica_updates
によって十分なバイナリログが残っていれば(実行済みの gtid_next
はスキップされるのでGTIDに隙間ができないように余裕をもって指定すれば良いだけで、完全にGTIDの切れ目で一致させる必要はない)楽なんだけれども、今検証しているこれの範疇からは外れそう。
$ mysqlsh -h 172.17.0.3 -uroot --no-password --js -- util dumpInstance /path/to/another_full_backup/
WARNING: Using a password on the command line interface can be insecure.
Acquiring global read lock
Global read lock acquired
Initializing - done
2 out of 6 schemas will be dumped and within them 2 tables, 0 views.
4 out of 7 users will be dumped.
Gathering information - done
All transactions have been started
Locking instance for backup
Global read lock has been released
Writing global DDL files
Writing users DDL
Running data dump using 4 threads.
NOTE: Progress information uses estimated values and may not be accurate.
Writing schema metadata - done
Writing DDL - done
Writing table metadata - done
Starting data dump
101% (1.08M rows / ~1.06M rows), 536.44K rows/s, 99.41 MB/s uncompressed, 44.03 MB/s compressed
Dump duration: 00:00:02s
Total duration: 00:00:02s
Schemas dumped: 2
Tables dumped: 2
Uncompressed data size: 200.43 MB
Compressed data size: 88.74 MB
Compression ratio: 2.3
Rows written: 1077870
Bytes written: 88.74 MB
Average uncompressed throughput: 95.47 MB/s
Average compressed throughput: 42.27 MB/s
$ mysqlsh -h 172.17.0.3 -uroot --no-password --js -- util dumpBinlogs /path/to/another_binlog { --since=/path/to/another_full_backup/ }
WARNING: Using a password on the command line interface can be insecure.
Starting from previous dump: /path/to/another_full_backup, created at: 2025-07-04 02:28:23 UTC
Starting from binary log file: binlog.000003:48101470
Will finish at binary log file: binlog.000003:48107225
Dumping 1 binlogs (5.75 KB of data) using 4 threads
102% (5.88 KB / 5.75 KB), 0.00 B/s, 0.00 B/s compressed, 1 / 1 binlogs done
Dump was written to: /path/to/another_binlog/2025-07-04-03-31-48
Total duration: 00:00:00s
Binlogs dumped: 1
GTID set dumped: 8e7dfcc0-57b7-11f0-a722-0242ac110003:1687-1701
Uncompressed data size: 5.88 KB
Compressed data size: 1.39 KB
Compression ratio: 4.2
Events written: 70
Bytes written: 1.39 KB
Average uncompressed throughput: 5.88 KB/s
Average compressed throughput: 1.39 KB/s
話は変わるけど、バイナリログを吸い上げたディレクトリの中の日付のディレクトリの中に、「この util.dumpBinlogs
が終わった時点での最後のバイナリログファイル名とポジション、 gtid_executed
」がメタデータとして吸い上げられていることに気が付いた。
$ ll binlog/2025-07-04-02-45-04/
total 140
-rw-r-----. 1 yoku0825 yoku0825 42 Jul 4 02:45 @.binlog.done.json
-rw-r-----. 1 yoku0825 yoku0825 47356 Jul 4 02:45 @.binlog.json
-rw-r-----. 1 yoku0825 yoku0825 329 Jul 4 02:45 binary-log.043707.json
-rw-r-----. 1 yoku0825 yoku0825 7116 Jul 4 02:45 binary-log.043707.zst
-rw-r-----. 1 yoku0825 yoku0825 303 Jul 4 02:45 binary-log.043708.json
-rw-r-----. 1 yoku0825 yoku0825 22974 Jul 4 02:45 binary-log.043708.zst
-rw-r-----. 1 yoku0825 yoku0825 303 Jul 4 02:45 binary-log.043709.json
-rw-r-----. 1 yoku0825 yoku0825 23087 Jul 4 02:45 binary-log.043709.zst
-rw-r-----. 1 yoku0825 yoku0825 330 Jul 4 02:45 binary-log.043710.json
-rw-r-----. 1 yoku0825 yoku0825 15741 Jul 4 02:45 binary-log.043710.zst
$ jq .endAt binlog/2025-07-04-02-45-04/@.binlog.json
{
"file": "binary-log.043710",
"position": 103420,
"gtidExecuted": "9330ac0f-e14a-11ef-a07c-0200170681c1:1-2455837"
}
話を戻して、切り替わったソースから取ったバイナリログでPITRできるかの話。
$ mysql -h172.17.0.4 -uroot -e "SET GLOBAL local_infile=1"
$ mysqlsh root@172.17.0.4 --js -- util loadDump /path/to/another_full_backup/ { --loadUsers=true --ignoreExistingObjects=true --updateGtidSet=replace --skipBinlog=true }
$ mysql -h172.17.0.4 -uroot -e "SELECT * FROM sakura.hb ORDER BY seq DESC LIMIT 1"
+-------+--------------+---------------------+-------------------------------------------------------------------------------------------+
| seq | hostname | recorded | gtid_executed |
+-------+--------------+---------------------+-------------------------------------------------------------------------------------------+
| 77870 | 756eebf85ccb | 2025-07-03 05:50:33 | 3b5d02fb-56e7-11f0-8f01-0242ac110002:1-87887,
8e7dfcc0-57b7-11f0-a722-0242ac110003:1-1685 |
+-------+--------------+---------------------+-------------------------------------------------------------------------------------------+
$ mysql -h172.17.0.4 -uroot -e "RESTART"
たとえば7/4 03:34(UTC) に着地したい場合は、バックアップ元でこんな風に引くことを 前回 やっていたけれど、
$ mysql -h172.17.0.3 -e "SELECT * FROM sakura.hb WHERE recorded = '2025-07-04 03:34:00'"
+-------+--------------+---------------------+-------------------------------------------------------------------------------------------+
| seq | hostname | recorded | gtid_executed |
+-------+--------------+---------------------+-------------------------------------------------------------------------------------------+
| 78015 | 756eebf85ccb | 2025-07-04 03:34:00 | 3b5d02fb-56e7-11f0-8f01-0242ac110002:1-87887,
8e7dfcc0-57b7-11f0-a722-0242ac110003:1-1832 |
+-------+--------------+---------------------+-------------------------------------------------------------------------------------------+
$ mysqlsh root@172.17.0.4 --js -- util loadBinlogs /path/to/another_binlog { --stopAfter="3b5d02fb-56e7-11f0-8f01-0242ac110002:87887" } ### こっちは最初のソース172.17.0.2が持っていたGTIDの末尾
Please provide the password for 'root@172.17.0.4':
Save password for 'root@172.17.0.4'? [Y]es/[N]o/Ne[v]er (default No):
ERROR: ArgumentError: Could not find the final binary log file to be loaded that contains GTID '3b5d02fb-56e7-11f0-8f01-0242ac110002:87887'.
$ mysqlsh root@172.17.0.4 --js -- util loadBinlogs /path/to/another_binlog { --stopAfter="8e7dfcc0-57b7-11f0-a722-0242ac110003:1832" } ### こっちが直接バックアップを取った172.17.0.3が持っていた(復元したい)GTIDの末尾
Please provide the password for 'root@172.17.0.4':
Save password for 'root@172.17.0.4'? [Y]es/[N]o/Ne[v]er (default No):
Loading 2 binlogs, 112.52 KB of data
Opening dump '/path/to/another_binlog'
Loading dump '2025-07-04-03-31-48' created at 2025-07-04 03:31:48 UTC
Loading binary log file 'binlog.000003', GTID set: 8e7dfcc0-57b7-11f0-a722-0242ac110003:1687-1701 (5.88 KB)
Found starting GTID: 8e7dfcc0-57b7-11f0-a722-0242ac110003:1687
Loading dump '2025-07-04-03-36-11' created at 2025-07-04 03:36:11 UTC
Loading binary log file 'binlog.000003', GTID set: 8e7dfcc0-57b7-11f0-a722-0242ac110003:1702-1964 (106.64 KB)
Stopped after GTID: 8e7dfcc0-57b7-11f0-a722-0242ac110003:1832
56% (63.23 KB / 112.52 KB), 0.00 B/s, 0.00 B/s compressed, 0.00 stmts/s, 2 / 2 binlogs done
Total duration: 00:00:00s
Binlogs loaded: 2
Uncompressed data size: 112.52 KB
Compressed data size: 16.59 KB
Statements executed: 1330
Average uncompressed throughput: 63.23 KB/s
Average compressed throughput: 16.59 KB/s
Average statement throughput: 1.33 KB/s
$ mysql -h172.17.0.4 -uroot -e "SELECT * FROM sakura.hb ORDER BY seq DESC LIMIT 1"
+-------+--------------+---------------------+-------------------------------------------------------------------------------------------+
| seq | hostname | recorded | gtid_executed |
+-------+--------------+---------------------+-------------------------------------------------------------------------------------------+
| 78014 | 756eebf85ccb | 2025-07-04 03:33:59 | 3b5d02fb-56e7-11f0-8f01-0242ac110002:1-87887,
8e7dfcc0-57b7-11f0-a722-0242ac110003:1-1831 |
+-------+--------------+---------------------+-------------------------------------------------------------------------------------------+
$ mysql -h172.17.0.4 -uroot -e "SHOW BINARY LOG STATUS"
+---------------+----------+--------------+------------------+-------------------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------------------------------------------------------------------------------+
| binlog.000002 | 59702 | | | 3b5d02fb-56e7-11f0-8f01-0242ac110002:1-87887,
8e7dfcc0-57b7-11f0-a722-0242ac110003:1-1832 |
+---------------+----------+--------------+------------------+-------------------------------------------------------------------------------------------+
その時の @@server_uuid
の値はバイナリログのディレクトリに残ってるので、アプリケーションとかでパースするならこれを使える気がする。
$ jq . /path/to/another_binlog/@.binlog.info.json
{
"dumper": "mysqlsh Ver 9.3.0 for Linux on x86_64 - for MySQL 9.3.0 (MySQL Community Server (GPL))",
"version": "2.0.1",
"origin": "dumpBinlogs",
"timestamp": "2025-07-04 03:31:48",
"source": {
"version": "8.4.5",
"hostname": "756eebf85ccb",
"port": 3306,
"serverUuid": "8e7dfcc0-57b7-11f0-a722-0242ac110003",
"topology": {
"canonicalAddress": "756eebf85ccb:3306"
}
}
}
が、あんまり凝ったことはしたくないのでハートビートテーブルから取り出すことにする。
### ターゲットはこれ
mysql> SELECT * FROM sakura.hb WHERE recorded = '2025-07-04 03:34:00';
+-------+--------------+---------------------+-------------------------------------------------------------------------------------------+
| seq | hostname | recorded | gtid_executed |
+-------+--------------+---------------------+-------------------------------------------------------------------------------------------+
| 78015 | 756eebf85ccb | 2025-07-04 03:34:00 | 3b5d02fb-56e7-11f0-8f01-0242ac110002:1-87887,
8e7dfcc0-57b7-11f0-a722-0242ac110003:1-1832 |
+-------+--------------+---------------------+-------------------------------------------------------------------------------------------+
1 row in set (0.04 sec)
### 少し前の(直近でもいい)レコードを取り出す。単一のGTIDだけが取り出されると話が終わっちゃうのでレンジになるように少し前のレコードにしているだけ
mysql> SELECT * FROM sakura.hb WHERE recorded < '2025-07-04 03:33:00' ORDER BY recorded DESC LIMIT 1;
+-------+--------------+---------------------+-------------------------------------------------------------------------------------------+
| seq | hostname | recorded | gtid_executed |
+-------+--------------+---------------------+-------------------------------------------------------------------------------------------+
| 77954 | 756eebf85ccb | 2025-07-04 03:32:59 | 3b5d02fb-56e7-11f0-8f01-0242ac110002:1-87887,
8e7dfcc0-57b7-11f0-a722-0242ac110003:1-1771 |
+-------+--------------+---------------------+-------------------------------------------------------------------------------------------+
1 row in set (0.05 sec)
### 両者を比較する
mysql> SELECT GTID_SUBTRACT((SELECT gtid_executed FROM sakura.hb WHERE recorded = '2025-07-04 03:34:00'), (SELECT gtid_executed FROM sakura.hb WHERE recorded < '2025-07-04 03:33:00' ORDER BY recorded DESC LIMIT 1)) AS diff;
+------------------------------------------------+
| diff |
+------------------------------------------------+
| 8e7dfcc0-57b7-11f0-a722-0242ac110003:1772-1832 |
+------------------------------------------------+
1 row in set (0.09 sec)
グループレプリケーションのマルチプライマリーモードやマルチソースレプリケーション、この時間の間にフェイルオーバーが走っていなければここで抽出されるGTIDは単一の server_uuid
になるはず。
となれば :
と -
で切り出せば最後の1つは出せる。
mysql> SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(GTID_SUBTRACT((SELECT gtid_executed FROM sakura.hb WHERE recorded = '2025-07-04 03:34:00'), (SELECT gtid_executed FROM sakura.hb WHERE recorded < '2025-07-04 03:33:00' ORDER BY recorded DESC LIMIT 1)), ':', -1), '-', -1) AS last_gtid_seq;
+---------------+
| last_gtid_seq |
+---------------+
| 1832 |
+---------------+
1 row in set (0.08 sec)
mysql> SELECT CONCAT(@@server_uuid, ':', SUBSTRING_INDEX(SUBSTRING_INDEX(GTID_SUBTRACT((SELECT gtid_executed FROM sakura.hb WHERE recorded = '2025-07-04 03:34:00'), (SELECT gtid_executed FROM sakura.hb
WHERE recorded < '2025-07-04 03:33:00' ORDER BY recorded DESC LIMIT 1)), ':', -1), '-', -1)) AS last_gtid;
+-------------------------------------------+
| last_gtid |
+-------------------------------------------+
| 657f3412-588a-11f0-9230-0242ac110003:1832 |
+-------------------------------------------+
1 row in set (0.08 sec)
これで dump.loadBinlogs
に渡す引数が完成した気がする。
もう一つコンテナを起動して、
$ mysql -h172.17.0.5 -uroot -e "SET GLOBAL local_infile=1"
$ mysqlsh root@172.17.0.5 --js -- util loadDump /path/to/another_full_backup/ { --loadUsers=true --ignoreExistingObjects=true --updateGtidSet=replace --skipBinlog=true }
$ mysqlsh root@172.17.0.5 --js -- util loadBinlogs /path/to/another_binlog { --stopAfter=$(mysql -h172.17.0.3 -uroot -sse "SELECT CONCAT(@@server_uuid, ':', SUBSTRING_INDEX(SUBSTRING_INDEX(GTID_SUBTRACT((SELECT gtid_executed FROM sakura.hb WHERE recorded = '2025-07-04 03:34:00'), (SELECT gtid_executed FROM sakura.hb WHERE recorded < '2025-07-04 03:33:00' ORDER BY recorded DESC LIMIT 1)), ':', -1), '-', -1)) AS last_gtid;") }
Please provide the password for 'root@172.17.0.5':
Save password for 'root@172.17.0.5'? [Y]es/[N]o/Ne[v]er (default No):
Loading 2 binlogs, 112.52 KB of data
Opening dump '/path/to/another_binlog'
Loading dump '2025-07-04-03-31-48' created at 2025-07-04 03:31:48 UTC
Loading binary log file 'binlog.000003', GTID set: 8e7dfcc0-57b7-11f0-a722-0242ac110003:1687-1701 (5.88 KB)
Found starting GTID: 8e7dfcc0-57b7-11f0-a722-0242ac110003:1687
Loading dump '2025-07-04-03-36-11' created at 2025-07-04 03:36:11 UTC
Loading binary log file 'binlog.000003', GTID set: 8e7dfcc0-57b7-11f0-a722-0242ac110003:1702-1964 (106.64 KB)
Stopped after GTID: 8e7dfcc0-57b7-11f0-a722-0242ac110003:1832
56% (63.23 KB / 112.52 KB), 0.00 B/s, 0.00 B/s compressed, 0.00 stmts/s, 2 / 2 binlogs done
Total duration: 00:00:00s
Binlogs loaded: 2
Uncompressed data size: 112.52 KB
Compressed data size: 16.59 KB
Statements executed: 1330
Average uncompressed throughput: 63.23 KB/s
Average compressed throughput: 16.59 KB/s
Average statement throughput: 1.33 KB/s
$ mysql -h172.17.0.5 -uroot -e "SELECT * FROM sakura.hb ORDER BY seq DESC LIMIT 1"
+-------+--------------+---------------------+-------------------------------------------------------------------------------------------+
| seq | hostname | recorded | gtid_executed |
+-------+--------------+---------------------+-------------------------------------------------------------------------------------------+
| 78014 | 756eebf85ccb | 2025-07-04 03:33:59 | 3b5d02fb-56e7-11f0-8f01-0242ac110002:1-87887,
8e7dfcc0-57b7-11f0-a722-0242ac110003:1-1831 |
+-------+--------------+---------------------+-------------------------------------------------------------------------------------------+
$ mysql -h172.17.0.5 -uroot -e "SHOW BINARY LOG STATUS"
+---------------+----------+--------------+------------------+-------------------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------------------------------------------------------------------------------+
| binlog.000002 | 59702 | | | 3b5d02fb-56e7-11f0-8f01-0242ac110002:1-87887,
8e7dfcc0-57b7-11f0-a722-0242ac110003:1-1832 |
+---------------+----------+--------------+------------------+-------------------------------------------------------------------------------------------+
OKいけた。