レプリケーションの設定方法について
ここでは、MySQL における最も基本的なレプリケーションの設定方法について記載する。
このプロセスを通して MySQL におけるレプリケーションの構造を理解するのが目的。
マスター側の設定
マスター側で必要な設定は、バイナリログを有効化し、スレーブからの接続受け入れの準備をすることの２つであり、具体的には以下のような設定が必要になる。
server_id の設定
レプリケーションを構成する MySQL サーバはいずれも固有の ID を持つ必要がある (server_id)。server_id はバイナリログにも記録され、これによりバイナリログを受け取ったスレーブが、どの MySQL サーバが更新の発信源になっているかを認識できる。
server_id は 32 ビット符号なし整数として設定し、レプリケーショングループ内で重複しないようにする必要がある。同一サブネット内に全ての MySQL サーバが存在するのであれば、IP アドレスのホスト部を使うのが良い。
$ ifconfig
eth0 Link encap:Ethernet HWaddr 06:A0:54:39:55:B4
inet addr:10.1.11.105 Bcast:10.1.11.255 Mask:255.255.255.0
inet6 addr: fe80::4a0:54ff:fe39:55b4/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:9001 Metric:1
RX packets:891263 errors:0 dropped:0 overruns:0 frame:0
TX packets:769514 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:261246341 (249.1 MiB) TX bytes:177487813 (169.2 MiB)
lo Link encap:Local Loopback
inet addr:127.0.0.1 Mask:255.0.0.0
inet6 addr: ::1/128 Scope:Host
UP LOOPBACK RUNNING MTU:65536 Metric:1
RX packets:82 errors:0 dropped:0 overruns:0 frame:0
TX packets:82 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:8280 (8.0 KiB) TX bytes:8280 (8.0 KiB)
$ cat /etc/my.cnf | grep server_id
server_id=105 バイナリログの有効化
レプリケーションを行うには、その原理上バイナリログが有効化されている必要がある。
具体的には /etc/my.cnf にて log_bin オプションを指定し、mysqld を再起動する。
$ cat /etc/my.cnf | grep log_bin
log_bin=mysql-bin
$ sudo service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ] スレーブ接続用アカウントの作成
REPLICATION SLAVE を持ったユーザをスレーブからログイン可能な状態にしておく必要がある。
MySQL のユーザーアカウントは、どのクライアントからログインするかにより異なるユーザであると認識される仕組みになっているため、スレーブが複数あれば、スレーブの数だけアカウントを作成する必要がある。
ただ、MySQL では接続元のホスト名をワイルドカードで指定したり、IP アドレスとサブネットマスクの組み合わせで表現する方法があるので、このような方法を使うと良い。
mysql> CREATE USER 'repl'@'10.1.11.0/255.255.255.0' IDENTIFIED BY 'password';
Query OK, 0 rows affected (0.00 sec)
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.1.11.0/255.255.255.0';
Query OK, 0 rows affected (0.00 sec) スレーブへデータをコピー
既存の MySQL サーバをマスターとし、新たにスレーブを追加したい場合には、マスターからスレーブへのデータコピーが必要になる。
この際、マスターからバックアップを取得し、スレーブへリストアすることになる。
まずはマスター側で以下のコマンドを実行し、テーブルをロックしたのち、File と Position の値を取得する。
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 | 376 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec) 次に、mysqldump によりスナップショットを取得する。
$ mysqldump -u [user] -p --all-databases --lock-all-tables > dbdump.db
Enter password:
-- Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.
$ ls -l dbdump.db
-rw-rw-r-- 1 ec2-user ec2-user 562669 8月 6 11:53 dbdump.db スナップショットを取得したら、以下のコマンドによりロックを解除する。
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec) 最後に、スレーブ側にデータをコピーする。
$ mysql -u [user] -p < dbdump.db スレーブの設定
server_id の設定
マスター同様、server_id を設定する。
$ ifconfig
eth0 Link encap:Ethernet HWaddr 06:03:35:FA:8B:40
inet addr:10.1.11.31 Bcast:10.1.11.255 Mask:255.255.255.0
inet6 addr: fe80::403:35ff:fefa:8b40/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:9001 Metric:1
RX packets:6034 errors:0 dropped:0 overruns:0 frame:0
TX packets:2496 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:5171141 (4.9 MiB) TX bytes:271422 (265.0 KiB)
lo Link encap:Local Loopback
inet addr:127.0.0.1 Mask:255.0.0.0
inet6 addr: ::1/128 Scope:Host
UP LOOPBACK RUNNING MTU:65536 Metric:1
RX packets:2 errors:0 dropped:0 overruns:0 frame:0
TX packets:2 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:140 (140.0 b) TX bytes:140 (140.0 b)
$ cat /etc/my.cnf | grep server_id
server_id=31
$ sudo service mysqld restart
Stopping mysqld: [ OK ]
Starting mysqld: [ OK ] レプリケーションの設定
レプリケーションの設定は CHANGE MASTER コマンドで行う。その際、マスター側で取得したバイナリログの File と Position が必要になる。
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.1.11.105',
-> MASTER_PORT=3306,
-> MASTER_USER='repl',
-> MASTER_PASSWORD='password',
-> MASTER_LOG_FILE='mysql-bin.000002',
-> MASTER_LOG_POS=376,
-> MASTER_HEARTBEAT_PERIOD=60;
Query OK, 0 rows affected (0.01 sec) なお、ここでは MASTER_USER 及び MASTER_PASSWORD を指定しているが、MySQL 5.6 からは CHANGE MASTER コマンド実行時にマスターへ接続するために使用するパスワードを指定すると警告がでるようになった。
これは、平文でパスワードを保存することに対するセキュリティ上の懸念からである。もしここでパスワードを指定すると、スレーブ上の master.info ファイルか、リポジトリがテーブルに指定されているなら、mysql.slave_master_info テーブルに平文で保存されることになる。
よって、現在は START SLAVE コマンド実行時にログイン情報を指定することが推奨されている。なお、START SLAVE でパスワードを指定する場合、コマンドを実行するセッションが SSL 接続でなければ別の警告が発生することになる。
なお、上記で設定した場合の master.info は以下。
$ sudo cat /var/lib/mysql/master.info
18
mysql-bin.000002
659
10.1.11.105
repl
password
3306
60
0
0
60.000
0 レプリケーションの開始
最後に START SLAVE コマンドによりレプリケーションを開始する。
mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec) レプリケーションの確認
実際にレプリケーションが正常に動作しているかどうかを確認する。まずはスレーブ側で SHOW SLAVE STATUS にて確認する。
mysql> SHOW SLAVE STATUS;
+----------------------------------+-------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+
| Slave_IO_State | Master_Host | Master_User | Master_Port | Connect_Retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error | Replicate_Ignore_Server_Ids | Master_Server_Id |
+----------------------------------+-------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+
| Waiting for master to send event | 10.1.11.105 | repl | 3306 | 60 | mysql-bin.000002 | 447 | mysqld-relay-bin.000002 | 324 | mysql-bin.000002 | Yes | Yes | | | | | | | 0 | | 0 | 447 | 481 | None | | 0 | No | | | | | | 0 | No | 0 | | 0 | | | 105 |
+----------------------------------+-------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+-----------------------------+------------------+
1 row in set (0.00 sec) ここで SlaveIORunning 及び SlaveSQLRunning が Yes になっていることを確認する。
実際に Master 側でレコードの変更を行ってみる。
mysql> SELECT * FROM hoge.hogehoge;
+------+------+
| id | name |
+------+------+
| 0 | hoge |
+------+------+
1 row in set (0.00 sec)
mysql> INSERT INTO hoge.hogehoge (id, name) VALUES (1, 'foo');
Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM hoge.hogehoge;
+------+------+
| id | name |
+------+------+
| 0 | hoge |
| 1 | foo |
+------+------+
2 rows in set (0.00 sec) スレーブ側で確認する。
mysql> SELECT * FROM hoge.hogehoge;
+------+------+
| id | name |
+------+------+
| 0 | hoge |
| 1 | foo |
+------+------+
2 rows in set (0.00 sec) 上記より実際に変更が反映されていることが確認できる。
また、マスター側で SHOW processlist を実行することで、スレーブから接続されていることがわかる。
mysql> SHOW processlist;
+----+------+-----------------------------------------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------------------------------------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
| 4 | repl | ip-10-1-11-31.ap-northeast-1.compute.internal:49510 | NULL | Binlog Dump | 405 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL |
| 5 | root | localhost | hoge | Query | 0 | NULL | SHOW processlist |
+----+------+-----------------------------------------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)