第72回　行ベースレプリケーション関連のオプション

この連載でも何度か紹介していますが、MySQLのレプリケーション方式には、ステートメントベースレプリケーション（SBR）と行ベースレプリケーション（RBR）があります。今回は行ベースレプリケーションにおいて、設定しておくと得するオプションについて紹介したいと思います。行ベースレプリケーションについては、過去の連載をご確認ください。

binlog_rows_query_log_events オプション

障害時やデバックするためにSQL文をバイナリログから調査することがあると思います。しかし、MySQL5.7以降のデフォルト設定（RBR）環境では実際に実行したSQL文（以後、オリジナルSQL）を確認することはできません。それを解決するためにbinlog_rows_query_log_eventsオプションを使用します。

デフォルトはOFF（無効）で、その場合でも更新内容を推測できる疑似のSQL文（以後、疑似SQL）は確認することができます。ON（有効）にすると、RBR環境化でもマスターがオリジナルSQLをバイナリログに記録するようになります。そして、mysqlbinlogコマンドを使用して、そのオリジナルSQLを確認することができます。ちなみに、このオプションはオンラインで設定変更可能です。

例として、以下パターンをmysqlbinlogコマンドとオプションを付けて、バイナリログを確認してみます。オプションについては後述いたします。

①binlog_rows_query_log_eventsを無効化して、INSERT INTO t0 (col1, col2) VALUES (1,'MODE_OFF');というINSERT文を実行した後にバイナリログを確認します。

$ mysqlbinlog -vv  --base64-output=DECODE-ROWS  binary_log.000024
# at 293
#180519 17:29:29 server id 1  end_log_pos 343 CRC32 0x05580798  Table_map: `testdb`.`t0` mapped to number 232
# at 343
#180519 17:29:29 server id 1  end_log_pos 401 CRC32 0x79fb8714  Write_rows: table id 232 flags: STMT_END_F
### INSERT INTO `testdb`.`t0`
### SET
###   @1=1 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2=1 /* INT meta=0 nullable=1 is_null=0 */
###   @3='MODE_OFF' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
# at 401
#180519 17:29:29 server id 1  end_log_pos 432 CRC32 0x99cfd0f5  Xid = 11
COMMIT/*!*/;

###の部分のINSERT~から疑似SQLが確認できます。しかし、オリジナルSQLではありません。

②次にbinlog_rows_query_log_eventsを有効化して、INSERT INTO t0 (col1, col2) VALUES (2,'MODE_ON');というINSERT文を実行した後にバイナリログを確認します。

$ mysqlbinlog -vv  --base64-output=DECODE-ROWS  binary_log.000024
# at 293
#180519 17:33:16 server id 1  end_log_pos 365 CRC32 0xaf7afb2e  Rows_query
# INSERT INTO t0 (col1, col2) VALUES (2,'MODE_ON')
# at 365
#180519 17:33:16 server id 1  end_log_pos 415 CRC32 0xc15c1b25  Table_map: `testdb`.`t0` mapped to number 232
# at 415
#180519 17:33:16 server id 1  end_log_pos 472 CRC32 0x82e0053d  Write_rows: table id 232 flags: STMT_END_F
### INSERT INTO `testdb`.`t0`
### SET
###   @1=3 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2=2 /* INT meta=0 nullable=1 is_null=0 */
###   @3='MODE_ON' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
# at 472
#180519 17:33:16 server id 1  end_log_pos 503 CRC32 0x0e3c2907  Xid = 22
COMMIT/*!*/;

Rows_queryが記述してある行の下に、オリジナルSQLが表示されているのがわかります。Rows_queryでgrepすることで検索することも可能となります。

$ mysqlbinlog -vv --base64-output=DECODE-ROWS  binary_log.000024 | grep -A1 Rows_query
#180519 17:33:16 server id 1  end_log_pos 365 CRC32 0xaf7afb2e  Rows_query
# INSERT INTO t0 (col1, col2) VALUES (2,'MODE_ON')

mysqlbinlogコマンドのオプションについて

前項で、mysqlbinlogコマンドに-vvと--base64-output=DECODE-ROWSのオプションを指定しました。binlog_rows_query_log_eventsを有効化した環境で、バイナリログからオリジナルSQLを確認するためには-vvオプションが必須となります。--base64-output=DECODE-ROWSに関してはなくても大丈夫ですが、表示を見やすくするために、以下のBINLOG 'から始まるbase64文字列を表示させないように付けています。

--base64-output=DECODE-ROWSを指定せず実行

$ mysqlbinlog -vv binary_log.000024
# at 293
#180519 17:33:16 server id 1  end_log_pos 365 CRC32 0xaf7afb2e  Rows_query
# INSERT INTO t0 (col1, col2) VALUES (2,'MODE_ON')
# at 365
#180519 17:33:16 server id 1  end_log_pos 415 CRC32 0xc15c1b25  Table_map: `testdb`.`t0` mapped to number 232
# at 415
#180519 17:33:16 server id 1  end_log_pos 472 CRC32 0x82e0053d  Write_rows: table id 232 flags: STMT_END_F

BINLOG '
TOH/Wh0BAAAASAAAAG0BAACAADBJTlNFUlQgSU5UTyB0MCAoY29sMSwgY29sMikgVkFMVUVTICgy
LCdNT0RFX09OJyku+3qv
TOH/WhMBAAAAMgAAAJ8BAAAAAOgAAAAAAAEABnRlc3RkYgACdDAAAwgD/AECBiUbXME=
TOH/Wh4BAAAAOQAAANgBAAAAAOgAAAAAAAEAAgAD//gDAAAAAAAAAAIAAAAHAE1PREVfT049BeCC
'/*!*/;
### INSERT INTO `testdb`.`t0`
### SET
###   @1=3 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2=2 /* INT meta=0 nullable=1 is_null=0 */
###   @3='MODE_ON' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */

ちなみに、 -vvは--verboseを2回指定したものと同様です。

-vのみを指定すると疑似SQLのみを表示します。-vvを指定すると疑似SQLと各カラムのデータ型と一部のメタデータも表示され、binlog_rows_query_log_events=ONの環境化ではオリジナルSQLを表示します。

-vの疑似SQL

### INSERT INTO `testdb`.`t0`
### SET
###   @1=3
###   @2=2
###   @3='MODE_ON'

-vvの疑似SQL

### INSERT INTO `testdb`.`t0`
### SET
###   @1=3 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2=2 /* INT meta=0 nullable=1 is_null=0 */
###   @3='MODE_ON' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */

binlog_row_imageオプション

続いて、binlog_row_imageオプションです。バイナリログに記録する行の更新イメージを指定するオプションです。MySQL5.6とそれ以降からのオプションであり、オンラインで設定変更可能です。

以下の3つの値から設定できます。

full: 更新前と後のすべてのカラム情報を記録（デフォルト）
minimal: 更新するカラム情報とその行を識別できるカラムのみ記録
noblob: 更新対象ではないtext/blob型は除外した、更新前と後のすべてのカラム情報を記録

例として、疑似SQLからfullとminimalの違いを見てみましょう。

以下のようなテーブルを作成して、UPDATE t1 SET col1=10 WHERE id=1;というUPDATE文を実行してみます。

CREATE TABLE `t1` (
  `id` bigint(20) unsigned NOT NULL ,
  `col1` int(11) DEFAULT NULL,
  `col2` text,
  PRIMARY KEY `id` (`id`));

fullの疑似SQL

#180519 19:28:35 server id 1  end_log_pos 481 CRC32 0xbdf8d7cc  Update_rows: table id 233 flags: STMT_END_F
### UPDATE `test`.`t1`
### WHERE
###   @1=1
###   @2=1
###   @3='test_data'
### SET
###   @1=1
###   @2=10
###   @3='test_data'

minimalの疑似SQL

#180519 19:31:01 server id 1  end_log_pos 807 CRC32 0xcf6abb61  Update_rows: table id 233 flags: STMT_END_F
### UPDATE `test`.`t1`
### WHERE
###   @1=1
### SET
###   @2=10

fullは変更前と変更後のすべてのカラムのデータが記録されますが、minimalは更新するカラム（col1）とその行を特定するためのプライマリキーであるカラム（id）の情報のみを記録します。

minimalにすることのメリットとして、バイナリログのディスク量の削減、バイナリログのキャッシュ量（メモリ）の削減やスレーブ転送のネットワーク量の削減ができます。ただし、識別できるカラム（プライマリーキーやNOT NULLなユニークキー）が存在しない場合は、更新前の情報にすべてのカラムを記録するようになります。

基本はすべてのテーブルにプライマリキーがあるはずだと思うので、minimalに設定することでRBRの最適化ができます。

まとめ

MySQL5.7以降ではRBRがデフォルトの設定になっています。InnoDB ClusterにおいてもRBRが必須の条件となっているように、SBRでの運用は今後なくなっていくと思われます。RBRに関するオプションを理解して、障害や問題を対処できるようにしておきましょう。