レプリケーションを破壊するクエリを事前に検知したい

Question

レプリケーション対象にしているテーブルとしていないテーブルが混在している場合、更新系のクエリに対象にしていないテーブルを含めてしまうとエラーになってレプリケーションが崩れてしまいます。

どのテーブルをレプリケーションしてるかをちゃんと把握していればいいのですが、そういう特殊なクエリを発行することが稀なのでついうっかりやってしまいます。

上記のような問題を含め、レプリケーションを破壊してしまうようなクエリを検知する仕組みなどご存じないでしょうか。

Answer 1

どのような方法でクエリを発行しているのか分かりませんが、直接mysql系関数を呼び出していると仮定しますね。

$result = mysql_query($sql);

などとしている場合は、これをラッピングして、スレーブ側に存在しないテーブルを発行しようとしたら何らかの警告処理を追加するのが一番シンプルだと思います。

function _mysql_update($sql){
  $alertTables = array('no_replications1', 'no_replications2'); //レプリケーションしてないテーブルたち
  foreach($alertTables as $table){
    if(strpos($sql, $table)!==false){
      throw new Exception('このクエリは利用できません');
    }
  }
  return mysql_query($sql);
}

もちろん、これは実践的なコードではありません。

パフォーマンスよりリアルタイムの安全性を追求するなら、$alertTablesの内容もハードコーディングではなく、毎回もしくは何かしらのタイミングでスレーブとマスターのテーブル一覧を比較した差分をとり、それを元にするのが良いと思いますし、そもそもこのような値はどこか別にまとめて定義しておくべきです。
クエリ内のテーブル判定も正規表現等を使ってもっと厳密に行うべきですね。

ただ、レプリケーション指定していないテーブルに書き込みを行っても、通常はスレーブ側で送られてきたバイナリログを無視する設定になっていると思います。
今一度、mysqldの設定に

slave-skip-errors = 1146

がきちんと書かれているか確認されてはいかがでしょう。
1146はテーブルが無いというエラーコードで、これが指定されていたらこのエラーは無視します。