Command disabled: index


̃Gg[͂ĂȃubN}[Nɒlj

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



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

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

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

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

今回の構成

マスターMySQL5.5.62
スレーブMySQL5.7.29

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

マスター側の設定

  • 専用ユーザー「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」は他サーバーと被らないように、例えば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)

重要

ここで別ターミナルから以下のコマンドでスナップショットを取る
ロックをかけた mysql のターミナルを閉じるとロックが解除されてしまうので注意!

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

# スナップショットを取り終わったらロックを解除する
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>;
注意復元用のDB(レプリカDB)のDB名<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 接続ができるか確認してください。


mysql コマンドによる接続確認(スレーブサーバーにて実行)

% mysql -urepli -p -h <IP_or_DOMAIN>
  • mysql プロンプトになれば問題なし


3306ポートがリッスンされているか確認(マスターサーバーにて実行)

% netstat -ln | grep 3306
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN
  • 上記のように表示されれば問題なし
    • 「0.0.0.0:3306」の部分が「127.0.0.1:3306」となっている場合はリモートサーバーからの接続ができない設定となっている


最終確認

マスター側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> : 通知先メールアドレス





database/mysql/multisource_replication.txt