[MySQL5.7]マルチソースレプリケーション設定方法
![[MySQL5.7]マルチソースレプリケーション設定方法](https://nullpopopo.blogcube.info/wp-content/uploads/2015/10/mysql57_20151025_0002-1.png)
2015/10/21に、MySQL5.7(5.7.9) Major Versionが正式リリースされましたね。貧乏な私はMySQL 5.6の3倍の性能と言われましてもプアーなマシンでしか動かせないのでじっと指を咥えて他人様のベンチ結果を見守ることにします。 (´･_･`)
さて、MySQL 5.7の目玉機能といえば、マルチソースレプリケーションではないでしょうか。これが実現できることで何が嬉しいかといいますと、例えばバラバラと構築したマスターがn台あったとしてnx2台のスレーブ作る必要がなく、1〜2台の大きなスレーブに集約できたりするわけです。(んで、behindがなくなったらこいつを新マスターにしてしまえる！) というわけで、さくらのクラウドでCentOS7のサーバーを構築し、マルチソースレプリケーションを試してみました。ネットワーク構成は以下になります。
| master0 | 192.168.0.230 |
| master1 | 192.168.0.231 |
| slave0 | 192.168.0.232 |
| slave1 | 192.168.0.233 |
プライベートIPアドレスはeth1に振っています。さくらのクラウドでCentOS7のeth1にIPアドレスを振る手順はこちらを参考にどうぞ。
今回構築するサーバーの、マスターとスレーブの関係は以下の通りです。
- master0 は test0 データベースのマスター。チャネルは0
- master1 は test1 データベースのマスター。チャネルは1
- slave0 は 上記すべてのデータベースのスレーブになる
- slave1 は 上記すべてのデータベースのスレーブになる
ちなみに、マルチソースレプリケーションはマルチマスターレプリケーションとイコールではありません。 こちらの図表を見ると違いが一目瞭然ですが、マルチマスターレプリケーションは循環型のレプリケーションで、SQLクライアントはどのノードに書き込みを行っても、レプリケーションを構成するすべてのノードに等しくデータが同期されます。いっぽう、マルチソースレプリケーションは上記のDB構成図のように、1つのスレーブが複数のマスターを持つことができる仕組みです。
■ MySQLサーバーを「ほぼ」一撃構築する
Master Slaveともに共通の設定をこちらの一撃シェルスクリプトをもとに作成します。スクリプトの文字数が10000文字を超えてしまったのでスタートアップスクリプトに登録できなかった。残念！
最初に変数「INTERFACE」「IPOCT4」を指定していますが、前者はipコマンドでループバックインターフェイス以外の一覧から先頭のデバイスを検出し、IPv4アドレスの最後のオクテットの数字を抜き出しています。これを「NEWHOSTNAME」変数でホスト名にしたり「SERVERID」変数でMySQLのサーバーIDに代入したりしています。ネットワークが/24までならホスト名やサーバーIDが被ることはないですが、それ以上のネットワークの場合はよしなに編集していただきたく。。。
一応ここまでが今回の4台に共通の設定なので、一撃構築となりますが、ここから先は個別のカスタマイズです。なのでフルオートメーションじゃありません＞＜ あと、今回の一撃シェルスクリプトはCentOS6とCentOS7で動作確認をしています。以下の作業はMasterとSlaveでそれぞれ別の作業をするので、どちらのサーバーで作業するかを見出しに書いておきますね。
■ [Master]my.cnf編集
/etc/my.cnf の [mysqld] セクションに binlog-ignore-db=mysql を追記してバイナリログにmysqlデータベースの変更を記録しないようにします。
sudo cp -p /etc/my.cnf{,.$(date +%Y%m%d)00}
sudo vi /etc/my.cnf
以下追記
binlog-ignore-db=mysql
保存したらmysqldを再起動します。
sudo service mysqld restart
sudo systemctl restart mysqld.service
■ [Master]レプリケーション用ユーザーを作成する
以下のID、パスワードでレプリケーション用ユーザーを作成します。すべてのDB、テーブルに対してREPLICATION SLAVEの権限を与えます。
| ID | msandbox |
| パスワード | msandbox |
mysql -e "GRANT REPLICATION SLAVE ON *.* TO 'msandbox'@'192.168.0.0/255.255.255.0' IDENTIFIED BY 'msandbox';"
■ [Slave]my.cnf編集
MySQLでマルチソースレプリケーションを行うためには、スレーブをクラッシュセーフにする必要があります。このため、 [mysqld] セクションに以下を追記します。
master_info_repository=TABLE relay_log_info_repository=TABLE
もう1つ、 replicate-ignore-db パラメータに mysql を指定しないと、MySQLのID、パスワードまでマスターから同期されてしまいます。なので、以下の1行も追記します。
replicate-ignore-db=mysql
上記3行を追記して保存したらmysqldを再起動します。
sudo service mysqld restart
sudo systemctl restart mysqld.service
■ [Master]バイナリログのポジションを確認する
master0サーバーとmaster1サーバーで、SHOW MASTER STATUSを実行します。
[nullpopopo@master0 ~]$ mysql -e "SHOW MASTER STATUS;" +-------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------+----------+--------------+------------------+-------------------+ | mysqld-bin.000005 | 465 | | mysql | | +-------------------+----------+--------------+------------------+-------------------+
[nullpopopo@master1 ~]$ mysql -e "SHOW MASTER STATUS;" +-------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------+----------+--------------+------------------+-------------------+ | mysqld-bin.000005 | 465 | | mysql | | +-------------------+----------+--------------+------------------+-------------------+
■ [Slave]チャネルを指定してスレーブのプロセスを作成する
CHANGE MASTERコマンドでマスターサーバーのスレーブになります。以下のコマンドを見ると、普通のMaster/Slave構成のレプリケーションと同じようですが、引数「FOR CHANNEL」をつけて、どのチャネルのスレーブになるかを指定する必要があります。また、チャネル名はダブルクォートで囲ってあげましょう。
mysqlクライアントからは以下のように実行します。
mysql> CHANGE MASTER TO MASTER_HOST="192.168.0.230", MASTER_PORT=3306, MASTER_LOG_FILE="mysqld-bin.000005", MASTER_LOG_POS=465, MASTER_USER="msandbox", MASTER_PASSWORD="msandbox" FOR CHANNEL "master0";
mysql> CHANGE MASTER TO MASTER_HOST="192.168.0.231", MASTER_PORT=3306, MASTER_LOG_FILE="mysqld-bin.000005", MASTER_LOG_POS=465, MASTER_USER="msandbox", MASTER_PASSWORD="msandbox" FOR CHANNEL "master1";
bash等のシェルからは以下のように実行します。
[nullpopopo@slave0 ~]$ mysql -e "CHANGE MASTER TO MASTER_HOST=\"192.168.0.230\", MASTER_PORT=3306, MASTER_LOG_FILE=\"mysqld-bin.000005\", MASTER_LOG_POS=465, MASTER_USER=\"msandbox\",MASTER_PASSWORD=\"msandbox\" FOR CHANNEL \"master0\";"
[nullpopopo@slave0 ~]$ mysql -e "CHANGE MASTER TO MASTER_HOST=\"192.168.0.231\", MASTER_PORT=3306, MASTER_LOG_FILE=\"mysqld-bin.000005\", MASTER_LOG_POS=465, MASTER_USER=\"msandbox\",MASTER_PASSWORD=\"msandbox\" FOR CHANNEL \"master1\";"
それでは START SLAVEします。mysqlクライアントからは以下のように実行します。
mysql> START SLAVE FOR CHANNEL "master0"; mysql> START SLAVE FOR CHANNEL "master1";
bash等のシェルからは以下のように実行します。
[nullpopopo@slave0 ~]$ mysql -e "START SLAVE FOR CHANNEL \"master0\";" [nullpopopo@slave0 ~]$ mysql -e "START SLAVE FOR CHANNEL \"master1\";"
CHANGE MASTERコマンドでマスターのバイナリログ名とポジションを引数に与えず実行してしまうと、SHOW SLAVE STATUSで以下のエラーが出てしまうことがあります。
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'A slave with the same server_uuid as this slave has connected to the master; the first event '' at 4, the last event read from '/var/lib/mysql/binlog/mysqld-bin.000004' at 465, the last byte read from '/var/lib/mysql/binlog/mysqld-bin.000004' at 465.'
そんな時は慌てず騒がず STOP SLAVEとRESET SLAVEしてスレーブのmysqldを再起動しましょう。なお、slave側の /etc/my.cnf で replicate-ignore-db=mysql の記述を忘れると、この時点でMySQLのrootパスワードがマスターから転送されて変更されてしまうので注意しましょう。
■ [Slave]ステータス確認
それではスレーブのステータスを確認してみましょう。Masterが1台、Slaveが1 or n台のときは「show slave status\G」でスレーブのステータスが確認できますが、今回のようにマルチソースレプリケーションだと、チャネル名を指定しないと何も出力されません。mysqlクライアントからは以下のように実行します。
mysql> SHOW SLAVE STATUS FOR CHANNEL "master0"\G mysql> SHOW SLAVE STATUS FOR CHANNEL "master1"\G
bash等のシェルからは以下のように実行します。
[nullpopopo@slave0 ~]$ mysql -e "SHOW SLAVE STATUS FOR CHANNEL \"master0\"\G" [nullpopopo@slave0 ~]$ mysql -e "SHOW SLAVE STATUS FOR CHANNEL \"master1\"\G"
実際の出力例はこんな感じです。(クリックで大きくなります)
[nullpopopo@slave0 ~]$ mysql -e "SHOW SLAVE STATUS FOR CHANNEL \"master0\"\G"
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.230
Master_User: msandbox
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysqld-bin.000005
Read_Master_Log_Pos: 627
Relay_Log_File: localhost-relay-bin-master0.000002
Relay_Log_Pos: 483
Relay_Master_Log_File: mysqld-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql
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: 627
Relay_Log_Space: 702
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: 230
Master_UUID: f1f2d908-7a39-11e5-ad4f-080027288d83
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: master0
[nullpopopo@slave0 ~]$ mysql -e "SHOW SLAVE STATUS FOR CHANNEL \"master1\"\G"
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.231
Master_User: msandbox
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysqld-bin.000005
Read_Master_Log_Pos: 627
Relay_Log_File: localhost-relay-bin-master1.000002
Relay_Log_Pos: 483
Relay_Master_Log_File: mysqld-bin.000005
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql
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: 627
Relay_Log_Space: 702
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: 231
Master_UUID: 84176354-7a3a-11e5-b242-080027295639
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: master1
チャネルを指定することで、スレーブのステータスを確認することができました。また、チャネルごとに Master_Host Master_UUID Channel_Name が異なっていることがわかります。(もちろんMaster->Slaveの転送状況も)
■ [Master]データベースを作成する
master0サーバーでtest0データベースを作成します。
[nullpopopo@master0 ~]$ mysql -e "CREATE DATABASE test0;"
次にmaster1サーバーでtest1データベースを作成します。
[nullpopopo@master1 ~]$ mysql -e "CREATE DATABASE test1;"
■ [Slave]データベース一覧を確認する
スレーブにこれらDBが転送されてきているかを確認します。
[nullpopopo@slave0 ~]$ mysql -e "SHOW DATABASES LIKE 'test%';" +------------------+ | Database (test%) | +------------------+ | test0 | | test1 | +------------------+
[nullpopopo@slave1 ~]$ mysql -e "SHOW DATABASES LIKE 'test%';" +------------------+ | Database (test%) | +------------------+ | test0 | | test1 | +------------------+
マスターサーバーにはtest0かtest1のどちらかのデータベースしかありませんでしたが、スレーブの2台には両方のデータベースができています。データがごちゃごちゃにならないよう、まだまだ細かい設計が必要ですが、これで最低限のマルチソースレプリケーション設定はおしまいです。ね、簡単でしょう？
[amazonjs asin=”4797353937″ locale=”JP” title=”MySQLによるタフなサイトの作り方”]