日々の覚書: 最新の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.

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

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

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

$ 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.loadBinlogs は stopBefore, 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】

試した続き

日々の覚書

2025/07/03

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

0 件のコメント :

コメントを投稿