ProxySQL + MySQL Group ReplicationによるDB構築

この記事はリンク情報システムの2018年アドベントカレンダーのリレー記事です。
engineer.hanzomon のグループメンバによってリレーされます。
（リンク情報システムのFacebookはこちらから）

はじめに

どうも、アドベントカレンダー6日目担当の@rysk001です。
請負開発チームに居ながらあまり開発をしてない雑用係の人です。

弊社の請負業務ではソフトウェアのみ開発してインフラは別会社というパターンが多いのですが、
インフラを含めたシステム構築をお願いされることもあります。
要件に「システムを停止させないこと。」なんてサラッと書いてあると悩むのがデータベースの選択。
諸般の事情で助けてOracle先生！ができないこともあるので、地味に研究が欠かせません。

以前はRDBMSとしてMySQL Clusterを使用していましたが、
主キーを使わないjoinがやたら遅かったり時々謎の速度劣化が起きたりと少々クセが強い印象。
最近もっぱら注目しているのはMySQL Group Replicationです。

この前ProxySQL + MySQL Group Replication + MySQL Shellで環境構築＆テストをしたので
手順やら注意点を共有してみようかと思います。
試行錯誤の産物のため、誤りがありましたら遠慮無くご指摘ください。

書いてあること

• 構築したインフラの概要
• 構築のあれこれ
• 運用のあれこれ

概要

MySQL Group ReplicationはMySQLのv5.7.17で正式導入された冗長化機能です。
大まかに言うとマスタの自動切替機能が付いたレプリケーションに相当します。
設定によりマルチマスタ構成を取ることもできますが、
デッドロックが起きる可能性があるためシングルマスタ構成が推奨されてます。

MySQL Group Replicationでシングルマスタ構成を採用した場合、
マスタ以外のノードに更新クエリを投げると当然エラーになります。
ノードが落ちるなどしてマスタノードが切り替わることがあるため、
マスタを認識して更新クエリをルーティングできるL7プロキシが必要です。
MySQL Routerも使えますが、今回はProxySQLを採用しています。
プロキシを挟むことで耐障害性を持ったクラスタとして動作します。
※ProxySQLをMySQL Routerに変更するとInnoDB Cluster構成です。

ロードバランサ入り構成をざっくりと書くと以下のようになります。
Web ServerにProxySQLを入れてしまいサーバ台数を削減することもできます。

今回はテストだけなので、ProxySQLノード1台とMySQLノード3台のみ構築しています。

構築にあたっての注意点

ドキュメントを確認したり実際に構築してみると注意点が色々と出てきます。
ハマりポイントになりますのでご注意ください。
特にGroup Replicationについては要件と制限は必読です。

• 奇数台のMySQLノードにより構成する
  → Split Brain発生時に多数決によるマスタ決定を行う仕組みのため
• ストレージエンジンにはInnoDBを用いる
• 同期するテーブルにはプライマリーキーを設定する
• IPv4ネットワークを用いる
  → IPv6に対応していないため
• DNS等の名前解決手段が無い場合は /etc/hosts を設定する
  → ホスト名で接続しに行く仕組みになっており名前解決ができないと接続に失敗するため
• ProxySQLの返すバージョンをMySQLのバージョンにあわせる
  → MySQLクライアントがサーバのバージョンに合わせたオプションを投げたときにエラーになることがあるため
• MySQLのバージョンが8.0.4以降の場合、default_authentication_plugin = mysql_native_password を設定する
  → ProxySQLとMySQLの認証方式の違いに対処するため ※ここ参照

構築手順

試した構築について書いていきます。

手順の前提

OSインストール以降の設定方法です。
設定するサーバは以下の通りです。

No	ノード名	IP	host名	ユーザ
1	ProxySQLノード	192.168.0.1	proxysql-node	admin … 管理用
2	MySQLノード1	192.168.0.2	mysql-node-1	icroot … 状態取得＆運用アカウント
3	MySQLノード2	192.168.0.3	mysql-node-2	icroot … 状態取得＆運用アカウント
4	MySQLノード3	192.168.0.4	mysql-node-3	icroot … 状態取得＆運用アカウント

状態取得というのはProxySQLノードからMySQLノードの情報取得に用いるユーザです。
また、運用アカウントはシステムの運用時に用いるユーザです。
MySQLの状態取得と運用のアカウントは分けるべきだと思いますが、お試し構築のため変えていません。

手順で書いてあるコマンドは基本的にLinuxシェル上のコマンドですが
MySQL ShellとMySQLのシェルを使用することもあります。
Linuxシェルのコマンドと区別するため、
MySQL Shellのコマンドは頭に「MYSQLSH>」を、
MySQLのコマンドは頭に「MYSQL>」を付けています。

構築環境

ハードウェア

仮想マシン
CPU 2core
メモリ 4GB
ストレージ HDD 50GB

ソフトウェア

CentOS Linux 7.4.1708
MySQL Community版 8.0.12
MySQL Shell 8.0.12
ProxySQL 1.4.12

ProxySQLノードの構築

SELinuxの無効化。

setenforce 0
sed -i -e 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config

MySQLで使用する3306ポートを開放。

firewall-cmd --add-port=3306/tcp --permanent
firewall-cmd --reload

ProxySQL設定用のMySQLをインストール。

yum -y install https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
yum -y install mysql

ProxySQLをインストール。

yum -y install https://github.com/sysown/proxysql/releases/download/v1.4.12/proxysql-1.4.12-1-centos7.x86_64.rpm

設定ファイルの編集

#
# /etc/proxysql.cnf
#

datadir="/var/lib/proxysql"

admin_variables =
{
        admin_credentials = "admin:admin"
        # ProxySQLの管理コンソールへの接続はunixドメインソケットを使用
        mysql_ifaces      = "/var/lib/proxysql/admin.sock"
}

# MySQLの接続先設定。
# IPアドレスは環境に合わせて設定する。
mysql_servers =
(
       { address="192.168.0.2" , port=3306 , hostgroup=1 },
       { address="192.168.0.3" , port=3306 , hostgroup=1 },
       { address="192.168.0.4" , port=3306 , hostgroup=1 }
)

# MySQLの状態取得接続に関する設定。
# ・ProxySQLからMySQLへの転送ポートは3306
# ・認証情報は状態取得アカウントを指定
# ・server_version はMySQLノードのバージョンに合わせる
mysql_variables =
{
        interfaces                 = "0.0.0.0:3306;/var/lib/proxysql/proxysql.sock"
        default_schema             = "information_schema"
        monitor_username           = "icroot"
        monitor_password           = "********"
        commands_stats             = true
        sessions_sort              = true
        server_version             = "8.0.12 (ProxySQL)"
}

# MySQLの運用接続に関する設定
# 認証情報は運用アカウントを指定している
mysql_users:
(
{
        username          = "icroot"
        password          = "********"
        default_hostgroup = 1
        active            = 1
        max_connections   = 1000
        default_schema    = "mysql"
}
)
# ホストグループの定義
# 更新ノードと参照ノードをグループ分け
mysql_replication_hostgroups=
(
       {
                writer_hostgroup=0
                reader_hostgroup=1
                comment="replication"
       }
)
# クエリの振り分けルール設定
# 一旦全クエリをマスタノードに割り振る
# 参照処理を分散させる場合はここに設定する
mysql_query_rules:
(
       {
               rule_id=99
               active=1
               match_digest="."
               destination_hostgroup=0
               apply=1
       }
)

ProxySQLを起動する。

systemctl start proxysql
systemctl enable proxysql

MySQLノードの構築

SELinuxの無効化。

setenforce 0
sed -i -e 's/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config

MySQL Group Replicationで使用する3306/33060/33061ポートを開放。

firewall-cmd --add-port=3306/tcp --permanent
firewall-cmd --add-port=33060/tcp --permanent
firewall-cmd --add-port=33061/tcp --permanent
firewall-cmd --reload

/etc/hostsファイルの設定。
DNS等でホスト名を解決できない場合に必要です。

echo '192.168.0.2 mysql-node-1' >> /etc/hosts
echo '192.168.0.3 mysql-node-2' >> /etc/hosts
echo '192.168.0.4 mysql-node-3' >> /etc/hosts

MySQLとMySQL Shellのインストール。
サーバIDとしてIPアドレスの4バイト目の数値を設定しています。
rootユーザのパスワードも設定してます。

yum -y install https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm
yum -y install mysql-community-server mysql-shell

echo "server_id = $(ip a show dev ens192 | grep 'inet ' | awk '{print $2}' | awk -F/ '{print $1}' | awk -F. '{print $4}')" >> /etc/my.cnf
echo "default_authentication_plugin = mysql_native_password" >> /etc/my.cnf
systemctl enable mysqld
systemctl start mysqld
mysql -uroot -p$(grep 'temporary password' /var/log/mysqld.log | cut -d' ' -f13) --connect-expired-password -e "ALTER USER root@localhost IDENTIFIED WITH mysql_native_password BY '********'"

MySQLが起動したらMySQL Shellで設定を行います。
mysqlshコマンドを実行するとMySQL Shellが起動するのでコマンドを実行します。

mysqlsh
MYSQLSH> dba.configureLocalInstance('root@localhost:3306', {clusterAdmin: 'icroot@\'%\'',clusterAdminPassword: '********'})
Do you want to perform the required configuration changes? [y/n]: y
Do you want to restart the instance after configuring it? [y/n]: y

ここまでの手順を実施するとGroup Replicationへの参加準備が整います。
あとはマスタノードの操作だけになります。
今回はMySQLノード1をマスタにして、クラスタ名を「cluster」としています。

MYSQLSH> shell.connect('icroot@mysql-node-1')
MYSQLSH> cluster = dba.createCluster('cluster')
MYSQLSH> cluster.addInstance('icroot@mysql-node-2')
MYSQLSH> cluster.addInstance('icroot@mysql-node-3')

特に問題がなければ以上で構築完了です。

問題が起きた場合の切り分けは結構難しいですが、確認ポイントはだいたい以下の通りです。

• ホスト名が設定されて名前解決ができていること
• mysqlshで dba.checkInstanceConfiguration() を実行し、statusがokになっていること

あとは /var/log/mysqld.log を確認してみてください。

運用コマンド(ProxySQLノード)

ProxySQLの管理コンソール

ProxySQLの管理コンソールへ接続します。
前に記載した設定のままであればパスワードはadminになっています。

mysql -u admin -p --socket=/var/lib/proxysql/admin.sock

設定変数確認

MYSQL> select * from global_variables;

接続先サーバステータス確認

更新用のhostgroupは0、参照用のhostgroupは1になります。

MYSQL> select * from runtime_mysql_servers;

設定ファイルの変更反映

/etc/proxysql.cnf の修正をProxySQLに反映します。
ProxySQLはRUNTIME、MEMORY、DISK、設定ファイルという設定の階層がありまして
設定ファイルの変更が再起動するだけでは反映されないことがあります。
今回はテストなのでキャッシュを毎回削除して設定ファイルを強制的に反映させています。

vi /etc/proxysql.cnf
rm /var/lib/proxysql/proxysql.db
systemctl restart proxysql

ProxySQLの設定については以下の記事が詳しいので参考にしてください。
https://qiita.com/yoan/items/ba62dd65b24ac1b6a458

運用コマンド(MySQLノード)

ノードへの接続

ノード操作のために接続する必要があります。

# パスワードを入力して接続完了すると、コマンド入力部が「MYSQL mysql-node-1:33060+ ssl  JS >」に変わります
MYSQLSH> shell.connect('icroot@mysql-node-1')

ノードの設定確認

クラスタに参加できる設定になっているか判定した結果を出力します。
クラスタ参加済の場合はエラーになります。

MYSQLSH> dba.checkInstanceConfiguration()

クラスタの作成と取得、解散

クラスタはcreateClusterコマンドで作成します。

MYSQLSH> dba.createCluster('cluster')

作成したクラスタはgetClusterコマンドで取得できます。
引数無しでデフォルトのクラスタの取得になります。

# clusterという変数に結果を格納する
MYSQLSH> cluster = dba.getCluster()

作成したクラスタはdissolveコマンドで破棄することができます。

# forceオプションにより強制的に破棄する
MYSQLSH> cluster.dissolve({force:true})

ノードの追加・削除

クラスタに新しいノードを追加したり削除したりする操作です。

# 追加
MYSQLSH> cluster.addInstance('icroot@mysql-node-1')
# 削除
MYSQLSH> cluster.removeInstance('icroot@mysql-node-1')
# 再追加
MYSQLSH> cluster.rejoinInstance('icroot@mysql-node-1')

MISSING状態のノードはrescanで削除することができます。

MYSQLSH> cluster.rescan()

クラスタの状態確認

クラスタの状態を確認します。
ノードのstatusがONLINEになっていればクラスタに参加中。
RECOVERINGがデータの同期中、(MISSING)が未接続になっています。
ステータスの詳細はここを見てください。

MYSQLSH> shell.connect('icroot@mysql-node-1')
MYSQLSH> cluster = dba.getCluster()
MYSQLSH> cluster.status()
{
    "clusterName": "cluster",
    "defaultReplicaSet": {
        "name": "default",
        "primary": "mysql-node-1:3306",
        "ssl": "REQUIRED",
        "status": "OK",
        "statusText": "Cluster is ONLINE and can tolerate up to ONE failure.",
        "topology": {
            "mysql-node-1:3306": {
                "address": "mysql-node-1:3306",
                "mode": "R/W",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            },
            "mysql-node-2:3306": {
                "address": "mysql-node-2:3306",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            },
            "mysql-node-3:3306": {
                "address": "mysql-node-3:3306",
                "mode": "R/O",
                "readReplicas": {},
                "role": "HA",
                "status": "ONLINE"
            }
        }
    },
    "groupInformationSourceMember": "mysql://icroot@mysql-node-1:3306"
}

追放されたノードの再登録

ノードが一定期間反応しないとクラスタから追放されますが
MySQL Group Replicationでは追放ノードが自動では復帰しない仕様です。
復帰させる場合は手動で追加してください。

※mysql-node-2を復帰させる場合

MYSQLSH> shell.connect('icroot@mysql-node-1')
MYSQLSH> dba.getCluster().rejoinInstance('icroot@mysql-node-2')

停止したGroup Replicationの復活

Group Replicationが正常に動作している状態から全てのMySQLノードの停止→再起動を行うと、
メタ情報だけが残ってGroup Replicationが停止した状態になります。

この状態でクラスタを取得しようとするとエラーになります。

MYSQLSH> shell.connect('icroot@mysql-node-1')
MYSQLSH> dba.getCluster()
Dba.getCluster: This function is not available through a session to a standalone instance (metadata exists, but GR is not active) (RuntimeError)

その状態からGroup Replicationを起動するには以下の操作を行います。

MYSQLSH> dba.rebootClusterFromCompleteOutage()
Reconfiguring the default cluster from complete outage...

The instance 'mysql-node-2:3306' was part of the cluster configuration.
Would you like to rejoin it to the cluster? [y/N]: y

The instance 'mysql-node-3:3306' was part of the cluster configuration.
Would you like to rejoin it to the cluster? [y/N]: y


The cluster was successfully rebooted.

<Cluster:cluster>

メタ情報の削除と再作成

Group Replicationの運用をしているとメタ情報について意識する必要があります。
メタ情報がどういうものか詳細に書いてある資料が見つかりませんでしたが
各ノードが持つクラスタに関するメタ情報であり、必ずしもGroup Replicationの実際の状態と一致していません。
例えば、メタ情報がありMySQLノードも動いているがGroup Replicationが止まっているという状態もありえます。
(metadata exists, but GR is not activeと出力されます。)
特にこれが異常というわけではなく、ノードをクラスタに追加する前はそういう状態になっています。
この辺りの考え方がわかると理解しやすくなるかもしれません。

そのメタ情報を削除することができます。
メタ情報がない＝unmanaged replication groupという扱いになります。

MYSQLSH> shell.connect('icroot@mysql-node-1')
# クラスタのメタ情報の削除
# Group Replication自体は動いたままになる
MYSQLSH> dba.dropMetadataSchema()
# メタ情報が無いためクラスタが取得できなくなる
MYSQLSH> cluster = dba.getCluster()
This function is not available through a session to an instance belonging to an unmanaged replication group (RuntimeError)

Group Replicationが動作している場合、
adoptFromGRオプションによりメタ情報を再作成できます。

# Group Replicationを元にメタ情報を再作成
MYSQLSH> dba.createCluster('cluster', {adoptFromGR: true})
A new InnoDB cluster will be created based on the existing replication group on instance 'icroot@mysql-node-1:3306'.

Creating InnoDB cluster 'cluster' on 'icroot@mysql-node-1:3306'...
Adding Seed Instance...
Adding Instance 'mysql-node-2:3306'...
Adding Instance 'mysql-node-3:3306'...

Cluster successfully created based on existing replication group.

<Cluster:cluster>

スレーブノードの構築と追加

スレーブをクラスタに追加する操作のダンプ投入を含めた手順になります。

落ちたノードを再構築する場合だとGroup Replicationが動いていることもあるので
その場合はstop group_replicationの操作が必要になります。
また、Group Replicationに参加中のスレーブはsuper_read_onlyがonになっているため、
ダンプを投入する前にoffにします。

# マスタ側
mysqldump --all-databases --lock-all-tables -uroot --triggers --routines --events -p > /tmp/backup.sql
scp /tmp/backup.sql mysql-node-2:/tmp
# スレーブ側
MYSQL> stop group_replication;
MYSQL> set global super_read_only=off;
MYSQL> source /tmp/backup.sql;
# マスタ側
MYSQLSH> cluster.rejoinInstance('icroot@mysql-node-2')

クラスタ参加中のノードから見てスレーブが(MISSING)ステータスで残っている場合に
cluster.rejoinInstance()がうまくいかないこともあります。
そのときは、cluster.rescan()で削除してからcluster.addInstance()することで追加できることもありました。

運用にあたっての注意点

MySQL Group Replicationを動かして色々とテストをしてみました。
並列データ投入、大量データ投入、削除、ノードの強制切断などなど。
こちらでもやはり問題が起きたので記録している範囲で注意点をまとめておきます。

ノードが落ちる

データを投入しているといずれかのノードが落ちる事象がありました。
何度か確認した範囲ではスレーブの同期の遅延が大きくなりすぎるとノードが追放されます。
※同期遅延はデータ挿入中のテーブルのレコード数をマスタとスレーブで比較すると観測できます。

単に遅延が起きないようにすれば解消する事象のように見えるためチューニングを検討します。
確認するとI/O負荷がかなり高いのでbinlogの書き込みを若干緩やかに。
また、innodb_buffer_pool_sizeを設定していなかったので追加。
最終的に /etc/my.cnf に以下の設定を入れると解消しました。

innodb_buffer_pool_size = 2G
sync_binlog = 100
innodb_flush_log_at_trx_commit = 2

sync_binlog と innodb_flush_log_at_trx_commitはログの同期に関わる設定で、
上記設定によりログへの書き出しやディスクへの同期の頻度が初期設定より下がります。
データの消失が心配なためクエリ発行中にマスタノードを落としたり通信を切る試験を何度か行いましたが
例外が発生したクエリの再処理さえ行っておけばロストするデータはありませんでした。

スプリットブレインが発生してしまった

2つのMySQLノードでお互いのステータスがMISSINGに見える状態です。
ProxySQLがスプリットブレインに対応していないため対処できません。
最悪のケースとして同期遅延でノードが追放されたときに何故か2台-1台の二つのクラスタに分割されて
ProxySQLが二つのクラスタに交互にデータを投入するという動作に陥りました。
パフォーマンスチューニング後は発生していませんが、対処を検討する必要がありそうです。

とりあえずその状態を解消するために1台で構成されたクラスタを落として2台の方に追加します。
mysql-node-3が1台構成のクラスタになったと想定すると以下の操作で解消できます。

# 1台構成クラスタ側
MYSQLSH> shell.connect('icroot@mysql-node-3')
MYSQLSH> cluster = dba.getCluster()
MYSQLSH> cluster.dissolve({force: true})
MYSQL> reset master
# 2台構成クラスタ側
MYSQLSH> shell.connect('icroot@mysql-node-1')
MYSQLSH> cluster = dba.getCluster()
MYSQLSH> cluster.rejoinInstance('icroot@mysql-node-3')

落ちたノードをクラスタに追加できない

私の知識不足で原因が切り分けできていない事例ですが
以下のいずれかの対処でうまくいくことがありました。

対処1

特定のエラーが出てるときにうまくいったケースです。
バイナリログのずれがあるときにreset masterで対処できました。
同じメッセージなのにうまくいかないときもあります。

# マスタ側
MYSQLSH> cluster.addInstance('icroot@mysql-node-2')
ERROR:
Group Replication join failed.
ERROR: Error joining instance to cluster: 'mysql-node-2:3306' - Query failed. MySQL Error (3092): ClassicSession.query: 
The server is not configured properly to be an active member of the group. Please see more details on error log.. 
Query: START group_replication: MySQL Error (3092): ClassicSession.query: 
The server is not configured properly to be an active member of the group. Please see more details on error log. (RuntimeError)
# スレーブ側
MYSQL> reset master;
# マスタ側
MYSQLSH> cluster.addInstance('icroot@mysql-node-2')

対処2

クラスタのメタ情報を再作成してみる。
ちょっと怪しげですが。

dba.dropMetadataSchema()
cluster = dba.createCluster('cluster')
cluster.addInstance('icroot@mysql-node-2')
cluster.addInstance('icroot@mysql-node-3')

対処3

スレーブを丸ごと再作成する。（最終手段）

おわりに

ProxySQLとMySQL Group Replicationを試したときのメモを記事としてまとめました。
整理しきれていない部分はありますが参考になれば幸いです。