2025/07/04

最新のMySQL ShellだけでMySQLのPITR可能なバックアップを設定する(実践編)

日々の覚書: 最新の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いけた。

2025/07/03

最新のMySQL ShellだけでMySQLのPITR可能なバックアップを設定する(増分バックアップ編)

日々の覚書: 最新のMySQL ShellだけでMySQLのPITR可能なバックアップを設定する(フルバックアップ編) の続き。

最初の1回は必ず since または startFrom の指定が必要。

$ mysqlsh --login-path=backup -h 172.17.0.2 --js -- util dumpBinlogs /path/to/binlog_backup
ERROR: ArgumentError: One of the 'since' or 'startFrom' options must be set because the destination directory '/path/to/binlog_backup' does not contain any dumps yet.

startFrommysqlbinlog -R と同じようにバイナリログファイル名を指定する ( CHANGE REPLICATION SOURCE TO と同じ仕組みなので、ファイルの絶対パスではなくて SHOW BINARY LOGS で出てくるファイル名を指定する必要があるアレ )

since は過去に実行した util.dumpInstance()util.dumpBinlogs() のディレクトリを指定するとそれ以降のバイナリログを吸い上げてくれる。

2回目以降は「バックアップ先に指定した /path/to/binlog_backupsince に渡されたと思ってそれ以降のバイナリログだけを増分でバックアップしてくれる。

$ mysqlsh --login-path=backup -h 172.17.0.2 --js -- util dumpBinlogs /path/to/binlog_backup --since=/path/to/full_backup
Starting from previous dump: /path/to/full_backup, created at: 2025-07-02 02:26:38 UTC
Starting from binary log file: binlog.000002:190942962
Will finish at binary log file: binlog.000002:195333734
Dumping 1 binlogs (4.39 MB of data) using 4 threads
100% (4.39 MB / 4.39 MB), 0.00 B/s, 0.00 B/s compressed, 1 / 1 binlogs done
Dump was written to: /path/to/binlog_backup/2025-07-02-05-50-55
Total duration: 00:00:00s
Binlogs dumped: 1
GTID set dumped: 3b5d02fb-56e7-11f0-8f01-0242ac110002:456-12680
Uncompressed data size: 4.39 MB
Compressed data size: 681.10 KB
Compression ratio: 6.4
Events written: 61123
Bytes written: 681.10 KB
Average uncompressed throughput: 4.39 MB/s
Average compressed throughput: 681.10 KB/s

$ ll /path/to/binlog_backup/ -R
/path/to/binlog_backup/:
total 4
drwxr-x---. 2 yoku0825 yoku0825 104 Jul  2 05:50 2025-07-02-05-50-55
-rw-r-----. 1 yoku0825 yoku0825 454 Jul  2 05:50 @.binlog.info.json

/path/to/binlog_backup/2025-07-02-05-50-55:
total 708
-rw-r-----. 1 yoku0825 yoku0825     42 Jul  2 05:50 @.binlog.done.json
-rw-r-----. 1 yoku0825 yoku0825  31443 Jul  2 05:50 @.binlog.json
-rw-r-----. 1 yoku0825 yoku0825    358 Jul  2 05:50 binlog.000002.json
-rw-r-----. 1 yoku0825 yoku0825 681099 Jul  2 05:50 binlog.000002.zst

というわけで、 util.dumpInstanceで取ったフルバックアップ のディレクトリを指定すると「そのバックアップをフルリストアして以降に生成されたバイナリログだけ」が手に入る。

更には since なしで実行すると(というかバックアップ先に一度でもダンプしたことがあると since オプションは拒否される)、 /path/to/binlog_backup からの増分だけを取ってくれるので

$ mysqlsh --login-path=backup -h 172.17.0.2 --js -- util dumpBinlogs /path/to/binlog_backup
Starting from previous dump: /path/to/binlog_backup/2025-07-02-05-50-55, created at: 2025-07-02 05:50:55 UTC
Starting from binary log file: binlog.000002:195333734
Will finish at binary log file: binlog.000002:212891636
Dumping 1 binlogs (17.56 MB of data) using 4 threads
100% (17.56 MB / 17.56 MB), 0.00 B/s, 0.00 B/s compressed, 1 / 1 binlogs done
Dump was written to: /path/to/binlog_backup/2025-07-02-05-53-16
Total duration: 00:00:00s
Binlogs dumped: 1
GTID set dumped: 3b5d02fb-56e7-11f0-8f01-0242ac110002:12681-22848
Uncompressed data size: 17.56 MB
Compressed data size: 5.76 MB
Compression ratio: 3.1
Events written: 111003
Bytes written: 5.76 MB
Average uncompressed throughput: 17.56 MB/s
Average compressed throughput: 5.76 MB/s

$ ll /path/to/binlog_backup/ -R
/path/to/binlog_backup/:
total 4
drwxr-x---. 2 yoku0825 yoku0825 104 Jul  2 05:50 2025-07-02-05-50-55
drwxr-x---. 2 yoku0825 yoku0825 104 Jul  2 05:53 2025-07-02-05-53-16
-rw-r-----. 1 yoku0825 yoku0825 454 Jul  2 05:50 @.binlog.info.json

/path/to/binlog_backup/2025-07-02-05-50-55:
total 708
-rw-r-----. 1 yoku0825 yoku0825     42 Jul  2 05:50 @.binlog.done.json
-rw-r-----. 1 yoku0825 yoku0825  31443 Jul  2 05:50 @.binlog.json
-rw-r-----. 1 yoku0825 yoku0825    358 Jul  2 05:50 binlog.000002.json
-rw-r-----. 1 yoku0825 yoku0825 681099 Jul  2 05:50 binlog.000002.zst

/path/to/binlog_backup/2025-07-02-05-53-16:
total 5664
-rw-r-----. 1 yoku0825 yoku0825      42 Jul  2 05:53 @.binlog.done.json
-rw-r-----. 1 yoku0825 yoku0825   31383 Jul  2 05:53 @.binlog.json
-rw-r-----. 1 yoku0825 yoku0825     363 Jul  2 05:53 binlog.000002.json
-rw-r-----. 1 yoku0825 yoku0825 5756607 Jul  2 05:53 binlog.000002.zst

こうなる。
ファイルが散らばるのは玉に瑕だけどまあ仕方ない(本当はアーカイブにしてgpgでも使ってファイルを暗号化したい…)

ちなみに別のMySQLだけど、 util.dumpBinlogs はちゃんと並列化されていて速い。

2025-07-03T02:17:32.216078-00:00        89296 Connect   root@localhost on  using Socket
2025-07-03T02:17:32.450935-00:00        89296 Query     SELECT @@SESSION.session_track_system_variables
2025-07-03T02:17:32.451152-00:00        89296 Query     SET SESSION session_track_system_variables = 'time_zone,autocommit,character_set_client,character_set_results,character_set_connection, sql_mode'
2025-07-03T02:17:32.451629-00:00        89296 Query     show GLOBAL variables where `variable_name` in ('log_bin')
2025-07-03T02:17:32.452602-00:00        89296 Query     show GLOBAL variables where `variable_name` in ('gtid_mode')
2025-07-03T02:17:32.453317-00:00        89296 Query     SELECT @@GLOBAL.VERSION
2025-07-03T02:17:32.453394-00:00        89296 Query     SHOW GLOBAL VARIABLES
2025-07-03T02:17:32.454547-00:00        89296 Query     SHOW MASTER STATUS
2025-07-03T02:17:32.454649-00:00        89296 Query     SELECT COALESCE(@@report_host, @@hostname),  COALESCE(@@report_port, @@port)
2025-07-03T02:17:32.454741-00:00        89296 Query     show GLOBAL variables where `variable_name` in ('group_replication_group_name')
2025-07-03T02:17:32.455317-00:00        89296 Query     show GLOBAL variables where `variable_name` in ('group_replication_view_change_uuid')
2025-07-03T02:17:32.455898-00:00        89296 Query     SHOW SCHEMAS LIKE 'mysql_innodb_cluster_metadata'
2025-07-03T02:17:32.456951-00:00        89296 Query     SHOW BINARY LOGS
2025-07-03T02:17:32.458562-00:00        89297 Connect   root@localhost on  using Socket
2025-07-03T02:17:32.458579-00:00        89298 Connect   root@localhost on  using Socket
2025-07-03T02:17:32.458762-00:00        89298 Query     SELECT VERSION()
2025-07-03T02:17:32.458860-00:00        89299 Connect   root@localhost on  using Socket
2025-07-03T02:17:32.458883-00:00        89298 Query     SET @master_binlog_checksum = 'NONE', @source_binlog_checksum = 'NONE'
2025-07-03T02:17:32.458888-00:00        89297 Query     SELECT VERSION()
2025-07-03T02:17:32.458963-00:00        89298 Binlog Dump GTID  Log: 'bin.000011' Pos: 4 GTIDs: ''
2025-07-03T02:17:32.459024-00:00        89297 Query     SET @master_binlog_checksum = 'NONE', @source_binlog_checksum = 'NONE'
2025-07-03T02:17:32.459089-00:00        89299 Query     SELECT VERSION()
2025-07-03T02:17:32.459107-00:00        89297 Binlog Dump GTID  Log: 'bin.000012' Pos: 4 GTIDs: ''
2025-07-03T02:17:32.459240-00:00        89299 Query     SET @master_binlog_checksum = 'NONE', @source_binlog_checksum = 'NONE'
2025-07-03T02:17:32.459305-00:00        89299 Binlog Dump GTID  Log: 'bin.000013' Pos: 4 GTIDs: ''
2025-07-03T02:17:35.289987-00:00        89296 Quit

util.dumpBinlogs を定期的に繰り返して十分バイナリログが貯まった段階で、 前回作っておいたハートビートテーブル からPITR対象になるGTIDを検索する。

mysql> SELECT * FROM sakura.hb WHERE recorded = '2025-07-03 00:00:00';
+-------+--------------+---------------------+----------------------------------------------+
| seq   | hostname     | recorded            | gtid_executed                                |
+-------+--------------+---------------------+----------------------------------------------+
| 77470 | a7786309934c | 2025-07-03 00:00:00 | 3b5d02fb-56e7-11f0-8f01-0242ac110002:1-87887 |
+-------+--------------+---------------------+----------------------------------------------+
1 row in set (0.04 sec)

util.loadBinlogsstopBefore, stopAfter どちらもGTIDしか取れないので、ハートビートテーブルでGTIDを保管しておかないとたぶん割と大変。
/path/to/binlog_backup/* を覗くとわかるけれど、それぞれのバイナリログファイルは「バイナリログの途中からでもちゃんと差分で取得」されているし、「zstdで圧縮」もされているので一つずつ展開してそれっぽい時間を当たって GTID_NEXT を得るのは大変…。

### リストア用のまっさらなコンテナ
docker run -d -P \
  --restart=on-failure \
  -e MYSQL_ALLOW_EMPTY_PASSWORD=1 \
  -e MYSQL_ROOT_PASSWORD="""" \
  -e MYSQL_ROOT_HOST=""%"" \
  container-registry.oracle.com/mysql/community-server:8.4.5 \
    --gtid-mode=ON --enforce-gtid-consistency=ON --lower-case-table-names

### フルリストア
$ mysql -h172.17.0.3 -uroot -e "SET GLOBAL local_infile=1"
$ mysqlsh root@172.17.0.3 --js -- util loadDump /path/to/full_backup/ { --loadUsers=true --ignoreExistingObjects=true --updateGtidSet=replace }   ### デフォルトではユーザーまでは生成されないのと、既存のアカウントは無視してくれればいい、gtid_executedは上書きする

### 昨日取ったフルバックアップの地点
$ mysql -h172.17.0.3 -uroot -e "SELECT * FROM sakura.hb ORDER BY seq DESC LIMIT 1"
+-----+--------------+---------------------+--------------------------------------------+
| seq | hostname     | recorded            | gtid_executed                              |
+-----+--------------+---------------------+--------------------------------------------+
|  65 | a7786309934c | 2025-07-02 02:26:38 | 3b5d02fb-56e7-11f0-8f01-0242ac110002:1-454 |
+-----+--------------+---------------------+--------------------------------------------+

### mysqlスキーマが書き換わってる(はずな)ので一応RESTARTを挟む。
$ mysql -h172.17.0.3 -uroot -e "RESTART"

$ mysqlsh root@172.17.0.3 --js -- util loadBinlogs /path/to/binlog_backup { --stopAfter="3b5d02fb-56e7-11f0-8f01-0242ac110002:1-87887" }
ERROR: ArgumentError: Argument #2: Invalid value of the 'stopAfter' option, expected a GTID in format: source_id:[tag:]:transaction_id

げ、 stopAfter は単発のGTIDじゃないといけなくて gtid_executed ではダメなのか…。これだと複数の server_uuid が混じった時に「どれが最後にインクリメントされたGTIDなのか」を探すのが非常に面倒な気がする…。

$ mysqlsh root@172.17.0.3 --js -- util loadBinlogs /path/to/binlog_backup { --stopAfter="3b5d02fb-56e7-11f0-8f01-0242ac110002:87887" }
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 1 binlogs, 50.61 MB of data
Opening dump '/path/to/binlog_backup'
  Loading dump '2025-07-03-02-20-45' created at 2025-07-03 02:20:45 UTC
    Loading binary log file 'binlog.000002', GTID set: 3b5d02fb-56e7-11f0-8f01-0242ac110002:456-97567 (50.61 MB)
      Found starting GTID: 3b5d02fb-56e7-11f0-8f01-0242ac110002:456
      Stopped after GTID: 3b5d02fb-56e7-11f0-8f01-0242ac110002:87887
89% (45.37 MB / 50.61 MB), 0.00 B/s, 0.00 B/s compressed, 3.59K stmts/s, 1 / 1 binlogs done
Total duration: 00:03:52s
Binlogs loaded: 1
Uncompressed data size: 50.61 MB
Compressed data size: 11.26 MB
Statements executed: 816983
Average uncompressed throughput: 194.95 KB/s
Average compressed throughput: 43.31 KB/s
Average statement throughput: 3.51 KB/s

一応それっぽい時間にはなったけど… stopAfter=3b5d02fb-56e7-11f0-8f01-0242ac110002:87887 なのにその1つ手前で止まるのは合っているのか。。

$ mysql -h172.17.0.3 -uroot -e "SELECT * FROM sakura.hb ORDER BY seq DESC LIMIT 1"
+-------+--------------+---------------------+----------------------------------------------+
| seq   | hostname     | recorded            | gtid_executed                                |
+-------+--------------+---------------------+----------------------------------------------+
| 77469 | a7786309934c | 2025-07-02 23:59:59 | 3b5d02fb-56e7-11f0-8f01-0242ac110002:1-87886 |
+-------+--------------+---------------------+----------------------------------------------+

【2025/07/04 10:24】

ハートビートテーブルのgtid_executedの方がむしろ合ってなかった。

$  mysql -h172.17.0.4 -uroot -e "SHOW BINARY LOG STATUS"
+---------------+----------+--------------+------------------+-----------------------------------------------------------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                       |
+---------------+----------+--------------+------------------+-----------------------------------------------------------------------------------------+
| binlog.000003 | 45729885 |              |                  | 3b5d02fb-56e7-11f0-8f01-0242ac110002:1-87887,
bf458fdd-586d-11f0-99ef-0242ac110004:1-18 |
+---------------+----------+--------------+------------------+-----------------------------------------------------------------------------------------+



たぶんまだ続く(スイッチオーバーした後にgtid_executedが複数になるケースを試してから)


【2025/07/04 14:45】

試した続き

日々の覚書: 最新のMySQL ShellだけでMySQLのPITR可能なバックアップを設定する(実践編)