俺です。こんばんわ
ふつうのMySQLサーバをAuroraにするため
レプリケーションでのデータ移行を検討してみます。
参照ドキュメント: Amazon Aurora とのレプリケーション
構成図
異なるVPCかつPrivate SubnetにいるMySQLとAuroraをReplicationするため、
Public Subnetに配備しているEC2同士でStunnelを張ってレプリケーションします。
今回はAWStoAWSで実践していますが、オンプレミスtoAWSでもVPStoAWSでも実装可能です。
- Stunnel Server(Source MySQL)の存在するVPCで稼働するEC2
- Stunnel Client(Destination Aurora)の存在するVPCで稼働するEC2
全般設定
ざっくりとこんな感じで。stunnelは別途記載
ソースデータベースはInnoDBエンジンのみで稼働しているかつ gtid-mode = OFF を前提としています。
- MyISAMが存在する場合レプリケーションエラーになるので、ソースデータベースのMyISAMエンジンをInnoDBに変換しなければなりません。
- AuroraはGTIDモードのレプリケーションができません
| 設定項目 | 設定内容 | 移行元環境 | 移行後環境 |
|---|---|---|---|
| AWS | stunnelサーバのSG | 移行後VPCのstunnelで稼働するstunnelサーバのEIPとstunnel acceptポート(13306) | 特に無し |
| DB | バージョン | mysql5.6.27 | Aurora 5.6.10a |
| DB | GTID | OFF |
OFF |
| DB | ストレージエンジン | InnoDBのみ | InnoDBのみ |
stunnelサーバの構築
stunnelサーバ
- インストール
$ sudo yum install -y stunnel
- 証明書とサーバ秘密鍵の作成
省略
- /etc/stunnel/stunnel.conf
cert = /etc/stunnel/server.crt
key = /etc/stunnel/server.key
sslVersion = TLSv1
setuid = nobody
setgid = nobody
pid = /var/run/stunnel/stunnel.pid
socket = l:TCP_NODELAY=1
socket = r:TCP_NODELAY=1
debug = 1
output = /var/log/stunnel.log
client = no
[mysql]
accept = 13306
connect = <MySQLサーバの内部IP>:3306
- stunnel起動
$ sudo stunnel /etc/stunnel/stunnel.conf
stunnelクライアント
- インストール
$ sudo yum install -y stunnel
- 証明書とサーバ秘密鍵の作成
省略
- /etc/stunnel/stunnel.conf
cert = /etc/stunnel/server.crt
key = /etc/stunnel/server.key
sslVersion = TLSv1
setuid = nobody
setgid = nobody
pid = /var/run/stunnel/stunnel.pid
socket = l:TCP_NODELAY=1
socket = r:TCP_NODELAY=1
debug = 1
output = /var/log/stunnel.log
client = yes
[mysql]
accept = 13306
connect = <StunnelサーバのグローバルIP>:13306
- stunnel起動
$ sudo stunnel /etc/stunnel/stunnel.conf
接続確認
- stunnelクライアントで実行
$ mysql -u <username> -p<userpassword> -P 13306 -h 127.0.0.1
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 347
Server version: 5.6.27-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
これでAuroraが移行元MySQLにReplicationできるようになりました。
Auroraでレプリケーションプロシージャの実行
Auroraに接続可能なクライアントで実行します。
stunnelクライアントにmysqlコマンドをインストールしているので、やってみましょう。
ちなみにこの作業の前に、移行元MySQLからdumpしたデータを入れ込んでおいて、ポジションメモしておけば
差分レプリケーションが実現できますね。
InnoDBだけなら最高。
mysql -u <username> -p<userpassword> -h aurora.cluster-saikou.ap-northeast-1.rds.amazonaws.com
mysql> CALL mysql.rds_set_external_master ('<stunnelクライアントの内部IP>', 13306,'<MySQLサーバのレプリケーションユーザ名', '<MySQLサーバのレプリケーションユーザパスワード>', '<バイナリログファイル名>', <ポジション>, 0);
mysql> CALL mysql.rds_start_replication;
レプリケーション確認
以下のようにio_threadとsql_threadが起動していればOKです。やったぜ。
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: <stunnelクライアントの内部IP>
Master_User: repl
Master_Port: 13306
Connect_Retry: 60
Master_Log_File: mysqld-bin.000032
Read_Master_Log_Pos: 15257039
Relay_Log_File: relaylog.000154
Relay_Log_Pos: 236
Relay_Master_Log_File: mysqld-bin.000032
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table: mysql.rds_history,mysql.rds_replication_status,mysql.rds_sysinfo
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 15257039
Relay_Log_Space: 850
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: 2580
Master_UUID: 0c0e2cff-ecd9-11e5-95f8-0a28d442afe1
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)
レプリケーションを試してみる
sysbenchクライアントを使ってダミーデータを生成します。
移行元MySQLサーバに1億件ほどつっこんでみましょう。
データの生成
- 移行元MySQLサーバで実行
今回のテストで使ったstunnelサーバとMySQLサーバはt2.largeで稼働しています。
データの作成完了まで約16分30秒かかりました。(timeコマンドのresultデータ取り逃がした)
$ mysql -u root -p -e "create database sbtest"
$ sysbench \
--test=oltp \
--db-driver=mysql \
--oltp-table-size=100000000 \
--mysql-password=sbtest \
prepare
レプリケーション確認
- stunnelクライアントで実行
データ生成後に show slave status を実行すると、Seconds_Behind_Master は約2000秒の差があります。
VPC越え+stunnelなのでこの遅延はしかたないものです。
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: <stunnelクライアントの内部IP>
Master_User: repl
Master_Port: 13306
Connect_Retry: 60
Master_Log_File: mysqld-bin.000032
Read_Master_Log_Pos: 15256755
Relay_Log_File: relaylog.000116
Relay_Log_Pos: 21359384
Relay_Master_Log_File: mysqld-bin.000029
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table: mysql.rds_history,mysql.rds_replication_status,mysql.rds_sysinfo
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 21359220
Relay_Log_Space: 645048921
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: 1998★
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: 2580
Master_UUID: 0c0e2cff-ecd9-11e5-95f8-0a28d442afe1
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: update
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.01 sec)
レプリケーション遅延は待てば良いので、実用性はあると考えています。
VPNを張らない(張れない)環境だったり、VPCピアリングできない環境(ソースDBがAWSではない場合)や
AuroraをPublic Accessible = true せずに実現できるので便利ではないでしょうか。
（そういえばDatabase Migration Serviceもありますね..)
ただーし、エラー無しでレプリケーションを完了させるには移行元MySQLが InnoDB のみで稼働していることが超大前提です。
おわり。
付録: MyISAM -> InnoDBレプリケーションを試してみる
移行元MySQLサーバにMyISAMが存在する場合、どのようにレプリケーションするか試してみました。
- 移行元MySQLサーバでMyISAMテーブルを作成する
hoge@localhost [sbtest] > create table myisam_to_innodb (a int) engine='MyISAM';
Query OK, 0 rows affected (0.01 sec)```
- 移行先Auroraのレプリケーション状態確認
AuroraでMyISAMエンジンはサポートされていないためレプリケーションエラーが発生してしまいました。
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: <stunnelクライアントの内部IP>
Master_User: repl
Master_Port: 13306
Connect_Retry: 60
Master_Log_File: mysqld-bin.000003
Read_Master_Log_Pos: 611000
Relay_Log_File: relaylog.000002
Relay_Log_Pos: 611042
Relay_Master_Log_File: mysqld-bin.000003
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table: mysql.rds_history,mysql.rds_replication_status,mysql.rds_sysinfo
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1289
Last_Error: Error 'The 'MyISAM' feature is disabled; you need MySQL built with 'MyISAM' to have it working' on query. Default d
atabase: 'sbtest'. Query: 'create table myisam_to_innodb (a int) engine='MyISAM''
Skip_Counter: 0
Exec_Master_Log_Pos: 610878
Relay_Log_Space: 612320
- 移行先AuroraにInnoDBとしてテーブルの作成
移行元MySQLサーバに存在するMyISAMエンジンで稼働するテーブルを、
InnoDBとして作成します。
mysql > create table myisam_to_innodb (a int);
- 移行先Auroraで発生しているレプリケーションエラーをスキップ
レプリケーションエラーをスキップします。
mysql> CALL mysql.rds_skip_repl_error;
- 移行元MySQLサーバに存在するMyISAMエンジンのテーブルにデータ挿入
root@localhost [sbtest] > INSERT INTO myisam_to_innodb values (1);
Query OK, 1 row affected (0.01 sec)
- 移行先Auroraでデータ反映状態確認
やったぜ
mysql> SELECT * FROM myisam_to_innodb;
+------+
| a |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
ということで先にInnoDB化したテーブルを定義して、レプリケーションするという手段が通用しそうです。
(int型しか試していないので、実際に問題なくうまくいくかは移行テストで確認しましょう)
おわり