MySQL Shellには util.dumpInstance = フルバックアップと、9.2くらいから util.dumpBinlogs = 増分バックアップがあるので、ひょっとしたらMySQL ShellだけでPITR可能なバックアップをシンプルに設定できるのではないかと思った。
TL;DR
util.dumpBinlogsは「前回吸い上げたバイナリログから増えたぶんだけをMySQLプロトコルで吸い上げてローカルFSに保管」することができる。初回のみ最初のバイナリログファイル名の指定が必要mysqlbinlog --stop-never --raw -Rではプロセスが再起動する時に常に自分でコピーを開始するバイナリログファイル名を指定しなければならなかった
util.dumpBinlogsは gtid_mode=ON でしか使えない- リストア用である util.loadBinlogs は終端を示す
stopAfterまたはstopBeforeにGTIDしか使えないmysqlbinlog --stop-datetime=..が使えないのでPITRと考えると時間とGTIDのマップを自分でやらないといけない- じゃあ時間とGTIDを定期的に記録すればいけるんじゃないかなと思った
- リストア用である util.loadBinlogs は終端を示す
長くなったので今回はフルバックアップのみ。
大前提としてMySQL側は gtid_mode=ON でないといけない。あと、 util.dumpBinlogs はMySQL Shell 9.2だかそこいらからの機能なので今現在で 最新のMySQL Shell 9.3にしておく。
sudo dnf install -y https://dev.mysql.com/get/Downloads/MySQL-Shell/mysql-shell-9.3.0-1.el8.x86_64.rpm
リストアターゲットにするMySQLはdockerコンテナで済ます。--restart=on-failure は RESTART ステートメントに必要だから（今回使わないけどクセにしている）。-e のいくつかはパスワードレスでネットワークがつながっている限りどこからでもrootでログインできるようにするもの。
URLが指定してあるのは MySQL公式のDockerリポジトリがcontainer-registry.oracle.comに引っ越していた から。
イメージの指定のあとに続いているのはそのまま mysqld に渡されるオプションになる。ここで --gtid-mode=ON --enforce-gtid-consistency=ON にしてGTIDを有効にしている。
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> CREATE USER mysqlsh_backup IDENTIFIED BY 'secret_password';
Query OK, 0 rows affected (0.02 sec)
mysql> GRANT SELECT, RELOAD, REPLICATION SLAVE, REPLICATION CLIENT, EVENT, TRIGGER, BACKUP_ADMIN ON *.* TO mysqlsh_backup;
Query OK, 0 rows affected (0.01 sec)
mysql> SHOW GRANTS FOR mysqlsh_backup;
+------------------------------------------------------------------------------------------------------------+
| Grants for mysqlsh_backup@% |
+------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, RELOAD, REPLICATION SLAVE, REPLICATION CLIENT, EVENT, TRIGGER ON *.* TO `mysqlsh_backup`@`%` |
| GRANT BACKUP_ADMIN ON *.* TO `mysqlsh_backup`@`%` |
+------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
MySQL Shellは mysql_config_editor で作ったクレデンシャルを食えるので、先に設定しておく。
$ mysql_config_editor set --login-path=backup --user=mysqlsh_backup --password
Enter password: ### 対話的に入れるしかない
$ mysqlsh --login-path=backup -h 172.17.0.2 -e "SHOW GRANTS"
Grants for mysqlsh_backup@%
GRANT SELECT, RELOAD, REPLICATION CLIENT, EVENT, TRIGGER ON *.* TO `mysqlsh_backup`@`%`
GRANT BACKUP_ADMIN ON *.* TO `mysqlsh_backup`@`%`
GTIDとPITRの時刻を紐づけるためにハートビートテーブルを作って定期的に実行する。
#!/usr/bin/perl
use strict;
use warnings;
use utf8;
use Time::Piece;
use DBI;
my $dsn = "dbi:mysql:;host=172.17.0.2";
my $user= "root";
my $pass= "";
my $opt = { RaiseError => 1, PrintError => 1, use_mysql_utf8 => 1, mysql_connect_timeout => 1 };
my $conn= DBI->connect($dsn, $user, $pass, $opt) or die;
$conn->do("CREATE DATABASE IF NOT EXISTS sakura");
$conn->do("CREATE TABLE IF NOT EXISTS sakura.hb (seq SERIAL, hostname VARCHAR(255) NOT NULL, recorded DATETIME NOT NULL, gtid_executed TEXT NOT NULL)");
while ()
{
my $now= Time::Piece::localtime;
$conn->do(q|INSERT INTO sakura.hb (hostname, recorded, gtid_executed) VALUES (@@hostname, ?, @@global.gtid_executed)|, undef, $now->strftime("%Y-%m-%d %H:%M:%S"));
printf("%s\n", $now->strftime("%Y-%m-%d %H:%M:%S"));
sleep 1;
}
まずフルバックアップ。MySQL Shellはいくつからだったかデフォルトモードが --js から --sql に変わっているので util.* 系を呼び出す時には --js が必要。
$ mysqlsh --login-path=backup -h 172.17.0.2 --js -- util dumpInstance /path/to/full_backup
-e "util.dumpInstance('/path/to/full_backup')" でも取れると思うけど俺は -- 記法の方が好き（単に先にそっちでおぼえたから）
$ ll /path/to/full_backup/
total 86260
-rw-r-----. 1 yoku0825 yoku0825 599 Jul 2 02:26 @.done.json
-rw-r-----. 1 yoku0825 yoku0825 31733 Jul 2 02:26 @.json
-rw-r-----. 1 yoku0825 yoku0825 238 Jul 2 02:26 @.post.sql
-rw-r-----. 1 yoku0825 yoku0825 238 Jul 2 02:26 @.sql
-rw-r-----. 1 yoku0825 yoku0825 4708 Jul 2 02:26 @.users.sql
-rw-r-----. 1 yoku0825 yoku0825 386 Jul 2 02:26 sakura.json
-rw-r-----. 1 yoku0825 yoku0825 807 Jul 2 02:26 sakura.sql
-rw-r-----. 1 yoku0825 yoku0825 639 Jul 2 02:26 sakura@hb.json
-rw-r-----. 1 yoku0825 yoku0825 830 Jul 2 02:26 sakura@hb.sql
-rw-r-----. 1 yoku0825 yoku0825 318 Jul 2 02:26 sakura@hb@@0.tsv.zst
-rw-r-----. 1 yoku0825 yoku0825 8 Jul 2 02:26 sakura@hb@@0.tsv.zst.idx
-rw-r-----. 1 yoku0825 yoku0825 401 Jul 2 02:26 sbtest.json
-rw-r-----. 1 yoku0825 yoku0825 807 Jul 2 02:26 sbtest.sql
-rw-r-----. 1 yoku0825 yoku0825 637 Jul 2 02:26 sbtest@sbtest1.json
-rw-r-----. 1 yoku0825 yoku0825 856 Jul 2 02:26 sbtest@sbtest1.sql
-rw-r-----. 1 yoku0825 yoku0825 29377820 Jul 2 02:26 sbtest@sbtest1@0.tsv.zst
-rw-r-----. 1 yoku0825 yoku0825 496 Jul 2 02:26 sbtest@sbtest1@0.tsv.zst.idx
-rw-r-----. 1 yoku0825 yoku0825 29415751 Jul 2 02:26 sbtest@sbtest1@1.tsv.zst
-rw-r-----. 1 yoku0825 yoku0825 496 Jul 2 02:26 sbtest@sbtest1@1.tsv.zst.idx
-rw-r-----. 1 yoku0825 yoku0825 29415671 Jul 2 02:26 sbtest@sbtest1@2.tsv.zst
-rw-r-----. 1 yoku0825 yoku0825 496 Jul 2 02:26 sbtest@sbtest1@2.tsv.zst.idx
-rw-r-----. 1 yoku0825 yoku0825 117 Jul 2 02:26 sbtest@sbtest1@@3.tsv.zst
-rw-r-----. 1 yoku0825 yoku0825 8 Jul 2 02:26 sbtest@sbtest1@@3.tsv.zst.idx
$ jq . /path/to/full_backup/@.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": "dumpInstance",
"schemas": [
"sakura",
"sbtest"
],
"basenames": {
"sakura": "sakura",
"sbtest": "sbtest"
},
"users": [
"'healthchecker'@'localhost'",
"'mysqlsh_backup'@'%'",
"'root'@'%'",
"'root'@'localhost'"
],
"defaultCharacterSet": "utf8mb4",
"tzUtc": true,
"bytesPerChunk": 64000000,
"user": "mysqlsh_backup",
"hostname": "yoku0825-oci",
"gtidExecutedInconsistent": false,
"consistent": true,
"targetVersion": "9.3.0",
"hasLibraryDdl": false,
"source": {
"binlog": {
"file": "binlog.000002",
"position": 190942962,
"gtidExecuted": "3b5d02fb-56e7-11f0-8f01-0242ac110002:1-455"
},
"sysvars": {
..
},
"topology": {
"canonicalAddress": "a7786309934c:3306"
}
},
"server": "a7786309934c",
"serverVersion": "8.4.5",
"binlogFile": "binlog.000002",
"binlogPosition": 190942962,
"gtidExecuted": "3b5d02fb-56e7-11f0-8f01-0242ac110002:1-455",
"partialRevokes": false,
"compatibilityOptions": [],
"capabilities": [],
"checksum": false,
"begin": "2025-07-02 02:26:38"
}
$ jq -r .begin /path/to/full_backup/@.json
2025-07-02 02:26:38
バックアッププロセス自体の情報は @.json と @.post.json に含まれている。論理バックアップである util.dumpInstance のデータの切断面は開始時刻になると期待するので、 @.json に含まれる begin を確認しておく。
リストア用にもう1つコンテナを立ち上げて、 util.loadDump に必要な local_infile をONにして実行。
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> SET GLOBAL local_infile = 1;
Query OK, 0 rows affected (0.00 sec)
$ mysqlsh root@172.17.0.3 --js -- util loadDump /path/to/full_backup/ { --loadUsers=true --ignoreExistingObjects=true --updateGtidSet=replace } ### デフォルトではユーザーまでは生成されないのと、既存のアカウントは無視してくれればいい、gtid_executedは上書きする
狙った通りにリストアできてるか、ハートビートテーブルから確認できる。
綺麗にぴったり @.json の begin と一致してよかった。
mysql> 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 |
+-----+--------------+---------------------+--------------------------------------------+
1 row in set (0.00 sec)
特に仕組みを作っている最中はこのハートビートテーブルが役に立つというのが一番の知見かもしれない。
