[MySQL5.7]マルチソースレプリケーション設定方法

[MySQL5.7]マルチソースレプリケーション設定方法

2015/10/21に、MySQL5.7(5.7.9) Major Versionが正式リリースされましたね。貧乏な私はMySQL 5.6の3倍の性能と言われましてもプアーなマシンでしか動かせないのでじっと指を咥えて他人様のベンチ結果を見守ることにします。 (´・_・`)

さて、MySQL 5.7の目玉機能といえば、マルチソースレプリケーションではないでしょうか。これが実現できることで何が嬉しいかといいますと、例えばバラバラと構築したマスターがn台あったとしてnx2台のスレーブ作る必要がなく、1〜2台の大きなスレーブに集約できたりするわけです。(んで、behindがなくなったらこいつを新マスターにしてしまえる!) というわけで、さくらのクラウドでCentOS7のサーバーを構築し、マルチソースレプリケーションを試してみました。ネットワーク構成は以下になります。

mysql57_20151025_0001

master0 192.168.0.230
master1 192.168.0.231
slave0 192.168.0.232
slave1 192.168.0.233

 

プライベートIPアドレスはeth1に振っています。さくらのクラウドでCentOS7のeth1にIPアドレスを振る手順はこちらを参考にどうぞ。

今回構築するサーバーの、マスターとスレーブの関係は以下の通りです。

  • master0 は test0 データベースのマスター。チャネルは0
  • master1 は test1 データベースのマスター。チャネルは1
  • slave0 は 上記すべてのデータベースのスレーブになる
  • slave1 は 上記すべてのデータベースのスレーブになる

mysql57_20151025_0002

ちなみに、マルチソースレプリケーションはマルチマスターレプリケーションとイコールではありません。 こちらの図表を見ると違いが一目瞭然ですが、マルチマスターレプリケーションは循環型のレプリケーションで、SQLクライアントはどのノードに書き込みを行っても、レプリケーションを構成するすべてのノードに等しくデータが同期されます。いっぽう、マルチソースレプリケーションは上記のDB構成図のように、1つのスレーブが複数のマスターを持つことができる仕組みです。

■ MySQLサーバーを「ほぼ」一撃構築する

Master Slaveともに共通の設定をこちらの一撃シェルスクリプトをもとに作成します。スクリプトの文字数が10000文字を超えてしまったのでスタートアップスクリプトに登録できなかった。残念!

最初に変数「INTERFACE」「IPOCT4」を指定していますが、前者はipコマンドでループバックインターフェイス以外の一覧から先頭のデバイスを検出し、IPv4アドレスの最後のオクテットの数字を抜き出しています。これを「NEWHOSTNAME」変数でホスト名にしたり「SERVERID」変数でMySQLのサーバーIDに代入したりしています。ネットワークが/24までならホスト名やサーバーIDが被ることはないですが、それ以上のネットワークの場合はよしなに編集していただきたく。。。

一応ここまでが今回の4台に共通の設定なので、一撃構築となりますが、ここから先は個別のカスタマイズです。なのでフルオートメーションじゃありません>< あと、今回の一撃シェルスクリプトはCentOS6とCentOS7で動作確認をしています。以下の作業はMasterとSlaveでそれぞれ別の作業をするので、どちらのサーバーで作業するかを見出しに書いておきますね。

■ [Master]my.cnf編集

/etc/my.cnf の [mysqld] セクションに binlog-ignore-db=mysql を追記してバイナリログにmysqlデータベースの変更を記録しないようにします。

sudo cp -p /etc/my.cnf{,.$(date +%Y%m%d)00}
sudo vi /etc/my.cnf

以下追記

binlog-ignore-db=mysql

保存したらmysqldを再起動します。

sudo service mysqld restart
sudo systemctl restart mysqld.service

■ [Master]レプリケーション用ユーザーを作成する

以下のID、パスワードでレプリケーション用ユーザーを作成します。すべてのDB、テーブルに対してREPLICATION SLAVEの権限を与えます。

ID msandbox
パスワード msandbox
mysql -e "GRANT REPLICATION SLAVE ON *.* TO 'msandbox'@'192.168.0.0/255.255.255.0' IDENTIFIED BY 'msandbox';"

■ [Slave]my.cnf編集

MySQLでマルチソースレプリケーションを行うためには、スレーブをクラッシュセーフにする必要があります。このため、 [mysqld] セクションに以下を追記します。

master_info_repository=TABLE
relay_log_info_repository=TABLE

もう1つ、 replicate-ignore-db パラメータに mysql を指定しないと、MySQLのID、パスワードまでマスターから同期されてしまいます。なので、以下の1行も追記します。

replicate-ignore-db=mysql

上記3行を追記して保存したらmysqldを再起動します。

sudo service mysqld restart
sudo systemctl restart mysqld.service

■ [Master]バイナリログのポジションを確認する

master0サーバーとmaster1サーバーで、SHOW MASTER STATUSを実行します。

[nullpopopo@master0 ~]$ mysql -e "SHOW MASTER STATUS;"
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| mysqld-bin.000005 |      465 |              | mysql            |                   |
+-------------------+----------+--------------+------------------+-------------------+
[nullpopopo@master1 ~]$ mysql -e "SHOW MASTER STATUS;"
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| mysqld-bin.000005 |      465 |              | mysql            |                   |
+-------------------+----------+--------------+------------------+-------------------+

■ [Slave]チャネルを指定してスレーブのプロセスを作成する

CHANGE MASTERコマンドでマスターサーバーのスレーブになります。以下のコマンドを見ると、普通のMaster/Slave構成のレプリケーションと同じようですが、引数「FOR CHANNEL」をつけて、どのチャネルのスレーブになるかを指定する必要があります。また、チャネル名はダブルクォートで囲ってあげましょう。

mysqlクライアントからは以下のように実行します。

mysql> CHANGE MASTER TO MASTER_HOST="192.168.0.230", MASTER_PORT=3306, MASTER_LOG_FILE="mysqld-bin.000005", MASTER_LOG_POS=465, MASTER_USER="msandbox", MASTER_PASSWORD="msandbox" FOR CHANNEL "master0";
mysql> CHANGE MASTER TO MASTER_HOST="192.168.0.231", MASTER_PORT=3306, MASTER_LOG_FILE="mysqld-bin.000005", MASTER_LOG_POS=465, MASTER_USER="msandbox", MASTER_PASSWORD="msandbox" FOR CHANNEL "master1";

bash等のシェルからは以下のように実行します。

[nullpopopo@slave0 ~]$ mysql -e "CHANGE MASTER TO MASTER_HOST=\"192.168.0.230\", MASTER_PORT=3306, MASTER_LOG_FILE=\"mysqld-bin.000005\", MASTER_LOG_POS=465, MASTER_USER=\"msandbox\",MASTER_PASSWORD=\"msandbox\" FOR CHANNEL \"master0\";"
[nullpopopo@slave0 ~]$ mysql -e "CHANGE MASTER TO MASTER_HOST=\"192.168.0.231\", MASTER_PORT=3306, MASTER_LOG_FILE=\"mysqld-bin.000005\", MASTER_LOG_POS=465, MASTER_USER=\"msandbox\",MASTER_PASSWORD=\"msandbox\" FOR CHANNEL \"master1\";"

それでは START SLAVEします。mysqlクライアントからは以下のように実行します。

mysql> START SLAVE FOR CHANNEL "master0";
mysql> START SLAVE FOR CHANNEL "master1";

bash等のシェルからは以下のように実行します。

[nullpopopo@slave0 ~]$ mysql -e "START SLAVE FOR CHANNEL \"master0\";"
[nullpopopo@slave0 ~]$ mysql -e "START SLAVE FOR CHANNEL \"master1\";"

CHANGE MASTERコマンドでマスターのバイナリログ名とポジションを引数に与えず実行してしまうと、SHOW SLAVE STATUSで以下のエラーが出てしまうことがあります。

                Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'A slave with the same server_uuid as this slave has connected to the master; the first event '' at 4, the last event read from '/var/lib/mysql/binlog/mysqld-bin.000004' at 465, the last byte read from '/var/lib/mysql/binlog/mysqld-bin.000004' at 465.'

そんな時は慌てず騒がず STOP SLAVEとRESET SLAVEしてスレーブのmysqldを再起動しましょう。なお、slave側の /etc/my.cnf で replicate-ignore-db=mysql の記述を忘れると、この時点でMySQLのrootパスワードがマスターから転送されて変更されてしまうので注意しましょう。

■ [Slave]ステータス確認

それではスレーブのステータスを確認してみましょう。Masterが1台、Slaveが1 or n台のときは「show slave status\G」でスレーブのステータスが確認できますが、今回のようにマルチソースレプリケーションだと、チャネル名を指定しないと何も出力されません。mysqlクライアントからは以下のように実行します。

mysql> SHOW SLAVE STATUS FOR CHANNEL "master0"\G
mysql> SHOW SLAVE STATUS FOR CHANNEL "master1"\G

bash等のシェルからは以下のように実行します。

[nullpopopo@slave0 ~]$ mysql -e "SHOW SLAVE STATUS FOR CHANNEL \"master0\"\G"
[nullpopopo@slave0 ~]$ mysql -e "SHOW SLAVE STATUS FOR CHANNEL \"master1\"\G"

実際の出力例はこんな感じです。(クリックで大きくなります)

[nullpopopo@slave0 ~]$ mysql -e "SHOW SLAVE STATUS FOR CHANNEL \"master0\"\G"
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.230
                  Master_User: msandbox
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysqld-bin.000005
          Read_Master_Log_Pos: 627
               Relay_Log_File: localhost-relay-bin-master0.000002
                Relay_Log_Pos: 483
        Relay_Master_Log_File: mysqld-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB: mysql
           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: 627
              Relay_Log_Space: 702
              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: 230
                  Master_UUID: f1f2d908-7a39-11e5-ad4f-080027288d83
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name: master0
[nullpopopo@slave0 ~]$ mysql -e "SHOW SLAVE STATUS FOR CHANNEL \"master1\"\G"
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.231
                  Master_User: msandbox
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysqld-bin.000005
          Read_Master_Log_Pos: 627
               Relay_Log_File: localhost-relay-bin-master1.000002
                Relay_Log_Pos: 483
        Relay_Master_Log_File: mysqld-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB: mysql
           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: 627
              Relay_Log_Space: 702
              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: 231
                  Master_UUID: 84176354-7a3a-11e5-b242-080027295639
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name: master1

チャネルを指定することで、スレーブのステータスを確認することができました。また、チャネルごとに Master_Host Master_UUID Channel_Name が異なっていることがわかります。(もちろんMaster->Slaveの転送状況も)

■ [Master]データベースを作成する

master0サーバーでtest0データベースを作成します。

[nullpopopo@master0 ~]$ mysql -e "CREATE DATABASE test0;"

次にmaster1サーバーでtest1データベースを作成します。

[nullpopopo@master1 ~]$ mysql -e "CREATE DATABASE test1;"

■ [Slave]データベース一覧を確認する

スレーブにこれらDBが転送されてきているかを確認します。

[nullpopopo@slave0 ~]$ mysql -e "SHOW DATABASES LIKE 'test%';"
+------------------+
| Database (test%) |
+------------------+
| test0            |
| test1            |
+------------------+
[nullpopopo@slave1 ~]$ mysql -e "SHOW DATABASES LIKE 'test%';"
+------------------+
| Database (test%) |
+------------------+
| test0            |
| test1            |
+------------------+

マスターサーバーにはtest0かtest1のどちらかのデータベースしかありませんでしたが、スレーブの2台には両方のデータベースができています。データがごちゃごちゃにならないよう、まだまだ細かい設計が必要ですが、これで最低限のマルチソースレプリケーション設定はおしまいです。ね、簡単でしょう?

[amazonjs asin=”4797353937″ locale=”JP” title=”MySQLによるタフなサイトの作り方”]