Help us understand the problem. What is going on with this article?

MySQL&MariaDBのbinlog formatの話

More than 5 years have passed since last update.

まとめ

MySQLの古いバージョンとMariaDBのデフォルト(2015年11月現在)のbinlog formatが、状況が復元できない可能性のあるunsafeなstatement_based formatであるので、必要なら変更した方が良いという話。

個人的に重要な気がしたのでメモ代わりに。

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含む)

などは「ありそう」な気がする。(最後は実際仕事であった)

https://dev.mysql.com/doc/refman/5.6/ja/replication-sbr-rbr.html

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になってはやいよ、という話はあった)ので、ちゃんと調査した方が良さげ。

ponkotuy
ScalaとかAWSとか好きな社畜です。でもゲームとか鉄道とかはもっと好きです
https://twitter.com/ponkotuy
m3dev
インターネット、最新IT技術を活用し日本・世界の医療を改善することを目指します
https://m3.recruitment.jp/engineer/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away