MySQL
AWS
mariadb

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

はじめに

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

勉強して分かったこと

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

前提条件

同じリージョンで、且つアベイラビリティゾーンが異なるインスタンス間でレプリケーションするケースを想定します。マスターに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です

(マスター)
use sakila
MariaDB [sakila]> insert into actor ( first_name, last_name ) values ( 'KEN', 'TAKAKURA' );
MariaDB [sakila]> select * from actor;
(スレーブ)
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 //正常

参考