2026/05/07

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/*!*/;

..

とこんな感じ。

2026/04/30

MySQL 5.7とそれ以前のpartial_revokesもどき

TL;DR

さっさとMySQL 8.0とそれ以降にアップグレードして partial_revokes を使う
過去にはこんなテクニックがあったんだよという記憶だけ

日々の覚書: GRANTでデータベース名にワイルドカードを指定することとpartial revokesとでもちょっと書いていた、「GRANTでデータベース名にワイルドカード」を一捻りしたバージョン。

「一般ユーザーに(データベース名は動的に変わる、などの理由で)任意のスキーマに対するCREATE, DROPその他の権限を割り当てたいけど mysql と performance_schema と sys だけはダメ、グローバルGRANTはやりたくない」という、まさに partial_revokes が欲しい状況を5.7で何とかしたい。

mysql (とその他システム) スキーマには明示的な *_priv = 'N' を割り当てて、それ以外の % にはスキーマレベルでのALLをGRANTする。

mysql57 8> SELECT @@version;
+------------+
| @@version  |
+------------+
| 5.7.44-log |
+------------+
1 row in set (0.00 sec)

mysql57 8> CREATE USER yoku0825;
Query OK, 0 rows affected (0.00 sec)

mysql57 8> GRANT ALL ON `%`.* TO yoku0825 WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql57 8> INSERT INTO mysql.db (host, db, user) VALUES ('%', 'mysql', 'yoku0825'), ('%', 'sys', 'yoku0825'), ('%', 'performance_schema', 'yoku0825');   -- *_privカラムのデフォルトは 'N'
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql57 8> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

「明示的な *_priv = 'N' の割り当て」はGRANTステートメントではできない ( GRANT USAGE ON mysql.* TO yoku0825 とかやっても mysql.db に全部 ‘N’ の行ができたりはしない）っぽいので、INSERTステートメントとFLUSH PRIVILEGESで表現する。

と、

mysql57 9> SHOW GRANTS;  -- 自分がmysqlスキーマとかに権限がないことが見えないのが嫌だといえば嫌だが (partial_revokesは見える)
+-------------------------------------------------------------------+
| Grants for yoku0825@%                                             |
+-------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'yoku0825'@'%'                              |
| GRANT ALL PRIVILEGES ON `%`.* TO 'yoku0825'@'%' WITH GRANT OPTION |
+-------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql57 9> SELECT COUNT(*) FROM mysql.user;
ERROR 1142 (42000): SELECT command denied to user 'yoku0825'@'localhost' for table 'user'

mysql57 9> SHOW TABLES FROM sys;
ERROR 1044 (42000): Access denied for user 'yoku0825'@'%' to database 'sys'

mysql57 9> CREATE DATABASE yoku0825;
Query OK, 1 row affected (0.00 sec)

mysql57 9> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| d1                 |
| world              |
| yoku0825           |
+--------------------+
4 rows in set (0.00 sec)

だいたいやりたいことができそう。
partial_revokesを有効にするとスキーマ名ワイルドカードが効かなくなるからできなくなるけど、そもそもつなぎのためのテクニックだろうからそれはそれで（アップグレードする時に忘れずに処置すれば）大丈夫なはず。

【2026/05/07 18:04】

「この場合、yoku0825アカウントでCREATE USER yoku0826してGRANT ALL ON `%`.* TO yoku0826するとyoku0826アカウントはmysqlスキーマ読めますね？」

「じゃあ INSERT INTO mysql.db (host, db, user) VALUES ('%', 'mysql', ''), ('%', 'sys', ''), ('%', 'performance_schema', '') にすれば空文字がユーザー名におけるワイルドカード扱いなのでそれで防ごう」

2026/04/20

MySQL 9.7 Early Release(無印)とMySQL 9.7 Early Release2の違い

TL;DR

何かを直した結果テストケースが膨らんでいるのはわかる
その一方で enable_cascade_triggers なる機能が増えていて面白かった
以下ピコ太郎風にお願いします（？）

I have a MySQL 9.7 Early Release (first) ..

ダウンロードは https://t.co/eD2BeHLWbZ からだ！

MySQL Community Early Access Builds | mysqlhttps://t.co/fG8lMgfL9w
— yoku0825 (@yoku0825) March 14, 2026

I have a MySQL 9.7 Early Release 2 ..

MySQL 9.7.0 Early Access Release "2" !?https://t.co/UYa8jvUDZS pic.twitter.com/cJWfkppVPL
— yoku0825 (@yoku0825) April 8, 2026

Ah!

＿人人人人人＿
＞　diff -r　＜
￣Y^Y^Y^Y^Y^￣

ということで

$ diff -r mysql-9.7.0-er mysql-9.7.0-er2 | tee diff.txt
..
diff -r mysql-9.7.0-er/sql/sys_vars.cc mysql-9.7.0-er2/sql/sys_vars.cc
7691a7692,7727
>
> /**
>   Warn usage of enable_cascade_triggers variable. When it is set
>   to false, warning should include triggers do not fire during FK cascade.
> */
> bool enable_cascade_triggers_check(sys_var *self, THD *thd, set_var *setv) {
>   if (setv->save_result.ulonglong_value == 0)
>     push_warning_printf(
>         thd, Sql_condition::SL_WARNING, ER_WARN_DEPRECATED_WITH_NOTE,
>         ER_THD(thd, ER_WARN_DEPRECATED_WITH_NOTE), self->name.str,
>         "Triggers on child table will not fire during foreign key cascade.");
>   else {
>     if (!is_sql_fk_checks_enabled(thd)) {
>       push_warning_printf(
>           thd, Sql_condition::SL_WARNING, ER_WARN_DEPRECATED_WITH_NOTE,
>           ER_THD(thd, ER_WARN_DEPRECATED_WITH_NOTE), self->name.str,
>           "Enabling trigger execution on child table is supported only with "
>           "SQL Foreign Key handling "
>           "(i.e with innodb_native_foreign_keys = OFF).");
>     } else {
>       push_warning_printf(thd, Sql_condition::SL_WARNING,
>                           ER_WARN_DEPRECATED_SYNTAX_NO_REPLACEMENT,
>                           ER_THD(thd, ER_WARN_DEPRECATED_SYNTAX_NO_REPLACEMENT),
>                           self->name.str);
>     }
>   }
>   return false;
> }
>
> Sys_var_bool Sys_enable_cascade_triggers(
>     "enable_cascade_triggers",
>     "Execute trigger on child tables during foreign key cascade operations for "
>     "SQL Engine foreign key handling(i.e. innodb_native_foreign_keys = OFF).",
>     SESSION_VAR(enable_cascade_triggers),
>     CMD_LINE(OPT_ARG, OPT_CASCADE_TRIGGERS), DEFAULT(false), NO_MUTEX_GUARD,
>     IN_BINLOG, ON_CHECK(enable_cascade_triggers_check), ON_UPDATE(nullptr));

..

パッと見つかったのは enable_cascade_triggers なるシステム変数が増えているところ。今までのストレージエンジンForeign Keyを「使わなかった時でも」CASCADEされた更新がTRIGGERを叩けるようにする、みたいな意味なんだろうか。

MySQL 8.4でテスト。t1の子にt2をセットしてUPDATEとDELETEをカスケードさせる。

mysql84 8> CREATE TABLE t1 (num serial, val varchar(32));
Query OK, 0 rows affected (0.01 sec)

mysql84 8> CREATE TABLE t2 (num serial, val varchar(32));
Query OK, 0 rows affected (0.01 sec)

mysql84 8> ALTER TABLE t2 ADD FOREIGN KEY (num) REFERENCES t1(num) ON UPDATE CASCADE ON DELETE CASCADE;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql84 8> INSERT INTO t1 VALUES (1, 'one'), (2, 'two'), (3, 'three');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql84 8> INSERT INTO t2 VALUES (1, 'one'), (2, 'two'), (3, 'three');
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

で、カスケードされたt2側にトリガーで記録を残すようにする。

mysql84 8> CREATE TABLE log (num serial, op varchar(32), dt datetime);
Query OK, 0 rows affected (0.01 sec)

mysql84 8> CREATE TRIGGER tr1 AFTER UPDATE ON t2 FOR EACH ROW INSERT INTO log VALUES (OLD.num, 'UPDATE', NOW());
Query OK, 0 rows affected (0.01 sec)

mysql84 8> CREATE TRIGGER tr2 AFTER DELETE ON t2 FOR EACH ROW INSERT INTO log VALUES (OLD.num, 'DELETE', NOW());
Query OK, 0 rows affected (0.00 sec)

で、DELETEしたりUPDATEしたり。

mysql84 8> SELECT * FROM log;
Empty set (0.00 sec)

mysql84 8> DELETE FROM t1 WHERE num = 1;
Query OK, 1 row affected (0.00 sec)

mysql84 8> SELECT * FROM t1;
+-----+-------+
| num | val   |
+-----+-------+
|   2 | two   |
|   3 | three |
+-----+-------+
2 rows in set (0.00 sec)

mysql84 8> SELECT * FROM t2;
+-----+-------+
| num | val   |
+-----+-------+
|   2 | two   |
|   3 | three |
+-----+-------+
2 rows in set (0.00 sec)

mysql84 8> SELECT * FROM log;
Empty set (0.00 sec)

mysql84 8> UPDATE t1 SET num = 102 WHERE val = 'two';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql84 8> SELECT * FROM t1;
+-----+-------+
| num | val   |
+-----+-------+
|   3 | three |
| 102 | two   |
+-----+-------+
2 rows in set (0.00 sec)

mysql84 8> SELECT * FROM t2;
+-----+-------+
| num | val   |
+-----+-------+
|   3 | three |
| 102 | two   |
+-----+-------+
2 rows in set (0.00 sec)

mysql84 8> SELECT * FROM log;
Empty set (0.00 sec)

空っぽ。
これがMySQL 9.7.0-er2で enable_cascade_triggers をONにしてやると

mysql97 15> SELECT @@session.enable_cascade_triggers;
+-----------------------------------+
| @@session.enable_cascade_triggers |
+-----------------------------------+
|                                 0 |
+-----------------------------------+
1 row in set (0.001 sec)

mysql97 15> SET SESSION enable_cascade_triggers = ON;
Query OK, 0 rows affected, 1 warning (0.000 sec)

mysql97 15> SHOW WARNINGS;
+---------+------+----------------------------------------------------------------------------------+
| Level   | Code | Message                                                                          |
+---------+------+----------------------------------------------------------------------------------+
| Warning | 1681 | 'enable_cascade_triggers' is deprecated and will be removed in a future release. |
+---------+------+----------------------------------------------------------------------------------+
1 row in set (0.000 sec)

mysql97 15>
mysql97 15> SELECT @@session.enable_cascade_triggers;
+-----------------------------------+
| @@session.enable_cascade_triggers |
+-----------------------------------+
|                                 1 |
+-----------------------------------+
1 row in set (0.000 sec)

出たー。

mysql97 15> DELETE FROM t1 WHERE num = 1;
Query OK, 1 row affected (0.002 sec)

mysql97 15> UPDATE t1 SET num = 102 WHERE val = 'two';
Query OK, 1 row affected (0.001 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql97 15> SELECT * FROM t1;
+-----+-------+
| num | val   |
+-----+-------+
|   3 | three |
| 102 | two   |
+-----+-------+
2 rows in set (0.000 sec)

mysql97 15> SELECT * FROM t2;
+-----+-------+
| num | val   |
+-----+-------+
|   3 | three |
| 102 | two   |
+-----+-------+
2 rows in set (0.000 sec)

mysql97 15> SELECT * FROM log;
+-----+--------+---------------------+
| num | op     | dt                  |
+-----+--------+---------------------+
|   1 | DELETE | 2026-04-20 07:35:06 |
|   2 | UPDATE | 2026-04-20 07:35:08 |
+-----+--------+---------------------+
2 rows in set (0.000 sec)

使いどころがあるかどうか（インコンパチだから）は兎も角おもしろ（というか、今までできなかったこともあんまり気にしてなかった）

なお予告(?)通り、FOREIGN KEYのカスケードもトリガーの結果もちゃんとバイナリログに全部書くようになってた。

# at 3572
#260420  7:35:08 server id 1097  end_log_pos 3572 CRC32 0xf56f08ff      Rows_query
# UPDATE t1 SET num = 102 WHERE val = 'two'
# at 3572
#260420  7:35:08 server id 1097  end_log_pos 3572 CRC32 0x07966e20      Table_map: `d1`.`t1` mapped to number 130
# has_generated_invisible_primary_key=0
# at 3572
#260420  7:35:08 server id 1097  end_log_pos 3572 CRC32 0x7cbbaf93      Table_map: `d1`.`t2` mapped to number 133
# has_generated_invisible_primary_key=0
# at 3572
#260420  7:35:08 server id 1097  end_log_pos 3572 CRC32 0xf3902a3a      Table_map: `d1`.`log` mapped to number 128
# has_generated_invisible_primary_key=0
# at 3572
#260420  7:35:08 server id 1097  end_log_pos 3572 CRC32 0x3392556e      Update_rows: table id 133 flags: NO_FOREIGN_KEY_CHECKS_F UNKNOWN_FLAG(0x10)
# at 3572
#260420  7:35:08 server id 1097  end_log_pos 3572 CRC32 0xdecdd0e3      Write_rows: table id 128 flags: UNKNOWN_FLAG(0x10)
# at 3572
#260420  7:35:08 server id 1097  end_log_pos 3572 CRC32 0x58208ae7      Update_rows: table id 130 flags: STMT_END_F UNKNOWN_FLAG(0x10)

BINLOG '
LNflaR1JBAAAQQAAAAAAAACAAABVUERBVEUgdDEgU0VUIG51bSA9IDEwMiBXSEVSRSB2YWwgPSAn
dHdvJ/8Ib/U=
LNflaRNJBAAANgAAAAAAAAAAAIIAAAAAAAMAAmQxAAJ0MQACCA8CgAACAQGAAgP8/wAgbpYH
LNflaRNJBAAANgAAAAAAAAAAAIUAAAAAAAMAAmQxAAJ0MgACCA8CgAACAQGAAgP8/wCTr7t8
LNflaRNJBAAAOQAAAAAAAAAAAIAAAAAAAAMAAmQxAANsb2cAAwgPEgOAAAAGAQGAAgP8/wA6KpDz
LNflaR9JBAAAOgAAAAAAAAAAAIUAAAAAABIAAgACAf8AAgAAAAAAAAAAZgAAAAAAAAADdHdvblWS
Mw==
LNflaR5JBAAAOAAAAAAAAAAAAIAAAAAAABAAAgAD/wACAAAAAAAAAAZVUERBVEWZuah4yOPQzd4=
LNflaR9JBAAANgAAAAAAAAAAAIIAAAAAABEAAgACAQEAAgAAAAAAAAAAZgAAAAAAAADniiBY
'/*!*/;
### UPDATE `d1`.`t2`
### WHERE
###   @1=2 /* LONGINT meta=0 nullable=0 is_null=0 */
### SET
###   @1=102 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2='two' /* VARSTRING(128) meta=128 nullable=1 is_null=0 */
### INSERT INTO `d1`.`log`
### SET
###   @1=2 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2='UPDATE' /* VARSTRING(128) meta=128 nullable=1 is_null=0 */
###   @3='2026-04-20 07:35:08' /* DATETIME(0) meta=0 nullable=1 is_null=0 */
### UPDATE `d1`.`t1`
### WHERE
###   @1=2 /* LONGINT meta=0 nullable=0 is_null=0 */
### SET
###   @1=102 /* LONGINT meta=0 nullable=0 is_null=0 */
# at 3572
#260420  7:35:08 server id 1097  end_log_pos 3572 CRC32 0xbe416b78      Xid = 224
COMMIT/*!*/;

8.4だとこうなので、FKは書かれていないかった（し、トリガーは着火させるようにクエリを流してもトリガーぶんはバイナリログには書かれてない、これは従前といっしょ）

# at 515
#260420  7:32:22 server id 1084  end_log_pos 515 CRC32 0x46c03123       Rows_query
# UPDATE t1 SET num = 102 WHERE val = 'two'
# at 515
#260420  7:32:22 server id 1084  end_log_pos 515 CRC32 0x6de5ebb9       Table_map: `d1`.`t1` mapped to number 85
# has_generated_invisible_primary_key=0
# at 515
#260420  7:32:22 server id 1084  end_log_pos 515 CRC32 0x6a442810       Update_rows: table id 85 flags: STMT_END_F

BINLOG '
htblaR08BAAAQQAAAAAAAACAAClVUERBVEUgdDEgU0VUIG51bSA9IDEwMiBXSEVSRSB2YWwgPSAn
dHdvJyMxwEY=
htblaRM8BAAANgAAAAAAAAAAAFUAAAAAAAMAAmQxAAJ0MQACCA8CgAACAQGAAgP8/wC56+Vt
htblaR88BAAANgAAAAAAAAAAAFUAAAAAAAEAAgACAQEAAgAAAAAAAAAAZgAAAAAAAAAQKERq
'/*!*/;
### UPDATE `d1`.`t1`
### WHERE
###   @1=2 /* LONGINT meta=0 nullable=0 is_null=0 */
### SET
###   @1=102 /* LONGINT meta=0 nullable=0 is_null=0 */
# at 515
#260420  7:32:22 server id 1084  end_log_pos 515 CRC32 0x0f860ca6       Xid = 5
COMMIT/*!*/;

diffの全文はgistに上げたけど、WEBインターフェースだと後ろがちょん切れて見えないので（本当は33817行ある）ダウンロードしてみないといけなさそう。他に新機能っぽいものはないけどバグ直したついでのテストケースとか増えてる。

9.7.0-er_vs_9.7.0-er2.diff

2026/04/16

ansibleのcommunity.mysql.mysql_userでauth_socketでroot@localhostを作る

未来の自分にメモ。

MySQLには auth_socketプラグインがあるので、root@localhostをパスワードレスにしてOS rootからしかアクセスできなくすることができる（今日日OS rootに直接sshする機会はないと思うのでつまり sudo mysql でしかアクセスできなくする）
my.cnfのテンプレートファイルか何かの [mysqld] セクションに plugin_load を追加（もし、既に plugin_load を使っていてその後ろに書くのなら plugin_load_add を使う）

plugin_load= auth_socket.so

ユーザーをセットアップする時にroot@localhostはpluginを指定して作る。

- name: create root@localhost
  community.mysql.mysql_user:
    host: localhost
    name: root
    state: present
    login_user: root
    login_host: localhost
    login_unix_socket: "/var/lib/mysql/mysql.sock"
    sql_log_bin: false
    plugin: "auth_socket"

それ以降に別のアカウントを作る時は、OS rootでないとroot@localhostを使えなくなっているので become: true で作る。

- name: create app user
  community.mysql.mysql_user:
    host: "192.168.0.1"
    name: appuser
    password: "{{ appuser_password }}"
    priv: "app_schema.*:INSERT,UPDATE,SELECT,DELETE"
    login_user: root
    login_host: localhost
    login_unix_socket: "/var/lib/mysql/mysql.sock"
    sql_log_bin: false
  become: true

community.mysql.mysql_user module – Adds or removes a user from a MySQL or MariaDB database — Ansible Community Documentation

2026/04/06

InnoDB FTSがメモリを食う件その2 tcmallocでいくらかマシになりそう

日々の覚書: InnoDB FULLTEXT KEYがメモリを食う件(未解決) の続き。

何がメモリを使ってるのかを performance_schema.memory_summary_global_by_event_name テーブルで(できないだろうなと思いながら)観測してみる。

mysql80> RESTART;
$ sysbench --mysql-socket=/usr/mysql/8.0.45/data/mysql.sock --mysql-user=root oltp_read_write --table_size=1000000 --time=600 --report-interval=1 cleanup
$ sysbench --mysql-socket=/usr/mysql/8.0.45/data/mysql.sock --mysql-user=root oltp_read_write --table_size=1000000 --time=600 --report-interval=1 prepare
$ date ; sysbench --mysql-socket=/usr/mysql/8.0.45/data/mysql.sock --mysql-user=root oltp_write_only --table_size=1000000 --time=600 --report-interval=1 run ; date

$ ps -Ao pid,fname,rss | grep 3591371
3591371 mysqld   498060

↑まずはフルテキストインデックスなしの状態でベンチを流した後の memory_summary_global_by_event_name

mysql80 74> SELECT *, format_bytes(SUM(current_number_of_bytes_used) OVER (ORDER BY current_number_of_bytes_used DESC)) FROM performance_schema.memory_summary_global_by_event_name WHERE event_name NOT LIKE 'memory/performance_schema/%' ORDER BY current_number_of_bytes_used DESC LIMIT 10;
+---------------------------------+-------------+------------+---------------------------+--------------------------+----------------+--------------------+-----------------+--------------------------+------------------------------+---------------------------+---------------------------------------------------------------------------------------------------+
| EVENT_NAME                      | COUNT_ALLOC | COUNT_FREE | SUM_NUMBER_OF_BYTES_ALLOC | SUM_NUMBER_OF_BYTES_FREE | LOW_COUNT_USED | CURRENT_COUNT_USED | HIGH_COUNT_USED | LOW_NUMBER_OF_BYTES_USED | CURRENT_NUMBER_OF_BYTES_USED | HIGH_NUMBER_OF_BYTES_USED | format_bytes(SUM(current_number_of_bytes_used) OVER (ORDER BY current_number_of_bytes_used DESC)) |
+---------------------------------+-------------+------------+---------------------------+--------------------------+----------------+--------------------+-----------------+--------------------------+------------------------------+---------------------------+---------------------------------------------------------------------------------------------------+
| memory/innodb/buf_buf_pool      |           1 |          0 |                  68620288 |                        0 |              0 |                  1 |               1 |                        0 |                     68620288 |                  68620288 | 65.44 MiB                                                                                         |
| memory/innodb/ut0link_buf       |           2 |          0 |                  25165888 |                        0 |              0 |                  2 |               2 |                        0 |                     25165888 |                  25165888 | 89.44 MiB                                                                                         |
| memory/innodb/log_buffer_memory |           1 |          0 |                  16778224 |                        0 |              0 |                  1 |               1 |                        0 |                     16778224 |                  16778224 | 105.44 MiB                                                                                        |
| memory/mysys/KEY_CACHE          |           3 |          0 |                   8390864 |                        0 |              0 |                  3 |               3 |                        0 |                      8390864 |                   8390864 | 113.44 MiB                                                                                        |
| memory/sql/TABLE                |        6028 |       3948 |                  30576081 |                 22566230 |              0 |               2080 |            2082 |                        0 |                      8009851 |                   8041086 | 121.08 MiB                                                                                        |
| memory/innodb/sync0arr          |           3 |          0 |                   7373032 |                        0 |              0 |                  3 |               3 |                        0 |                      7373032 |                   7373032 | 128.11 MiB                                                                                        |
| memory/sql/log_sink_pfs         |           3 |          2 |                   5292128 |                    49216 |              0 |                  1 |               2 |                        0 |                      5242912 |                   5259328 | 133.11 MiB                                                                                        |
| memory/innodb/lock0lock         |          33 |          0 |                   5086600 |                        0 |              0 |                 33 |              33 |                        0 |                      5086600 |                   5086600 | 137.97 MiB                                                                                        |
| memory/innodb/ut0pool           |           2 |          0 |                   4194488 |                        0 |              0 |                  2 |               2 |                        0 |                      4194488 |                   4194488 | 141.97 MiB                                                                                        |
| memory/temptable/physical_ram   |          73 |         69 |                  76548384 |                 72353952 |              0 |                  4 |               4 |                        0 |                      4194432 |                   4194432 | 145.97 MiB                                                                                        |
+---------------------------------+-------------+------------+---------------------------+--------------------------+----------------+--------------------+-----------------+--------------------------+------------------------------+---------------------------+---------------------------------------------------------------------------------------------------+
10 rows in set (0.00 sec)

mysql80 74> SELECT SUM(current_number_of_bytes_used) FROM performance_schema.memory_summary_global_by_event_name;
+-----------------------------------+
| SUM(current_number_of_bytes_used) |
+-----------------------------------+
|                         410046116 |
+-----------------------------------+
1 row in set (0.00 sec)

mysql80 74> SHOW ENGINE PERFORMANCE_SCHEMA STATUS;
..
| performance_schema | performance_schema.memory                                   | 242396808 |
+--------------------+-------------------------------------------------------------+-----------+
248 rows in set (0.00 sec)

トータルではまあまあ合ってそうな気がする( memory/temptable/physical_ram はたぶん監視に使ってる pmm-agent だな…失敗したかも )

次にALTER TABLEでFULLTEXT INDEXを足す。
この時点でメモリ使用量が増えている（これ自体は不思議ではない）けど、p_sには計上されず…（これがぐぬぬ）

mysql80 90> ALTER TABLE sbtest.sbtest1 ADD FULLTEXT KEY (c);
Query OK, 0 rows affected, 1 warning (1 min 38.24 sec)
Records: 0  Duplicates: 0  Warnings: 1

$ ps -Ao pid,fname,rss | grep 3591371
3591371 mysqld   659868

mysql80 107> SELECT SUM(current_number_of_bytes_used) FROM performance_schema.memory_summary_global_by_event_name;
+-----------------------------------+
| SUM(current_number_of_bytes_used) |
+-----------------------------------+
|                         408419366 |
+-----------------------------------+
1 row in set (0.00 sec)

mysql80 107> SELECT *, format_bytes(SUM(current_number_of_bytes_used) OVER (ORDER BY current_number_of_bytes_used DESC)) FROM performance_schema.memory_summary_global_by_event_name WHERE event_name NOT LIKE 'memory/performance_schema/%' ORDER BY current_number_of_bytes_used DESC LIMIT 10;
+---------------------------------+-------------+------------+---------------------------+--------------------------+----------------+--------------------+-----------------+--------------------------+------------------------------+---------------------------+---------------------------------------------------------------------------------------------------+
| EVENT_NAME                      | COUNT_ALLOC | COUNT_FREE | SUM_NUMBER_OF_BYTES_ALLOC | SUM_NUMBER_OF_BYTES_FREE | LOW_COUNT_USED | CURRENT_COUNT_USED | HIGH_COUNT_USED | LOW_NUMBER_OF_BYTES_USED | CURRENT_NUMBER_OF_BYTES_USED | HIGH_NUMBER_OF_BYTES_USED | format_bytes(SUM(current_number_of_bytes_used) OVER (ORDER BY current_number_of_bytes_used DESC)) |
+---------------------------------+-------------+------------+---------------------------+--------------------------+----------------+--------------------+-----------------+--------------------------+------------------------------+---------------------------+---------------------------------------------------------------------------------------------------+
| memory/innodb/buf_buf_pool      |           1 |          0 |                  68620288 |                        0 |              0 |                  1 |               1 |                        0 |                     68620288 |                  68620288 | 65.44 MiB                                                                                         |
| memory/innodb/ut0link_buf       |           2 |          0 |                  25165888 |                        0 |              0 |                  2 |               2 |                        0 |                     25165888 |                  25165888 | 89.44 MiB                                                                                         |
| memory/innodb/log_buffer_memory |           1 |          0 |                  16778224 |                        0 |              0 |                  1 |               1 |                        0 |                     16778224 |                  16778224 | 105.44 MiB                                                                                        |
| memory/mysys/KEY_CACHE          |           3 |          0 |                   8390864 |                        0 |              0 |                  3 |               3 |                        0 |                      8390864 |                   8390864 | 113.44 MiB                                                                                        |
| memory/sql/TABLE                |        7177 |       5065 |                  37444758 |                 29329243 |              0 |               2112 |            2113 |                        0 |                      8115515 |                   8130336 | 121.18 MiB                                                                                        |
| memory/innodb/sync0arr          |           3 |          0 |                   7373032 |                        0 |              0 |                  3 |               3 |                        0 |                      7373032 |                   7373032 | 128.22 MiB                                                                                        |
| memory/sql/log_sink_pfs         |           3 |          2 |                   5292128 |                    49216 |              0 |                  1 |               2 |                        0 |                      5242912 |                   5259328 | 133.22 MiB                                                                                        |
| memory/innodb/lock0lock         |          33 |          0 |                   5086600 |                        0 |              0 |                 33 |              33 |                        0 |                      5086600 |                   5086600 | 138.07 MiB                                                                                        |
| memory/innodb/ut0pool           |           2 |          0 |                   4194488 |                        0 |              0 |                  2 |               2 |                        0 |                      4194488 |                   4194488 | 142.07 MiB                                                                                        |
| memory/temptable/physical_ram   |          96 |         92 |                 100666368 |                 96471936 |              0 |                  4 |               4 |                        0 |                      4194432 |                   4194432 | 146.07 MiB                                                                                        |
+---------------------------------+-------------+------------+---------------------------+--------------------------+----------------+--------------------+-----------------+--------------------------+------------------------------+---------------------------+---------------------------------------------------------------------------------------------------+
10 rows in set (0.01 sec)

一度リスタートして再度600秒ベンチ。

mysql80 107> RESTART;
Query OK, 0 rows affected (0.00 sec)

$ date ; sysbench --mysql-socket=/usr/mysql/8.0.45/data/mysql.sock --mysql-user=root oltp_write_only --table_size=1000000 --time=600 --report-interval=1 run ; date

$ ps -Ao pid,fname,rss | grep 3592261
3592261 mysqld   567808

mysql80 48> SELECT *, format_bytes(SUM(current_number_of_bytes_used) OVER (ORDER BY current_number_of_bytes_used DESC)) FROM performance_schema.memory_summary_global_by_event_name WHERE event_name NOT LIKE 'memory/performance_schema/%' ORDER BY current_number_of_bytes_used DESC LIMIT 10;
+---------------------------------+-------------+------------+---------------------------+--------------------------+----------------+--------------------+-----------------+--------------------------+------------------------------+---------------------------+---------------------------------------------------------------------------------------------------+
| EVENT_NAME                      | COUNT_ALLOC | COUNT_FREE | SUM_NUMBER_OF_BYTES_ALLOC | SUM_NUMBER_OF_BYTES_FREE | LOW_COUNT_USED | CURRENT_COUNT_USED | HIGH_COUNT_USED | LOW_NUMBER_OF_BYTES_USED | CURRENT_NUMBER_OF_BYTES_USED | HIGH_NUMBER_OF_BYTES_USED | format_bytes(SUM(current_number_of_bytes_used) OVER (ORDER BY current_number_of_bytes_used DESC)) |
+---------------------------------+-------------+------------+---------------------------+--------------------------+----------------+--------------------+-----------------+--------------------------+------------------------------+---------------------------+---------------------------------------------------------------------------------------------------+
| memory/innodb/buf_buf_pool      |           1 |          0 |                  68620288 |                        0 |              0 |                  1 |               1 |                        0 |                     68620288 |                  68620288 | 65.44 MiB                                                                                         |
| memory/innodb/ut0link_buf       |           2 |          0 |                  25165888 |                        0 |              0 |                  2 |               2 |                        0 |                     25165888 |                  25165888 | 89.44 MiB                                                                                         |
| memory/innodb/log_buffer_memory |           1 |          0 |                  16778224 |                        0 |              0 |                  1 |               1 |                        0 |                     16778224 |                  16778224 | 105.44 MiB                                                                                        |
| memory/mysys/KEY_CACHE          |           3 |          0 |                   8390864 |                        0 |              0 |                  3 |               3 |                        0 |                      8390864 |                   8390864 | 113.44 MiB                                                                                        |
| memory/sql/TABLE                |        4204 |       2239 |                  19897956 |                 12217500 |              0 |               1965 |             895 |                        0 |                      7680456 |                   7680456 | 120.77 MiB                                                                                        |
| memory/innodb/sync0arr          |           3 |          0 |                   7373032 |                        0 |              0 |                  3 |               3 |                        0 |                      7373032 |                   7373032 | 127.80 MiB                                                                                        |
| memory/sql/log_sink_pfs         |           3 |          2 |                   5292128 |                    49216 |              0 |                  1 |               2 |                        0 |                      5242912 |                   5259328 | 132.80 MiB                                                                                        |
| memory/innodb/lock0lock         |          33 |          0 |                   5086600 |                        0 |              0 |                 33 |              33 |                        0 |                      5086600 |                   5086600 | 137.65 MiB                                                                                        |
| memory/innodb/ut0pool           |           2 |          0 |                   4194488 |                        0 |              0 |                  2 |               2 |                        0 |                      4194488 |                   4194488 | 141.65 MiB                                                                                        |
| memory/temptable/physical_ram   |          37 |         33 |                  38798496 |                 34604064 |              0 |                  4 |               4 |                        0 |                      4194432 |                   4194432 | 145.65 MiB                                                                                        |
+---------------------------------+-------------+------------+---------------------------+--------------------------+----------------+--------------------+-----------------+--------------------------+------------------------------+---------------------------+---------------------------------------------------------------------------------------------------+
10 rows in set (0.02 sec)

mysql80 48> SHOW ENGINE PERFORMANCE_SCHEMA STATUS;

..
| performance_schema | performance_schema.memory                                   | 240987688 |
+--------------------+-------------------------------------------------------------+-----------+
248 rows in set (0.00 sec)

やっぱりperformance_schemaから調べるのは難しそう。
取り敢えずtcmalloc使ったら変わるかどうかの切り分けだけやっておく。

$ grep malloc /etc/my.cnf
malloc-lib=/usr/lib64/libtcmalloc.so

$ lsof -p 3639083 | grep mall
mysqld  3639083 yoku0825  mem    REG              252,0      334544  68185695 /usr/lib64/libtcmalloc.so.4.5.3

$ sysbench --mysql-socket=/usr/mysql/8.0.45/data/mysql.sock --mysql-user=root oltp_common --table_size=1000000 cleanup

$ sysbench --mysql-socket=/usr/mysql/8.0.45/data/mysql.sock --mysql-user=root oltp_common --table_size=1000000 prepare

$ date ; sysbench --mysql-socket=/usr/mysql/8.0.45/data/mysql.sock --mysql-user=root oltp_write_only --table_size=1000000 --time=600 --report-interval=1 run ; date

$ ps -Ao pid,fname,rss | grep 3639083
3639083 mysqld   518920

フルテキストインデックス無しのケースは大して変わらず（スコアは気持ち上がったけど複数回測ってないので確かではない）

mysql80 165> ALTER TABLE sbtest.sbtest1 ADD FULLTEXT KEY (c);
Query OK, 0 rows affected, 1 warning (1 min 37.04 sec)
Records: 0  Duplicates: 0  Warnings: 1

$ ps -Ao pid,fname,rss | grep 3639083
3639083 mysqld   704996

mysql80 184> RESTART;
Query OK, 0 rows affected (0.00 sec)

$ date ; sysbench --mysql-socket=/usr/mysql/8.0.45/data/mysql.sock --mysql-user=root oltp_write_only --table_size=1000000 --time=600 --report-interval=1 run ; date

$ ps -Ao pid,fname,rss | grep 3640646
3640646 mysqld   588272

おー、だいぶ穏やかになった。tcmallocに変えて改善するということはメモリのフラグメンテーション関連かな…。

登録: 投稿 ( Atom )