MySQLのバイナリログについて調べた
何となくでMySQLを使っているところがあるので、それなりに機能を理解しながら使っていきたいお気持ち。そんな中で最近バイナリログについて調べていたのでその内容をメモしておく。
バイナリログについて
バイナリログには、テーブルやデータの更新操作が格納される。（SELECTやSHOWのような読み込み操作は格納されず、それらを含めた全ての操作が見たい場合は一般クエリログを使用する。）
バイナリログへの書き込みは、SQL文の実行が完了し、トランザクションをCOMMITしたときに行われる。
バイナリログはデフォルトでは生成されないので、設定ファイル（my.cnfなど）にlog-binやserver-idを追加する必要がある。
バイナリログの見方
mysqlbinlogコマンドでファイルを指定すれば見れる。
ちなみにこのコマンドを使ったときにこんなことがあった。
my.cnfの[client]セクションに書いてあるのが引っかかるのであれば、loose-default-character-set=.. にすると対応してないやつだけ読み飛ばすというワザがあります
— yoku0825 (@yoku0825) 2017年11月6日
最初は--no-defaultsで回避していたけど、looseプリフィックスというのがあることを教えてもらった。こっちで回避するのが正しいっぽい。
Twitterで教えてもらった1時間後くらいにブログにもまとめていただいて、ホント神か〜って感じだ。（ありがとうございます） yoku0825.blogspot.jp
バイナリログ形式について
以下の3種類がある。MySQLのバージョンによってデフォルト値が異なり、例えば5.7の場合はROWがデフォルト。binlog_formatで設定することができる。
STATEMENT
実行されたSQL文をそのまま記録する。
例えば、以下のようなテーブル生成とデータ追加をする。
mysql> create table t_test_statement (id serial primary key, comment varchar(255), created_at datetime default current_timestamp); Query OK, 0 rows affected (0.23 sec) mysql> insert into t_test_statement (comment) values (sysdate()); Query OK, 1 row affected, 1 warning (0.04 sec)
すると、ログには以下のように記録される。
/*** 省略 ***/ SET TIMESTAMP=1510388348/*!*/; create table t_test_statement (id serial primary key, comment varchar(255), created_at datetime default current_timestamp) /*!*/; /*** 省略 ***/ BEGIN /*!*/; # at 761 # at 793 #171111 17:19:16 server id 1 end_log_pos 793 CRC32 0x649aa09c Intvar SET INSERT_ID=1/*!*/; #171111 17:19:16 server id 1 end_log_pos 932 CRC32 0xc1204652 Query thread_id=6 exec_time=0 error_code=0 SET TIMESTAMP=1510388356/*!*/; insert into t_test_statement (comment) values (sysdate()) /*!*/; # at 932 #171111 17:19:16 server id 1 end_log_pos 963 CRC32 0x9708ccad Xid = 117 COMMIT/*!*/; /*** 省略 ***/
このバイナリログ形式は、ログのデータ量が少ないメリットがあるが、非決定的なSQL文の場合にレプリケーションがうまくいかないデメリットがある。
先ほどINSERTしたときに非決定的な関数sysdate()が含まれていたので以下のような警告が出る。
mysql> show warnings; +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1592 | Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave. | +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
ROW
実行されたSQL文によるデータの変化を記録する。
例えば、以下のようなテーブル生成とデータ追加をする。
mysql> create table t_test_row (id serial primary key, comment varchar(255), created_at datetime default current_timestamp); Query OK, 0 rows affected (0.22 sec) mysql> insert into t_test_row (comment) values (sysdate()); Query OK, 1 row affected (0.05 sec)
すると、ログには以下のように記録される。
/*** 省略 ***/ SET TIMESTAMP=1510389361/*!*/; create table t_test_row (id serial primary key, comment varchar(255), created_at datetime default current_timestamp) /*** 省略 ***/ BEGIN /*!*/; # at 748 #171111 17:36:09 server id 1 end_log_pos 806 CRC32 0xe78091c3 Table_map: `test`.`t_test_row` mapped to number 229 # at 806 #171111 17:36:09 server id 1 end_log_pos 876 CRC32 0x44ba0200 Write_rows: table id 229 flags: STMT_END_F BINLOG ' ebYGWhMBAAAAOgAAACYDAAAAAOUAAAAAAAEABHRlc3QACnRfdGVzdF9yb3cAAwgPEgP8AwAGw5GA 5w== ebYGWh4BAAAARgAAAGwDAAAAAOUAAAAAAAEAAgAD//gBAAAAAAAAABMAMjAxNy0xMS0xMSAxNzoz NjowOZmeFxkJAAK6RA== '/*!*/; # at 876 #171111 17:36:09 server id 1 end_log_pos 907 CRC32 0xeffa076f Xid = 132 COMMIT/*!*/; /*** 省略 ***/
これだとよく分からないので、--base64-output=decode-rowsと-vを使っていい感じにする。
/*** 省略 ***/ BEGIN /*!*/; # at 748 #171111 17:36:09 server id 1 end_log_pos 806 CRC32 0xe78091c3 Table_map: `test`.`t_test_row` mapped to number 229 # at 806 #171111 17:36:09 server id 1 end_log_pos 876 CRC32 0x44ba0200 Write_rows: table id 229 flags: STMT_END_F ### INSERT INTO `test`.`t_test_row` ### SET ### @1=1 ### @2='2017-11-11 17:36:09' ### @3='2017-11-11 17:36:09' # at 876 #171111 17:36:09 server id 1 end_log_pos 907 CRC32 0xeffa076f Xid = 132 COMMIT/*!*/; /*** 省略 ***/
このバイナリログ形式は、STATEMENTと比べるとデータ量が増えるデメリットがあるが、非決定的なSQL文を考慮する必要がなくなるメリットがある。レプリケーションも問題なくできる。
MIXED
STATEMENTとROWを組み合わせたやつ。基本的にはSTATEMENTだが、特定の条件下（非決定的な関数が含まれる場合）ではROWとなる。
例えば、以下のようなテーブル生成とデータ追加×2をする。
mysql> create table t_test_mixed (id serial primary key, comment varchar(255), created_at datetime default current_timestamp); Query OK, 0 rows affected (0.25 sec) mysql> insert into t_test_mixed (comment) values ('\U+1F363'); Query OK, 1 row affected (0.05 sec) mysql> insert into t_test_mixed (comment) values (sysdate()); Query OK, 1 row affected (0.06 sec)
すると、ログには以下のように記録される。
/*** 省略 ***/ SET TIMESTAMP=1510391232/*!*/; create table t_test_mixed (id serial primary key, comment varchar(255), created_at datetime default current_timestamp) /*** 省略 ***/ BEGIN /*!*/; # at 2110 # at 2142 #171111 18:07:15 server id 1 end_log_pos 2142 CRC32 0x0379cb09 Intvar SET INSERT_ID=1/*!*/; #171111 18:07:15 server id 1 end_log_pos 2274 CRC32 0x232cabf5 Query thread_id=8 exec_time=0 error_code=0 SET TIMESTAMP=1510391235/*!*/; insert into t_test_mixed (comment) values ('🍣') /*!*/; # at 2274 #171111 18:07:15 server id 1 end_log_pos 2305 CRC32 0x3c15e8c3 Xid = 148 COMMIT/*!*/; /*** 省略 ***/ BEGIN /*!*/; # at 2450 #171111 18:07:23 server id 1 end_log_pos 2510 CRC32 0x358d0378 Table_map: `test`.`t_test_mixed` mapped to number 231 # at 2510 #171111 18:07:23 server id 1 end_log_pos 2580 CRC32 0x48df2b4f Write_rows: table id 231 flags: STMT_END_F BINLOG ' y70GWhMBAAAAPAAAAM4JAAAAAOcAAAAAAAEABHRlc3QADHRfdGVzdF9taXhlZAADCA8SA/wDAAZ4 A401 y70GWh4BAAAARgAAABQKAAAAAOcAAAAAAAEAAgAD//gCAAAAAAAAABMAMjAxNy0xMS0xMSAxODow NzoyM5meFyHXTyvfSA== '/*!*/; # at 2580 #171111 18:07:23 server id 1 end_log_pos 2611 CRC32 0xc54fd200 Xid = 149 COMMIT/*!*/; /*** 省略 ***/
このバイナリログ形式は、STATEMENTとROWのいいとこ取りなのでデメリットはない(?)
まとめ
目的にあった設定をすべきだろうが、個人的な意見としては、MIXEDを選んでおくのが無難なのかなと思った。
参考資料
