MySQL 5.7 : レプリケーションフィルターをオンラインで変更する

MySQL 5.7にはたくさんの機能拡張や新機能があります。これについては、別の記事(原文)で以前サマリーを書きました。レプリケーションフィルターをオンラインで追加できるのは、マニュアルにも書かれているようにMySQL 5.7の機能の一つです。この記事では、いくつかの例とともにこの機能を説明し、まとめてみます。

レプリケーションイベントをフィルターするのは、部分レプリケーションとしても知られています。部分レプリケーションはマスターまたはスレーブで設定します。マスタサーバーで binlog-do-db や binlog-ignore-db を使ってイベントをフィルタするのは、この記事で指摘したようにあまり良いアイディアではありません。しかし、必要ならばスレーブで部分レプリケーションを行うのは良い方法と言えます。私たちのCEOのPeter Zaitsevが、過去の記事で便利であろうフィルターを使ったレプリケーションについて詳細を書いています。

部分レプリケーションはステートメントベースのレプリケーションと行ベースのレプリケーションでは違った動きをします。詳細についてはマニュアル及びその中のこちらのページで確認できます。MySQLが部分レプリケーションのルールをどのように評価しているのかを知るのに便利です。

MySQL 5.7より前は、レプリケーションルールを追加したり変更したりするにはMySQLサーバーの再起動が必要でした。MySQL 5.7では、レプリケーションフィルタールールの追加や削除がMySQLサーバの再起動なしにできるオンライン操作になり、これには CHANGE REPLICATION FILTER コマンドを使います。いくつかの例をお見せしましょう。

現在、スレーブサーバーはレプリケーションにフィルタを使っておらず、これはスレーブのステータスの最後の5つの変数で確認できます。Replicate_*が空になっており、これはレプリケーションフィルターが何も設定されていないことを意味しています。

mysql> show slave statusG
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.130
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000002
          Read_Master_Log_Pos: 351
               Relay_Log_File: centos59-relay-bin.000003
                Relay_Log_Pos: 566
        Relay_Master_Log_File: master-bin.000002
             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:
.

マスターデータベースサーバーはdb1からdb4を持っています。4つのうちdb1とdb2だけをレプリケーションするようにしてみましょう。そうするには、 replicate-do-db オプションの力を借ります。

mysql> CHANGE REPLICATION FILTER REPLICATE_DO_DB = (db1, db2);
ERROR 3017 (HY000): This operation cannot be performed with a running slave sql thread; run STOP SLAVE SQL_THREAD first

このエラーは、 CHANGE REPLICATION FILTER コマンドは稼働中のスレーブではサポートされていないことを意味します。まずSQLスレーブスレッドを停止する必要があり、それから replicate-do-db オプションを設定するためにコマンドを実行し直します。スレーブのステータスを見てみると、マスタに4つあるデータベースのうちこのスレーブはdb1とdb2だけをレプリケーションしていることが確認できます。

mysql> STOP SLAVE SQL_THREAD;
Query OK, 0 rows affected (0.00 sec)
mysql> CHANGE REPLICATION FILTER REPLICATE_DO_DB = (db1, db2);
Query OK, 0 rows affected (0.00 sec)
mysql> show slave statusG
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.130
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000002
          Read_Master_Log_Pos: 505
               Relay_Log_File: centos59-relay-bin.000003
                Relay_Log_Pos: 720
        Relay_Master_Log_File: master-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB: db1,db2
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
.

フィルターを消すには、フィルター名、つまり上の例ではreplicate-do-dbを空にする必要があります。スレーブのステータスで、replicate-do-db変数にレプリケーションフィルターが設定されていないことを確認できます。

mysql> STOP SLAVE SQL_THREAD;
Query OK, 0 rows affected (0.03 sec)
mysql> CHANGE REPLICATION FILTER REPLICATE_DO_DB = ();
Query OK, 0 rows affected (0.00 sec)
mysql> START SLAVE SQL_THREAD;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW SLAVE STATUSG
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.130
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000002
          Read_Master_Log_Pos: 1629
               Relay_Log_File: centos59-relay-bin.000003
                Relay_Log_Pos: 1844
        Relay_Master_Log_File: master-bin.000002
             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:
.

さらに、以下のようにコンマで区切ることで異なるレプリケーションフィルターをひとつのコマンドで設定することもできます。

mysql> STOP SLAVE SQL_THREAD;
Query OK, 0 rows affected (0.03 sec)
mysql> CHANGE REPLICATION FILTER
REPLICATE_WILD_DO_TABLE = ('db1.db1_new%'),
REPLICATE_WILD_IGNORE_TABLE =  ('db1.db1_old%');
mysql> START SLAVE SQL_THREAD;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW SLAVE STATUSG
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.130
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000003
          Read_Master_Log_Pos: 448
               Relay_Log_File: centos59-relay-bin.000006
                Relay_Log_Pos: 663
        Relay_Master_Log_File: master-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: db1.db1_new%
  Replicate_Wild_Ignore_Table: db1.db1_old1%
.

スレーブのステータスから、db1.db1_newはバイナリーログのイベントをスレーブにレプリケーションし、db1.db1_oldテーブルは Replicate_Wild_Ignore_Table フィルターによりレプリケーションイベントが無視されるのが確認できます。なお、データベースやテーブル名に特殊文字が含まれていないなら、フィルターの値をクオートで囲う必要はありません。しかし、 Replicate_Wild_Do_Table や Replicate_Wild_Ignore_Table は普通ワイルドカードの文字を含むでしょうから、クオートで囲う必要があります。

my.cnfには同じフィルターに同じフィルタリングルールを複数書いてしまえるのですが、 CHANGE REPLICATION FILTER コマンドでは、同じフィルタリングルールを複数回設定することはできません。 CHANGE REPLICATION FILTER コマンドでは、同じフィルターに複数のルールを設定しようとすると、最後のルールだけが有効になり、それ以前のルールは以下の例にあるように無視されます。

mysql> SELECT * FROM db1.db1_old;
Empty set (0.00 sec)
mysql> STOP SLAVE SQL_THREAD;
Query OK, 0 rows affected (0.03 sec)
mysql> CHANGE REPLICATION FILTER
REPLICATE_WILD_DO_TABLE = ('db2.db2_tbl1%'),
REPLICATE_WILD_DO_TABLE =  ('db2.db2_tbl2%');
mysql> START SLAVE SQL_THREAD;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW SLAVE STATUSG
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.130
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000003
          Read_Master_Log_Pos: 980
               Relay_Log_File: centos59-relay-bin.000006
                Relay_Log_Pos: 1195
        Relay_Master_Log_File: master-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: db2.db2_tbl2%
  Replicate_Wild_Ignore_Table:
.

見ての通り、db2.db2_tbl1に関しては無視され、最後のdb2.db2_tbl2に対するルールが有効になりました。前述の通り、フィルターの設定を消すにはそのフィルターに空の値をセットします。以下の例では、 Replicate_Wild_Do_Table フィルターの設定を消しています。

mysql> CHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE = ();

先ほど Replicate_Do_DB に複数のフィルターをコンマ区切りで指定して1コマンドで設定できる例を挙げました。ここでも、 CHANGE REPLICATION FILTER コマンドで複数の Replicate_Wild_Do_Table オプションを設定してみましょう。

mysql> STOP SLAVE SQL_THREAD;
Query OK, 0 rows affected (0.03 sec)
mysql> CHANGE REPLICATION FILTER
REPLICATE_WILD_DO_TABLE = ('db2.db2_tbl1%','db2.db2_tbl2%');
Query OK, 0 rows affected (0.00 sec)
mysql> START SLAVE SQL_THREAD;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW SLAVE STATUSG
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.130
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000008
          Read_Master_Log_Pos: 154
               Relay_Log_File: centos59-relay-bin.000013
                Relay_Log_Pos: 369
        Relay_Master_Log_File: master-bin.000008
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table: db2.db2_tbl1%,db2.db2_tbl2%
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
.

結論

部分レプリケーションは基本的には良い方法とは言えません。 replicate-do-table 、 replicate-ignore-table 、 replicate-wild-do-table 、 replicate-wild-ignore-table といった全てのレプリケーションオプションは、それぞれ違った挙動をします。フィルタリングが正しく動くように、基本となるデータベースを決める必要があり、バイナリーログのフォーマットによって挙動が変わります。replicate-wild-do-tableとreplicate-wild-ignore-table以外のフィルタは期待した通りに動かないかもしれませんし、ワイルドカードフィルタを使うとストアドプロシージャーとストアドファンクションでイベントに不整合が出る可能性があります。