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

異なったホスト上で稼働する複数DBのマスターに対して、一箇所のスレーブ（一台のスレーブサーバー）に集約してレプリケーションを行うことができる機能です。（MySQL5.7からの新機能）
従来のレプリケーションでは、マスタとスレーブの関係が「1:N」しか実現できませんでしたがマルチソースレプリケーションでは「N:1」が可能となります。
後述する「チャンネル」という概念によって、単一のスレーブから複数のマスターに対してレプリケーション用コネクションを張ることができます。

関連リンク： MySQL5.7 のインストール

• 専用ユーザー「repli」の作成

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repli'@'<IP_or_DOMAIN>' IDENTIFIED BY '<PASSWORD>';

• <IP_or_DOMAIN> : スレーブサーバーのIPアドレスまたはホスト名
• <PASSWORD>: repliユーザーのパスワードを設定

• 確認

mysql> select user, host from mysql.user where user = 'repli';
+-------+----------------+
| user  | host           |
+-------+----------------+
| repli | <IP_or_DOMAIN> |
+-------+----------------+

• my.cnf に設定を追記

% sudo vi /etc/my.cnf

[mysqld]

  :

# バイナリログのプレフィクス
log-bin=mysql-bin

# サーバーID
server-id=184140

# バイナリログのローテート日数
expire_logs_days=3

• 「server-id」は自サーバーのIDを設定し、他サーバーと被らないようにします。例えばIPアドレスの下6桁を使うなどで工夫します（設定可能な値は 1 から 4294967295 まで）

• MySQL 再起動

% sudo /etc/init.d/mysqld restart

• DBスナップショットの作成

# まず全DBの全テーブルをロックする（更新系クエリのみロックされ、参照系はロックされない）
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)

# マスターステータスの確認
#  -> File と Position を確認する（メモしておく）
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      107 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

% mysqldump -uroot -p<PASSWORD> --lock-all-tables <DB_NAME> > snapshot.dump

- OR -

% mysqldump -h 127.0.0.1 --column-statistics=0 -uroot -p --lock-all-tables <DB_NAME> > snapshot.dump
 -> <PASSWORD>

# スナップショットを取り終わったらロックを解除する
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

• my.cnf に設定を追記

% sudo vi /etc/my.cnf

• （% sudo vi /usr/local/mysql-5.7.29/my.cnf） ← 個人的メモ

[mysqld]

  :

# for replication
server-id=5729
relay_log_info_repository = TABLE
master_info_repository = TABLE
relay_log_recovery = ON
relay_log_purge=ON

# replicate-do-db
replicate-do-db=<DB1_NAME>
replicate-do-db=<DB2_NAME> # 複数の場合

# replicate-ignore-db
replicate-ignore-db=<DB3_NAME>
replicate-ignore-db=<DB4_NAME> # 複数の場合

• server-id : マスターのIDと重複しないようにする

• replicate-do-db : レプリケーション対象のデータベース名
• replicate-ignore-db : レプリケーションから除外するデータベース名
  • 上記ともに、複数の場合はその分記述する（1行に1DBの設定）
    • すべてのDBをレプリケーションする場合、これらの設定は不要

• MySQL 再起動

% sudo /etc/init.d/mysqld restart

• scp にてマスター側に保存したスナップショットを転送する

% scp <USER>@<IP_or_DOMAIN>:~/shapshot.dump .

• スレーブ用のDBを作成

mysql> create database <DB_NAME>;

• スナップショットを復元

% mysql -uroot -p <DB_NAME> < shapshot.dump

• マスター情報の登録

mysql> CHANGE MASTER TO
  MASTER_HOST='<IP_or_DOMAIN>',
  MASTER_USER='repli',
  MASTER_PASSWORD='<PASSWORD>',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000002',
  MASTER_LOG_POS=107,
  MASTER_CONNECT_RETRY=10
  for channel '<CHANNEL_NAME>';

※ MASTER_LOG_FILE(mysql-bin.000002) と MASTER_LOG_POS(107) はメモしておいた値を設定する

• channel

    for channel '<CHANNEL_NAME>';

上記の「channel」がマルチソースレプリケーションの肝で、これが設定できるおかげで、複数ホストに別れた別々のマスターとのレプリケーションを可能にしています。

• レプリケーションの開始

mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

• スレーブステータスの確認

mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: <IP_or_DOMAIN>
                  Master_User: repli
                  Master_Port: 3306
                Connect_Retry: 10
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 2250
               Relay_Log_File: <RELAY_LOG_FILE_NAME>
                Relay_Log_Pos: 304
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: <DB_NAME>
          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: 2250
              Relay_Log_Space: 544
              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: 184140
                  Master_UUID: 
             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: <CHANNEL_NAME>
           Master_TLS_Version: 

• ※ 1行目が「Waiting for master to send event」となれば待機中を意味する

error connecting to master 'repli@<IP_or_DOMAIN>:3306' - retry-time: 60  retries: 86400

% mysql -urepli -p -h <IP_or_DOMAIN>

% netstat -ln | grep 3306
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN

マスター側DBのレコードを適当に追加・更新して、スレーブに伝搬されているか確かめてみる

簡易的ですが、cron にて定期的にレプリケーションの動作監視を行うワンライナーになります。
レプリケーションにエラーがあると指定のメールアドレスに通知します。（本ワンライナーでは、1時間に1回の監視）

• スレーブサーバーの /etc/cron.d/ 配下に cronファイルを設定する前提となります。

# Check mysql replication
0 * * * * <USER> mysql -uroot -p<PASSWORD> -e 'show slave status \G' | grep -E 'Last_(IO|SQL)_Errno: 0' | wc -l | (read cnt; if test $cnt -ne 2; then mail -s 'Replication is not running!' <E-MAIL> > /dev/null; fi)

• <USER> : ワンライナーを実行するユーザー
• <PASSWORD> : mysql ユーザーのパスワード
• <E-MAIL> : 通知先メールアドレス