MySQL DB サーバは自身へのクエリをバイナリログとして書き出すことができます。レプリケーションとは、追加の MySQL DB サーバが、別の MySQL DB サーバが出力したバイナリログを自分のリレーログとよばれるログにコピーして、更にリレーログに記載されたクエリを自分自身のテーブルに実行する機能です。バイナリログを出力する DB をマスターとよび、自分のリレーログにコピーする DB をスレーブとよびます。マスターに対して発行したクエリは自動的にスレーブにも反映されることになります。スレーブは一台のマスターに対して複数台設定できます。クエリは UPDATE, INSERT, DELETE などの更新系と SELECT などの参照系に大別できます。Web アプリケーションのクエリは参照系が大部分であるため、例えばマスターには更新系のみを発行し、参照系は複数台のスレーブのうちの一台に行うという仕組みにすれば負荷分散が実現できます。あるいは、ゲームサーバのように更新系の割合が比較的高い場合でも、スレーブサーバを用意しておくことでマスターサーバのデータバックアップが実現でき、マスターがダウンした場合のフェイルオーバ先として使用できたりします。レプリケーション自体は MySQL 内の機能です。これを利用したフェイルオーバを自動で実現するための仕組みとしては MHA (Master High Availability) が有名です。

レプリケーションには 2015-1-24(Sat) 現在「非同期」と「準同期」の二種類があります。非同期は MySQL のレプリケーションにおける既定の設定です。準同期は MySQL 5.5 で新規に実装されました。準同期は semi-synchronization ともよばれます。

• 非同期: マスターは自分のバイナリログとテーブルにクエリを反映させる。そして、スレーブから「バイナリログとリレーログの同期がとれた」と連絡されるのを待たずにクエリを発行したクライアントに完了したと伝える
• 準同期: マスターは自分のバイナリログにクエリを反映させる。スレーブから「バイナリログとリレーログの同期がとれた」と連絡を受けてからマスターはクエリをテーブルに反映させる。その後クエリを発行したクライアントに完了したと伝える

準同期ではバイナリログとリレーログの同期が常にとれているため、フェイルオーバ先としての信頼性が高まります。マスターとスレーブにおけるログの不整合が発生しないためです。ただし、リレーログに反映されているだけでありそのリレーログがスレーブのテーブルにまで反映されているとは限りません。非同期か準同期かによらず、スレーブにおけるリレーログとテーブルの状態の遅延による差異は発生し得ります。スレーブがリレーログ内のクエリをテーブルに素早く実行し、スレーブのテーブルとマスターのテーブルが遅延なく同じ状態であることが理想です。この遅延は、マスターとスレーブのストレージエンジンが InnoDB である場合、例えば innodb_flush_log_at_trx_commit を既定値の 1 から 0 にすべての DB サーバで変更することで軽減できます。また、一概に準同期が非同期より優れている訳ではなく、例えば準同期には非同期と比較してクライアントへの応答速度が遅いというデメリットがあります。

レプリケーション設定の手順

マスターにおけるレプリケーションユーザの作成

スレーブがバイナリログを取得するために使用するユーザをマスターで作成します。スレーブの IP アドレスを指定して REPLICATION SLAVE 権限を付与します。サブネットワーク単位で指定することもできます。

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.33.0/255.255.255.0' IDENTIFIED BY 'replpass';
(or mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.33.102' IDENTIFIED BY 'replpass';)

作成されたことは例えば下記コマンドで確認できます。

mysql> show grants for 'repl'@'192.168.33.0/255.255.255.0';

+------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for repl@192.168.33.0/255.255.255.0                                                                                               |
+------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.33.0/255.255.255.0' IDENTIFIED BY PASSWORD '*D982...' |
+------------------------------------------------------------------------------------------------------------------------------------------+

マスターにおけるバイナリログの設定

/etc/my.cnf

[mysqld]
log-bin=mysql-bin
server-id=1001

### 以下は準同期を使用する場合のみ必要です
# プラグインのロード
plugin-load=rpl_semi_sync_master=semisync_master.so
# マスタを準同期として動作させてスレーブの応答を待つようにする
rpl_semi_sync_master_enabled=1
# スレーブからの応答がない場合に待つ限界時間 (ミリセカンド)
rpl_semi_sync_master_timeout=1000

再起動して設定を反映させます。

$ sudo service mysqld restart

準同期の場合、設定が反映されていることを確認します。状態を確認してみます。

mysql> SHOW GLOBAL STATUS LIKE 'Rpl_semi_sync%';

+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 0     |  ← 接続してきているクライアント数は 0 です
| Rpl_semi_sync_master_net_avg_wait_time     | 0     |
| Rpl_semi_sync_master_net_wait_time         | 0     |
| Rpl_semi_sync_master_net_waits             | 0     |
| Rpl_semi_sync_master_no_times              | 0     |
| Rpl_semi_sync_master_no_tx                 | 0     |
| Rpl_semi_sync_master_status                | ON    |  ← ここが ON になっています
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 0     |
| Rpl_semi_sync_master_tx_wait_time          | 0     |
| Rpl_semi_sync_master_tx_waits              | 0     |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 0     |
+--------------------------------------------+-------+

次に設定値を確認してみます。

mysql> SHOW GLOBAL VARIABLES LIKE 'rpl_semi_sync%';

+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled       | ON    |  ← ここが ON になっています
| rpl_semi_sync_master_timeout       | 1000  |
| rpl_semi_sync_master_trace_level   | 32    |
| rpl_semi_sync_master_wait_no_slave | ON    |
+------------------------------------+-------+

mysql> SHOW GLOBAL VARIABLES LIKE 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1001  |
+---------------+-------+

準同期であるか非同期であるかによらず、バイナリログは以下のディレクトリに出力されています。

$ sudo file /var/lib/mysql/mysql-bin.000001 
/var/lib/mysql/mysql-bin.000001: MySQL replication log

マスターの現在の状態を調査

マスターの現在の状態をバックアップとして取得してそれをスレーブに手動でコピーします。バックアップを作成した時点からレプリケーションを開始させます。バックアップ中にデータが更新されると不都合なためテーブルをロックします。

mysql> FLUSH TABLES WITH READ LOCK;

レプリケーションを開始する際に使用する現在の状態を表す情報を取得します。

mysql> SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      107 |              |                  |
+------------------+----------+--------------+------------------+

ログアウトするとロックが解除されるため例えば Ctrl-z でバックグラウンド処理に移します。以下のコマンドでバックアップを作成します。

$ mysqldump -uroot -p --all-databases --events --lock-all-tables > /tmp/dbdump.sql
$ fg
mysql> UNLOCK TABLES;

スレーブにデータを転送します。

$ scp /tmp/dbdump.sql username@192.168.33.102:/tmp/

テーブルロックせずに mysqldump を実行する (補足)

マルチバージョニングをサポートする InnoDB テーブルエンジンについてはロックせずにオンラインでのダンプが可能です。

$ mysqldump --master-data=2 --single-transaction --all-databases \
--events --skip-lock-tables -uroot -p > databases.dump

オプションの意味は mysqldump --help で確認できます。

• --master-data=2 (ダンプ時の binlog と position をダンプ結果にコメントとして挿入)
• --single-transaction (ダンプ中に別トランザクションで更新があっても問題ないように独自のトランザクションを張ります)
• --all-databases --events (すべての DB をダンプしたい場合)
• --skip-lock-tables (念の為。テーブルロックしない宣言)

binlog と position を確認してみましょう。

$ less databases.dump
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005', MASTER_LOG_POS=26839756;
...

MySQL 5.5 以降では --dump-slave オプションが利用できます。これはスレーブを複製するためにスレーブでダンプを取得するオプションです。ダンプ前に stop slave が実行されてダンプが再開されると start slave されます。ダンプ中はレプリケーションが停止することに注意してください。

$ mysqldump --dump-slave=2 --single-transaction --all-databases \
--events --skip-lock-tables -uroot -p > databases.dump

スレーブにおけるレプリケーション設定

既にレプリケーションしている場合は停止しておきます。

mysql> STOP SLAVE;

設定ファイルを編集します。

/etc/my.cnf

[mysqld]
server-id=1002

# 通常のユーザによって更新系のクエリを実行できないようにする
read_only

### 以下は準同期を使用する場合のみ必要です
# プラグインのロード
plugin-load=rpl_semi_sync_slave=semisync_slave.so
# 準同期のスレーブとして動作させる
rpl_semi_sync_slave_enabled=1

再起動して設定を反映させます。

$ sudo service mysqld restart

マスターで取得したバックアップを読み込みます。

$ mysql -uroot -p < /tmp/dbdump.sql

スレーブをマスターに向けます。MASTER_LOG_FILE と MASTER_LOG_POS にはマスターで SHOW MASTER STATUS して取得した値を記載します。

mysql> CHANGE MASTER TO
       MASTER_HOST='192.168.33.101',
       MASTER_USER='repl',
       MASTER_PORT=3306,
       MASTER_PASSWORD='replpass',
       MASTER_LOG_FILE='mysql-bin.000001',
       MASTER_LOG_POS=107;

以下のコマンドでレプリケーションを開始します。

mysql> START SLAVE;

my.cnf に server-id が適切に設定されていなかったりするとエラーが出ます。

mysql> START SLAVE;
ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO

スレーブの状態を確認してみましょう。

mysql> SHOW GLOBAL VARIABLES LIKE 'server_id';

+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1002  |
+---------------+-------+

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.33.101
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 820
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 966
        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: 820
              Relay_Log_Space: 1123
              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

もしも以下のようなエラーが発生したら、そもそもネットワーク的に接続できない可能性などがあります。

Last_IO_Error: error connecting to master 'repl@192.168.33.101:3306' - retry-time: 60  retries: 86400

以下のコマンドで疎通確認を行ってみるとよいです。ちなみに TCP 3306 は mysql のポートです。

$ telnet 192.168.33.101 3306
Trying 192.168.33.101...
Connected to 192.168.33.101.
Escape character is '^]'.
GHost '192.168.33.102' is not allowed to connect to this MySQL serverConnection closed by foreign host.
↑ アクセスできない場合の出力例

$ mysql -urepl -h 192.168.33.101 -p
Enter password: 
ERROR 1130 (HY000): Host '192.168.33.102' is not allowed to connect to this MySQL server
↑ アクセスできない場合の出力例

マスター側でもポートを LISTEN しているかを確認しましょう。

$ netstat -ltn

Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address               Foreign Address             State
tcp        0      0 0.0.0.0:55948               0.0.0.0:*                   LISTEN
tcp        0      0 0.0.0.0:111                 0.0.0.0:*                   LISTEN
tcp        0      0 0.0.0.0:22                  0.0.0.0:*                   LISTEN
tcp        0      0 127.0.0.1:25                0.0.0.0:*                   LISTEN
tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LISTEN   ← 正常な場合の出力例

さて、準同期の場合は、更に以下のコマンドでスレーブの準同期に関する状態を確認してみましょう。

mysql> SHOW GLOBAL STATUS LIKE 'Rpl_semi_sync%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Rpl_semi_sync_slave_status | ON    |  ← ここが ON になっています
+----------------------------+-------+

mysql> SHOW GLOBAL VARIABLES LIKE 'rpl_semi_sync%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| rpl_semi_sync_slave_enabled     | ON    |  ← ここが ON になっています
| rpl_semi_sync_slave_trace_level | 32    |
+---------------------------------+-------+

マスター側にもクライアントが認識されています。先程紹介した以下のコマンドをマスターサーバで実行してみてください。

mysql> SHOW GLOBAL STATUS LIKE 'Rpl_semi_sync%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 1     |   ← 先程 0 だったのが 1 になっています
...

この状態でマスターにおいて更新系のクエリ CREATE などを実行するとスレーブにも反映されます。例えばデータベース作成をするとマスターのバイナリログ (BINLOG) のイベントとして記録されます。

mysql> SHOW BINLOG EVENTS;
(or mysql> SHOW BINLOG EVENTS IN 'mysql-bin.000001';)
+------------------+-----+-------------+-----------+-------------+------------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                     |
+------------------+-----+-------------+-----------+-------------+------------------------------------------+
| mysql-bin.000001 |   4 | Format_desc |      1001 |         107 | Server ver: 5.5.41-log, Binlog ver: 4    |
| mysql-bin.000001 | 107 | Query       |      1001 |         178 | FLUSH TABLES                             |
| mysql-bin.000001 | 178 | Query       |      1001 |         820 | create database my_new_database          |
+------------------+-----+-------------+-----------+-------------+------------------------------------------+

三段レプリケーション

更に追加で DB サーバを用意して、上記手順で用意したスレーブをマスターとするレプリケーションを行うことができます。上流から下流までレプリケーションが伝搬していきます。ただし、現状 slave1 と slave2 を準同期させることはできません。

192.168.33.101 (master) → 192.168.33.102 (slave1) → 192.168.33.103 (slave2)

slave1 の設定

192.168.33.102:/etc/my.cnf

[mysqld]
...
log-bin=mysql-bin   ← 追記
log_slave_updates   ← 追記

master と slave1 が準同期されている場合は slave1 と slave2 を準同期させることができないため、以下の設定を入れないようにしましょう。

plugin-load=rpl_semi_sync_master=semisync_master.so
rpl_semi_sync_master_enabled=1
rpl_semi_sync_master_timeout=1000

slave1 の mysqld 再起動

$ sudo service mysqld restart

slave2 がレプリケーションを開始するポイントの確認および DB ダンプ

mysql> STOP SLAVE;
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.33.0/255.255.255.0' IDENTIFIED BY 'replpass';
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      347 |              |                  |
+------------------+----------+--------------+------------------+
$ mysqldump -uroot -p --all-databases --events --lock-all-tables > /tmp/dbdump.sql
$ scp /tmp/dbdump.sql username@192.168.33.103:/tmp/
$ fg
mysql> UNLOCK TABLES;
mysql> START SLAVE;

slave2 の設定

mysql> STOP SLAVE;

192.168.33.103:/etc/my.cnf

[mysqld]
server-id=1003
read_only

master と slave1 が準同期されている場合は slave1 と slave2 を準同期させることができないため、以下の設定を入れないようにしましょう。

plugin-load=rpl_semi_sync_slave=semisync_slave.so
rpl_semi_sync_slave_enabled=1

再起動による設定の反映

$ sudo service mysqld restart

レプリケーション設定

$ mysql -uroot -p < /tmp/dbdump.sql
mysql> CHANGE MASTER TO
       MASTER_HOST='192.168.33.102',
       MASTER_USER='repl',
       MASTER_PORT=3306,
       MASTER_PASSWORD='replpass',
       MASTER_LOG_FILE='mysql-bin.000002',
       MASTER_LOG_POS=347;
mysql> START SLAVE;

レプリケーションの無効化

レプリケーションを一時的に無効化

マスターにおいて以下のコマンドを実行しておきます。

mysql> SELECT @@SQL_LOG_BIN;
+---------------+
| @@SQL_LOG_BIN |
+---------------+
|             1 |
+---------------+
mysql> SET SQL_LOG_BIN = 0;
mysql> SELECT @@SQL_LOG_BIN;
+---------------+
| @@SQL_LOG_BIN |
+---------------+
|             0 |
+---------------+

この状態で発行したクエリはバイナリログに記載されず、結果としてレプリケーションされません。作業が終了したら

mysql> SET SQL_LOG_BIN = 1;

によって元に戻しておきましょう。

特定のテーブルをレプリケーション対象外とする

スレーブの設定を追加します。

/etc/my.cnf

[mysqld]
...(略
replicate-wild-ignore-table=mysql.%

上記の設定をしておくと mysql データベースに関するクエリはスレーブ側で無視されます。「SET SQL_LOG_BIN = 1」の場合と異なりマスターのバイナリログには記載されます。

バイナリログの操作コマンド

バイナリログの一覧表示

mysql> SHOW MASTER LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       107 |
+------------------+-----------+
1 row in set (0.00 sec)

ログローテーション

mysql> FLUSH LOGS;
Query OK, 0 rows affected (0.03 sec)

mysql> SHOW MASTER LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       150 |
| mysql-bin.000002 |       107 |
+------------------+-----------+
2 rows in set (0.00 sec)

ログの削除

mysql> PURGE MASTER LOGS TO 'mysql-bin.000002';
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW MASTER LOGS;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000002 |       107 |
+------------------+-----------+
1 row in set (0.00 sec)

MySQL binlog の内容を閲覧 (mysqlbinlog)

mysql> CREATE DATABASE mydb;
mysql> DROP DATABASE mydb;

上記 SQL 文の binlog を閲覧してみます。

$ sudo mysqlbinlog /var/lib/mysql/mysql-bin.000001 | less
...
# at 107  ← ★
#150421 12:59:47 server id 1001  end_log_pos 190        Query   thread_id=4     exec_time=0     error_code=0
SET TIMESTAMP=1429621187/*!*/;
SET @@session.pseudo_thread_id=4/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, 
@@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create database mydb
/*!*/;
# at 190  ← ★
#150421 13:01:08 server id 1001  end_log_pos 271        Query   thread_id=5     exec_time=0     error_code=0
SET TIMESTAMP=1429621268/*!*/;
drop database mydb
/*!*/;
...

ヘッダーの読み方を記載します (man mysqlbinlog より)

• 「at 107」→ binlog ポジション
• 「150421 12:59:47」→ 2015/04/21 12:59:47 (UTC) に発生したイベント
• 「server id 1001」→ イベントが発生した MySQL サーバーの ID
• 「end_log_pos 190」→ 次のイベントの binlog ポジション (今回のイベントの終了ポジション + 1)