MySQL道普請便り

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

この記事を読むのに必要な時間:およそ 3 分

この連載でも何度か紹介していますが,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からfullminimalの違いを見てみましょう。

以下のようなテーブルを作成して,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に関するオプションを理解して,障害や問題を対処できるようにしておきましょう。

著者プロフィール

北川健太郎(きたがわけんたろう)

LINE株式会社所属のデータベースエンジニア。担当はMySQLとOracle Database。好きなMySQLの機能はレプリケーションで,好きなOracleDatabaseの機能はログオントリガー。

Twitter:@keny_lala

コメント

コメントの記入