Master/Slave関係にあるMySQLサーバでMasterに無いテーブルをSlaveに作成した時の挙動

モチベーション

Masterに存在しないテーブルをSlaveで作成した時の挙動とさらに同じテーブルをMasterで作成した時の挙動を検証ベースで確認したい。

Masterに存在しないテーブルをSlaveで作成する

レプリケーションの基本的な考え方として、Slave側から更新をしてはならないというものがある。実際には、レプリケーションが止まるだけなのでDBとしては利用できるのだが、再びレプリケーションを開始するには色々とDBの状態を同期する必要がある。一方で、Masterに存在しないテーブルをSlaveで作成しても実はレプリケーションは止まらないことを知った。実際に確認してみる。

@ Master
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |      107 |              |                  |
+------------------+----------+--------------+------------------+

mysql> use repl_test;

mysql> show tables;
Empty set (0.00 sec)

@ Slave
mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.33.10
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 107
               Relay_Log_File: mysqld-relay-bin.000006
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 107
              Relay_Log_Space: 556
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1

この状態でSlaveの方に新しいテーブルを作成すると、レプリケーションが続いていることがわかる。

@ Slave
mysql> use repl_test;
Database changed
mysql> CREATE TABLE emp (
    ->   emp_id INTEGER UNSIGNED AUTO_INCREMENT,
    ->   emp_name VARCHAR(50) NOT NULL,
    ->   emp_roman VARCHAR(50) NOT NULL,
    ->   emp_email VARCHAR(100) NOT NULL,
    ->   INDEX(emp_roman),
    ->   PRIMARY KEY (emp_id)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+---------------------+
| Tables_in_repl_test |
+---------------------+
| emp                 |
+---------------------+

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.33.10
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 200
               Relay_Log_File: mysqld-relay-bin.000006
                Relay_Log_Pos: 346
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 200
              Relay_Log_Space: 649
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1

なるほど。

同じテーブルをMasterで作成してみる

現状はSlaveにしかempテーブルが無い状態だが、Masterで同じクエリを実行したらどうなるだろうか。

@ Master
mysql> use repl_test;
Database changed
mysql> CREATE TABLE emp (
    ->   emp_id INTEGER UNSIGNED AUTO_INCREMENT,
    ->   emp_name VARCHAR(50) NOT NULL,
    ->   emp_roman VARCHAR(50) NOT NULL,
    ->   emp_email VARCHAR(100) NOT NULL,
    ->   INDEX(emp_roman),
    ->   PRIMARY KEY (emp_id)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+---------------------+
| Tables_in_repl_test |
+---------------------+
| emp                 |
+---------------------+

Masterではテーブルが作られているが、すでに同じテーブルが存在するSlaveの方でレプリケーション状態を確認すると、当然ながらエラーが出ている。

@ Slave
mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.33.10
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 475
               Relay_Log_File: mysqld-relay-bin.000006
                Relay_Log_Pos: 346
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1050
                   Last_Error: Error 'Table 'emp' already exists' on query. Default database: 'repl_test'. Query: 'CREATE TABLE emp (
  emp_id INTEGER UNSIGNED AUTO_INCREMENT,
  emp_name VARCHAR(50) NOT NULL,
  emp_roman VARCHAR(50) NOT NULL,
  emp_email VARCHAR(100) NOT NULL,
  INDEX(emp_roman),
  PRIMARY KEY (emp_id)
)'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 200
              Relay_Log_Space: 924
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1050
               Last_SQL_Error: Error 'Table 'emp' already exists' on query. Default database: 'repl_test'. Query: 'CREATE TABLE emp (
  emp_id INTEGER UNSIGNED AUTO_INCREMENT,
  emp_name VARCHAR(50) NOT NULL,
  emp_roman VARCHAR(50) NOT NULL,
  emp_email VARCHAR(100) NOT NULL,
  INDEX(emp_roman),
  PRIMARY KEY (emp_id)
)'
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1

この状態でMasterでデータを挿入するクエリを走らせるとMasterにはデータが追加される。

@ Master
mysql> INSERT INTO emp (emp_name, emp_roman, emp_email)
    ->   VALUES("Alice", "alice", "alice@example.com"),
    ->         ("Bob", "bob", "bob@example.com"),
    ->         ("Cris", "cris", "cris@example.com");
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

しかし依然として、CREATE TABLEクエリでレプリケーションが詰まった状態でストップしている。

@ Slave
mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.33.10
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 856
               Relay_Log_File: mysqld-relay-bin.000006
                Relay_Log_Pos: 346
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1050
                   Last_Error: Error 'Table 'emp' already exists' on query. Default database: 'repl_test'. Query: 'CREATE TABLE emp (
  emp_id INTEGER UNSIGNED AUTO_INCREMENT,
  emp_name VARCHAR(50) NOT NULL,
  emp_roman VARCHAR(50) NOT NULL,
  emp_email VARCHAR(100) NOT NULL,
  INDEX(emp_roman),
  PRIMARY KEY (emp_id)
)'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 200
              Relay_Log_Space: 1305
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1050
               Last_SQL_Error: Error 'Table 'emp' already exists' on query. Default database: 'repl_test'. Query: 'CREATE TABLE emp (
  emp_id INTEGER UNSIGNED AUTO_INCREMENT,
  emp_name VARCHAR(50) NOT NULL,
  emp_roman VARCHAR(50) NOT NULL,
  emp_email VARCHAR(100) NOT NULL,
  INDEX(emp_roman),
  PRIMARY KEY (emp_id)
)'
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1

この状態から脱するために、SQL_SLAVE_SKIP_COUNTER でステートメントを1文スキップする。

@ Slave
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> set global SQL_SLAVE_SKIP_COUNTER = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

すると、レプリケーションが正常状態に戻る。

@ Slave
mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.33.10
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 856
               Relay_Log_File: mysqld-relay-bin.000007
                Relay_Log_Pos: 253
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 856
              Relay_Log_Space: 1305
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
1 row in set (0.00 sec)

うーん、このSKIP_COUNTERを増やしてステートメントを飛ばす運用はしたくないね。

Masterにmysqldumpで生成したクエリから流し込んでみる

今度も先にSlaveに新しいテーブルを作り、同様にMasterで同じテーブルを作ってみる。ただし、今回はSlaveにてmysqldumpを使ってテーブルを抽出して、Masterの方に流し込む。dumpしたクエリの先頭にはSlaveで生成したテーブルをDROPするクエリがはいるはずである。

@ Slave
mysql> CREATE TABLE dept (
    ->   dept_id SMALLINT UNSIGNED,
    ->   dept_name VARCHAR(50) NOT NULL,
    ->   dep_tel VARCHAR(20) NOT NULL,
    ->   PRIMARY KEY (dept_id)
    -> );
Query OK, 0 rows affected (0.01 sec)

@ Slave
vagrant@mysql-slave1:~$ mysqldump -uroot repl_test emp > table_dump.sql
vagrant@mysql-slave1:~$ ls
table_dump.sql
vagrant@mysql-slave1:~$ scp table_dump.sql  vagrant@192.168.33.10:/tmp

@ Master
vagrant@mysql-master:~$ cat /tmp/table_dump.sql
--
-- Table structure for table `dept`
--

DROP TABLE IF EXISTS `dept`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `dept` (
  `dept_id` smallint(5) unsigned NOT NULL DEFAULT '0',
  `dept_name` varchar(50) NOT NULL,
  `dep_tel` varchar(20) NOT NULL,
  PRIMARY KEY (`dept_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `dept`
--
LOCK TABLES `dept` WRITE;

@ Master
vagrant@mysql-master:~$ mysql -uroot repl_test < /tmp/table_dump.sql
vagrant@mysql-master:~$ mysql -uroot
mysql> use repl_test;
mysql> show tables;
+---------------------+
| Tables_in_repl_test |
+---------------------+
| dept                |
| emp                 |
+---------------------+
2 rows in set (0.00 sec)
mysql> show master status \G
*************************** 1. row ***************************
            File: mysql-bin.000003
        Position: 2482
    Binlog_Do_DB:
Binlog_Ignore_DB:
1 row in set (0.00 sec)

Masterにdumpしたところで、Slaveの状態を確認するとレプリケーションが続いていることが確認できる。

@ Slave
vagrant@mysql-slave1:~$ mysql -uroot
mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.33.10
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 2482
               Relay_Log_File: mysqld-relay-bin.000007
                Relay_Log_Pos: 1879
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 2482
              Relay_Log_Space: 2931
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1

結論

SLAVEでMASTERに存在しないテーブルを作ることはレプリケーションの妨害にはならない。また、SLAVEで生成したテーブルをDROP IF EXISTS table_nameのようなクエリ、つまり、mysqldumpで生成したクエリをMasterで流せば、SLAVEでもMASTERに存在しないテーブルが消えるためレプリケーションは継続される。(SLAVEに渡したbinlog実行前はSLAVEのほうが進んでいる状態を取りうる)

参考

この２つの本はMySQLサーバの運用、管理についてかゆいところまで説明しているので良い。

実践ハイパフォーマンスMySQL 第3版

Author: Baron Schwartz

Manufacturer: オライリージャパン

Publish date: 2013-11-25

エキスパートのためのMySQL[運用+管理]トラブルシュ ーティングガイド

Author: 奥野 幹也

Manufacturer: 技術評論社

Publish date: 2010-06-12