（続）MySQLでのレプリケーション 〜 実践編１「レプリケーションの設定」
「MySQLでのレプリケーション」では、レプリケーションの定義や種類、概念、レプリケーションの用途およびメリット・デメリットについて触れた。
今回は、実際にレプリケーション環境を構築してみる。
その前に、レプリケーション環境を構築するといっても、次の３パターンが考えられる。
そして、どのパターンかによって、スレーブを構築する際に実施しなければならないことが若干変わるので注意が必要である。
【パターン１】マスタもスレーブも新規に作成する場合
この場合は、マスタとスレーブをそれぞれ作成して同時に稼働させれば良いだろう。
【パターン２】マスタが既に稼働している環境に、スレーブを追加する場合
この場合は、スレーブを構築するまでにマスタが稼働していたことにより蓄積されたマスタのデータを、スレーブにコピーしてからスレーブを稼働させる必要がある。つまり、マスタの状態とスレーブの状態の同期化をしてから、スレーブを稼働させる必要がある。なぜなら、レプリケーションの仕組み上、スレーブを稼働した後のデータからレプリケートされるからのようだ。マスタがバイナリログに更新情報を記録し始めて以降のデータしかスレーブに反映することができないから。例えば、もともとマスタがバイナリログを出力しておらず、途中からバイナリログに更新情報を記録し始めていた場合、バイナリログを使用する前の情報はマスタとスレーブでデータベースファイルを同期してスレーブに反映させるしか無い。
【パターン３】既にレプリケーション環境が構築されているところにスレーブを追加する場合
この場合は、パターン２と同じ方法も使えるが、既に稼働しているスレーブとデータを同期する方法も可能である。
それでは次に作業内容を確認する。ここではパターン２を実施する想定で作業内容を洗い出してみた。
| マスタ側の作業 | スレーブ側の作業 | |
| パラメータ設定 | ・マスタのserver-idの設定 | ・スレーブのserver-idの設定 |
| ・バイナリログの出力設定(log-binの設定） | ・バイナリログの出力設定(log-binの設定） ※）スレーブの場合、バイナリログの出力設定は必須ではないが、スレーブをマスタに昇格させる場合も考慮すると設定しておいたほうが良いかも。 |
|
| レプリケーション用ユーザの作成 | スレーブからマスタに接続するためのレプリケーション用ユーザをマスタに作成する。 | − |
| マスタとスレーブのデータ同期 | 【DBファイルをコピーする方法】 1.テーブルを読み取り専用にロックする。 2.マスタの状態を確認し、FileとPositionの情報をメモする。 3.mysqldを停止する。 4.データベースファイルをスレーブにコピーする。 ※）master.infoファイル、バイナリログファイル、リレーログファイルはコピー不要。 【mysqldumpでデータダンプする方法】 1.mysqldumpでマスタデータをダンプする。 ※）テーブルはREADロックされる 2.ダンプしたSQLファイルをスレーブにコピーする。 |
【DBファイルをコピーする方法】 5.mysqldを停止する。 6. 4でマスタからコピーしたデータを配置する。 7.mysqldに--skip-slave-startオプションをつけて、スレーブを起動せずにmysqlを起動する。 8.change master文によってマスタの設定をする。 ※）ここで2でメモしたFileとPosition情報を使用する。 9.start slaveを実行し、スレーブを起動する。これによりレプリケーションが開始する（はず）。 【mysqldumpでデータダンプする方法】 3.mysqldに--skip-slave-startオプションをつけて、スレーブを起動せずにmysqlを起動する。 4.change master文によってマスタの設定をする。 5.マスタからコピーしたダンプSQLファイルを実行する。 6.start slaveを実行し、スレーブを起動する。これによりレプリケーションが開始する（はず）。 |
※）（追記）bind-addressの設定も確認し、スレーブがマスタへ接続できるように設定しておくこと。
作業内容を洗い出したところで、実践してみる。
まずは作業順序に制約が無い設定ファイルの編集を行い、次にマスタとスレーブのデータ同期を行う。
１．設定ファイル(/etc/mysql/my.cnf)の編集
◎マスタ側での作業
root@ubuntu:~# vi /etc/mysql/my.cnf log-bin = /var/log/mysql/master-bin log-bin-index = /var/log/mysql/master-bin server-id=131
◎スレーブ側での作業
root@ubuntu:~# vi /etc/mysql/my.cnf log-bin = /var/log/mysql/master-bin log-bin-index = /var/log/mysql/master-bin server-id=132
２．レプリケーション用ユーザの作成（マスタ側での作業）
root@ubuntu:~# mysql -uroot -p mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave-mysql'@'%' IDENTIFIED BY 'password'; Query OK, 0 rows affected (0.05 sec)
３．マスタとスレーブのデータ同期
【DBファイルをコピーする方法】
１．マスタDB でテーブルを読み取り専用にロックする
mysql> flush tables with read lock; Query OK, 0 rows affected (0.00 sec)
２．マスタDBでマスタの状態を確認し、FileとPositionの情報をメモする
mysql> show master status\G; *************************** 1. row *************************** File: master-bin.000009 Position: 325 Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec)
実際にディレクトリを確認するとmaster-bin.000009まで作成されている(この確認は不要)。
root@ubuntu:~# ls -l /var/log/mysql total 1456 -rw-r----- 1 mysql adm 12217 Sep 18 19:07 error.log -rw-r----- 1 mysql adm 20 Sep 17 12:57 error.log.1.gz -rw-r----- 1 mysql adm 20 Sep 16 07:56 error.log.2.gz -rw-r----- 1 mysql adm 11274 Sep 15 18:33 error.log.3.gz -rw-r----- 1 mysql adm 3111 Sep 13 05:33 error.log.4.gz -rw-r----- 1 mysql adm 1358 Sep 12 09:50 error.log.5.gz -rw-r----- 1 mysql adm 2338 Sep 11 04:52 error.log.6.gz -rw-r----- 1 mysql adm 4226 Sep 10 07:22 error.log.7.gz -rw-rw---- 1 mysql adm 496117 Sep 18 19:25 general.log -rw-rw---- 1 mysql adm 143 Sep 14 18:50 master-bin.000001 -rw-rw---- 1 mysql adm 143 Sep 14 18:57 master-bin.000002 -rw-rw---- 1 mysql adm 143 Sep 14 18:58 master-bin.000003 -rw-rw---- 1 mysql adm 143 Sep 14 19:07 master-bin.000004 -rw-rw---- 1 mysql adm 898676 Sep 16 07:56 master-bin.000005 -rw-rw---- 1 mysql adm 168 Sep 17 12:57 master-bin.000006 -rw-rw---- 1 mysql adm 168 Sep 18 07:59 master-bin.000007 -rw-rw---- 1 mysql adm 143 Sep 18 19:07 master-bin.000008 -rw-rw---- 1 mysql adm 325 Sep 18 19:19 master-bin.000009 -rw-rw---- 1 mysql adm 297 Sep 18 19:07 master-bin.index
３．マスタDBのmysqldを停止する
root@ubuntu:~# /etc/init.d/mysql stop
....
* MySQL Community Server 5.6.20 is stopped
４．マスタDBからデータベースファイルをスレーブDBにコピーする
/var/lib/mysqlをdatadirに指定しているので、それをスレーブにコピーする。
※）master.infoファイル、バイナリログファイル、リレーログファイルはコピー不要。
※）（追記）データディレクトリ直下に有る、auto.cnfもコピー不要。これをコピーしてしまうと、UUIDがマスタとスレーブで重複してスレーブが稼働しなかった。
５．スレーブDBでmysqldを--skip-slave-startオプションを付与して起動する
/etc/mysql/my.cnfを編集して「skip-slave-start」というキーワードを追記。その後、/etc/init.d/mysqlを起動する。
root@ubuntu:/# vi /etc/mysql/my.cnf
root@ubuntu:/var/lib# /etc/init.d/mysql start
スレーブが起動していない常態かどうかをmysqladminコマンドを使用して確認する。
root@ubuntu:/var/lib# mysqladmin extended-status -uroot -p Enter password: +-----------------------------------------------+-------------+ | Variable_name | Value | +-----------------------------------------------+-------------+ | Aborted_clients | 0 | | Aborted_connects | 0 | | Binlog_cache_disk_use | 0 | ・・・・・・・・・・・・・・・・・・・ | Slave_heartbeat_period | 0.000 | | Slave_last_heartbeat | | | Slave_open_temp_tables | 0 | | Slave_received_heartbeats | 0 | | Slave_retried_transactions | 0 | | Slave_running | OFF | ・・・・・・・・・・・・・・・・・・・
６．スレーブでmysqldに接続してchange masterクエリを発行する。
mysql> change master to -> master_host='192.168.133.131', -> master_user='slave-mysql', -> master_password='password', -> master_log_file='master-bin.000009', -> master_log_pos=325; Query OK, 0 rows affected, 2 warnings (0.04 sec)
７．スレーブDBでスレーブを起動する
mysql> start slave
スレーブが稼働したかを確認する。
mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Connecting to master Master_Host: 192.168.133.131 Master_User: slave-mysql Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000009 Read_Master_Log_Pos: 325 Relay_Log_File: mysqld-relay-bin.000001 Relay_Log_Pos: 4 Relay_Master_Log_File: master-bin.000009 Slave_IO_Running: Connecting 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: 325 Relay_Log_Space: 120 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: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 2003 Last_IO_Error: error connecting to master 'slave-mysql@192.168.133.131:3306' - retry-time: 60 retries: 1 Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 0 Master_UUID: Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: 140919 22:58:38 Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 1 row in set (0.00 sec)
「 Slave_SQL_Running: Yes」であり、SQLプロセスは正常に稼働したようだ。しかし、「Slave_IO_Running: Connecting」であり、IOプロセスがYESとならない。
さっそく、/var/log/mysql/error.logを確認するとエラーが発生している。
2014-09-19 22:59:38 17278 [ERROR] Slave I/O: error connecting to master 'slave-mysql@192.168.133.131:3306' - retry-time: 60 retries: 2, Error_code: 2003
エラーコード2003はアクセス拒否のエラーだから、ホスト側のアクセス許可設定を調べる。
可能性としては、次の2通りくらいだろうか。
・MySQLの設定でスレーブからマスタへの接続が拒否されている
・Linuxのファイアウォールの設定、iptablesの設定で、スレーブからマスタへの接続が拒否されている
まず、/etc/mysql/my.cnfでbind-addressの設定を確認してみると、「bind-address=127.0.0.1」となっていた。これだと127.0.0.1への接続しか受け付けない。
試しに、mysqlコマンドを使用してslave-mysqlユーザでスレーブからマスタにリモート接続してみるが確かに接続できない。
root@ubuntu:/var/lib# mysql -h 192.168.133.131 -u slave-mysql -p Enter password: ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.133.131' (111)
そこで、マスタのmy.cnfのbind-addressの設定を変更して、192.168.133.131への接続を許可するようにする。
今回は練習なので一旦bind-addressをコメントアウトして再度マスタへ接続を試みると今度は成功。
root@ubuntu:/var/lib# mysql -h 192.168.133.131 -u slave-mysql -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g.
スレーブを再起動して再度スレーブの状態を確認する。
root@ubuntu:/var/lib# /etc/init.d/mysql restart root@ubuntu:/var/lib# mysql -uroot -p mysql> mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.133.131 Master_User: slave-mysql Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000009 Read_Master_Log_Pos: 325 Relay_Log_File: mysqld-relay-bin.000004 Relay_Log_Pos: 4 Relay_Master_Log_File: master-bin.000009 Slave_IO_Running: No Slave_SQL_Running: Yes
スレーブが起動していないので、/var/log/mysq/error.logを確認する。
2014-09-19 23:39:07 19031 [ERROR] Slave I/O: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work. Error_code: 1593
UUIDがユニークじゃないと怒られている。server-idはマスタとスレーブで異なるものを設定したのに、何故だろうと思ったが、まずはUUIDがどこで設定されているのかを調べる。
UUIDは/etc/mysql/my.cnfで設定するものではなく、MySQLのデータディレクトリ（今回は/var/lib/mysql）直下にあるauto.cnfファイルに設定されている。
ここで、マスタのデータディレクトリのバックアップを取得して、スレーブに配置した際に全てをコピーしたのだった。スレーブのauto.cnfファイルを削除してからスレーブを再起動する（ただし、削除する前にバックアップはとっておくこと）。
スレーブの稼働状況を確認する。
root@ubuntu:/var/lib# mysqladmin extended-status -uroot -p Enter password: +-----------------------------------------------+-------------+ | Variable_name | Value | +-----------------------------------------------+-------------+ | Aborted_clients | 0 | ・・・・・・・・・・・ | Slave_retried_transactions | 0 | | Slave_running | ON |
どうやらスレーブは稼働したようだ。mysqldに接続してshow slave statusによって確認してみる。
root@ubuntu:/var/lib# mysql -uroot -p mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.133.131 Master_User: slave-mysql Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000015 Read_Master_Log_Pos: 120 Relay_Log_File: mysqld-relay-bin.000012 Relay_Log_Pos: 284 Relay_Master_Log_File: master-bin.000015 Slave_IO_Running: Yes Slave_SQL_Running: Yes
「Slave_IO_Running: Yes」、「Slave_IO_Running: Yes」となっている。漸くレプリケーションが稼働した。
ためしに、マスタにテーブルを作成して、それがスレーブに反映されるかを確認してみる。
まず、マスタ（192.168.133.131）とスレーブ（192.168.133.132）に接続する。
次に、テスト用のtestdbに接続する。そして、テーブルの数を確認する。
最後に、マスタにテーブルを作成して、スレーブにレプリケイトされるかを確認する。
成功。
◎最後に
・今回は練習だから良いが、本番作業でこうやっていくつも詰まっていたら困るので、当然手順の精緻化は必須。
・今回はコールドバックアップを例にしたが、mysqldumpを使用してデータダンプする方法もある。さらにInnoDBに限定すれば、--single-transactionオプションを使用してロックの時間を短くすることができる。
