MySQL Utilitiesの導入とmysqlfailoverの動作確認

準同期レプリケーションの環境向けにMySQL Utilitiesを導入し、mysqlfailoverの動作を確認します。最後に実運用へのmysqlfailover導入に向けての課題出しもしてみたいと思います。

mysqlfailoverとは

MySQLの準同期レプリケーションはスレーブの台数はいくらでも増やすことができますが、マスターとしては、1台しか動作できず、そのマスターでしかINSERTやUPDATEなどの更新(DML文)を実行できない仕組み上の制約があります。
そのマスターの生死を監視し、障害が発生した時にスレーブの1台をマスターに昇格するフェイルオーバーを自動で実行してくれるのが今回導入するmysqlfailoverです。mysqlfailoverはOracleが開発・配布しているMySQL Utilitiesの一つの機能です。
従来、この様なマスターの可用性の確保はMaster-HA for MySQL(以下MHA)で実現するのが一般的でしたが、残念ながらMHAの開発やrpmパッケージの配布が終了してしまっているそうです。

環境・構成

今回MySQLとは別に監視用のサーバを立てています。監視専用のサーバを確保できない場合などは、スレーブの1台にmysqlfailoverを導入する事も可能です。
MySQLの導入や準同期レプリケーションの設定は過去の記事で紹介した手順で導入しています。

CentOS7&MySQL5.7でクラッシュセーフな準同期レプリケーションを構築する - tkn4416 Tech Blog

f:id:tkn4416:20180410074330p:plain

監視用のサーバの構成

MySQL Utilities1.6.5
CentOS Linux release 7.4.1708 (Core)
・ESXi6.5仮想サーバー(RAM:2GB、HDD:16GB(シンプロビジョニング))
NIC:1枚(192.168.1.0/24に接続)

監視対象のMySQLサーバの仕様・パラメータ

以下の過去記事の手順で、マスター1台、スレーブ2台の準同期レプリケーションを設定しています。明記しない限り、システム変数(パラメータ)も以下の記事の設定です。

サーバ構成も記事の通りで、全サーバ(3台)とも以下の通りです。
・MySQL5.7.21
CentOS Linux release 7.4.1708 (Core)
・ESXi6.5仮想サーバー(RAM:2GB、HDD:16GB(シンプロビジョニング))
NIC:1枚(192.168.1.0/24に接続)

手順

パスワードやGTID等は*(アスタリスク)で伏字にしています。

1. レプリケーション設定の変更

前回の記事ではSTART SLAVE構文でレプリケーション用のユーザとパスワードを設定していましたが、CHANGE MASTER TOでユーザとパスワードを設定し、master info repositoryにマスター接続情報が登録されないとmysqlfailoverが動作しなかった為、スレーブ全台で設定をし直します。
セキュリティについてNoteレベルのメッセージ(Code 1759/1760)がでていますが、1760については後ほど実運用に向けての課題1で触れています。

[MySQLスレーブ全台(192.168.1.1213)]
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> reset slave all;
Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE MASTER TO
    ->     MASTER_HOST = '192.168.1.11',
    ->     MASTER_PORT = 3306,
    ->     MASTER_USER= 'repl',
    ->     MASTER_PASSWORD= '******',
    ->     MASTER_AUTO_POSITION = 1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

Note (Code 1759): Sending passwords in plain text without SSL/TLS is extremely insecure.
Note (Code 1760): Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.11
                  Master_User: repl
                  Master_Port: 3306
(中略)
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
(以下略)

2. MySQL Utilitiesのインストール

監視用サーバにMySQL UtilitiesとMySQL Clientを導入します。

初めに、MySQL公式Yumリポジトリ情報をインストールします。インストールするRPMパッケージは、下記のURLから最新版のファイル名を確認してコマンドを置き換えてください。
https://dev.mysql.com/downloads/repo/yum/

[監視用サーバ]
shell> yum localinstall https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm

MySQL UtilitiesとMySQL Clientをインストールします。

[監視用サーバ]
shell> yum install mysql-utilities mysql-community-client

以下のパッケージがインストールまたは置換されました。

インストール:
  mysql-community-client.x86_64 0:5.7.21-1.el7
  mysql-community-libs.x86_64 0:5.7.21-1.el7
  mysql-community-libs-compat.x86_64 0:5.7.21-1.el7
  mysql-utilities.noarch 0:1.6.5-1.el7

依存性関連をインストールしました:
  mysql-community-common.x86_64 0:5.7.21-1.el7
  mysql-connector-python.x86_64 0:2.1.7-1.el7

置換:
  mariadb-libs.x86_64 1:5.5.56-2.el7

3. 監視用ユーザを作成と接続確認

監視用サーバ(Linuxユーザ)

監視用のサーバに監視用のLinuxユーザを追加します。

[監視用サーバ]
shell> useradd dbmon
shell> passwd dbmon

監視対象MySQLサーバ(MySQLユーザ)

MySQLのマスター(192.168.1.11)のみで実行します。(スレーブにも反映されます。)

[MySQLマスター]
CREATE USER 'utilities'@'192.168.1.%' identified by '******';

GRANT ALL PRIVILEGES ON *.* TO 'utilities'@'192.168.1.%' WITH GRANT OPTION;

接続確認

監視用サーバに作成したユーザ(dbmon)でログインし直し、監視対象MySQLサーバへの接続を確認します。

[監視用サーバ]
shell> mysql -u utilities -h 192.168.1.11 -p

4. MySQL Utilitiesの動作確認

MySQL Utilitiesの動作確認として、mysqlfailoverの前にmysqlrpladminというツールでレプリケーションのヘルスチェックをしてみます。

[監視用サーバ]
[dbmon@**** ~]$ mysqlrpladmin --master=utilities:******@192.168.1.11:3306 \
> --slaves=utilities:******@192.168.1.12:3306,utilities:******@192.168.1.13:3306 \
> health
WARNING: Using a password on the command line interface can be insecure.
# Checking privileges.
#
# Replication Topology Health:
+------------------+-------+---------+--------+------------+---------+
| host             | port  | role    | state  | gtid_mode  | health  |
+------------------+-------+---------+--------+------------+---------+
| 192.168.1.11     | 3306  | MASTER  | UP     | ON         | OK      |
| 192.168.1.12     | 3306  | SLAVE   | UP     | ON         | OK      |
| 192.168.1.13     | 3306  | SLAVE   | UP     | ON         | OK      |
+------------------+-------+---------+--------+------------+---------+
# ...done.

5. mysqlfailoverの監視用デーモン起動

mysqlfailoverのデーモンを起動します。

[dbmon@**** ~]$ mysqlfailover \
> --master=utilities:******@192.168.1.11:3306 \
> --slaves=utilities:******@192.168.1.12:3306,utilities:******@192.168.1.13:3306 \
> --log=/home/dbmon/mysqlfailover.log \
> --pidfile=/home/dbmon/failover_daemon.pid \
> --rpl-user=repl:****** \
> --failover-mode=auto \
> --daemo=start \
> -v
WARNING: Using a password on the command line interface can be insecure.
NOTE: Log file '/home/dbmon/mysqlfailover.log' does not exist. Will be created.
Starting failover daemon...

[dbmon@**** ~]$ ps aux | grep mysqlfailover
dbmon    22176  0.5  0.6 242532 13080 ?        S    07:18   0:00 /usr/bin/python /usr/bin/mysqlfailover --master=utilities:******@192.168.1.11:3306 --slaves=utilities:******@192.168.1.12:3306,utilities:******@192.168.1.13:3306 --log=/home/dbmon/mysqlfailover.log --pidfile=/home/dbmon/failover_daemon.pid --rpl-user=repl:****** --failover-mode=auto --daemo=start -v
dbmon    22188  0.0  0.0 112676   992 pts/0    R+   07:19   0:00 grep --color=auto mysqlfailover

起動しました。
psコマンドでパスワードが丸見えですが、実運用に向けての課題2で簡単に対処方法を紹介しています。

パラメータで指定したログファイルには以下の様に起動時のログと定期的なヘルスチェック結果が出力されます。

[dbmon@**** ~]$ cat /home/dbmon/mysqlfailover.log
2018-04-04 07:18:56 AM INFO MySQL Utilities mysqlfailover version 1.6.5.
2018-04-04 07:18:57 AM INFO Server '192.168.1.11:3306' is using MySQL version 5.7.21-log.
2018-04-04 07:18:57 AM INFO Server '192.168.1.12:3306' is using MySQL version 5.7.21-log.
2018-04-04 07:18:57 AM INFO Server '192.168.1.13:3306' is using MySQL version 5.7.21-log.
2018-04-04 07:18:57 AM INFO Checking privileges.
2018-04-04 07:18:57 AM INFO Unregistering existing instances from slaves.
2018-04-04 07:18:57 AM INFO Registering instance on master.
2018-04-04 07:18:57 AM INFO Failover daemon started.
2018-04-04 07:18:57 AM INFO Failover mode = auto.
2018-04-04 07:19:00 AM INFO Master Information
2018-04-04 07:19:00 AM INFO Binary Log File: mysql-bin.000003, Position: 403, Binlog_Do_DB: N/A, Binlog_Ignore_DB: N/A
2018-04-04 07:19:00 AM INFO GTID Executed Set: ********-****-****-****-************:1-4
2018-04-04 07:19:00 AM INFO Getting health for master: 192.168.1.11:3306.
2018-04-04 07:19:00 AM INFO Health Status:
2018-04-04 07:19:00 AM INFO host: 192.168.1.11, port: 3306, role: MASTER, state: UP, gtid_mode: ON, health: OK, version: 5.7.21-log, master_log_file: mysql-bin.000003, master_log_pos: 403, IO_Thread: , SQL_Thread: , Secs_Behind: , Remaining_Delay: , IO_Error_Num: , IO_Error: , SQL_Error_Num: , SQL_Error: , Trans_Behind:
2018-04-04 07:19:00 AM INFO host: 192.168.1.12, port: 3306, role: SLAVE, state: UP, gtid_mode: ON, health: OK, version: 5.7.21-log, master_log_file: mysql-bin.000003, master_log_pos: 403, IO_Thread: Yes, SQL_Thread: Yes, Secs_Behind: 0, Remaining_Delay: No, IO_Error_Num: 0, IO_Error: , SQL_Error_Num: 0, SQL_Error: , Trans_Behind: 0
2018-04-04 07:19:00 AM INFO host: 192.168.1.13, port: 3306, role: SLAVE, state: UP, gtid_mode: ON, health: OK, version: 5.7.21-log, master_log_file: mysql-bin.000003, master_log_pos: 403, IO_Thread: Yes, SQL_Thread: Yes, Secs_Behind: 0, Remaining_Delay: No, IO_Error_Num: 0, IO_Error: , SQL_Error_Num: 0, SQL_Error: , Trans_Behind: 0
(以下、Master Informationt部分とHealth Status部分の繰り返し)

6. マスターを停止してフェイルオーバーを発動させる

フェイルオーバーを発動させて動作を確認する為、マスター(192.168.1.11)のMySQLサーバを停止します。

[マスター]
# systemctl stop mysqld.service

しばらくするとフェイルオーバーが発動しました。 mysqlfailoverのログファイルにフェイルオーバーの状況の詳細が出力されます。

正常にフェイルオーバーすると残りの2台構成で監視を継続しています。MHAは1回しかフェイルオーバーできず二重障害に対応できなかったので、この点はMHAより優れています。

[監視用サーバ]
(/home/dbmon/mysqlfailover.logから抜粋)
2018-04-04 07:27:46 AM INFO host: 192.168.1.12, port: 3306, role: SLAVE, state: UP, gtid_mode: ON, health: OK, version: 5.7.21-log, master_log_file: mysql-bin.000003, master_log_pos: 403, IO_Thread: Yes, SQL_Thread: Yes, Secs_Behind: 0, Remaining_Delay: No, IO_Error_Num: 0, IO_Error: , SQL_Error_Num: 0, SQL_Error: , Trans_Behind: 0
2018-04-04 07:27:46 AM INFO host: 192.168.1.13, port: 3306, role: SLAVE, state: UP, gtid_mode: ON, health: OK, version: 5.7.21-log, master_log_file: mysql-bin.000003, master_log_pos: 403, IO_Thread: Yes, SQL_Thread: Yes, Secs_Behind: 0, Remaining_Delay: No, IO_Error_Num: 0, IO_Error: , SQL_Error_Num: 0, SQL_Error: , Trans_Behind: 0
2018-04-04 07:28:10 AM INFO Master may be down. Waiting for 3 seconds.
2018-04-04 07:28:25 AM INFO Failed to reconnect to the master after 3 attempts.
2018-04-04 07:28:25 AM CRITICAL Master is confirmed to be down or unreachable.
2018-04-04 07:28:25 AM INFO Failover starting in 'auto' mode...
2018-04-04 07:28:25 AM INFO Checking eligibility of slave 192.168.1.12:3306 for candidate.
2018-04-04 07:28:25 AM INFO GTID_MODE=ON ... Ok
2018-04-04 07:28:25 AM INFO Replication user exists ... Ok
2018-04-04 07:28:25 AM INFO Candidate slave 192.168.1.12:3306 will become the new master.
2018-04-04 07:28:25 AM INFO Checking slaves status (before failover).
2018-04-04 07:28:25 AM INFO Preparing candidate for failover.
2018-04-04 07:28:25 AM INFO Reading events in relay log for slave 192.168.1.12:3306
2018-04-04 07:28:25 AM INFO Reading events in relay log for slave 192.168.1.13:3306
2018-04-04 07:28:25 AM INFO No missing transactions found on 192.168.1.13:3306. Skipping connection of candidate as slave.
2018-04-04 07:28:25 AM INFO Creating replication user if it does not exist.
2018-04-04 07:28:25 AM INFO Stopping slaves.
2018-04-04 07:28:25 AM INFO Performing STOP on all slaves.
2018-04-04 07:28:26 AM WARNING Executing stop on slave 192.168.1.12:3306 WARN - slave is not configured with this master
2018-04-04 07:28:26 AM INFO Executing stop on slave 192.168.1.12:3306 Ok
2018-04-04 07:28:26 AM WARNING Executing stop on slave 192.168.1.13:3306 WARN - slave is not configured with this master
2018-04-04 07:28:26 AM INFO Executing stop on slave 192.168.1.13:3306 Ok
2018-04-04 07:28:26 AM INFO Switching slaves to new master.
2018-04-04 07:28:26 AM INFO Disconnecting new master as slave.
2018-04-04 07:28:26 AM INFO Execute on 192.168.1.12:3306: RESET SLAVE ALL
2018-04-04 07:28:26 AM INFO Starting slaves.
2018-04-04 07:28:26 AM INFO Performing START on all slaves.
2018-04-04 07:28:26 AM INFO Executing start on slave 192.168.1.13:3306 Ok
2018-04-04 07:28:26 AM INFO Checking slaves for errors.
2018-04-04 07:28:27 AM INFO 192.168.1.13:3306 status: Ok
2018-04-04 07:28:27 AM INFO Failover complete.
2018-04-04 07:28:32 AM INFO Unregistering existing instances from slaves.
2018-04-04 07:28:32 AM INFO Registering instance on new master 192.168.1.12:3306.
2018-04-04 07:28:32 AM INFO Master Information
2018-04-04 07:28:32 AM INFO Binary Log File: mysql-bin.000002, Position: 968, Binlog_Do_DB: N/A, Binlog_Ignore_DB: N/A
2018-04-04 07:28:32 AM INFO GTID Executed Set: ********-****-****-****-************:1-4[...]
2018-04-04 07:28:32 AM INFO Getting health for master: 192.168.1.12:3306.
2018-04-04 07:28:32 AM INFO Health Status:
2018-04-04 07:28:32 AM INFO host: 192.168.1.12, port: 3306, role: MASTER, state: UP, gtid_mode: ON, health: OK, version: 5.7.21-log, master_log_file: mysql-bin.000002, master_log_pos: 968, IO_Thread: , SQL_Thread: , Secs_Behind: , Remaining_Delay: , IO_Error_Num: , IO_Error: , SQL_Error_Num: , SQL_Error: , Trans_Behind:
2018-04-04 07:28:32 AM INFO host: 192.168.1.13, port: 3306, role: SLAVE, state: UP, gtid_mode: ON, health: OK, version: 5.7.21-log, master_log_file: mysql-bin.000002, master_log_pos: 968, IO_Thread: Yes, SQL_Thread: Yes, Secs_Behind: 0, Remaining_Delay: No, IO_Error_Num: 0, IO_Error: , SQL_Error_Num: 0, SQL_Error: , Trans_Behind: 0
(以下、Master Informationt部分とHealth Status部分の繰り返し)

7. 監視デーモンの停止

デーモンを停止するだけですが、ちょっと注意が必要です。
MySQL Utilities1.6.5のバグ(※BUG#85254)の回避の為、デーモン停止の前に追加手順が必要です。

MySQL Bugs: #85254: mysqlfailover 1.6.5 stop breaks replication
監視用テーブル(mysql.failover_console)のCREATE時、マスターでバイナリログの出力をOFFにしている(スレーブにレプリケーションしない)が、DROP時にバイナリログの出力を停止していないので、スレーブで存在しないテーブルを削除しようとしてエラーが発生し、レプリケーションが停止してしまう事象。

回避策はデーモンの停止の前に、MySQLのマスターで以下のDROP文を流すだけです。

[新マスター]
DROP TABLE IF EXISTS mysql.failover_console;

監視デーモンの停止は監視用サーバーで以下のコマンドを実行します。

[監視用サーバ]
[dbmon@**** ~]$ mysqlfailover --daemo=stop --pidfile=/home/dbmon/failover_daemon.pid

8. 旧マスターの後処理

動作確認の後片付けとして、フェイルオーバーを発動させる為に停止した旧マスターを新マスターのスレーブとして設定します。

MySQLサーバを起動します。

[旧マスター]
# systemctl start mysqld.service

旧マスターを新マスターのスレーブに設定します。

[旧マスター]
mysql> reset slave all;
Query OK, 0 rows affected (0.01 sec)

mysql> CHANGE MASTER TO
    ->     MASTER_HOST = '192.168.1.12',
    ->     MASTER_PORT = 3306,
    ->     MASTER_USER= 'repl',
    ->     MASTER_PASSWORD= '******',
    ->     MASTER_AUTO_POSITION = 1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

Note (Code 1759): Sending passwords in plain text without SSL/TLS is extremely insecure.
Note (Code 1760): Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.12
                  Master_User: repl
                  Master_Port: 3306
(中略)
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
(以下略)

実運用へのmysqlfailover導入に向けての課題

ここまでの動作確認で分かった対処の検討が必要な課題や、実現できていないことをまとめます。

(課題1) slave_master_infoでレプリケーション用ユーザのパスワードが見えてしまう

mysql.slave_master_infoにレプリケーション用ユーザのパスワードが平文で載ってしまいます(★の部分)。これは、手順1でSTART SLAVE文でパスワードを指定する方法だとmysqlfailoverが動作しない問題の対処として、CHANGE MASTER TO構文でパスワードを指定した為です。
これについては、権限設定でmysqlスキーマかこのテーブル単体で管理者以外に見せない設定にしてしまえば良いだけですね。

mysql> select * from mysql.slave_master_info\G
*************************** 1. row ***************************
       Number_of_lines: 25
       Master_log_name: mysql-bin.000003
        Master_log_pos: 194
                  Host: 192.168.1.11
             User_name: repl
         User_password: ******(★)
                  Port: 3306
         Connect_retry: 60
           Enabled_ssl: 0
                Ssl_ca:
            Ssl_capath:
              Ssl_cert:
            Ssl_cipher:
               Ssl_key:
Ssl_verify_server_cert: 0
             Heartbeat: 30
                  Bind:
    Ignored_server_ids: 0
                  Uuid: 1d1d635c-257d-11e8-a4e9-000c2969ff50
           Retry_count: 86400
               Ssl_crl:
           Ssl_crlpath:
 Enabled_auto_position: 1
          Channel_name:
           Tls_version:
1 row in set (0.00 sec)

(課題2) psコマンドでmysqlfailover用のパスワードが見えてしまう

手順5で前述の通り、監視用サーバでpsコマンドを叩くとmysqlfailover監視デーモンの起動時に指定したパスワードが見えてしまいます。(以下の*伏字部分)
これはlogin-path(.mylogin.cnf)を使って認証することで対処可能です。login-pathなら他のユーザから見えてしまっても.mylogin.cnfを設定したLinuxユーザでログインしないとMySQLに接続できないからです。

[dbmon@**** ~]$ ps aux | grep mysqlfailover
dbmon    22176  0.5  0.6 242532 13080 ?        S    07:18   0:00 /usr/bin/python /usr/bin/mysqlfailover --master=utilities:******@192.168.1.11:3306 --slaves=utilities:******@192.168.1.12:3306,utilities:******@192.168.1.13:3306 --log=/home/dbmon/mysqlfailover.log --pidfile=/home/dbmon/failover_daemon.pid --rpl-user=repl:****** --failover-mode=auto --daemo=start -v
dbmon    22188  0.0  0.0 112676   992 pts/0    R+   07:19   0:00 grep --color=auto mysqlfailover

(課題3) 監視デーモン停止でレプリケーションが停止してしまう(BUG#85254)

手順7で回避手順を実施したMySQL Utilities1.6.5のバグです。MySQL Utilitiesのアップデートで根本解決するまでは、以下のDROP文をデーモン停止の前に流しましょう。 ※MySQL Bugs: #85254: mysqlfailover 1.6.5 stop breaks replication

なお、このバグは、MySQL Utilities1.6.4のバグの対処として変更された箇所に起因していて、MySQL Utilities 1.6.4以前は別の問題を抱えています。運用で回避しながら1.6.5を使うのがベストでしょう。

DROP TABLE IF EXISTS mysql.failover_console;

(課題4) クライアントからマスターへの接続の切り替え

mysqlfailoverやMySQL Utilitiesのその他のコマンドではアプリなどのクライアントからの接続の切り替えはしてくれません。MySQLがフェイルオーバーしてもクライアントからの接続を新しいマスターに切り替えないと意味がありません。
mysqlfailoverのデーモン起動の際、フェイルオーバー前後で呼び出す外部スクリプトを指定できます。そのスクリプトにフェイルオーバー前後のサーバのIPアドレスを引数で渡してくれるので、vip(仮想IPアドレス)を付け替えるスクリプトを(シェルスクリプトなどで)書くのが良いと思います。MHAでも同様にvip(仮想IPアドレス)を付け替える方法が一般的でしたので、MHAからの乗り換えの方ならスクリプトをある程度流用できそうですね。