まとめ
MySQLの古いバージョンとMariaDBのデフォルト(2015年11月現在)のbinlog formatが、状況が復元できない可能性のあるunsafeなstatement_based formatであるので、必要なら変更した方が良いという話。最新のMariaDBのデフォルトはmixedなので問題は少ない。
個人的に重要な気がしたのでメモ代わりに。
binlog formatの種類
binlog formatにはstatement-basedとrow-basedの2種類がある。簡単に言えば、statement-basedはクエリ単位で、row-basedは行変更単位で変更を保存する仕組みだと認識している。
運用上問題となるのは、statement-basedだと、復元時に異なった状態になる可能性がある、unsafeなクエリが稀に存在するということである。
詳細は以下のURLが詳しいが、どのような操作がunsafeかの例で言うと、
- 不定の対象に対してUPDATEをかける(例：UNIQUEなROWに対してORDER BYせずにLIMITしてUPDATE)
- 変更クエリでRAND()などのSTATEMENT
- INSERT ... ON DUPLICATE KEY UPDATEで複数のUNIQUEなKEYを持つ(PRIMARY含む)
などは「ありそう」な気がする。(最後は実際仕事であった)
row-basedではそのような問題はないが、logの量が多くなりがちなので、パフォーマンス上の問題が出る可能性がある。このため、問題のあるクエリだけをrow-basedで処理するMIXEDというモードも用意されている。
対処
binlog_format変数がGLOBALとSESSIONそれぞれで参照、設定できるので、STATEMENTをMIXEDなりROWなりに変更する。
確認
SHOW GLOBAL VARIABLES LIKE 'binlog_format';
一時的な変更
SET GLOBAL binlog_format = 'MIXED';
永続的にはmy.cnfに書けば良い。
どのMODEを選ぶべきか
結局のところは、unsafeなクエリがどれぐらいありそうか、パフォーマンスは、という2つの問題に帰着する。
正直なところデフォルトのSTATEMENTを選ぶ理由は無いように思える。パフォーマンスの問題でstatement_basedにしたいとしても、unsafeなクエリだけrow_basedにするMIXEDがある以上それを使えばいいからである。ただbinlogをそもそも重視してない(replicationしてない)のであれば面倒だし変えないという話は勿論あるか。
MyFleetGirlsでこの問題に気付いたときは既にSTATEMENTで運用していたので、変更を最小にするべくMIXEDにしたが、最新のMySQL(5.7.7以降らしいと聞いた)ではROWがデフォルトらしい。この2つのどちらかを選ぶかはパフォーマンスの話が大きい気がするが、一概にMIXEDの場合が早いとも限らないらしい(実際InnoDBだとReplication側が行Lockになってはやいよ、という話はあった)ので、ちゃんと調査した方が良さげ。
追記
最新のMariaDBのbinlog formatはmixedらしいので、最初から問題の出にくい仕様になっており、あんまり気にしなくてもよくなりそうな雰囲気はある。
https://mariadb.com/kb/en/binary-log-formats/