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

俺です。こんばんわ
ふつうのMySQLサーバをAuroraにするため
レプリケーションでのデータ移行を検討してみます。

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

構成図

異なるVPCかつPrivate SubnetにいるMySQLとAuroraをReplicationするため、
Public Subnetに配備しているEC2同士でStunnelを張ってレプリケーションします。

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

f1de4d1b-bbc4-686f-3459-78ff0262ac12

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 = :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 =  :13306

stunnel起動

$ sudo stunnel /etc/stunnel/stunnel.conf

接続確認

stunnelクライアントで実行

$ mysql -u   -p -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  -p -h aurora.cluster-saikou.ap-northeast-1.rds.amazonaws.com
mysql> CALL mysql.rds_set_external_master ('', 13306,'', '<バイナリログファイル名>', <ポジション>, 0);
mysql> CALL mysql.rds_start_replication;

レプリケーション確認

以下のようにio_threadとsql_threadが起動していればOKです。やったぜ。

mysql> show slave statusG
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 
                  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 statusG;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host:  
                  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 statusG
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 
                  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型しか試していないので、実際に問題なくうまくいくかは移行テストで確認しましょう)

おわり

元記事はこちら

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

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

構成図

全般設定

stunnelサーバの構築

stunnelサーバ

stunnelクライアント

接続確認

Auroraでレプリケーションプロシージャの実行

レプリケーション確認

レプリケーションを試してみる

データの生成

レプリケーション確認

付録: MyISAM -> InnoDBレプリケーションを試してみる

元記事はこちら

IT健保「鮨一新」食レポ

Arduinoを初めて使う時のセットアップと実例集

X (旧 Twitter) を複数人で安全に運用する方法

Amazon Managed Grafanaへのログイン手段を考える〜IAM Identity Center以外のSAML認証の選択肢〜

第四開発事業部の「PM 育成研修」をご紹介！〜唯一無二の PM とは？〜

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

構成図

全般設定

stunnelサーバの構築

stunnelサーバ

stunnelクライアント

接続確認

Auroraでレプリケーションプロシージャの実行

レプリケーション確認

レプリケーションを試してみる

データの生成

レプリケーション確認

付録: MyISAM -> InnoDBレプリケーションを試してみる

元記事はこちら

関連記事Related Articles

MySQL(Aurora) への接続を tcpdump でのぞき見してみて判ったことのメモ

Xtrabackupを使ったRDS Auroraへの移行パス、名付けて~俺のリストア(orenorestore)~

Galera Cluster に Asynchronous replicateion ノードを追加する

RDSでParameter Groups変更時のDBリブートについて調査してみた

【cloudpack 大阪 BLOG】MSPのシステム化について Hubotでの状態管理(mysql)とGoogleカレンダーとslack連携 その1

【cloudpack 大阪 BLOG】MSPのシステム化について Hubotでの状態管理(mysql)とGoogleカレンダーとslack連携その1