CentOS8+MySQL 8.0のActive-Standby構成、HAProxy経由で

ちょっと、用語が変わったり、ややこしくなる前に現状のMySQL、Active-Standby構成の手順をまとめておこうと思った。

CentOS 8.3のインストールDVDの中に入っているMySQLはバージョン8.0.21で、これはギリギリ、アメリカのBLM活動から始まる用語改革の波に飲まれていないバージョンである。

最近はInnoDBクラスターじゃないのか？mysql-shellとmysql-routerがCentOS 8（≒RHEL 8）のインストールDVDの中に入っていないのでちょっと使い勝手が悪い。
今回説明する構成の良いところは、MySQL Workbench以外のサーバー側コンポーネントについて、RHEL 8のサブスクリプションを持っていればそのサポートのカバー範囲内のものしか使っていない点にある。

（1/25追記）OpenShift 4で似たような構成を試してみた。
https://qiita.com/rk05231977/items/de9680d9a2dc6eb640df

構成

手元のMySQL WorkbenchからMySQLサーバーに繋ぐ下図のような構成を作ってみる。

mysql1とmysql2はマニュアルの以下で説明される準同期レプリケーションを構成する。
https://dev.mysql.com/doc/refman/8.0/en/replication-howto.html

mysql1障害時はmysql2がレプリケーションを止めて、自分がActiveサーバーになる。
MySQL WorkbenchからDBへの接続は、途中にHAProxyが挟まってActive DB側にリクエストを振る構成である。

OSのインストールイメージからmysql-server、haproxy等が導入できれば、mysqlサーバー、haproxyサーバーはインターネット非接続でもOK。

本記事ではHAproxyの冗長化には触れないが、CentOS/RHELで、Keepalivedを使ってHAproxyを冗長化する方法は別途確立されている。
https://access.redhat.com/documentation/ja-jp/red_hat_enterprise_linux/7/html/load_balancer_administration/ch-lvs-overview-vsa

HAproxyでなくiptablesのport forwardingでもいいし、クラウドならNLBでもいいと思うが、KeepalivedをMySQLサーバー上で直接実行するのは手動切り替え、切り戻しに気を使うのでやめた方が良い。と思う。
HAproxyだと、statsの画面で現在のConnection数がWeb画面で見えるのが良い。後述する。

また、本記事内ではActive-Standby切り替えを自動化する事については触れないが、手順は十分に機械的なのでcron、あるいは秒単位で制御したいならsystemd、により切り替えを自動化するのはそれほど難しくないだろう（「フェールオーバーしてみる」の手順３～５が自動化できればいいのだ）。

mysql1サーバーを作る

とりあえず、OSにCentOS 8.3が導入済みで、IPアドレスが設定され、SSHで接続が可能なサーバーを作る。
IPアドレスは 192.168.0.201、ホスト名はmysql1とする。
また、OS導入に使ったDVDメディアが/dev/cdromでアクセスできるものとする。

１．mysql-serverをインストールする。

# mount /dev/cdrom /media
# rm -f /etc/yum.repos.d/*
# cat > /etc/yum.repos.d/media.repo << 'EOF'
[media-baseos]
name=CentOS Linux 8 - Media - BaseOS
baseurl=file:///media/BaseOS
gpgcheck=1
enabled=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-centosofficial

[media-appstream]
name=CentOS Linux $releasever - Media - AppStream
baseurl=file:///media/AppStream
gpgcheck=1
enabled=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-centosofficial
EOF

# yum install -y mysql-server
# systemctl enable --now mysqld
# firewall-cmd --add-service=mysql --zone=public --permanent
# firewall-cmd --reload

２．何もデータが無いのもつまらないので、国データベースを初期データとしてロードしようか。
サーバーがインターネットに繋がっていない場合はworld.sql.gzを別途ダウンロードしてmysql1サーバーにコピーしておく。

# curl -k -O -L https://downloads.mysql.com/docs/world.sql.gz
# gunzip world.sql.gz
# mysql < world.sql

３．MySQL Workbenchから繋ぐ用のadminユーザーを作る。

# mysql -e "CREATE USER 'admin'@'%' IDENTIFIED BY 'password';"
# mysql -e "GRANT ALL ON *.* TO 'admin'@'%';"

４．ここからがレプリケーション用の構成。
https://dev.mysql.com/doc/refman/8.0/en/binlog-replication-configuration-overview.html

MySQLにserver-idを設定し、Standbyサーバーからレプリケーションで繋ぐためのユーザーを作る。
mysql1サーバーのIPアドレスが192.168.0.201なので、server-idには201を設定しようか。
Standbyサーバーmysql2には、IPアドレス192.168.0.202を使用する。

# cat > /etc/my.cnf.d/server-id.cnf << EOF
[mysqld]
server-id=201
EOF
# systemctl restart mysqld
# mysql -e "CREATE USER 'repl'@'192.168.0.202' IDENTIFIED BY 'password';"
# mysql -e "GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.0.202';"

５．レプリケーション用の初期データを用意し、レプリケーションの開始点を確認する。

# mysql -e "FLUSH TABLES WITH READ LOCK;"
# mysql -e "SHOW MASTER STATUS\G;" > master-status.txt
# mysqldump --all-databases --master-data > dbdump.db
# mysql -e "UNLOCK TABLES;"
# cat master-status.txt

（実行結果 - SHOW MASTER STATUS）
[root@mysql1 ~]# cat master-status.txt
*************************** 1. row ***************************
             File: binlog.000002
         Position: 683
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:

「binlog.～」と「Position」の値がStandby側でレプリケーションを開始するのに必要となる。

mysql2サーバーを作る

OSにCentOS 8.3が導入済みで、IPアドレスが設定され、SSHで接続が可能なサーバーを作る。
IPアドレスは 192.168.0.202、ホスト名はmysql2とする。

１．mysql-serverをインストールする。
手順割愛。mysql1の手順を参照。

２．MySQL Workbenchから繋ぐ用のadminユーザーを作る。
Standby状態のときは繋ぎに来ないが、Activeサーバーと同じユーザー名、パスワードの接続ユーザーを作る。

# mysql -e "CREATE USER 'admin'@'%' IDENTIFIED BY 'password';"
# mysql -e "GRANT ALL ON *.* TO 'admin'@'%';"

３．初期データをロードする。
mysql1のバックアップをロードする。

# scp 192.168.0.201:dbdump.db .
# mysql < dbdump.db

４．ここからがレプリケーション用の構成、準備編。
server-idを設定する。mysql2サーバーのIPアドレスが192.168.0.202なので、server-idは202。
また、mysql1サーバーがStandby落ちした時のレプリケーション用ユーザーも作る。

# cat > /etc/my.cnf.d/server-id.cnf << EOF
[mysqld]
server-id=202
EOF
# systemctl restart mysqld
# mysql -e "CREATE USER 'repl'@'192.168.0.201' IDENTIFIED BY 'password';"
# mysql -e "GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.0.201';"

５．レプリケーションを開始する。
MASTER_LOG_FILE、MASTER_LOG_POSは先のmaster1の手順で確認したレプリケーションの開始点である。

# MASTER_LOG_FILE=binlog.000002
# MASTER_LOG_POS=683
# mysql -e "CHANGE MASTER TO MASTER_HOST='192.168.0.201', \
MASTER_USER='repl', MASTER_PASSWORD='password', \
MASTER_LOG_FILE='${MASTER_LOG_FILE}', MASTER_LOG_POS=${MASTER_LOG_POS};"
# mysql -e "START SLAVE;"

レプリケーションの状態がどうなっているかはSHOW SLAVE STATUS\Gコマンドで確認するが、一旦それは置いておいて先に進もう。

HAProxyを作る

OSにCentOS 8.3が導入済みで、IPアドレスが設定され、SSHで接続が可能なサーバーを作る。
IPアドレスは 192.168.0.200、ホスト名はhaproxyとする。

１．haproxyをインストールする。

# mount /dev/cdrom /media
# rm -f /etc/yum.repos.d/*
# cat > /etc/yum.repos.d/media.repo << 'EOF'
[media-baseos]
name=CentOS Linux 8 - Media - BaseOS
baseurl=file:///media/BaseOS
gpgcheck=1
enabled=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-centosofficial

[media-appstream]
name=CentOS Linux $releasever - Media - AppStream
baseurl=file:///media/AppStream
gpgcheck=1
enabled=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-centosofficial
EOF

# yum install -y haproxy

２．mysql1にmysqlの接続を振る設定を追加する。

# cat > /etc/haproxy/haproxy.cfg << EOF
global
    log         127.0.0.1 local2
    chroot      /var/lib/haproxy
    pidfile     /var/run/haproxy.pid
    maxconn     10
    user        haproxy
    group       haproxy
    daemon
    stats socket /var/lib/haproxy/stats
defaults
    mode                    tcp
    log                     global
    retries                 3
    timeout connect         10s
    timeout client          1m
    timeout server          1m
    maxconn     10

frontend mysql
    bind *:3306
    default_backend mysql
backend mysql
    server mysql1 192.168.0.201

EOF

# setsebool -P haproxy_connect_any=1
# systemctl enable --now haproxy
# firewall-cmd --add-service=mysql --zone=public --permanent
# firewall-cmd --reload

これで、手元の端末からHAproxy経由でmysql1サーバーのMySQLに繋がるようになったはずである。

MySQL WorkbenchからMySQLに繋ぐ

１．手元のPCにMySQL Workbenchをダウンロードしてインストールする。
https://www.mysql.com/jp/products/workbench/

２．HAproxyのIPアドレスに向けたConnectionを作って繋ぐ。

３．レコードを追加してみよう。
Schemas > world > Tables > countryのテーブルを開き、以下のSQL文を実行（⚡）する。

INSERT INTO `world`.`country` (`Code`, `Name`, `Continent`, `Region`, `SurfaceArea`, `Population`, `LocalName`, `GovernmentForm`, `Code2`) VALUES ('ASR', 'Asura Kingdom', 'Europe', 'Central Region', '100000.00', '1000000', 'Asura Kingdom', 'Monarchy', 'AA');

レコードが追加されただろうか。

フェールオーバーしてみる

それでは、おもむろにmysql1サーバーの電源を落として、mysql2サーバーにフェールオーバーしてみよう。

１．mysql1サーバーの電源を落とす。仮想マシンならPower Offで。

２．MySQL Workbenchで表がロードできなくなった事を確認する。

３．念のため、haproxyサーバーからmysqlへの振り分けを止めておく。
haproxyサーバーで以下を実行する。

# systemctl stop haproxy

４．mysql2サーバーで以下を実行する。
レプリケーションを停止し、Activeサーバーになっても良い様に準備する。

# mysql -e "SHOW SLAVE STATUS\G;"
　→ 「Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates」となっているのを確認する。
# mysql -e "STOP SLAVE;"
# mysql -e "RESET SLAVE ALL;"

# mysql -e "FLUSH TABLES WITH READ LOCK;"
# mysql -e "SHOW MASTER STATUS\G;" > master-status.txt
# mysql -e "UNLOCK TABLES;"
# cat master-status.txt

（実行結果）
[root@mysql2 ~]# cat master-status.txt
*************************** 1. row ***************************
             File: binlog.000002
         Position: 1076
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:

「binlog.～」と「Position」の値は、mysql1がStandbyとしてレプリケーションを開始するのに必要となる。

この時点でmysql2は新しいActiveサーバーとして機能しているので、HAproxyの振り分けをmaster2に変更する。

５．haproxyサーバーで以下を実行する。

# sed -i -e 's/server mysql1 192.168.0.201/server mysql2 192.168.0.202/' /etc/haproxy/haproxy.cfg
# systemctl start haproxy

６．MySQL Workbenchで再度、表をロードする。
繋がるようになっているはずである。ついでに、先にmysql1で追加したレコードがmysql2にもデータ反映されている事を確認しよう。

７．mysql2がActiveサーバーになっている段階で、もう一つレコードを追加してみる。
MySQL Workbenchで以下の文を実行（⚡）する。

INSERT INTO `world`.`country` (`Code`, `Name`, `Continent`, `Region`, `SurfaceArea`, `Population`, `LocalName`, `GovernmentForm`, `Code2`) VALUES ('MLS', 'Holy Country of Millis', 'South America', 'Southern Millis', '100000', '1000000', 'Millis', 'Church State', 'MI');

元のActiveサーバーに切り戻す

mysql1サーバーに切り戻すところもやってみよう。

１．mysql1サーバーの電源を入れ、sshで接続する。

２．まずはmysql1サーバーを、mysql2サーバーのレプリカ（Standby）サーバーとして構成する。シャットダウン中のデータ更新に追いつかせるためである。
mysql1サーバーで以下を実行する。

# mysql -e "CHANGE MASTER TO MASTER_HOST='192.168.0.202', \
MASTER_USER='repl', MASTER_PASSWORD='password', \
MASTER_LOG_FILE='binlog.000002', MASTER_LOG_POS=1076;"
# mysql -e "START SLAVE;"

３．mysql1サーバーで以下のコマンドを実行し、レプリケーションが追いつくのを待つ。

# mysql -e "SHOW SLAVE STATUS\G;"
　→ 「Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates」となるのを確認する。

レプリケーションが追いついたらmysql1をActiveサーバーとして復帰するための準備完了である。

４．念のため、haproxyサーバーからmysqlへの振り分けを止めておく。
haproxyサーバーで以下を実行する。

# systemctl stop haproxy

５．mysql2サーバーで以下を実行する。一回、mysqldを止める。

# systemctl stop mysqld

６．mysql1サーバーで以下を実行する。
レプリケーションを止め、Activeサーバーに復帰するための準備である。

# mysql -e "SHOW SLAVE STATUS\G;"
→ 「Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates」を待つ。

# mysql -e "STOP SLAVE;"
# mysql -e "RESET SLAVE ALL;"

# mysql -e "FLUSH TABLES WITH READ LOCK;"
# mysql -e "SHOW MASTER STATUS\G;" > master-status.txt
# mysql -e "UNLOCK TABLES;"
# cat master-status.txt

（実行結果）
[root@mysql1 ~]# cat master-status.txt
*************************** 1. row ***************************
             File: binlog.000003
         Position: 556
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:

「binlog.～」と「Position」の値は、mysql2が再度Standbyとしてレプリケーションを開始するのに必要となる。もういいか。

master1がActiveサーバーとして復帰する準備が整った。

７．HAproxyでリクエストをmaster1に振るよう設定し、開始する。
haproxyサーバーで以下を実行する。

# sed -i -e 's/server mysql2 192.168.0.202/server mysql1 192.168.0.201/' /etc/haproxy/haproxy.cfg
# systemctl start haproxy

８．MySQL Workbenchで再度、表をリロードする。表がリロードされ、master2側に追加したレコードがmaster1にも反映されていることを確認する。

９．最後に、mysql2サーバーが改めてStandbyとなるように再構成する。
mysql2サーバーで以下を実行する。

# systemctl start mysqld
# mysql -e "CHANGE MASTER TO MASTER_HOST='192.168.0.201', \
MASTER_USER='repl', MASTER_PASSWORD='password', \
MASTER_LOG_FILE='binlog.000003', MASTER_LOG_POS=556;"
# mysql -e "START SLAVE;"

（おまけ）HAproxyでMySQLのコネクション数を確認する

最初の方でちょろっと触れたHAproxyで現在のコネクション数を確認する件。

１．haproxyサーバーで、haproxyの設定ファイルを修正し、statsを有効にする。
admin:adminはユーザー名とパスワード。

/etc/haproxy/haproxy.cfg

（ファイルの最後に以下を追加して保存）

frontend stats
    bind *:8404
    mode http
    stats enable
    stats realm Haproxy\ Statistics
    stats uri /
    stats auth admin:admin

２．haproxyの設定をreloadする。

# systemctl reload haproxy
# firewall-cmd --add-port=8404/tcp

３．Webブラウザでhaproxyサーバーにアクセスする。
http://192.168.0.200:8404/

以下のような画面が表示される。字が小さく読めないかもしれないが、現在のセッション数などがGUIで確認できて便利。