mysql レプリケーション周り
mysql レプリケーション周り
とある人にcreate temporary tableでテーブルを作成した場合、slaveにレプリケーションってされるの?と素朴な質問をされ「?」って思ったのでちょっと調べてみた。途中からはtemporary tableとかあんま関係なくなってきてるけど気にしない。
master側で以下のDDL、DMLを実行した
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、権限)だけ転送しないって処理は考えると重いので妥当と言えば妥当な気がしてきた。