MySQL Statement-based-replicationでの安全な関数
今日は、最近導入が増えてきたMySQLのレプリケーション関連の記事を書いてみようと思います。
ご存知のようにMySQL5.1では以下の2種類+1のレプリケーションフォーマットを使用することが可能です。
・Statement-based-replication (SBR)
・Row-based-replication (RBR) ※5.1.5新機能
・Mixed ※5.1.8新機能
マスターからスレイブに対してどのようなフォーマットのバイナリログを送信するかの設定です。
SBRはSQLステートメントベースのイベントがログに書き込まれます。対してRBRは変更対象行自体をログに書き込んでスレイブに送り込む形式となります。
Non-Deterministic(実行するごとに異なる結果が出る）な関数はステートメントベースでは使用不能（Unsafe)であるというのが一般論だと思いますが、MySQL5.1では一部例外があります。Non-Deterministicなものについては全部RBRで対応すれば良いと思われるかもしれませんが、諸般の事情でSBRでやりたいケースはあると思います。
以下２つの関数について取り上げてみたいと思います。
・RAND()
・NOW()
RAND関数は、0以上1より小さい乱数を生成する関数です。実行結果はNon-Deterministicであり、実行ごとに結果が変化します。ステートメントベースでバイナリログにRAND()を書いてしまうと、スレーブにこのSQL文が送られリプレイされた時にマスターでの結果とは違う数字を生成してしまうと危惧される方もおられるのではないでしょうか。
実はMySQLではバイナリログ部分でRAND関数を扱うための特別なロジックを搭載しており、SBRでもRBRでもマスターの実行結果とスレイブの実行結果が同じになるように設計されています。また、バイナリログを使用したロールフォワードでも結果が同じになります。
ステートメントベースレプリケーションで、RAND()を使ってみます。
MASTER:[3306]> set binlog_format='STATEMENT';
Query OK, 0 rows affected (0.00 sec)
MASTER:[3306]> insert into test values(rand());
Query OK, 1 row affected (0.09 sec)
マスターでの結果
MASTER:[3306]> select * from test;
+------------------+
| a |
+------------------+
| 0.34047383287966 |
+------------------+
1 row in set (0.00 sec)
スレイブでの結果
SLAVE:[3307]> select * from test;
+------------------+
| a |
+------------------+
| 0.34047383287966 |
+------------------+
1 row in set (0.00 sec)
全く同じ結果ですね。結論を先に書いてしまうと、RAND()関数はSBRで安全です。
ソースの該当部分を見てみると理由が分かります。RAND()が呼び出された時に、シード値をスレッドクラスに保存しておくようになっています。
if (!thd->rand_used)
{
thd->rand_used= 1;
thd->rand_saved_seed1= thd->rand.seed1;
thd->rand_saved_seed2= thd->rand.seed2;
}
rand= &thd->rand;
バイナリログの書き込みタイミングで、先ほど保存しておいたシード値を本体SQLに先立って書き込んでいます。
if (thd->rand_used)
{
Rand_log_event e(thd,thd->rand_saved_seed1,thd->rand_saved_seed2);
if (e.write(file))
goto err;
}
バイナリログに２つのシード値が保存されているのが確認できます。
# at 951
#090319 21:48:45 server id 1 end_log_pos 986 Rand
SET @@RAND_SEED1=934874093, @@RAND_SEED2=782184184/*!*/;
# at 986
#090319 21:48:45 server id 1 end_log_pos 1080 Query thread_id=3 exec_time=0
SET TIMESTAMP=1237466925/*!*/;
insert into test values(rand())
/*!*/;
# at 1080
#090319 21:48:45 server id 1 end_log_pos 1107 Xid = 36
COMMIT/*!*/;
DELIMITER ;
# End of log file
スレイブがバイナリログ（リレーログ）からログイベントを読み込み、適用する時に保存されていたSEED1とSEED2がスレッド変数に再格納されます。スレイブはこのシードを使って乱数を生成するので結果が同じになります。
NOW()関数も同様となります。NOW()は現在のdate/timeを返す関数ですが、同じくNon-Deterministicなのでマスターとスレイブ間で差異が出そうな気がします。しかしRAND()同様に特別な処理が入っているのでSBRで安全な関数となっています。
ソースの該当箇所は以下となります。
/*
Timestamp on the master(for debugging and replication of
NOW()/TIMESTAMP). It is important for queries and LOAD DATA
INFILE. This is set at the event's creation time, except for Query
and Load (et al.) events where this is set at the query's
execution time, which guarantees good replication (otherwise, we
could have a query and its event with different timestamps).
*/
time_t when;
Log_event::Log_event(THD* thd_arg, uint16 flags_arg, bool using_trans)
:log_pos(0), temp_buf(0), exec_time(0), flags(flags_arg), thd(thd_arg)
{
server_id= thd->server_id;
when= thd->start_time;
cache_stmt= using_trans;
}
クエリとLoadステートメントの場合は、ステートメントの実行時のタイムスタンプが入ります。
ここで保存されたタイムスタンプはバイナリログにSET TIMESTAMP=xxxxxxという形で書き込まれます。
SET TIMESTAMP=1237087230/*!*/;
SET @@session.time_zone='SYSTEM'/*!*/;
insert into test values(now())
以下のSQLで実験してみましょう。(マスターとスレイブでNOW()の実行タイミングに差が出るように、スレイブのIO_THREADを一時停止しました）
MASTER:[3306]> set binlog_format='STATEMENT';
Query OK, 0 rows affected (0.00 sec)
MASTER:[3306]> insert into test values(now());
Query OK, 1 row affected (0.08 sec)
マスター結果
MASTER:[3306]> select * from test;
+---------------------+
| a |
+---------------------+
| 2009-03-19 22:31:14 |
+---------------------+
1 row in set (0.00 sec)
スレイブ結果
SLAVE:[3307]> select * from test;
+---------------------+
| a |
+---------------------+
| 2009-03-19 22:31:14 |
+---------------------+
1 row in set (0.00 sec)
同じ結果がテーブルに格納されました。
[注意点]
SBRでは、RAND()やNOW()と違って以下の関数は安全ではないので注意が必要です。
LOAD_FILE()
UUID()
UUID_SHORT()
SYSDATE()
FOUND_ROWS()
ROW_COUNT()
USER()
CURRENT_USER()
これらを処理するための特別なロジックが実装されていないためですが、
レプリケーションフォーマットをMIXEDに設定することで、いくつかの関数は安全に実行することができるようになります。MIXEDモードではこれらの関数を実行するときにレプリケーションフォーマット内部的にRBRへ変更します。(MySQL5.1.8からの機能）
UUID()
UUID_SHORT()
SYSDATE() 注1
FOUND_ROWS() 注2
ROW_COUNT() 注2
USER() 注2
CURRENT_USER() 注2
注1: NOW()のエイリアスとするオプションも使用可能
注2: MySQL 5.1.24から
関数以外にも、例えばSELECTをORDER BY句なしでLIMITと一緒に使った場合などにもMIXEDに指定していれば自動でRBRに変換してくれます。
噂のUUID()で実験してみましょう。
MASTER:[3306]> set binlog_format='MIXED';
Query OK, 0 rows affected (0.00 sec)
MASTER:[3306]> insert into uuidtest values(uuid());
Query OK, 1 row affected (0.09 sec)
マスター結果
MASTER:[3306]> select * from uuidtest;
+--------------------------------------+
| a |
+--------------------------------------+
| ab19b40b-1457-11de-8726-c70ad9252c6d |
+--------------------------------------+
1 row in set (0.00 sec)
スレイブ結果
SLAVE:[3307]> select * from uuidtest;
+--------------------------------------+
| a |
+--------------------------------------+
| ab19b40b-1457-11de-8726-c70ad9252c6d |
+--------------------------------------+
1 row in set (0.02 sec)
結果が同じになりました。
ついでなので、変換する部分のソースをチェックしてみます。
例えばUUID()は、以下のように作成時にSBRではUnSafeであるとマークしています。
Item*
Create_func_uuid::create(THD *thd)
{
thd->lex->set_stmt_unsafe();
thd->lex->safe_to_cache_query= 0;
return new (thd->mem_root) Item_func_uuid();
}
UnSafeであるとマークされたステートメントは、後でログフォーマットチェックロジックの中で、
MIXED設定の場合にRBRに変換されます。
if (thd->lex->is_stmt_unsafe() ||
(flags_all_set & HA_BINLOG_STMT_CAPABLE) == 0)
{
thd->set_current_stmt_binlog_row_based_if_mixed();
}
また、5.1.20からは「安全ではない」関数が使われるシチュエーションが発生した場合
（BINLOG_FORMAT='STATEMENT'とされているなど）、ログに警告が表示されるようになりました。
090319 16:35:35 [Warning] Statement is not safe to log in statement format. Stat
ement: insert into uuidtest values(uuid())
[オマケ]
MySQLのバージョンによる、binlog_formatのデフォルトは以下です。
5.1.5 - 5.1.7 STATEMENT
5.1.8 - 5.1.11 STATEMENT
5.1.12 - 5.1.28 MIXED
5.1.29 - STATEMENT