MySQL :: レプリケーションの設定

MySQLでは、マスタースレーブ構成のレプリケーションを比較的簡単に実現できます。
参照系の負荷分散やホットバックアップとして有用な手段となります。

マスター側の設定

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

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

<IP_or_DOMAIN> : スレーブサーバーのIPアドレスまたはホスト名

確認

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=1001

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

server-id：他のMySQLサーバの server-id とかぶらないように設定する。設定値は 1 から 4294967295（2の32乗 - 1）
バイナリログは通常 datadir（/var/lib/mysql）に生成されます。

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.000001 |    55888 |              |                  |
+------------------+----------+--------------+------------------+
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.5.41/my.cnf） ← 個人的メモ

[mysqld]

# for replication
server-id=1002

# 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

マスター情報の登録

CHANGE MASTER TO
MASTER_HOST='<IP_or_DOMAIN>',
MASTER_USER='repli',
MASTER_PASSWORD='<PASSWORD>',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=55888;

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

実際にコマンドを実行した様子

mysql> CHANGE MASTER TO
    -> MASTER_HOST='<IP_or_DOMAIN>',
    -> MASTER_USER='repli',
    -> MASTER_PASSWORD='<PASSWORD>',
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=55888;
Query OK, 0 rows affected (0.02 sec)

レプリケーションの開始

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: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 55959
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 324
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          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: 55959
              Relay_Log_Space: 481
              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: 1001

※ 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/replication/setup.txt

文書の先頭へ