MySQL
AWS
俺でもわかるシリーズ

MySQLサーバのデータをAuroraにレプリケーションする stunnel編

More than 1 year has passed since last update.

俺です。こんばんわ

ふつうのMySQLサーバをAuroraにするため

レプリケーションでのデータ移行を検討してみます。

参照ドキュメント: Amazon Aurora とのレプリケーション


構成図

異なるVPCかつPrivate SubnetにいるMySQLとAuroraをReplicationするため、

Public Subnetに配備しているEC2同士でStunnelを張ってレプリケーションします。

今回はAWStoAWSで実践していますが、オンプレミスtoAWSでもVPStoAWSでも実装可能です。

レプリケーション.png


  • 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型しか試していないので、実際に問題なくうまくいくかは移行テストで確認しましょう)

おわり