AWS上のMariaDBを異なるインスタンス間でレプリケーションする方法

#はじめに
自社の勉強会でAWSを触って、DBのレプリケーションでハマったときの解決方法が元ネタです。
AMIインスタンスを作成するところから記述しましたが、MariaDBの設定に重きを置いておりますので、AWSに一度触れたことのある方が対象になると思います。
記述が粗い箇所があるかもしれないので、備忘録程度の認識です。ご容赦下さい。

#勉強して分かったこと
<dl>
  <dt>アベイラビリティーゾーンが異なっていても、レプリケーションできるのはメリット</dt>
  <dd>物理的に離れているサーバ間でDBを構築できるので、冗長性が向上する</dd>
  <dd>停電や地震、津波、戦争など大規模な災害で障害が発生しても、正常な他地域で稼働できる（同じリージョンが全滅したら終わりだけど）</dd>
  <dd>自社にインフラ系のスペシャリストが居なくても、平均的なエンジニアで冗長性のあるDBを平易に構築できる（私のような経験の浅い人間など）</dd>
  <dt>MySQLのDocをある程度読めると、MariaDBのトラブルシューティングに役立つ</dt>
  <dd>レプリケーションに失敗した原因をMySQLのドキュメント、ブログを参考に解決できた</dd>
  <dd>→短いパスワードハッシュを使うと、secure_authが発動してIO Connectingに失敗する（後述）</dd>
</dl>

#前提条件
同じリージョンで、且つアベイラビリティゾーンが異なるインスタンス間でレプリケーションするケースを想定します。マスターにmariadbをインストールし、サンプル用のsakila DBをインポートします。マスターのAMIインスタンスからスレーブを作成し、DBをリンクさせます。DBをレプリケーションした状態で、マスターのDBに変更を加え、スレーブ側で変更が同期されれば完了とします。
以下、環境を明記します。

- リージョン：Oregon
- アベイラビリティゾーン： west-2b（マスター）、west-2a（スレーブ）
- インスタンス：t1.micro（ボリュームサイズは任意ですが、30GBあれば十分です）
- OS：CentOS 7 
- DB：MariaDB (10.1.22-MariaDB MariaDB Server)
- DBのレコード：sakila DB（MySQL配布のサンプル用DB）
- AWSの操作：Windows 7からteratermで接続


#DBレプリケーションの手順
AWS上のmariadbを異なるインスタンス間でレプリケーションします。

1.　AWSのWebコンソールからインスタンスイメージを作成し、マスターのAMIからインスタンスを作成します。

```:（マスター）
EC2インスタンス > インスタンスの複製 > [インスタンスを選択] > イメージ > [名前を付けて作成(ASCIIコード以外使用不可)]
```
```:（スレーブ）
AMI > [作成したマスターのAMIを選択して作成] > インスタンスの設定 > セキュリティグループをマスターと同じ地域に設定
```

　
2. 　インスタンスに紐づいているセキュリティグループに、MYSQL/Auroraを追加
セキュリティグループ内で、SSH/ICMP/MYSQLを許可します。
※ルールに書かれていないプロトコルは全て暗黙の否定になります。
※SSHも一度無効化されるため、改めて明記する必要があります。

```:（マスターとスレーブ）
  [インスタンスのセキュリティグループを選択] > アクション > [インバウンド/アウトバウンド]ルールを編集
```

　
3.　サーバー間で疎通確認
マスターとスレーブのインスタンスにログインして、お互いにpingを撃ちます。予め、セキュリティグループでICMPを許可をしておきます。

```:（マスター）
ec2-user[172.31.XXX.XXX] $ ping 172.31.YYY.YYY //スレーブのPrivate IP
```
```:（スレーブ）
ec2-user[172.31.YYY.YYY] $ ping 172.31.XXX.XXX //マスターのPrivate IP
```

　
4.　サーバIDの設定変更
レプリケーション時にマスターとスレーブで異なるIDに設定する必要があります。今回は、マスターIDを10、スレーブIDを20に変更します。

```:server.cnf（マスター）
  $ sudo vi /etc/my.cnf.d/server.cnf
  $ server-id	=	10
```
```:server.cnf（スレーブ）
  $ sudo vi /etc/my.cnf.d/server.cnf
  $ server-id	=	20
```
　
5.　MariaDBの再起動
マスターとスレーブの両方のmariaDBを再起動します。

```:（マスターとスレーブ）
$ sudo systemctl restart mysqld
```
　
6.　レプリケーションユーザーの作成
マスター側で、レプリケーションユーザーを作成します。今回はAWSのPrivateアドレス空間に対してGRANT権限を付与していますが、本番環境では控えた方が良いでしょう。ユーザー名/パスワードは"repl/REPL"としています。

```:（マスター）
$ mysql -u root -p
MariaDB []> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'172.31.0.0/255.255.240.0' IDENTIFIED BY 'REPL';
```
　
7.　DBのダンプ取得
マスター側でDBのダンプファイルを取得します。DB名はsakilaとします。

```:（マスター）
$ mysqldump -u root -p --single-transaction --flush-logs --master-data=2 sakila > sakila.sql
```
　
8.　サーバー間でDBダンプファイルを移動（マスター→スレーブ）
  teratermのSSH SCP機能を使って、マスターからDBのダンプファイルをラップトップ経由でスレーブにコピーします。
　
9.　ダンプのインポート（スレーブ）
スレーブのDBにダンプファイルをインポートします。

```:（スレーブ）
$ mysql -u root -p sakila < sakila.sql
```
　
10.　ログ情報を確認
以下のような情報が出力されていればOK（mysql-binはDBによって異なります）。

```:（マスターまたはスレーブ）
$ head -n 100 sakila.sql|grep CHANGE
.
.
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.XXXXXX', MASTER_LOG_POS=382;
.
.
```
　
11.　マスターの情報をスレーブに登録
マスターで設定したレプリケーションユーザーの情報をスレーブに登録します。

```:（スレーブ）
$ mysql -u root -p
MariaDB []> CHANGE MASTER TO
MariaDB []> MASTER_HOST = '172.31.XXX.XXX', //マスターのPrivate IP
MariaDB []> MASTER_USER = 'repl', //マスターのレプリケーションユーザー名
MariaDB []> MASTER_PASSWORD = 'REPL', //マスターのレプリケーションユーザー名
MariaDB []> MASTER_LOG_FILE='mysql-bin.0XXXXX', //10.のmysql-binを指定
MariaDB []> MASTER_LOG_POS=382;`
```

　
12. 念のために、mariaDBで使用するポートを許可
mariaDBは3306番を使用するため、CentOS 7のfirewalldを使って恒久的にTCPを許可します。

```:（マスターとスレーブ）
$ sudo firewall-cmd --add-port=3306/tcp --permanent
$ firewall-cmd --reload //設定を即時反映
```
　
13. DBのレプリケーションの開始と確認
レプリケーションを開始します。 実行して以下のように表示されればOKです。
※Yesと表示されなかった場合、後述の「DBのレプリケーションに失敗したら」に従って失敗を回避します。

```:（スレーブ）
$ mysql -u root -p
MariaDB []> START SLAVE;
MariaDB []> SHOW SLAVE STATUS\G; //￥Gは大文字にする
.
.
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
```
　
14. レプリケーションが成功しているか確認
マスターに値を追加して、スレーブ側に同じ内容が反映されていればOKです

```MariaDB:（マスター）
use sakila
MariaDB [sakila]> insert into actor ( first_name, last_name ) values ( 'KEN', 'TAKAKURA' );
MariaDB [sakila]> select * from actor;
```
```MariaDB:（スレーブ）
MariaDB [sakila]> select * from actor;
#マスターで追加したレコードと同じものが追加されることを確認する
```

#DBのレプリケーションに失敗したら
ユーザのパスワードハッシュが短い場合、DBの`secure_auth`が原因でレプリケーションに失敗します。新しい、長いパスワードハッシュを使うことで、この問題を回避できます。
`secure_auth`そのものを無効化するオプションもありますが、将来のバージョンでは「無効化機能」が「無効化」されるので使用しない方が良いでしょう。
スレーブからマスターDBへのSSH接続が成功しても、レプリケーション時にSlave_IO_Runningが`Connecting`のままで、`OK`に変化するのを待っているとハマります（ハマりました）。
次項の手順で、長いパスワードハッシュを使うように変更すると、正常にレプリケーションできるようになります。
今回使用したmariadbのバージョン(10.1.22-MariaDB MariaDB Server)では、短いハッシュがデフォルトで有効のため本手順が必要になります。

###短いパスワードハッシュが使われているか調べる
以下のコマンドを実行して、どちらのハッシュが使われているか調べます。結果は1か0で表示されます。短いハッシュが使用されている場合、マスターとスレーブのハッシュを両方とも長いハッシュに変更します。

-  1：従来の短いハッシュが使われているため、secure_authが発動する
-  0：新しい長いハッシュが使われているため、secure_authが発動しない


```:（マスター）
MariaDB [(none)]> select @@session.old_passwords, @@global.old_passwords;
+-------------------------+------------------------+
| @@session.old_passwords | @@global.old_passwords |
+-------------------------+------------------------+
|                       1 |                      0 |
+-------------------------+------------------------+
```

短いパスワードハッシュを無効にする

```:（マスターとスレーブ）
MariaDB [(none)]> set session old_passwords = 0;
```

スレーブを一度無効にし、再度有効にする

```:（マスター）
MariaDB [sakila]> stop slave;
MariaDB [sakila]> start slave;
```

レプリケーションの状態を確認する
`Slave_IO_Running: Yes`となっていればOKです。

```:（スレーブ）
MariaDB [sakila]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.31.21.6
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.0YYYYY
          Read_Master_Log_Pos: 382
               Relay_Log_File: ip-172-31-YYY-YYY-relay-bin.0YYYYY
                Relay_Log_Pos: 537
        Relay_Master_Log_File: mysql-bin.0YYYYY
             Slave_IO_Running: Yes　//正常（レプリケーションに失敗すると、Connectingのまま変化しない）
            Slave_SQL_Running: Yes　//正常
```

#参考
- Sakila Sample Database - MySQL公式
https://dev.mysql.com/doc/sakila/en/

- Sakila Sample Database - 空色ブログ
https://blogs.oracle.com/solairo/sakila-sample-database

- 6.1.2.4 Password Hashing in MySQL - MySQL公式
  https://dev.mysql.com/doc/refman/5.6/en/password-hashing.html

- ＜続＞MySQL 5.6 old_passwords=1 環境へのユーザー情報移行が難しい - In a moment of madness 
http://moontalk.hatenablog.com/entry/2013/12/16/221508

- MySQL access and replication blocked by secure_auth - Another MySQL DBA
http://anothermysqldba.blogspot.jp/2013/09/mysql-access-and-replication-blocked-by.html