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



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


参照ドキュメント: [Amazon Aurora とのレプリケーション](https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/Aurora.Replication.html)

# 構成図

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

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

![レプリケーション.png](https://qiita-image-store.s3.amazonaws.com/0/42755/f1de4d1b-bbc4-686f-3459-78ff0262ac12.png "レプリケーション.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型しか試していないので、実際に問題なくうまくいくかは移行テストで確認しましょう)

おわり