hironomiu's Blog

こそっと書いていきます。twitter_id:hironomiu

mysql レプリケーション周り

mysql レプリケーション周り

とある人にcreate temporary tableでテーブルを作成した場合、slaveにレプリケーションってされるの?と素朴な質問をされ「?」って思ったのでちょっと調べてみた。途中からはtemporary tableとかあんま関係なくなってきてるけど気にしない。

master側で以下のDDLDMLを実行した

slave側のignore-do-db設定でtest2を許可している状態からaaa、bbb(temporary table)を作成しデータをINSERTした。(autocommit=on)

mysql> use test2;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> create table aaa(id int);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into aaa values (10);
Query OK, 1 row affected (0.01 sec)

mysql> create temporary table bbb (id int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into bbb values (50);
Query OK, 1 row affected (0.01 sec)

mysql> insert into aaa values (100);
Query OK, 1 row affected (0.00 sec)

mysql> select * from aaa;
 +------+
 | id   |
 +------+
 |   10 |
 |  100 |
 +------+
2 rows in set (0.00 sec)

mysql> select * from bbb;
 +------+
 | id   |
 +------+
 |   50 |
 +------+
1 row in set (0.00 sec)

上のオペレーションが終わったところでslaveを確認すると

まぁ当然bbb(temporary table)は存在してない。

mysql> show tables;
 +-----------------+
 | Tables_in_test2 |
 +-----------------+
 | aaa             |
 +-----------------+
1 row in set (0.00 sec)

mysql> select * from aaa;
 +------+
 | id   |
 +------+
 |   10 |
 |  100 |
 +------+
2 rows in set (0.00 sec)

slaveのrelayログを確認

mysqlbinlogで整形して中身を見ると、bbbのSQLは伝播していた(49行目、59行目)。master側で出力されたbinaryログをioスレッドはちゃんとピックアップするけど、込み入った設定をしない場合、単純にsqlスレッドが実行しないことでレプリケーションされていな模様。

      1 /*!40019 SET @@session.max_insert_delayed_threads=0*/;
      2 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
      3 DELIMITER /*!*/;
      4 # at 4
      5 #120316  6:58:38 server id 5112  end_log_pos 114        Start: binlog v 4, server v 5.6.4-m7-log created 120316  6:58:38
      6 BINLOG '
      7 DmZiTw/4EwAAbgAAAHIAAABAAAQANS42LjQtbTctbG9nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
      8 AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVgAEGggAAAAICAgCAAAAAF85430=
      9 '/*!*/;
     10 # at 114
     11 #700101  9:00:00 server id 1  end_log_pos 0     Rotate to mysql-bin.000014  pos: 4
     12 # at 157
     13 #120316  6:58:24 server id 1  end_log_pos 114   Start: binlog v 4, server v 5.6.4-m7-log created 120316  6:58:24 at startup
     14 ROLLBACK/*!*/;
     15 BINLOG '
     16 AGZiTw8BAAAAbgAAAHIAAAAAAAQANS42LjQtbTctbG9nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
     17 AAAAAAAAAAAAAAAAAAAAZmJPEzgNAAgAEgAEBAQEEgAAVgAEGggAAAAICAgCAAAAAO/C2kU=
     18 '/*!*/;
     19 # at 267
     20 #120316  6:59:26 server id 1  end_log_pos 210   Query   thread_id=2     exec_time=0     error_code=0
     21 use test2/*!*/;
     22 SET TIMESTAMP=1331848766/*!*/;
     23 SET @@session.pseudo_thread_id=2/*!*/;
     24 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
     25 SET @@session.sql_mode=0/*!*/;
     26 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
     27 /*!\C utf8 *//*!*/;
     28 SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
     29 SET @@session.lc_time_names=0/*!*/;
     30 SET @@session.collation_database=DEFAULT/*!*/;
     31 create table aaa(id int)
     32 /*!*/;
     33 # at 363
     34 #120316  6:59:56 server id 1  end_log_pos 287   Query   thread_id=2     exec_time=0     error_code=0
     35 SET TIMESTAMP=1331848796/*!*/;
     36 BEGIN
     37 /*!*/;
     38 # at 440
     39 #120316  6:59:56 server id 1  end_log_pos 386   Query   thread_id=2     exec_time=0     error_code=0
     40 SET TIMESTAMP=1331848796/*!*/;
     41 insert into aaa values (10)
     42 /*!*/;
     43 # at 539
     44 #120316  6:59:56 server id 1  end_log_pos 413   Xid = 20
     45 COMMIT/*!*/;
     46 # at 566
     47 #120316  7:00:18 server id 1  end_log_pos 520   Query   thread_id=2     exec_time=0     error_code=0
     48 SET TIMESTAMP=1331848818/*!*/;
     49 create temporary table bbb (id int)
     50 /*!*/;
     51 # at 673
     52 #120316  7:00:32 server id 1  end_log_pos 597   Query   thread_id=2     exec_time=0     error_code=0
     53 SET TIMESTAMP=1331848832/*!*/;
     54 BEGIN
     55 /*!*/;
     56 # at 750
     57 #120316  7:00:32 server id 1  end_log_pos 696   Query   thread_id=2     exec_time=0     error_code=0
     58 SET TIMESTAMP=1331848832/*!*/;
     59 insert into bbb values (50)
     60 /*!*/;
     61 # at 849
     62 #120316  7:00:32 server id 1  end_log_pos 774   Query   thread_id=2     exec_time=0     error_code=0
     63 SET TIMESTAMP=1331848832/*!*/;
     64 COMMIT
     65 /*!*/;
     66 # at 927
     67 #120316  7:00:43 server id 1  end_log_pos 851   Query   thread_id=2     exec_time=0     error_code=0
     68 SET TIMESTAMP=1331848843/*!*/;
     69 BEGIN
     70 /*!*/;
     71 # at 1004
     72 #120316  7:00:43 server id 1  end_log_pos 951   Query   thread_id=2     exec_time=0     error_code=0
     73 SET TIMESTAMP=1331848843/*!*/;
     74 insert into aaa values (100)
     75 /*!*/;
     76 # at 1104
     77 #120316  7:00:43 server id 1  end_log_pos 978   Xid = 25
     78 COMMIT/*!*/;
     79 DELIMITER ;
     80 # End of log file
     81 ROLLBACK /* added by mysqlbinlog */;
     82 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

binlog-ignore

そうは言っても不要なDDLやDMLでネットワーク転送が発生するのって何か嫌だなぁって思って調べて見ると、DB単位しか無理だけどbinlog-ignoreでmaster側でbinlogそのものを吐かない方法がある。

mysql> create table ccc (id int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into ccc values (111);
Query OK, 1 row affected (0.00 sec)

mysql> select * from ccc;
 +------+
 | id   |
 +------+
 |  111 |
 +------+
1 row in set (0.00 sec)

binlogにも上のDDL、DMLは出力されていないし、当然slaveのrelayログにも無いし、test2上にテーブルもデータもない。

replicate-ignore-table

slave側でreplicate-ignore-tableでtest2.cccを設定し再起動して下記SQLをmaster側で実施する。

mysql> create table ddd (id int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into ddd values(100);
Query OK, 1 row affected (0.00 sec)

mysql> create table ccc (id int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into ccc values ( 200);
Query OK, 1 row affected (0.00 sec)

mysql> insert into ddd values (500);
Query OK, 1 row affected (0.00 sec)

mysql> show tables;
 +-----------------+
 | Tables_in_test2 |
 +-----------------+
 | aaa             |
 | ccc             |
 | ddd             |
 +-----------------+
3 rows in set (0.00 sec)

mysql> select * from ddd;
 +------+
 | id   |
 +------+
 |  100 |
 |  500 |
 +------+
2 rows in set (0.00 sec)

mysql> select * from ccc;
 +------+
 | id   |
 +------+
 |  200 |
 +------+
1 row in set (0.00 sec)

上を実施したところでslaveも確認

mysql> show tables;
 +-----------------+
 | Tables_in_test2 |
 +-----------------+
 | aaa             |
 | ddd             |
 +-----------------+
2 rows in set (0.00 sec)

mysql> select * from ddd;
 +------+
 | id   |
 +------+
 |  100 |
 |  500 |
 +------+
2 rows in set (0.00 sec)

ちゃんとcccがignoreされている。肝心のslaveのrelayログの中身はというと?

      1 /*!40019 SET @@session.max_insert_delayed_threads=0*/;
      2 /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
      3 DELIMITER /*!*/;
      4 # at 4
      5 #120316  7:32:14 server id 5112  end_log_pos 114        Start: binlog v 4, server v 5.6.4-m7-log created 120316  7:32:14
      6 BINLOG '
      7 7m1iTw/4EwAAbgAAAHIAAABAAAQANS42LjQtbTctbG9nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
      8 AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVgAEGggAAAAICAgCAAAAACed1Yg=
      9 '/*!*/;
     10 # at 114
     11 #700101  9:00:00 server id 1  end_log_pos 0     Rotate to mysql-bin.000016  pos: 4
     12 # at 157
     13 #120316  7:31:31 server id 1  end_log_pos 114   Start: binlog v 4, server v 5.6.4-m7-log created 120316  7:31:31 at startup
     14 ROLLBACK/*!*/;
     15 BINLOG '
     16 w21iTw8BAAAAbgAAAHIAAAAAAAQANS42LjQtbTctbG9nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
     17 AAAAAAAAAAAAAAAAAADDbWJPEzgNAAgAEgAEBAQEEgAAVgAEGggAAAAICAgCAAAAAMqY3wM=
     18 '/*!*/;
     19 # at 267
     20 #120316  7:32:51 server id 1  end_log_pos 211   Query   thread_id=2     exec_time=0     error_code=0
     21 use test2/*!*/;
     22 SET TIMESTAMP=1331850771/*!*/;
     23 SET @@session.pseudo_thread_id=2/*!*/;
     24 SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
     25 SET @@session.sql_mode=0/*!*/;
     26 SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
     27 /*!\C utf8 *//*!*/;
     28 SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
     29 SET @@session.lc_time_names=0/*!*/;
     30 SET @@session.collation_database=DEFAULT/*!*/;
     31 create table ddd (id int)
     32 /*!*/;
     33 # at 364
     34 #120316  7:33:08 server id 1  end_log_pos 288   Query   thread_id=2     exec_time=0     error_code=0
     35 SET TIMESTAMP=1331850788/*!*/;
     36 BEGIN
     37 /*!*/;
     38 # at 441
     39 #120316  7:33:08 server id 1  end_log_pos 387   Query   thread_id=2     exec_time=0     error_code=0
     40 SET TIMESTAMP=1331850788/*!*/;
     41 insert into ddd values(100)
     42 /*!*/;
     43 # at 540
     44 #120316  7:33:08 server id 1  end_log_pos 414   Xid = 17
     45 COMMIT/*!*/;
     46 # at 567
     47 #120316  7:33:15 server id 1  end_log_pos 511   Query   thread_id=2     exec_time=0     error_code=0
     48 SET TIMESTAMP=1331850795/*!*/;
     49 create table ccc (id int)
     50 /*!*/;
     51 # at 664
     52 #120316  7:33:27 server id 1  end_log_pos 588   Query   thread_id=2     exec_time=0     error_code=0
     53 SET TIMESTAMP=1331850807/*!*/;
     54 BEGIN
     55 /*!*/;
     56 # at 741
     57 #120316  7:33:27 server id 1  end_log_pos 689   Query   thread_id=2     exec_time=0     error_code=0
     58 SET TIMESTAMP=1331850807/*!*/;
     59 insert into ccc values ( 200)
     60 /*!*/;
     61 # at 842
     62 #120316  7:33:27 server id 1  end_log_pos 716   Xid = 19
     63 COMMIT/*!*/;
     64 # at 869
     65 #120316  7:33:35 server id 1  end_log_pos 793   Query   thread_id=2     exec_time=0     error_code=0
     66 SET TIMESTAMP=1331850815/*!*/;
     67 BEGIN
     68 /*!*/;
     69 # at 946
     70 #120316  7:33:35 server id 1  end_log_pos 893   Query   thread_id=2     exec_time=0     error_code=0
     71 SET TIMESTAMP=1331850815/*!*/;
     72 insert into ddd values (500)
     73 /*!*/;
     74 # at 1046
     75 #120316  7:33:35 server id 1  end_log_pos 920   Xid = 20
     76 COMMIT/*!*/;
     77 DELIMITER ;
     78 # End of log file
     79 ROLLBACK /* added by mysqlbinlog */;
     80 /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

cccのSQLはやはり伝播してしまうらしい。(49行目、59行目)、binaryログの中身をある条件(オブジェクト、SQL、権限)だけ転送しないって処理は考えると重いので妥当と言えば妥当な気がしてきた。