- Looker から Snowflake への OAuth 接続
- Looker (Google Cloud core) 30日間 フリートライアル設定
- AWS 落穂拾い (Security)
- AWS 落穂拾い (Storage)
- マイクロマウス
概要
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 ともよばれます。yum レポジトリに MySQL 5.5 を追加する方法はこちらをご参照ください。
- 非同期: マスターは自分のバイナリログとテーブルにクエリを反映させる。そして、スレーブから「バイナリログとリレーログの同期がとれた」と連絡されるのを待たずにクエリを発行したクライアントに完了したと伝える
- 準同期: マスターは自分のバイナリログにクエリを反映させる。スレーブから「バイナリログとリレーログの同期がとれた」と連絡を受けてからマスターはクエリをテーブルに反映させる。その後クエリを発行したクライアントに完了したと伝える
準同期ではバイナリログとリレーログの同期が常にとれているため、フェイルオーバ先としての信頼性が高まります。マスターとスレーブにおけるログの不整合が発生しないためです。ただし、リレーログに反映されているだけでありそのリレーログがスレーブのテーブルにまで反映されているとは限りません。非同期か準同期かによらず、スレーブにおけるリレーログとテーブルの状態の遅延による差異は発生し得ります。スレーブがリレーログ内のクエリをテーブルに素早く実行し、スレーブのテーブルとマスターのテーブルが遅延なく同じ状態であることが理想です。この遅延は、マスターとスレーブのストレージエンジンが 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 や telnet についてはこちらをご参照ください。
$ 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 |
+------------------+-----+-------------+-----------+-------------+------------------------------------------+
三段レプリケーション
MySQL レプリケーション設定 (2段, 3段)
