MariaDB Multi-Source Replicationを使ってサービス無停止でDBサーバーを統合する

次世代システム研究室の Y.I. です。

MariaDB のマルチソースレプリケーション (Multi-source replication) について、まとめます。

マルチソースレプリケーションとは、複数の DB をレプリケーション元であるマスター DB として１つのスレーブ DB にレプリケーションする機能です。

以前は、１つのスレーブ DB に対して１つのマスター DB しか指定できませんでした。それが以下のバージョンから複数のマスター DB からレプリケーションする事が可能となりました。

・MariaDB では、 version 10.0 から
・MySQL では、version 5.7 から

マルチソースレプリケーションの利用シーンは、
・複数台に分割されている DB server を１つにまとめた DB を作り集計に利用する
・DB サーバーの統合/削減に利用する
※ 要件があえばサービス無停止でDBサーバーを統合できます
などが考えられます。

今回は、「サービス無停止で　DB サーバーの統合/削減に利用する」想定でマルチソースレプリケーションを設定して、サービス無停止（イメージ）で DB 統合してみましょう。

構成

mysql,mariadb muliti-source replication image

MariaDB 10.4.10 (執筆時点の最新バージョン)

db-server 3台
- s01(server01) マスターDB
- s02(server02) マスターDB
- s03(server03) スレーブDB
- ※ s01およびs02 から s03 へレプリケーション
database schema
- d01(database01)　s01-serverにて作成するDB　s03-serverへレプリケーションされる
- d02(database02)　s01-serverにて作成するDB　s03-serverへレプリケーションされる
- d03(database03)　s03-serverにて作成するDB
replication 設定 (my.cnf)
- GTID を利用 (デフォルト有効)
- gtid_domain_id を設定
- server_id を設定
- binlog_format=MIXED
- log_slave_updates=1

my.cnf 設定例
[mysqld]
gtid_domain_id=1
server_id=1
binlog_format=MIXED
log_bin=mysql-bin
log_slave_updates=1

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

まとめ

簡単にまとめると、スレーブDB (s03) にて２つのレプリケーション設定を行うだけです。

– 複数マスター(s01, s02)の GTID を設定

SET GLOBAL gtid_slave_pos = "1-1-1,2-2-1";

※ GTID の値はマスターDB(s01, s02)にて 
　[show master status] および 
　[select BINLOG_GTID_POS('{binlog file name}', {position})] で調べられます

– default_master_connection を設定 (s01用)

set @@default_master_connection='d01';

– CHANGE MASTER で default_master_connection を指定 (s01用)

CHANGE MASTER 'd01' TO
MASTER_HOST = '192.168.0.1',
MASTER_USER = 'repl',
MASTER_PASSWORD = '{repl userのパスワード}',
MASTER_USE_GTID = slave_pos
;

– default_master_connection を設定 (s02用)

set @@default_master_connection='d02';

– CHANGE MASTER で default_master_connection を指定 (s02用)

CHANGE MASTER 'd02' TO
MASTER_HOST = '192.168.0.2',
MASTER_USER = 'repl',
MASTER_PASSWORD = '{repl userのパスワード}',
MASTER_USE_GTID = slave_pos
;

– レプリケーション開始

START ALL SLAVES;

– レプリケーション設定確認
成功すると以下の様に２つのレプリケーション設定（d01, d02）が動作しているのを確認できます。

mysql d03 (none) show all slaves status\G
*************************** 1. row ***************************
               Connection_name: d01
               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: 192.168.33.1
                   Master_User: repl
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000001
           Read_Master_Log_Pos: 850
                Relay_Log_File: d03-relay-bin-d01.000002
                 Relay_Log_Pos: 670
         Relay_Master_Log_File: mysql-bin.000001
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
　・・・省略・・・
*************************** 2. row ***************************
               Connection_name: d02
               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: 192.168.33.2
                   Master_User: repl
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000001
           Read_Master_Log_Pos: 328
                Relay_Log_File: d03-relay-bin-d02.000002
                 Relay_Log_Pos: 627
         Relay_Master_Log_File: mysql-bin.000001
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
　・・・省略・・・
2 rows in set (0.000 sec)

以上で、s01/s02 から s03　へのマルチソースレプリケーションが設定できました。

マルチソースレプリケーションで気をつけること

– slave 系コマンドが slaves 複数形

show slave status -> show all slaves status
start slave -> start all slaves
stop slave -> stop all slaves

– gtid_domain_id が同じだとエラー

mysql d03 (none) SET GLOBAL gtid_slave_pos = "0-1-5,0-2-3";
ERROR 1943 (HY000): GTID 0-2-3 and 0-1-5 conflict (duplicate domain id 0)

=>マスターDB(s01, s02) で異なる domain_id を設定しましょう

– 現在の default_master_connection が何か意識しましょう

設定
set @@default_master_connection='s01';
参照
select @@default_master_connection;

database/table 作成

以下の database/table を作成して以降でマスターDB切り替えを行なっていきます。

– database

@s01
CREATE DATABASE IF NOT EXISTS d01 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

@s02
CREATE DATABASE IF NOT EXISTS d02 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

@s03 に d01 および d02 DATABASE がレプリケーションされる

– table

@s01
CREATE TABLE IF NOT EXISTS d01.t1 (id int not null auto_increment, s varchar(30), PRIMARY KEY (id));

@s02
CREATE TABLE IF NOT EXISTS d02.t2 (id int not null auto_increment, s varchar(30), PRIMARY KEY (id));

@s03 に d01.t1 および d02.t2 TABLE がレプリケーションされる

マスターDB切り替え

mysql,mariadb multi-source replication change master image

ここからは、サービス無停止をイメージして、 s01とs02 のマスター DB を s03 へ切り替えてみましょう。データ登録先 DB を s01/s02 から s03 へ変更します。キモは、レプリケーションエラーを発生させない事です。そのため切り替え後マスター DB (s03)にて id が重複しない様に auto_increment 値を増やしておきます。無事に登録先 DB が s03 へ切り替わり s01 へのアクセスがないことを確認したら s01 サーバーを破棄できます。

マスター DB を s01 から s03 へ切り替えます（成功する手順）

– s01 にて1レコード登録

@s01
mysql s01 d01 insert into t1 values (null, 'マスターDB切り替えBefore t1');
Query OK, 1 row affected (0.001 sec)

– （確認）s03 にレプリケーションされている

@s03
mysql s03 (none) select * from d01.t1;
+----+-------------------------------------+
| id | s                                   |
+----+-------------------------------------+
|  1 | マスターDB切り替えBefore t1         |
+----+-------------------------------------+
1 row in set (0.000 sec)

– s03 にて t1 テーブルの auto_increment を増やす（=データ重複回避のため）

ALTER TABLE d01.t1 AUTO_INCREMENT=1002;

– （確認）AUTO_INCREMENT=1002 になっている（=s03のd01.t1へinsertすると id が1002で登録される様になった）

mysql s03 (none) show create table d01.t1;
+-------+----------------------------------+
| Table | Create Table  | t1    | CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `s` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1002 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin |
+-------+----------------------------------+
1 row in set (0.000 sec)

– s01 にてデータ登録（=アプリケーション側のDB切り替えが行われる前のイメージ）

@s01
mysql s01 d01 insert into t1 values (null, '02.マスターDB切り替えBefore s01 t1');
Query OK, 1 row affected (0.012 sec)

– （確認）s03 にレプリケーションされている

mysql s03 (none) select * from d01.t1;
+----+--------------------------------------------+
| id | s                                          |
+----+--------------------------------------------+
|  1 | マスターDB切り替えBefore t1                |
|  2 | 02.マスターDB切り替えBefore s01 t1         |
+----+--------------------------------------------+
2 rows in set (0.000 sec)

– s03 へデータ登録 (=登録先マスターDBをs01からs03へ切り替え完了したアプリケーションからの登録イメージ)

@s03
mysql s03 (none) insert into d01.t1 values (null, '03.マスターDB切り替えAfter @s03 t1');
Query OK, 1 row affected (0.007 sec)

– （確認）id 1002 で登録されている（=s01で登録されレプリケーションされたデータとid重複していない）

mysql s03 (none) select * from d01.t1;
+------+--------------------------------------------+
| id   | s                                          |
+------+--------------------------------------------+
|    1 | マスターDB切り替えBefore t1                |
|    2 | 02.マスターDB切り替えBefore s01 t1         |
| 1002 | 03.マスターDB切り替えAfter @s03 t1         |
+------+--------------------------------------------+
3 rows in set (0.000 sec)

– s01 へデータ登録 (=登録先マスターDBを切り替え完了していないアプリケーションからの登録イメージ)

@s01
mysql s01 d01 insert into t1 values (null, '04.マスターDB切り替えBefore @s01 t1');
Query OK, 1 row affected (0.001 sec)

– （確認）id 3 で登録されて s01 から s03 へレプリケーションされている

mysql s03 (none) select * from d01.t1;
+------+---------------------------------------------+
| id   | s                                           |
+------+---------------------------------------------+
|    1 | マスターDB切り替えBefore t1                 |
|    2 | 02.マスターDB切り替えBefore s01 t1          |
|    3 | 04.マスターDB切り替えBefore @s01 t1         |
| 1002 | 03.マスターDB切り替えAfter @s03 t1          |
+------+---------------------------------------------+
4 rows in set (0.000 sec)

– s03 へデータ登録 (=全てのアプリケーションの登録先マスターDBがs03へ切り替え完了した登録イメージ)

mysql s03 (none) insert into d01.t1 values (null, '05.マスターDB切り替えAfter @s03 t1');
Query OK, 1 row affected (0.002 sec)

– （確認）id 1003 で登録されている（=s01で登録されレプリケーションされたデータとid重複していない）

mysql s03 (none) select * from d01.t1;
+------+---------------------------------------------+
| id   | s                                           |
+------+---------------------------------------------+
|    1 | マスターDB切り替えBefore t1                 |
|    2 | 02.マスターDB切り替えBefore s01 t1          |
|    3 | 04.マスターDB切り替えBefore @s01 t1         |
| 1002 | 03.マスターDB切り替えAfter @s03 t1          |
| 1003 | 05.マスターDB切り替えAfter @s03 t1          |
+------+---------------------------------------------+
5 rows in set (0.000 sec)

マスター DB を s02 から s03 へ切り替えます（こちらは失敗させてみます）

– s03 にてinsertしてrepエラー待ち（= auto_increment　を増やさない）

mysql s03 (none)> insert into d02.t2 values (null, '02.マスターDB切り替えAfter s03 t2');
Query OK, 1 row affected (0.001 sec)

– （確認）s03 に id 2 で登録されている (=s01ではid 1のデータのみ)

mysql s03 (none)> select * from d02.t2;
+----+-------------------------------------------+
| id | s                                         |
+----+-------------------------------------------+
|  1 | マスターDB切り替えBefore t2               |
|  2 | 02.マスターDB切り替えAfter s03 t2         |
+----+-------------------------------------------+
2 rows in set (0.000 sec)

– s02 にて insert して s03 へレプリケーションされる

mysql s02 d02> insert into t2 values (null, '03.マスターDB切り替えBefore s02 t2');
Query OK, 1 row affected (0.013 sec)

– （確認）s02 にて id 2 で登録されている

mysql s02 d02> select * from d02.t2;
+----+--------------------------------------------+
| id | s                                          |
+----+--------------------------------------------+
|  1 | マスターDB切り替えBefore t2                |
|  2 | 03.マスターDB切り替えBefore s02 t2         |
+----+--------------------------------------------+
2 rows in set (0.000 sec)

– （確認）s03 へ登録データがレプリケーションされていない

mysql s03 (none) select * from d02.t2;
+----+-------------------------------------------+
| id | s                                         |
+----+-------------------------------------------+
|  1 | マスターDB切り替えBefore t2               |
|  2 | 02.マスターDB切り替えAfter s03 t2         |
+----+-------------------------------------------+
2 rows in set (0.000 sec)

– （確認）s03 のレプリケーション Connecion_name s02 が Pkey 重複によりエラー

mysql s03 (none) show all slaves status\G
*************************** 1. row ***************************
               Connection_name: s01
               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: 192.168.33.1
                   Master_User: repl
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000002
           Read_Master_Log_Pos: 1196
                Relay_Log_File: s03-relay-bin-s01.000002
                 Relay_Log_Pos: 1495
         Relay_Master_Log_File: mysql-bin.000002
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
                    Last_Errno: 0
                    Last_Error:
　・・・省略・・・
*************************** 2. row ***************************
               Connection_name: s02
               Slave_SQL_State:
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 192.168.33.2
                   Master_User: repl
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000002
           Read_Master_Log_Pos: 829
                Relay_Log_File: s03-relay-bin-s02.000002
                 Relay_Log_Pos: 883
         Relay_Master_Log_File: mysql-bin.000002
              Slave_IO_Running: Yes
             Slave_SQL_Running: No
               Replicate_Do_DB: d02,d02a,d02b
           Replicate_Ignore_DB:
            Replicate_Do_Table:
        Replicate_Ignore_Table:
       Replicate_Wild_Do_Table:
   Replicate_Wild_Ignore_Table:
                    Last_Errno: 1062
                    Last_Error: Error 'Duplicate entry '2' for key 'PRIMARY'' on query. Default database: 'd02'. Query: 'insert into t2 values (null, '03.マスターDB切り替えBefore s02 t2')'
                  Skip_Counter: 0
　・・・
                Last_SQL_Errno: 1062
                Last_SQL_Error: Error 'Duplicate entry '2' for key 'PRIMARY'' on query. Default database: 'd02'. Query: 'insert into t2 values (null, '03.マスターDB切り替えBefore s02 t2')'
　・・・
2 rows in set (0.000 sec)

参考

MariaDB 公式サイト Multi-Source Replication
https://mariadb.com/kb/en/library/multi-source-replication/

最後に

マルチソースレプリケーションのイメージは伝わりましたでしょうか？　MySQL（MariaDB）は以前からレプリケーション機能が優れていて、色々な構成を取れる様にレプリケーションを扱えると運用の幅が広がります。マスターDB（1）：スレーブDB（N）（=スレーブからみて1：1）の基本構成とマルチソースレプリケーション（N:N）構成をとれるのでユースケースに応じての利用をおすすめします。

次世代システム研究室では、グループ全体のインテグレーションを支援してくれるアーキテクトを募集しています。アプリケーション開発の方、次世代システム研究室にご興味を持って頂ける方がいらっしゃいましたら、ぜひ募集職種一覧からご応募をお願いします。

皆さんのご応募をお待ちしています。

MariaDB Multi-Source Replicationを使ってサービス無停止でDBサーバーを統合する

構成

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

まとめ

マルチソースレプリケーションで気をつけること

database/table 作成

マスターDB切り替え

マスター DB を s01 から s03 へ切り替えます（成功する手順）

マスター DB を s02 から s03 へ切り替えます（こちらは失敗させてみます）

参考

最後に

関連記事

TiDBのREPEATABLE READの挙動

Galera Cluster を無停止で新規ノードにデータを完全同期する

MySQL でシンプルな排他制御を　GET_LOCK　で実現する！

MySQL5.7の全文検索を試してみた

MariaDB Multi-Source Replicationを使ってサービス無停止でDBサーバーを統合する

構成

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

まとめ

マルチソースレプリケーションで気をつけること

database/table 作成

マスターDB切り替え

マスター DB を s01 から s03 へ切り替えます（成功する手順）

マスター DB を s02 から s03 へ切り替えます（こちらは失敗させてみます）

参考

最後に

関連記事

TiDBのREPEATABLE READの挙動

Galera Cluster を無停止で新規ノードにデータを完全同期する

MySQL でシンプルな排他制御を GET_LOCK で実現する！

MySQL5.7の全文検索を試してみた

MySQL でシンプルな排他制御を　GET_LOCK　で実現する！