2013年9月30日月曜日

MySQL Cluster Casual Talksで話せなかった、俺がNDBCLUSTERを使わない理由

なぁんて、もったいぶった話じゃないんですけど。

あ、@kamipo さんアイスごちそうさまです。





NDBCLUSTERを使いたくても使えない(んだか使わないんだかよく判らなくなってきた)理由は、

  • 今MySQL Serverで動いているのを全て突っ込むには容量がつらい。
    • 更新主体のテーブルだけ、というのは障害点が増えるだけなのでパス。
    • Diskテーブルに落としたらオワコンなベンチマークも見られたし。
  • ウチではmroongaによる全文検索マンセー。
    • だったらやりようによって全文検索&クラスタリングのXtraDB Clusterやりたい。
  • 何でもいいから突っ込むというのはどうも美しくない。
    • InnoDBの方が性能が出るパターンなら(ほとんど、ですね)InnoDBで上手く冗長化したい。
    • あと、それじゃあ説得力不足でサーバー出してもらえない。
あたりです。

シングルサインオンって訳じゃないんですが、1回ユーザー登録するといろんなサービスでそのアカウント情報を使い回せるっていう仕組みがあるので、そこの認証部分はNDBCLUSTER向きだなっと思っていたりしますが、そういうとこに限ってやっぱり改修しにくかったりするんですよねー。。


スライドは半分くらいが余談で占められてるんですが、
 @Rkajiyama さんの話と め ち ゃ め ち ゃ カ ブ っ て る し
でもまあいいんです色々お話楽しかったです。

最後になりますが、ご登壇いただいた @Rkajiyama さん、@nippondanji さん、@tsakurada さん、@yyamasaki1 さん、ご来場いただいたみなさま、ハッシュタグまとめてくれた @76whizkidz さん、ブログ書いてくれたみなさま、手伝ってくれた会社のみなさま。

どうもありがとうございました。とても楽しかったです(俺が)
次はMyISAM Deep Talksですかね :) 楽しみにしています。

さて、今日はMyNA会だー。

2013年9月19日木曜日

pt-online-schema-changeのクエリーをレプリケーションしない方法

みんなのヒーロー pt-online-schema-change ですが、HandlerSocketと非常に相性が悪い。というかHandlerSocketとテーブルのメタデータをロックするDDL(つまりフツーのALTER TABLEでも)相性がすごく悪い。

去年もハマってましたね。季節ものなんでしょうか。まだ試す予定ってタイトルに書いてあった割に1年放置してましたはい。
⇒ HandlerSocketの不思議な動作(まだ試す予定)

HandlerSocketで読み書きしている環境でALTER TABLEをかけようとすると、

  1. ALTER TABLEはHandlerSocketのTable Closeを待つ
  2. 後続のクエリーはALTER TABLEのメタデータロックを待つ
  3. 何故かいつまでもTableをクローズせず使いまわすHandlerSocketがいる気配
  4. HandlerSocketの中にはテーブルをクローズして、ALTER TABLEのメタデータロック待ちに入るスレッドもいる
  5. もうだめぽ
という訳で、HandlerSocketを(readの時だけ)使っているウチの環境でpt-online-schema-changeをする時は、

  1. マスターにはHandlerSocketが来ていないのでsql_log_bin= OFFでpt-online-schema-change
  2. スレーブに来ているHandlerSocketの向き先を全てマスターに向ける
  3. スレーブのHSコネクションが全部切れたら、FLUSH TABLESしてからpt-online-schema-change
というステップでやっています。


pt-online-schema-changeでセッション変数を押し込むには--set-vars=".."で設定します。

$ pt-online-schema-change --execute --set-vars="sql_log_bin= OFF" --alter "MODIFY  order_col varchar(100) COLLATE utf8_bin NOT NULL, DROP KEY IX_ORDER_COL, Engine= InnoDB" h=localhost,D=dbname,t=tablename,u=root,p=xxxx,S=/var/lib/mysql/mysql.sock

これで一連の操作の手前に"SET SESSION sql_log_bin= OFF;"が叩き込まれるので、1つ目のステップが実行できます。

Percona-toolkit 2.2.1, 2.2.2のpt-online-schema-changeには--set-vars="SQL_LOG_BIN= 0"を渡すとInvalidだと怒られるバグがあるので、なんとなくsql_log_bin= OFFで設定するようにしてあります。
⇒ https://bugs.launchpad.net/percona-toolkit/+bug/1182856

これ素敵だよなぁ。。

2013年9月17日火曜日

MySQL 5.6.11以降のInnoDBテーブルでAUTO_INCREMENTの値を小さくできない件

なんか変だなーと思っていたんですがすっきりしました。

mysql56> CREATE TABLE t1 (num serial, val varchar(32)) Engine= InnoDB;
Query OK, 0 rows affected (0.05 sec)

mysql56> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `num` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `val` varchar(32) DEFAULT NULL,
  UNIQUE KEY `num` (`num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql56> INSERT INTO t1 VALUES (1, 'one'), (2, 'two');
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql56> SELECT * FROM t1;
+-----+------+
| num | val  |
+-----+------+
|   1 | one  |
|   2 | two  |
+-----+------+
2 rows in set (0.00 sec)

mysql56> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `num` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `val` varchar(32) DEFAULT NULL,
  UNIQUE KEY `num` (`num`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql56> ALTER TABLE t1 AUTO_INCREMENT= 10000;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql56> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `num` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `val` varchar(32) DEFAULT NULL,
  UNIQUE KEY `num` (`num`)
) ENGINE=InnoDB AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql56> ALTER TABLE t1 AUTO_INCREMENT= 10;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql56> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `num` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `val` varchar(32) DEFAULT NULL,
  UNIQUE KEY `num` (`num`)
) ENGINE=InnoDB AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

5.5のInnoDBならこれできるんです。5.6のMyISAMでもできます。

マニュアルの書き方を比較しても、5.6のInnoDBでだけAUTO_INCREMENTを小さくできない様には読めないのでなんかもやもやします。

5.5 ALTER TABLE: http://dev.mysql.com/doc/refman/5.5/en/alter-table.html
⇒MAX(auto_increment_column)より小さい値を与えた場合、MyISAMはMAX(auto_increment_column)+ 1に勝手に切り詰めて、InnoDBは値が変更されない。
⇒MAX(auto_increment_column)より大きい値の場合は言及なし。

5.6 ALTER TABLE: http://dev.mysql.com/doc/refman/5.6/en/alter-table.html
⇒MyISAMもInnoDBもMAX(auto_increment_column)より小さい値を与えた場合はMAX(auto_increment_column)+ 1に切り詰める。
⇒MAX(auto_increment_column)より大きい値の場合は言及なし。


と思ったらバグレポート見つけた。これだ。

MySQL Bug #69882: http://bugs.mysql.com/bug.php?id=69882


バグレポートのWorkaroundにはTRUNCATEしか紹介されていませんが、ALTER TABLE .. ALGORITHM= COPYを使えば小さくできました。ドキュメントの記載漏れなのか、バグとして扱われるのか微妙なとこですね。。

【2014/02/03 12:48】 結構前ですが、バグとしてVerifyされてます。5.6.16(2014/01/31)でFIXされています。


mysql56> ALTER TABLE t1 ALGORITHM= COPY, AUTO_INCREMENT= 10;
Query OK, 2 rows affected (0.11 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql56> SHOW CREATE TABLE t1\G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `num` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `val` varchar(32) DEFAULT NULL,
  UNIQUE KEY `num` (`num`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

これ見ても、InnoDB Online ALTER TABLEの時はできない、と言ってるようには見えないんですけどねー。。
http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-overview.html

2013年9月14日土曜日

MariaDB 10.0のMulti-source replicationを試す

MariaDB 10.0.2でインプリメントされたMulti-source replication(1スレーブに複数マスター構成)、MySQL 5.6に紛れて試していなかったんだけれど時間があったのでちょっとだけ試してみた。

リファレンス ⇒ https://mariadb.com/kb/en/multi-source-replication/

MariaDB KnowledgebaseはWEB遅い、マニュアル見づらい感。。



mariadb> SHOW ALL SLAVES STATUS\G
Empty set (0.00 sec)

mariadb> CHANGE MASTER 'mysql56' TO master_host= 'localhost', master_port= 64056, master_user= 'replicator', master_log_file= 'bin.000001', master_log_pos= 1;
Query OK, 0 rows affected (0.03 sec)

mariadb> SHOW SLAVE 'mysql56' STATUS\G
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: localhost
                  Master_User: replicator
                  Master_Port: 64056
                Connect_Retry: 60
              Master_Log_File: bin.000001
          Read_Master_Log_Pos: 4
               Relay_Log_File: relay-mysql56.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: bin.000001
             Slave_IO_Running: No
            Slave_SQL_Running: No
..
1 row in set (0.00 sec)

mariadb> CHANGE MASTER 'mysql55' TO master_host= 'localhost', master_port= 64055, master_user= 'replicator', master_log_file= 'bin.000001', master_log_pos= 1;
Query OK, 0 rows affected (0.02 sec)

mariadb> SHOW ALL SLAVES STATUS\G
*************************** 1. row ***************************
              Connection_name: mysql55
              Slave_SQL_State:
               Slave_IO_State:
                  Master_Host: localhost
                  Master_User: replicator
                  Master_Port: 64055
                Connect_Retry: 60
              Master_Log_File: bin.000001
          Read_Master_Log_Pos: 4
               Relay_Log_File: relay-mysql55.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: bin.000001
             Slave_IO_Running: No
            Slave_SQL_Running: No
..
*************************** 2. row ***************************
              Connection_name: mysql56
              Slave_SQL_State:
               Slave_IO_State:
                  Master_Host: localhost
                  Master_User: replicator
                  Master_Port: 64056
                Connect_Retry: 60
              Master_Log_File: bin.000001
          Read_Master_Log_Pos: 4
               Relay_Log_File: relay-mysql56.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: bin.000001
             Slave_IO_Running: No
            Slave_SQL_Running: No
..
2 rows in set (0.00 sec)

mariadb> SHOW SLAVE STATUS\G
Empty set (0.00 sec)

mariadb> SET SESSION default_master_connection= 'mysql55';
Query OK, 0 rows affected (0.01 sec)

mariadb> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: localhost
                  Master_User: replicator
                  Master_Port: 64055
                Connect_Retry: 60
              Master_Log_File: bin.000001
          Read_Master_Log_Pos: 107
               Relay_Log_File: relay-mysql55.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: bin.000001
             Slave_IO_Running: No
            Slave_SQL_Running: No
..

mariadb> START SLAVE;
ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO

こんな感じで設定。
取り敢えずハマったのは、
  • SHOW ALL SLAVESとか。
  • SET SESSION default_master_connectionしておくと、コネクション名を指定しないSTART SLAVEとかはそのコネクションあてになる。
  • connection name(mysql55とかmysql56とかしてるやつ)はクォートしないといけない。
  • START SLAVE 'mysql55'; もSTART ALL SLAVES; も何故か通らない(何が足りないのか調べてない。。)
とはいえ、まあ何か面白そうですよね!

以上、PHP Conference 2013のLT聞きながらでした。。


【2013/09/17 12:49】
START SLAVEできなかったのは、server-idを指定していなかったというまさかのオチでしたよ! orz

2013年9月10日火曜日

知られざるinnodb_flush_methodの値

innodb_flush_methodの値といえば、fdatasync(暗黙のデフォルト)かO_DIRECTくらいしか知らなかった訳だが、O_DSYNCやO_DIRECT_NO_FSYNC(5.6.7から)もあったり、Percona ServerにはALL_O_DIRECTなんてものがあったりする。
http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_flush_method
http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_flush_method
http://www.percona.com/doc/percona-server/5.1/scalability/innodb_io.html?id=percona-server:features:innodb_io_51&redirect=2#innodb_flush_method


が、実はlittlesyncとnosyncという値も設定できるっぽい。mysql-5.6.13/storage/innobase/srv/srv0start.cc には、

1665         } else if (0 == ut_strcmp(srv_file_flush_method_str, "fsync")) {
1666                 srv_unix_file_flush_method = SRV_UNIX_FSYNC;
1667
1668         } else if (0 == ut_strcmp(srv_file_flush_method_str, "O_DSYNC")) {
1669                 srv_unix_file_flush_method = SRV_UNIX_O_DSYNC;
1670
1671         } else if (0 == ut_strcmp(srv_file_flush_method_str, "O_DIRECT")) {
1672                 srv_unix_file_flush_method = SRV_UNIX_O_DIRECT;
1673
1674         } else if (0 == ut_strcmp(srv_file_flush_method_str, "O_DIRECT_NO_FSYNC")) {
1675                 srv_unix_file_flush_method = SRV_UNIX_O_DIRECT_NO_FSYNC;
1676
1677         } else if (0 == ut_strcmp(srv_file_flush_method_str, "littlesync")) {
1678                 srv_unix_file_flush_method = SRV_UNIX_LITTLESYNC;
1679
1680         } else if (0 == ut_strcmp(srv_file_flush_method_str, "nosync")) {
1681                 srv_unix_file_flush_method = SRV_UNIX_NOSYNC;
 しかもちゃんと

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_flush_method';
+---------------------+--------+
| Variable_name       | Value  |
+---------------------+--------+
| innodb_flush_method | nosync |
+---------------------+--------+
1 row in set (0.00 sec)

認識されてるし。。


こんな機能メンテしたくないからドキュメントからは外した、とInnoDB開発者の弁。
 ⇒http://lists.mysql.com/mysql/148920

かなり速そうだから、バックアップからの戻しならアリかもね。。


【2015/07/24 16:43】
今ドキュメント見たら載ってるなぁ。。

でもこっちはクローズされてないなぁ。。
MySQL Bugs: #26588: innodb_flush_method, nosync and littlesync undocumented

2013年9月6日金曜日

MySQLのView, Stored Procedureのセキュリティ設定、デフォルトはDEFINER

DEFINER= rootでSQL SECURITY DEFINERの話です。
論より実験。


d2user> SHOW GRANTS;
+------------------------------------------------+
| Grants for d2user@%                            |
+------------------------------------------------+
| GRANT USAGE ON *.* TO 'd2user'@'%'             |
| GRANT ALL PRIVILEGES ON `d2`.* TO 'd2user'@'%' |
+------------------------------------------------+
2 rows in set (0.00 sec)


d2データベースにだけ権限を持ったd2userがいます。
ここにrootで、おとなりd1データベースのテーブルをSELECTするViewを作ります。


root> CREATE VIEW d2.v1 AS SELECT * FROM d1.t1;
Query OK, 0 rows affected (0.01 sec)

root> SELECT * FROM d2.v1;
+-----+-------+
| num | val   |
+-----+-------+
|   1 | one   |
|   2 | two   |
|   3 | three |
+-----+-------+
3 rows in set (0.00 sec)


こんな感じで。
さてオチは読めると思いますがこのd2.v1はd2userで


d2user> SELECT * FROM d2.v1;
+-----+-------+
| num | val   |
+-----+-------+
|   1 | one   |
|   2 | two   |
|   3 | three |
+-----+-------+
3 rows in set (0.00 sec)


参照できます。
どんなロジックが働いているかというと、


d2user> SHOW CREATE VIEW d2.v1\G
*************************** 1. row ***************************
                View: v1
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `d1`.`t1`.`num` AS `num`,`d1`.`t1`.`val` AS `val` from `d1`.`t1`
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.00 sec)


さっきのCREATE VIEWで設定しなかったところが暗黙のデフォルトで補完されてますね。

ALGORITHMはViewを参照するときのベーステーブルからの取り出し方に関わるものなので今回は関係なく、DEFINER= root@localhost と SQL SECURITY DEFINERがキモです。

DEFINERはこのViewに紐付けられたユーザー属性で、暗黙のデフォルトはCURRENT_USERです。CREATE VIEWを叩いたのはroot@localhostだったのでその値が来ています。DEFINER= でCURRENT_USER以外の値を設定できるのはSuper_priv持ちのユーザーだけです。

SQL SECURITY DEFINERは「このビューを操作する時はDEFINERの権限を使ってViewの中身を処理する」という属性です。つまり、このビューに対するあらゆる操作はroot@localhostの権限で行われます。つまりが、

d2user> INSERT INTO v1 VALUES (4, 'four');
Query OK, 1 row affected (0.01 sec)

d2user> SELECT * FROM v1;
+-----+-------+
| num | val   |
+-----+-------+
|   1 | one   |
|   2 | two   |
|   3 | three |
|   4 | four  |
+-----+-------+
4 rows in set (0.00 sec)

d2user> DELETE FROM v1 WHERE num= 2;
Query OK, 1 row affected (0.02 sec)

d2user> SELECT * FROM v1;
+-----+-------+
| num | val   |
+-----+-------+
|   1 | one   |
|   3 | three |
|   4 | four  |
+-----+-------+
3 rows in set (0.01 sec)


なんでもできます。
これを防ぐにはDEFINERをテキトーなユーザー権限に変えるか、SQL SECURITYをINVOKERにするかのどちらかです。

DEFINERを書き換えるのはSuper_priv持ちのユーザーでないといけない、また、結局「操作したユーザーではないユーザー権限でベーステーブルにアクセスする」可能性が捨てきれないので、SQL SECURITY INVOKERにすることが多いんじゃないでしょうか。


root> ALTER SQL SECURITY INVOKER VIEW v1 AS SELECT * FROM d1.t1;
Query OK, 0 rows affected (0.00 sec)

d2user> SELECT * FROM v1;
ERROR 1356 (HY000): View 'd2.v1' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

Stored Procedure, Stored FunctiomもSQL SECURITY {DEFINER|INVOKER}を持っていて、同じような動き方をします。
RDSでは逆にSQL SECURITY DEFINERを上手く使って、mysql.slow_logテーブルを「rootだろうと直接操作させず、rdsadmin(でしたっけ?)の権限で操作する」ことでローテーションさせたりできますね。

ちなみにTRIGGERはDEFINER項目はありますがSQL SECURITY {DEFINER|INVOKER}は無く、常にDEFINER権限で操作されます。


root> CREATE TRIGGER t1_ins_trigger AFTER INSERT ON d2.t1 FOR EACH ROW INSERT INTO d1.t2 VALUES (new.num, new.val)//
Query OK, 0 rows affected (0.01 sec)

root> SHOW CREATE TRIGGER t1_ins_trigger\G
*************************** 1. row ***************************
               Trigger: t1_ins_trigger
              sql_mode: NO_ENGINE_SUBSTITUTION
SQL Original Statement: CREATE DEFINER=`root`@`localhost` TRIGGER t1_ins_trigger AFTER INSERT ON d2.t1 FOR EACH ROW INSERT INTO d1.t2 VALUES (new.num, new.val)
  character_set_client: utf8
  collation_connection: utf8_general_ci
    Database Collation: utf8_general_ci
1 row in set (0.00 sec)

d2user> INSERT INTO d2.t1 VALUES (10, 'ten');
Query OK, 1 row affected (0.01 sec)

root> SELECT * FROM d1.t2;
+-----+------+
| num | val  |
+-----+------+
|  10 | ten  |
+-----+------+
1 row in set (0.00 sec)


ちなみに、存在しないユーザーをDEFINER= で設定してSQL SECURITY DEFINERすると、誰からも操作できないViewが出来上がります。mysqldumpでビュー定義だけ引っ張り出してテスト環境にリストアした時にたまにやりますね。

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さん でした。ごちそうさまです。