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サーバーに繋ぐ下図のような構成を作ってみる。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/146381/9d92d4ad-717a-d070-7d2a-09b1fbbedae5.png)

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を作って繋ぐ。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/146381/bd7a78a4-307d-946c-39ba-674316275e15.png)

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

```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');
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/146381/9226dc7d-0943-0c1d-d398-4cb6e726c2f5.png)

レコードが追加されただろうか。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/146381/627e3871-49f0-4761-4a29-8c263a3ca174.png)

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

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

２．MySQL Workbenchで表がロードできなくなった事を確認する。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/146381/2f0f0cb1-acbf-eb83-c03f-3a0557857c9c.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/146381/6df774d2-3f50-2a3a-a660-df1ea6b0725a.png)

３．念のため、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にもデータ反映されている事を確認しよう。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/146381/29e94aae-89d9-4054-9790-ea4622463c80.png)

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

```sql:
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');
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/146381/98394985-3e68-efb7-06e6-4c4cd9ed8494.png)
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/146381/84013adf-ad72-7d91-5705-07a61afa634d.png)

# 元の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にも反映されていることを確認する。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/146381/ba74ec5a-24c5-02f1-a41f-1ff433a81671.png)

９．最後に、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で確認できて便利。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/146381/6233c14c-0daa-c4ef-a2a6-17a5a69eb89f.png)