【AWS】MySQL on EC2→RDSのDBレプリケーションを試してみた

2013.09.08

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

はじめに

こんにちは植木和樹です。先日AWSより非RDSなMySQLからRDSへのレプリケーションを用いたデータ移行機能が発表されました。

Migrate On-Premises MySQL Data to Amazon RDS (and back)

非RDS→RDSへのレプリケーションについては、以前都元さんが「Tungsten Replicatorを使って、非RDS→RDSのMySQLレプリケーションを行う」というブログを書いています。サービスの停止を極力短くしつつ大量のデータを移行する際、今まではデータ移行ツールを使う必要がありました。

今回追加された機能によって簡単にデータ移行ができるようになるのでしょうか。試してみたいと思います。

条件

非RDS→RDSへのレプリケーションは以下の条件を満たす必要があります。

  • RDS: MySQL 5.5.33 以上 または 5.6.13 以上
  • RDS: Single-AZ!(Multi-AZには対応していません/後述)

検証環境

用意した環境

バージョン
マスター: 5.5.32-1.36.amzn1
スレーブ:5.6.13
VPC
デフォルトVPC
AvailabilityZone
ap-northeast-1c
サブネットグループ
default
セキュリティグループ
EC2: ssh(tcp/22) と mysql(tcp/3306)を許可する(MySQLは同一VPC内からの接続を許可)
RDS: mysql(tcp/3306)(EC2からの接続を許可)
Multi-AZ
マスター:EC2環境でMulti-AZにはしていません
スレーブ:Multi-AZ(ただし問題があるためSingle-AZでの運用必須となります)

EC2とRDSはマネージメントコンソールから作成しておきます。なおデータに日本語を使ったので事前にRDS Parameter Groupを作成しておき、RDS作成時に指定しました。

RDS Parameter Group

character-set-client-handshake=0
character_set_client=utf8
character_set_connection=utf8
character_set_database=utf8
character_set_filesystem=utf8
character_set_results=utf8
character_set_server=utf8
skip-character-set-client-handshake=1

EC2にMySQLサーバーをインストール

RPMでMySQLサーバーをインストールします。こちらも日本語を扱うためにmy.cnfを修正しています。

$ sudo yum install mysql-server -y
$ sudo vi /etc/my.cnf
[mysqld]
default-character-set=utf8
skip-character-set-client-handshake

$ sudo service mysqld start

$ mysqladmin password -u root
New password:
Confirm new password:
$ mysqladmin -u root -p create testdb
$ mysql -u root -p testdb
mysql> exit

EC2のマスター側を設定する

MySQLでレプリケーションを行うにはマスター側でバイナリログ(トランザクションログ)を出力しておく必要があります。デフォルトではオフになっていますので、my.cnfを修正します。下の例では/var/lib/mysql/binarylogディレクトリにbinlog.000000というファイル名で作成されるようにしました。

$ mysql -u root -p testdb
mysql> show binary logs;
ERROR 1381 (HY000): You are not using binary logging (← デフォルトではオフになっている)
mysql> exit

(一度サーバーを止めて設定ファイルを修正してから再起動する)
$ sudo service mysqld stop
$ sudo vi /etc/my.cnf
[mysqld]
log-bin=/var/lib/mysql/binarylog/binlog (2行を追記する)
server-id=1

$ sudo mkdir -p /var/lib/mysql/binarylog
$ sudo chown mysql:mysql /var/lib/mysql/binarylog
$ sudo chmod 700 /var/lib/mysql/binarylog

$ sudo service mysqld start

$ mysql -u root -p testdb
mysql> show binary logs;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000001 |       107 |
+---------------+-----------+
1 row in set (0.00 sec)

mysql> show binlog events;
+---------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name      | Pos | Event_type  | Server_id | End_log_pos | Info                                  |
+---------------+-----+-------------+-----------+-------------+---------------------------------------+
| binlog.000001 |   4 | Format_desc |         1 |         107 | Server ver: 5.5.32-log, Binlog ver: 4 |
+---------------+-----+-------------+-----------+-------------+---------------------------------------+
1 row in set (0.00 sec)

mysql> exit

スレーブ(RDS)からマスター(EC2)に接続するためのユーザreplを作成し、REPLICATION SLAVE権限を与えます。接続元ホスト名を最初RDSのエンドポイントにしていたのですが、RDSは自身のプライベートDNS名(ip-172-xxx-xxx-xxx.ap-northeast-1.compute.internal)で接続しにくるため、すべてのホスト('%')からの接続を許可しています。

$ mysql -u root -p testdb
mysql> CREATE USER 'repl'@'%' IDENTIFIED BY 'slavepass';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
mysql> exit

レプリケーション手順

レプリケーションの大まかな手順は次の通りです。

  1. マスター側のバイナリログ情報を書き留めておく
  2. mysqldumpコマンドを使ってマスター(EC2)のデータベース(または一部のテーブル)をダンプする
  3. ダンプファイルをスレーブ(RDS)にインポートする
  4. スレーブでSLAVEを設定(mysql.rds_set_external_master)する
  5. スレーブでレプリケーションを開始する(mysql.rds_start_replication)
  6. データ同期を確認する
  7. スレーブのレプリケーションを停止する(mysql.rds_stop_replication)

マスター側のバイナリログ情報を書き留めておく

mysqldumpする前に、スレーブ側の同期開始地点となる場所を確認しておきます。この情報はスレーブ側でレプリケーション設定する際に必要となるのでメモしておきましょう。

またFLUSH TABLES WITH READ LOCKを実行してデータベースへの書き込みをロックしています。この間すべてのINSERT/UPDATE/DELETE処理は行えません(トランザクションが待機する)ので、本番でやる際には手早く行いましょう。なおロックを行ったセッションをクローズすると自動的に解除されます。

$ mysql -u root -p testdb
mysql> FLUSH TABLES WITH READ LOCK;
mysql> show master status;
+---------------+----------+--------------+------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| binlog.000002 |      727 |              |                  |
+---------------+----------+--------------+------------------+
1 row in set (0.00 sec)
mysql> exit

mysqldumpコマンドを使ってマスター(EC2)のデータベース(または一部のテーブル)をダンプする

データベースにmytableというテーブルを作成し、これをレプリケーションしてみましょう。まずはテーブルを作成してからレコードをINSERTします。その後mysqldumpコマンドでmytableのみをファイルにダンプします。

$ mysql -u root -p testdb
mysql> create table mytable (id int, remark text);
mysql> insert into mytable (id,remark) values(1, 'このレコードはmysqldumpでインポートされます');
mysql> commit;
mysql> select * from mytable;
+------+--------------------------------------------------------------+
| id   | remark                                                       |
+------+--------------------------------------------------------------+
|    1 | このレコードはmysqldumpでインポートされます                       |
+------+--------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> exit
$ mysqldump -u root -p --no-create-db testdb mytable > dump.sql

ダンプしたファイルをスレーブ(RDS)にインポートします。

$ mysql -u mydbuser -p -h slave.xxxx.ap-northeast-1.rds.amazonaws.com testdb < dump.sql
$ mysql -u mydbuser -p -h slave.xxxx.ap-northeast-1.rds.amazonaws.com testdb
mysql> show tables;
+------------------+
| Tables_in_testdb |
+------------------+
| mytable          |
+------------------+
1 row in set (0.00 sec)

mysql> select * from mytable;
+------+--------------------------------------------------------------+
| id   | remark                                                       |
+------+--------------------------------------------------------------+
|    1 | このレコードはmysqldumpでインポートされます                       |
+------+--------------------------------------------------------------+
1 row in set (0.00 sec)

スレーブでSLAVEを設定(mysql.rds_set_external_master)する

データのインポートが完了しました。インポート作業をしている間にもマスター側にはレコードを追加されていっているはずです。そのためバイナリログを使ってmysqldump以降のトランザクションをスレーブに反映させる必要があります。

まずはmysql.rds_set_external_masterプロシージャを使ってマスターの接続情報と、読み込むバイナリログの情報を設定します。ここで設定するバイナリログ情報はマスター側でshow master statusを実行した際に出力された結果になります。

マスターとなるEC2のアドレスは、VPC内での通信になるのでプライベートIPアドレスを指定してください。

$ mysql -u mydbuser -p -h slave.xxxx.ap-northeast-1.rds.amazonaws.com testdb
mysql> CALL mysql.rds_set_external_master(
'172.31.31.148', (マスターのIPアドレス)
3306, (ポート番号)
'repl', (接続するユーザー名)
'slavepass', (パスワード)
'binlog.000002', (バイナリログのファイル名)
'727', (同期開始位置)
0 (SSL接続を行わない)
);
Query OK, 0 rows affected (0.31 sec)

スレーブでレプリケーションを開始する(mysql.rds_start_replication)

レプリケーションの設定が終わったのでmysql.rds_start_replicationプロシージャを実行して同期を開始しましょう。

mysql> CALL mysql.rds_start_replication;
+-------------------------+
| Message                 |
+-------------------------+
| Slave running normally. |
+-------------------------+
1 row in set (1.05 sec)

Query OK, 0 rows affected (1.05 sec)

mysql> show slave status\G (\Gで実行すると出力を縦に表示してくれます)
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.31.31.148
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000002
          Read_Master_Log_Pos: 1422
               Relay_Log_File: relaylog.000019
                Relay_Log_Pos: 962
        Relay_Master_Log_File: binlog.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table: mysql.plugin,innodb_memcache.config_options,innodb_memcache.cache_policies,mysql.rds_replication_status,mysql.rds_history
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1422
              Relay_Log_Space: 1958
              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: 1
                  Master_UUID:
             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 the slave I/O thread to update it
           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
1 row in set (0.00 sec)

同期が開始されたので、マスター側のテーブルにレコードをINSERTしてみましょう。

$ mysql -u root -p testdb
mysql> insert into mytable (id,remark) values(2, 'このレコードはレプリケーションでインポートされます');
mysql> commit;
mysql> select * from mytable;
+------+-----------------------------------------------------------------------------+
| id   | remark                                                                      |
+------+-----------------------------------------------------------------------------+
|    1 | このレコードはmysqldumpでインポートされます                                      |
|    2 | このレコードはレプリケーションでインポートされます                                 |
+------+-----------------------------------------------------------------------------+
2 rows in set (0.00 sec)

スレーブに接続して、マスターにINSERTしたレコードが反映されているか確認します。

$ mysql -u mydbuser -p -h slave.xxxx.ap-northeast-1.rds.amazonaws.com testdb
ysql> select * from mytable;
+------+-----------------------------------------------------------------------------+
| id   | remark                                                                      |
+------+-----------------------------------------------------------------------------+
|    1 | このレコードはmysqldumpでインポートされます                                      |
|    2 | このレコードはレプリケーションでインポートされます                                 |
+------+-----------------------------------------------------------------------------+
2 rows in set (0.01 sec)

成功しました!正常にレコードが反映されていますね。

スレーブのレプリケーションを停止する(mysql.rds_stop_replication)

EC2上のMySQLからRDSへのデータ移行が完了したら、RDS側のレプリケーションを終了します。

実際の移行時にはアプリケーションを終了してマスターへの書き込みをすべて停止させ、スレーブへのデータ同期を確認してからレプリケーションを停止することになるかと思います。

mysql> CALL mysql.rds_stop_replication
mysql> CALL mysql.rds_reset_external_master

トラブルシューティング

レプリケーションがうまく動かない時には以下の点を確認してみましょう。

  • スレーブ→マスターのセキュリティグループを確認する(EC2でRDSからのtcp/3306を許可しているか)
  • Network ACLを確認する(Inbound,Outboundとも)
  • レプリケーション用ユーザーで接続できているか

レプリケーション用ユーザ(repl)で接続できているかを確認するには、マスター側のMySQLにログインしshow processlistを実行してみましょう。下記はreplユーザーが正常に接続できレプリケーションが行われている場合の出力例です。

mysql> show processlist;
+----+------+--------------------------------------------------------+--------+-------------+------+-----------------------------------------------------------------------+------------------+
| Id | User | Host                                                   | db     | Command     | Time | State                                                                 | Info             |
+----+------+--------------------------------------------------------+--------+-------------+------+-----------------------------------------------------------------------+------------------+
| 87 | repl | ip-172-31-18-206.ap-northeast-1.compute.internal:21093 | NULL   | Binlog Dump |  685 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL             |
| 88 | root | localhost                                              | testdb | Query       |    0 | NULL                                                                  | show processlist |
+----+------+--------------------------------------------------------+--------+-------------+------+-----------------------------------------------------------------------+------------------+
2 rows in set (0.01 sec)

おまけ

おまけ1:Multi-AZなRDSでのレプリケーションについて

スレーブ側をMulti-AZ構成にした場合、EC2とRDSが同一のAZ(サブネット?)にある場合には問題なくレプリケーションができるのですが、RDSがフェイルオーバーして別AZになるとRDS→EC2への接続に失敗します。以下は接続できない時にnetstatコマンドを実行した際の出力です。スレーブのRDS(172.31.1.70)からマスターのEC2(172.31.31.148)へのTCP接続がSYN_RECVのまま確立できていません。

$ netstat -ant|grep 172.31.1.70
tcp        0      0 172.31.31.148:3306          172.31.1.70:22063           SYN_RECV
tcp        0      0 172.31.31.148:3306          172.31.1.70:22065           SYN_RECV
tcp        0      0 172.31.31.148:3306          172.31.1.70:22064           SYN_RECV
tcp        1      0 172.31.31.148:55626         172.31.1.70:3306            CLOSE_WAIT
tcp        0      0 172.31.31.148:55629         172.31.1.70:3306            ESTABLISHED
tcp        1      0 172.31.31.148:55618         172.31.1.70:3306            CLOSE_WAIT

改めてドキュメントを確認したところスレーブとなるRDSは「Single-AZ」でなければいけないようです。(Amazon Relational Database Service User Guide

Create a MySQL instance in Amazon RDS specifying the correct DB instance class, parameter group, security group, PIOPS settings, and a single availability zone.

おまけ2:RDS→RDSのレプリケーションもできるかな?

機能が発表された当初、非RDS → RDSのインポートと、RDS → 非RDSへのエクスポートができるなら「RDS→RDSもできるんじゃない?」と期待したのですが、ドキュメントにしっかりと「できません」と書いてありました。残念。(Amazon Relational Database Service User Guide

Warning Do not use mysql.rds_set_external_master to manage replication between two RDS DB instances. Use it only when replicating with an instance of MySQL running outside of RDS. For information about managing replication between RDS instances, see Working with Read Replicas.

まとめ

非RDSなMySQLからRDSへレプリケーションができるようになり、オンプレミス環境からAWSへのデータ移行がとても手軽にできるようになりました。スレーブがMulti-AZに対応していないのは少々残念ですが、AWSは日々機能改善が行われていますので、いつか対応してくれることを心待ちにしたいと思います!

今回はRDSへのデータインポートを取り上げましたが、万が一RDSへの切り替えがうまくいかなった時には切り戻しが必要になります。今回の発表ではRDS→非RDSへのエクスポートも含まれていますので、近日中にそちらも試してみたいと思います。