2013年9月5日木曜日

--replicate-*-dbが判定するのは原則カレントデータベース

--replicate-do-db, --replicate-ignore-dbの判定ロジックについて。

公式はこちら。
http://dev.mysql.com/doc/refman/5.6/en/replication-rules-db-options.html

なんだけど、--replication-*-dbで指定しているスキーマなのにレプリケーションされない! という話はわりとあるあるなので書いておく。

  • STATEMENTモード(MIXEDでも非決定性の関数とかがなければこっち)でロギングされている場合は、default database(SELECT DATABASE()で出てくるやつ)
  • ROWモードの場合は、実際に影響を受けるデータベース
が、--replication-*-dbと比較判定される。

テスト。5.5がマスターで5.6がスレーブ。

mysql56> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: localhost
                  Master_User: replicator
                  Master_Port: 64055
                Connect_Retry: 60
              Master_Log_File: bin.000002
          Read_Master_Log_Pos: 1986
               Relay_Log_File: relay.000010
                Relay_Log_Pos: 1878
        Relay_Master_Log_File: bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: d1,d2
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:..

スレーブではd1, d2スキーマだけ--replicate-do-dbに指定。


マスターのbinlog_format= STATEMENTの場合。

$ mysql55 -e "SELECT @@global.binlog_format"
+------------------------+
| @@global.binlog_format |
+------------------------+
| STATEMENT              |
+------------------------+

$ mysql55 -e "INSERT INTO d3.t1 (val) VALUES (DATABASE())"
$ mysql55 -e "INSERT INTO d3.t1 (val) VALUES (DATABASE())" d1
$ mysql55 -e "INSERT INTO d3.t1 (val) VALUES (DATABASE())" d2
$ mysql55 -e "INSERT INTO d3.t1 (val) VALUES (DATABASE())" d3
$ mysql55 -e "INSERT INTO d3.t1 (val) VALUES (DATABASE())" information_schema

カレントデータベースを変えながら5行INSERT。


mysql55> SELECT * FROM d1.t1;
+---------------------+--------------------+
| ts                  | val                |
+---------------------+--------------------+
| 2013-09-05 11:52:21 | NULL               |
| 2013-09-05 11:52:25 | d1                 |
| 2013-09-05 11:52:26 | d2                 |
| 2013-09-05 11:52:27 | d3                 |
| 2013-09-05 11:52:31 | information_schema |
+---------------------+--------------------+
5 rows in set (0.05 sec)

マスターでは当然こうなる。


mysql56> SELECT * FROM d1.t1;
+---------------------+------+
| ts                  | val  |
+---------------------+------+
| 2013-09-05 11:52:25 | d1   |
| 2013-09-05 11:52:26 | d2   |
+---------------------+------+
2 rows in set (0.00 sec)

スレーブではこうなる。
d3, information_schemaはd1でもd2でもないのは自明として、NULLもd1でもd2でもない、というのがよくあるハマりどころ。シェルスクリプトからmysqlコマンドラインクライアント呼んでるときとか、MySQL Workbenchで接続プロパティを作る時にDefault Schemaを空っぽのままにしてるときとか。GUI系はこれ危うい気がする。

あとついでにこの設定はSQLスレッドのものでI/Oスレッドには関係ないので、

$ mysqlbinlog /usr/mysql/5.6.13/data/relay.000010
..
# at 2707
#130905 11:52:21 server id 1055  end_log_pos 2917       Query   thread_id=13    exec_time=0     error_code=0
SET TIMESTAMP=1378349541/*!*/;
INSERT INTO d1.t1 (val) VALUES (DATABASE())
/*!*/;
# at 2809
#130905 11:52:21 server id 1055  end_log_pos 2944       Xid = 87
COMMIT/*!*/;
# at 2836
#130905 11:52:25 server id 1055  end_log_pos 3010       Query   thread_id=14    exec_time=0     error_code=0
SET TIMESTAMP=1378349545/*!*/;
BEGIN
/*!*/;
# at 2902
#130905 11:52:25 server id 1055  end_log_pos 3114       Query   thread_id=14    exec_time=0     error_code=0
use `d1`/*!*/;
SET TIMESTAMP=1378349545/*!*/;
INSERT INTO d1.t1 (val) VALUES (DATABASE())
/*!*/;
# at 3006
#130905 11:52:25 server id 1055  end_log_pos 3141       Xid = 90
COMMIT/*!*/;
# at 3033
#130905 11:52:26 server id 1055  end_log_pos 3207       Query   thread_id=15    exec_time=0     error_code=0
SET TIMESTAMP=1378349546/*!*/;
BEGIN
/*!*/;
# at 3099
#130905 11:52:26 server id 1055  end_log_pos 3311       Query   thread_id=15    exec_time=0     error_code=0
use `d2`/*!*/;
SET TIMESTAMP=1378349546/*!*/;
INSERT INTO d1.t1 (val) VALUES (DATABASE())
/*!*/;
# at 3203
#130905 11:52:26 server id 1055  end_log_pos 3338       Xid = 93
COMMIT/*!*/;
# at 3230
#130905 11:52:27 server id 1055  end_log_pos 3404       Query   thread_id=16    exec_time=0     error_code=0
SET TIMESTAMP=1378349547/*!*/;
BEGIN
/*!*/;
# at 3296
#130905 11:52:27 server id 1055  end_log_pos 3508       Query   thread_id=16    exec_time=0     error_code=0
use `d3`/*!*/;
SET TIMESTAMP=1378349547/*!*/;
INSERT INTO d1.t1 (val) VALUES (DATABASE())
/*!*/;
# at 3400
#130905 11:52:27 server id 1055  end_log_pos 3535       Xid = 96
COMMIT/*!*/;
# at 3427
#130905 11:52:31 server id 1055  end_log_pos 3617       Query   thread_id=17    exec_time=0     error_code=0
SET TIMESTAMP=1378349551/*!*/;
BEGIN
/*!*/;
# at 3509
#130905 11:52:31 server id 1055  end_log_pos 3737       Query   thread_id=17    exec_time=0     error_code=0
use `information_schema`/*!*/;
SET TIMESTAMP=1378349551/*!*/;
INSERT INTO d1.t1 (val) VALUES (DATABASE())
/*!*/;
# at 3629
#130905 11:52:31 server id 1055  end_log_pos 3764       Xid = 99
COMMIT/*!*/;

リレーログまでは影響を受けずにちゃんと来ている。


逆のパターンとして、

$ mysql55 -e "INSERT INTO d3.t1 (val) VALUES (DATABASE())"
$ mysql55 -e "INSERT INTO d3.t1 (val) VALUES (DATABASE())" d1
$ mysql55 -e "INSERT INTO d3.t1 (val) VALUES (DATABASE())" d2
$ mysql55 -e "INSERT INTO d3.t1 (val) VALUES (DATABASE())" d3
$ mysql55 -e "INSERT INTO d3.t1 (val) VALUES (DATABASE())" information_schema

mysql55> SELECT * FROM d3.t1;
+---------------------+--------------------+
| ts                  | val                |
+---------------------+--------------------+
| 2013-09-05 12:00:18 | NULL               |
| 2013-09-05 12:00:20 | d1                 |
| 2013-09-05 12:00:21 | d2                 |
| 2013-09-05 12:00:23 | d3                 |
| 2013-09-05 12:00:28 | information_schema |
+---------------------+--------------------+
5 rows in set (0.01 sec)

mysql56> SELECT * FROM d3.t1;
+---------------------+------+
| ts                  | val  |
+---------------------+------+
| 2013-09-05 12:00:20 | d1   |
| 2013-09-05 12:00:21 | d2   |
+---------------------+------+
2 rows in set (0.00 sec)

--replicate-do-dbにリストされていなくても、カレントデータベースがマッチすればレプリケートされる。


binlog_format=ROWにすると、

$ mysql55 -e "SELECT @@global.binlog_format"
+------------------------+
| @@global.binlog_format |
+------------------------+
| ROW                    |
+------------------------+

$ mysql55 -e "INSERT INTO d2.t1 (val) VALUES (DATABASE())"
$ mysql55 -e "INSERT INTO d2.t1 (val) VALUES (DATABASE())" d1
$ mysql55 -e "INSERT INTO d2.t1 (val) VALUES (DATABASE())" d2
$ mysql55 -e "INSERT INTO d2.t1 (val) VALUES (DATABASE())" d3
$ mysql55 -e "INSERT INTO d2.t1 (val) VALUES (DATABASE())" information_schema

mysql55> SELECT * FROM d2.t1;
+---------------------+--------------------+
| ts                  | val                |
+---------------------+--------------------+
| 2013-09-05 12:07:16 | NULL               |
| 2013-09-05 12:07:18 | d1                 |
| 2013-09-05 12:07:19 | d2                 |
| 2013-09-05 12:07:20 | d3                 |
| 2013-09-05 12:07:24 | information_schema |
+---------------------+--------------------+
5 rows in set (0.01 sec)

mysql56> SELECT * FROM d2.t1;
+---------------------+--------------------+
| ts                  | val                |
+---------------------+--------------------+
| 2013-09-05 12:07:16 | NULL               |
| 2013-09-05 12:07:18 | d1                 |
| 2013-09-05 12:07:19 | d2                 |
| 2013-09-05 12:07:20 | d3                 |
| 2013-09-05 12:07:24 | information_schema |
+---------------------+--------------------+
5 rows in set (0.00 sec)


この通り。


本日のネタ提供は @nekogeruge_987さん でした。ごちそうさまです。

0 件のコメント :

コメントを投稿