Blackholeストレージエンジンとバイナリログと 2024

この記事は MySQL Advent Calendar 2024 の20日目の記事です。
昨日は asahideさん の Google Cloudでも使えるHeatWave MySQL でした。

---

TL;DR

• binlog_format=ROW で log_replica_updates=ON (MySQL 8.0とそれ以降のデフォルトのまま)
• ソースが BLACKHOLEストレージエンジン の場合は INSERT はバイナリログに載るが UPDATE/DELETE はバイナリログに載らない
  • これは知ってた
• ソースがInnoDBでレプリカがBLACKHOLEの場合、ソースで成立した INSERT だけでなく UPDATE/DELETE もレプリカのバイナリログに吐く
  • これうまく使えばbinlog_serverになるんじゃ？

---

まだ MySQL::Sandbox を使っている。

$ make_replication_sandbox --how_many_slaves=1 8.0.40
$ cd sandboxes/rsandbox_8_0_40/
$ ./m

CREATE TABLE d1.innodb (num int) Engine = InnoDB;
CREATE TABLE d1.myisam (num int) Engine = MyISAM;
CREATE TABLE d1.blackhole (num int) Engine = BLACKHOLE;

FLUSH BINARY LOGS;  -- 見やすくするため

INSERT INTO d1.innodb VALUES(1);
INSERT INTO d1.myisam VALUES(1);
INSERT INTO d1.blackhole VALUES(1);

UPDATE d1.innodb SET num = 2 WHERE num = 1;
UPDATE d1.myisam SET num = 2 WHERE num = 1;
UPDATE d1.blackhole SET num = 2 WHERE num = 1;

master [localhost] {msandbox} ((none)) > SHOW WARNINGS;
+---------+------+--------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                            |
+---------+------+--------------------------------------------------------------------------------------------------------------------+
| Warning | 1870 | Row events are not logged for UPDATE statements that modify BLACKHOLE tables in row format. Table(s): 'blackhole.' |
+---------+------+--------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

DELETE FROM d1.innodb;
DELETE FROM d1.myisam;
DELETE FROM d1.blackhole;

master [localhost] {msandbox} ((none)) > SHOW WARNINGS;
+---------+------+--------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                            |
+---------+------+--------------------------------------------------------------------------------------------------------------------+
| Warning | 1870 | Row events are not logged for DELETE statements that modify BLACKHOLE tables in row format. Table(s): 'blackhole.' |
+---------+------+--------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

$ mysqlbinlog -vv master/data/mysql-bin.000002  | grep '^###'
### INSERT INTO `d1`.`innodb`
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
### INSERT INTO `d1`.`myisam`
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
### INSERT INTO `d1`.`blackhole`
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
### UPDATE `d1`.`innodb`
### WHERE
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
### SET
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
### UPDATE `d1`.`myisam`
### WHERE
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
### SET
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
### DELETE FROM `d1`.`innodb`
### WHERE
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
### DELETE FROM `d1`.`myisam`
### WHERE
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */

SHOW WARNINGS でも出てくる通り、BLACKHOLEストレージエンジンへの UPDATE/DELETE は無視されてバイナリログに載らない。
ソースのバイナリログに載らないから当然レプリカのバイナリログにも載らない。

$ mysqlbinlog -vv node1/data/mysql-bin.000001 | grep '^###'
### INSERT INTO `d1`.`innodb`
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
### INSERT INTO `d1`.`myisam`
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
### INSERT INTO `d1`.`blackhole`
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
### UPDATE `d1`.`innodb`
### WHERE
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
### SET
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
### UPDATE `d1`.`myisam`
### WHERE
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
### SET
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
### DELETE FROM `d1`.`innodb`
### WHERE
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
### DELETE FROM `d1`.`myisam`
### WHERE
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */

仮にソースでBLACKHOLE, レプリカでInnoDBなんてことを今日日(昔は binlog_format=STATEMENT や MIXED なこともあったので UPDATE/DELETE もソースで空振りしてもバイナリログに落ちてレプリカに伝わる)やろうとすると、レプリカ側ではINSERTはされ続けるけどUPDATEやDELETEは常に伝わらないので無限に肥大化することになる。

$ ./m
CREATE TABLE d1.innodb_to_blackhole (num int) Engine = InnoDB;
FLUSH BINARY LOGS;

$ ./s
ALTER TABLE d1.innodb_to_blackhole Engine = BLACKHOLE;
FLUSH BINARY LOGS;

ところで、ソースでInnoDBにしてからレプリカをBLACKHOLEにする(昔のバッドノウハウの更に逆パターン)をすると(本当にやるなら SET SESSION sql_log_bin=OFF 推奨)

$ ./m

INSERT INTO d1.innodb_to_blackhole VALUES (1);
UPDATE d1.innodb_to_blackhole SET num = 2 WHERE num = 1;
DELETE FROM d1.innodb_to_blackhole;

$ mysqlbinlog -vv master/data/mysql-bin.000003 | grep '^###'
### INSERT INTO `d1`.`innodb_to_blackhole`
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
### UPDATE `d1`.`innodb_to_blackhole`
### WHERE
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
### SET
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
### DELETE FROM `d1`.`innodb_to_blackhole`
### WHERE
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */

当然ソース(InnoDB)のバイナリログにも載るし

$ mysqlbinlog -vv node1/data/mysql-bin.000002 | grep '^###'
### INSERT INTO `d1`.`innodb_to_blackhole`
### SET
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
### UPDATE `d1`.`innodb_to_blackhole`
### WHERE
###   @1=1 /* INT meta=0 nullable=1 is_null=0 */
### SET
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */
### DELETE FROM `d1`.`innodb_to_blackhole`
### WHERE
###   @1=2 /* INT meta=0 nullable=1 is_null=0 */

( д ) ﾟ ﾟ レプリカのバイナリログに載ってるー！

これを上手く使えれば、

• mysqldだけどそんなにメモリもDiskも積まないマシンを作って
• でもmysqldだからsemisyncでACKを返せて
• リアルタイムでバイナリログだけを保管できて
• 何なら CHANGE REPLICATION SOURCE TO で source_host に指定してバイナリログだけ引っ張ってこられる ( gtid_mode=ON がいいと思う)
  ようなbinlogサーバーが作れると思いませんか！！

たぶん試します。

明日は discus_hamburgさん です！

​