MySQL 5.7のマルチソースレプリケーション機能を検証してみました

はじめまして。メディア開発本部の綿引です。

本日は個人的に前から気になっていたMySQL 5.7のマルチソースレプリケーションについて検証したいと思います。

マルチソースレプリケーション

マルチソースレプリケーションとは1台のスレーブが複数のマスタを持つことができる仕組みです。
これまでのMySQLのレプリケーションはマスタとスレーブの関係が1:Nしか出来ませんでしたが、
この新機能を使用すればN:1が可能になります。

今まで『こんなにスレーブいらないんだけど冗長化は必須だよな。。』と思ったことがある方は興味を持たれるかと。

MySQL 5.7で他にも追加された機能はありますが、マルチソースレプリケーションはその中でも一番目を引く機能ではないかと個人的には思っております。

そして今回構築に伴い、MySQLだけでなくスレーブ側を
互換性のある”MariaDB”にしてみるといったことも出来ればと思いますので、
早速構築を開始したいと思います。

構築開始！

今回は予め以下の構成を用意しました。

■マスタ側
mysql5.7-master1 (MySQL導入済み)
mysql5.7-master2 (MySQL導入済み)

■スレーブ側
mysql5.7-slave (MySQL導入済み)
mariadb10.1-slave (MariaDB導入済み)

以下、図の緑の点線部分をレプリケーションしていきたいと思います。

〔Master〕my.cnfの編集

まずはmy.cnfの設定です。

server-idがかぶらないようにmysql5.7-master1に以下を、

log-bin=mysql-bin
server-id=1001

mysql5.7-master2に以下を設定します。

log-bin=mysql-bin
server-id=1002

次に両マスターサーバにレプリケーション用のユーザを作成。

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'XXXXXX';

これでマスター側の設定自体は完了です。my.cnfを修正したのでmysqldの再起動を行っておきます。

〔Slave〕my.cnfの編集

続いてスレーブ側の設定です。まずはMariaDBではなくMySQLを先にやっていきたいと思います。
以下を追加します。

server-id=1003
relay_log_info_repository = TABLE
master_info_repository = TABLE
relay_log_recovery = ON

ここまで実施したらまたスレーブ側のmysqldを再起動しておきます。

〔Slave〕スレーブ・マスター接続！

両マスター側でバイナリログのポジションを「show master status」で確認しておき、スレーブ側でチャネルの設定を行います。

CHANGE MASTER TO
  MASTER_HOST='mysql5.7-master1',
  MASTER_USER='repl',
  MASTER_PASSWORD='XXXXX',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000005',
  MASTER_LOG_POS=309,
  MASTER_CONNECT_RETRY=10
  for channel 'master1';

CHANGE MASTER TO
  MASTER_HOST='mysql5.7-master2',
  MASTER_USER='repl',
  MASTER_PASSWORD='XXXXX',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000003',
  MASTER_LOG_POS=309,
  MASTER_CONNECT_RETRY=10
  for channel 'master2';

「show slave status」で確認してみます。

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: mysql5.7-master1
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 309
               Relay_Log_File: mysql5-relay-bin-master1.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: No
            Slave_SQL_Running: No
                             ・
                             ・
                             ・
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name: master1
           Master_TLS_Version:
*************************** 2. row ***************************
               Slave_IO_State:
                  Master_Host: mysql5.7-master2
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 309
               Relay_Log_File: mysql5-relay-bin-master2.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: No
            Slave_SQL_Running: No
                             ・
                             ・
                             ・
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name: master2
           Master_TLS_Version:
2 rows in set (0.00 sec)

2つのマスターが確認出来ました！後はレプリケーションを開始するだけです。

start slave for channel 'master1';
start slave for channel 'master2';

再度「show slave status」で確認してみます。

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: mysql5.7-master1
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 309
               Relay_Log_File: mysql5-relay-bin-master1.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                             ・
                             ・
                             ・
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name: master1
           Master_TLS_Version:
*************************** 2. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: mysql5.7-master2
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 309
               Relay_Log_File: mysql5-relay-bin-master2.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                             ・
                             ・
                             ・
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name: master2
           Master_TLS_Version:
2 rows in set (0.00 sec)

2つのチャネルの「Slave_IO_Running」と「Slave_SQL_Running」が「YES」になりました！

全サーバに以下テスト用のテーブルを作成して、レプリケーションの確認です！

mysql> desc item;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

確認！！

マスタの1台目で以下を実施。

mysql> insert into item (id,name) values (1,"master1");
Query OK, 1 row affected (0.01 sec)

2台目で以下を実施。

mysql> insert into item (id,name) values (2,"master2");
Query OK, 1 row affected (0.01 sec)

スレーブで確認！

mysql> select * from item;
+------+---------+
| id   | name    |
+------+---------+
|    1 | master1 |
|    2 | master2 |
+------+---------+
2 rows in set (0.00 sec)

ちゃんとレプリケーションされてますね。

MariaDBでのマルチソースレプリケーション

続いてMariaDBでも実施して見ます！

まずはMySQLと同様にmy.cnfの設定です。
server-idがかぶらないように以下を設定します。

server-id=1004

スレーブ・マスター接続！

一台ずつコネクションとレプリケーションの設定を行います。

MariaDB [(none)]> SET @@default_master_connection='mysql5.7-master1';
Query OK, 0 rows affected (0.00 sec)

コネクションの設定を確認し、

MariaDB [(none)]> select @@default_master_connection;
+-----------------------------+
| @@default_master_connection |
+-----------------------------+
| mysql5.7-master1            |
+-----------------------------+

レプリケーションの設定を行います。

MariaDB [(none)]> CHANGE MASTER TO
    ->   MASTER_HOST='mysql5.7-master1',
    ->   MASTER_USER='repl',
    ->   MASTER_PASSWORD='1qaZ_Xsw2',
    ->   MASTER_LOG_FILE='mysql-bin.000005',
    ->   MASTER_LOG_POS=1006;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> show all slaves status \G;
*************************** 1. row ***************************
              Connection_name: mysql5.7-master1
              Slave_SQL_State:
               Slave_IO_State:
                  Master_Host: mysql5.7-master1
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 1006
               Relay_Log_File: mariadb10-relay-bin-mysql5@002e7@002dmaster1.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: No
            Slave_SQL_Running: No
                             ・
                             ・
                             ・
         Executed_log_entries: 1
    Slave_received_heartbeats: 0
       Slave_heartbeat_period: 1800.000
               Gtid_Slave_Pos:
1 row in set (0.00 sec)

ここでまず一つ目のマスタのレプリケーション設定が出来ました。
続いて二つ目を同様の手順で実施。
「show all slaves status」で確認すると、

MariaDB [(none)]> show all slaves status \G;
*************************** 1. row ***************************
              Connection_name: mysql5.7-master1
              Slave_SQL_State:
               Slave_IO_State:
                  Master_Host: mysql5.7-master1
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 1006
               Relay_Log_File: mariadb10-relay-bin-mysql5@002e7@002dmaster1.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: No
            Slave_SQL_Running: No
                             ・
                             ・
                             ・
         Executed_log_entries: 1
    Slave_received_heartbeats: 0
       Slave_heartbeat_period: 1800.000
               Gtid_Slave_Pos:
*************************** 2. row ***************************
              Connection_name: mysql5.7-master2
              Slave_SQL_State:
               Slave_IO_State:
                  Master_Host: mysql5.7-master2
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 1006
               Relay_Log_File: mariadb10-relay-bin-mysql5@002e7@002dmaster2.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: No
            Slave_SQL_Running: No
                             ・
                             ・
                             ・
         Executed_log_entries: 1
    Slave_received_heartbeats: 0
       Slave_heartbeat_period: 1800.000
               Gtid_Slave_Pos:
2 rows in set (0.00 sec)

これでレプリケーションの設定も完了です。
スタートしてみます。

MariaDB [(none)]> start all slaves;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

MariaDB [(none)]> show all slaves status \G;
*************************** 1. row ***************************
              Connection_name: mysql5.7-master1
              Slave_SQL_State: Slave has read all relay log; waiting for the slave I/O thread to update it
               Slave_IO_State: Waiting for master to send event
                  Master_Host: mysql5.7-master1
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000005
          Read_Master_Log_Pos: 1006
               Relay_Log_File: mariadb10-relay-bin-mysql5@002e7@002dmaster1.000002
                Relay_Log_Pos: 411
        Relay_Master_Log_File: mysql-bin.000005
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                             ・
                             ・
                             ・
         Executed_log_entries: 6
    Slave_received_heartbeats: 0
       Slave_heartbeat_period: 1800.000
               Gtid_Slave_Pos:
*************************** 2. row ***************************
              Connection_name: mysql5.7-master2
              Slave_SQL_State: Slave has read all relay log; waiting for the slave I/O thread to update it
               Slave_IO_State: Waiting for master to send event
                  Master_Host: mysql5.7-master2
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 1006
               Relay_Log_File: mariadb10-relay-bin-mysql5@002e7@002dmaster2.000002
                Relay_Log_Pos: 411
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
                             ・
                             ・
                             ・
         Executed_log_entries: 6
    Slave_received_heartbeats: 0
       Slave_heartbeat_period: 1800.000
               Gtid_Slave_Pos:
2 rows in set (0.00 sec)

MariaDBでも「Slave_IO_Running」と「Slave_SQL_Running」がどちらも「YES」になりました！

後は確認です。

確認！！

マスタの1台目で以下を実施。

mysql> insert into item (id,name) values (3,"master1");
Query OK, 1 row affected (0.01 sec)

2台目で以下を実施。

mysql> insert into item (id,name) values (4,"master2");
Query OK, 1 row affected (0.01 sec)

スレーブで確認！

mysql> select * from item;
+------+---------+
| id   | name    |
+------+---------+
|    3 | master1 |
|    4 | master2 |
+------+---------+
2 rows in set (0.00 sec)

出来ました！

MySQLでもMariaDBでもマルチソースレプリケーションが比較的簡単に出来ることが判明しました。
今回はレプリケーションのみが目的だったので、今後はもっと実用的に使えるレベルまで持っていきたいです。

また今回少し触りましたが、あまりなじみがなかったのでMariaDBもどんどん触っていきたいと思います。

Join Us !

ウエディングパークでは、一緒に働く仲間を募集しています！
ご興味ある方は、お気軽にお問合せください（カジュアル面談から可）

採用情報を見る

MySQL 5.7のマルチソースレプリケーション機能を検証してみました

マルチソースレプリケーション

構築開始！

〔Master〕my.cnfの編集

〔Slave〕my.cnfの編集

〔Slave〕スレーブ・マスター接続！

確認！！

MariaDBでのマルチソースレプリケーション

スレーブ・マスター接続！

確認！！

クリエイターズブログ編集部

この著者の人気記事

Apacheで403 Forbiddenが表示された時のチェックポイント5選

Redmineをしっかり活用してチーム運用改善したら、チーム力がグンと上がった話

CentOS6でDNSサーバを構築してみた

データベースの人気記事

【MySQL】肥大化したInnoDBテーブルを圧縮機能で縮小する方法！

【DB設計入門|ER図|MySQL】コンビニレシートから学ぶ！データモデリング手法