今回はProxySQL 2.0でGalera Clusterを監視する機能についてご紹介したいと思います。
Oracle社が提供する同期レプリケーションのソリューションとしては、MySQL Group Replicationがあります。
同様の機能を持つサードパーティ製のソリューションとしてはCodership社が開発しているGalera Clusterが最も有名かと思います。
Group Replication
https://dev.mysql.com/doc/refman/8.0/en/group-replication.html
Galera Cluster
http://galeracluster.com/documentation-webpages/
これらのソフトウェアについては、過去に弊社ブログでも幾つかご紹介させていただいております。
ProxySQL 1.4ではMySQL Group Replication設定用のテーブルがありますが、Galera Clusterはネイティブにはサポートされていませんでした。
※ Percona社が開発するproxysql_galera_checkerをスケジューラに登録して監視を行うことが可能です
ProxySQL2.0よりGalera Clusterの設定用テーブルが追加されましたので、検証をしたいと思います。
https://github.com/sysown/proxysql/wiki/Main-(runtime)#mysql_galera_hostgroups
環境の用意
今回はdocker-composeを用いて3ノードのPercona XtraDB Cluster 5.7とProxySQLを構築しました。
Percona XtraDB ClusterはPercona社が開発するMySQL(Percona-server)とGalera Clusterを組み合わせたデータベース製品です。
ProxySQL2.0については公式のDockerImageが存在しないため、簡単なDockerfileを作成しています。
もし検証される場合は、こちらから各種ファイルをダウンロード下さい。
ディレクトリは以下の内容です。
|
1 2 3 4 5 6 7 8 9 10 |
$ tree . ├── Dockerfile ├── docker-compose.yml ├── node1_conf.d │ └── node.cnf ├── node2_conf.d │ └── node.cnf └── node3_conf.d └── node.cnf |
最初にnode01を起動し、mysqldの起動が完了するまで待ちます。
|
1 2 |
$ docker-compose up -d node01 $ docker-compose logs -f node01 |
node01の起動が完了したら他のノードも順番に起動します。
|
1 2 3 4 |
$ docker-compose up -d node02 $ docker-compose logs -f node02 $ docker-compose up -d node03 $ docker-compose logs -f node03 |
proxysqlはいつ起動しても大丈夫です。
|
1 |
$ docker-compose up -d proxysql |
ProxySQLの基本設定
ProxySQLの設定を行うためには、一般的なmysqlクライアントで6032(デフォルト)ポートへ接続します。
ユーザ名、パスワードはadmin/adminです。
|
1 |
$ docker-compose exec proxysql mysql -uadmin -p -h127.0.0.1 -P6032 |
ProxySQL 2.0 Galera Support
ProxySQL 2.0のGalera Supportでは以下のテーブルを使用します。
| Table name | Description |
|---|---|
| mysql_galera_hostgroups | Galera Cluster監視に必要な情報を登録する |
| runtime_mysql_galera_hostgroups | mysql_galera_hostgroupsの設定内容が実際に動作に反映されたものを確認できる |
| mysql_server_galera_log | Galera Cluster監視ログを格納 |
監視間隔の設定
ターゲットサーバの監視間隔は以下パラメータで設定可能です。
今回は特に変更を行いません。
|
1 2 3 4 5 6 7 |
mysql> select * from global_variables where variable_name like '%monitor_galera%'; +-------------------------------------------+----------------+ | variable_name | variable_value | +-------------------------------------------+----------------+ | mysql-monitor_galera_healthcheck_interval | 5000 | | mysql-monitor_galera_healthcheck_timeout | 800 | +-------------------------------------------+----------------+ |
mysql_galera_hostgroupsの設定
mysql_galera_hostgroupsへ設定をすると、mysql_serversに対象のhostgroupに所属するノードが存在した場合に監視が開始され、
Galera Clusterのステータスに合わせて適切なホストグループへの移動が行われます。
なお、接続ルーティング自体を管理するものではありませんのでご注意ください。
mysql_galera_hostgroupsの各列の意味は以下の通りです。
| Column name | Description |
|---|---|
| writer_hostgroup | 書き込み用のホストグループIDを指定 |
| backup_writer_hostgroup | max_writersの設定数値以上にwriter_hostgroup内のノードが存在した場合に、待機用Writerとなるノードが移動するホストグループID |
| reader_hostgroup | 読み込み用のホストグループIDを指定 |
| offline_hostgroup | 監視によって、機能しないと判断されたノードが移動するホストグループ |
| active | ProxySQLが監視を有効化するためのフラグ |
| max_writers | 最大書き込みノード数 |
| writer_is_also_reader | Writer hostgroup内のノードが同時にReader hostgroupノードに所属するかを決定 |
| max_transactions_behind | wsrep_local_recv_queueを監視し、他のノードよりもこの値以上のWrite-set分の遅れがあった場合にルーティング対象から外す |
以下のように設定を行いました。
|
1 2 3 4 5 |
mysql> INSERT INTO mysql_galera_hostgroups (writer_hostgroup, backup_writer_hostgroup, reader_hostgroup, offline_hostgroup, active, max_writers, writer_is_also_reader, max_transactions_behind) VALUES (1, 3, 2, 4, 1, 1, 0, 100); mysql> LOAD MYSQL SERVERS TO RUNTIME; |
※設定を永続化する場合は SAVE MYSQL SERVERS TO DISKも実行します。
writer_hostgroup=1, reader_hostgroup=2, backup_writer_hostgroup=3, offline_hostgroup=4
です。
Writer/Readerの仕分けについて
mysql_galera_hostgroupsはmysql_rerplication_hostgroupの動作を踏襲しています。
read_onlyが無効なサーバは書き込みが可能という事でwriter_hostgroupに所属、read_onlyが有効なサーバは読み取り専用ということでreader_hostgroupに所属という仕分けられ方をします。
この動作が前提となりますので、覚えておいてください。
Galera Clusterターゲットの追加
ProxySQLで接続先を追加するためにはターゲットとなるサーバの情報をmysql_serversテーブルにINSERTします。
mysql_galera_hostgroupsに登録したhostgroup_idを前提として、以下のように割り振ります。
writer_hostgroup = node01
reader_hostgroup = node02,node03
reader_hostgroupに所属させるノードはread_onlyを有効化する必要があります。
|
1 2 |
$ docker-compose exec node02 mysql -uroot -ppassword -e "set global read_only = 1" $ docker-compose exec node03 mysql -uroot -ppassword -e "set global read_only = 1" |
ではサーバを登録します。
|
1 2 |
mysql> INSERT INTO mysql_servers (hostgroup_id, hostname) values (1, "node01"),(2, "node02"),(2, "node03"); mysql> LOAD MYSQL SERVERS TO RUNTIME; |
各サーバの情報が正常に登録されました。
|
1 2 3 4 5 6 7 8 |
mysql> select hostgroup_id, hostname, status from runtime_mysql_servers; +--------------+----------+--------+ | hostgroup_id | hostname | status | +--------------+----------+--------+ | 1 | node01 | ONLINE | | 2 | node02 | ONLINE | | 2 | node03 | ONLINE | +--------------+----------+--------+ |
ルーティングの設定
ProxySQLのルーティングは様々な基準で行うことができますが、ここではユーザーのデフォルトホストグループにルーティングされることを基準として、reader, writerユーザを用意します。
|
1 2 3 |
mysql> INSERT INTO mysql_users (username, password, active, default_hostgroup) values ("reader", "password", 1, "2"),("writer", "password", 1, "1"); mysql> LOAD MYSQL USERS TO RUNTIME; |
これらのユーザは各データベースノードに存在している必要があります。
Galera Clusterであればいずれか1ノードの更新内容は他のノードにも反映されますので、今回はnode01でtestユーザを作成します。
|
1 2 3 4 5 |
$ docker-compose exec node01 mysql -uroot -ppassword -h127.0.0.1 mysql> CREATE USER `reader`@`%` IDENTIFIED BY 'password'; mysql> CREATE USER `writer`@`%` IDENTIFIED BY 'password'; mysql> GRANT ALL ON *.* TO `reader`@`%`; mysql> GRANT ALL ON *.* TO `writer`@`%`; |
接続テスト
各サーバへの接続先ポートは6033(デフォルト)です。
以下のSQLを数回実行し、各サーバに接続されることを確認します。
なおProxySQLは重み付け(weight)によってバランスするためラウンドロビンではありません。
readerユーザでの接続はreader_hostgroupに所属するノードでバランシングされるはずです。
|
1 2 3 4 5 |
$ docker-compose exec proxysql mysql -h127.0.0.1 -P6033 -ureader -ppassword -e "select @@hostname" mysql: [Warning] Using a password on the command line interface can be insecure. node02 node03 : |
writerユーザでの接続はwriter_hostgroupに所属するノードでバランシングされます。
|
1 2 3 4 |
$ docker-compose exec proxysql mysql -h127.0.0.1 -P6033 -ureader -ppassword -e "select @@hostname" mysql: [Warning] Using a password on the command line interface can be insecure. node01 : |
WriterグループもReaderにする
max_writers = 1かつwriter_is_also_reader = 1のときにread_only = 0のノードが複数いた場合以下のようになります。
|
1 2 3 4 5 6 7 8 |
+--------------+----------+--------------+ | hostgroup_id | hostname | status | +--------------+----------+--------------+ | 1 | node01 | ONLINE | | 3 | node02 | ONLINE | | 2 | node03 | ONLINE | | 2 | node02 | OFFLINE_HARD | +--------------+----------+--------------+ |
read_only = 0のノードはwriter_hostgroupに移動されるという動作がまず優先され、かつすでにnode01がPrimary Writerとして存在しているので、
node02はbackup_writer_hostgroupに加えられるとともに、reader_hostgroup内ではOFFLINE_HARDにされてしまったようです。
writer_is_also_readerを1にすれば、read_onlyではないノードもreaderとして使用されるはずです。
|
1 2 |
mysql> UPDATE mysql_galera_hostgroups SET writer_is_also_reader = 1; mysql> LOAD MYSQL SERVERS TO RUNTIME; |
Writerであるnode01もReaderに加えられました。
|
1 2 3 4 5 6 7 8 9 |
mysql> select hostgroup_id, hostname, status from runtime_mysql_servers; +--------------+----------+--------+ | hostgroup_id | hostname | status | +--------------+----------+--------+ | 1 | node01 | ONLINE | | 2 | node01 | ONLINE | | 2 | node03 | ONLINE | | 2 | node02 | ONLINE | +--------------+----------+--------+ |
Writer候補を増やす
writer_is_also_readerが1ならread_only = 1でないサーバはwriter_hostgroupとreader_hostgroupに所属します。ですので3ノードでも書き込みノードのフェイルオーバが可能となります。
試してみましょう。まずはread_only解除から。
|
1 2 |
$ docker-compose exec node02 mysql -uroot -ppassword -h127.0.0.1 -e "set global read_only = 0" $ docker-compose exec node03 mysql -uroot -ppassword -h127.0.0.1 -e "set global read_only = 0" |
確認してみるとmax_writerの数だけwriter_hostgroupに所属しているとともに、全てのノードがreader_hostgroupに所属しています。
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> select hostgroup_id, hostname, status from runtime_mysql_servers; +--------------+----------+--------+ | hostgroup_id | hostname | status | +--------------+----------+--------+ | 1 | node03 | ONLINE | | 3 | node02 | ONLINE | | 3 | node01 | ONLINE | | 2 | node01 | ONLINE | | 2 | node02 | ONLINE | | 2 | node03 | ONLINE | +--------------+----------+--------+ |
backup_writer_hostgroupに所属しているノードは、node03が停止したらwriter_hostgroupに移動されるはずです。
node03を止めてみましょう。
|
1 |
$ docker-compose stop node03 |
どうでしょうか。
|
1 2 3 4 5 6 7 8 9 10 |
mysql> select hostgroup_id, hostname, status from runtime_mysql_servers; +--------------+----------+---------+ | hostgroup_id | hostname | status | +--------------+----------+---------+ | 1 | node02 | ONLINE | | 4 | node03 | SHUNNED | | 3 | node01 | ONLINE | | 2 | node02 | ONLINE | | 2 | node01 | ONLINE | +--------------+----------+---------+ |
node02はwriter_hostgroupに移動されました。node03はOFFLINEグループに移動され、"SHUNNED"のステータスになりました。
"SHUNNED"は以下のように説明されています。
https://github.com/sysown/proxysql/wiki/Main-(runtime)#mysql_servers
|
1 |
SHUNNED - backend sever is temporarily taken out of use because of either too many connection errors in a time that was too short, or replication lag exceeded the allowed threshold |
SHNNEDとなったサーバへの接続中のセッションは切断され、以降ルーティングされませんので、想定通りに動作しました。
まとめ
今回の検証で、ProxySQL Native Galera Supportは、これまでのproxysql_galera_checkerを用いた動作と比較して動作に異なる部分がありますので、すでに導入されているユーザは戸惑う点もあると思いますが、機能としては上手く動いたように感じました。
実際のルーティングは、ユーザを分ける、クエリルールを使用する等別途設定を行う必要がありますが、hostgroup_idを基準として柔軟なロードバランスを実現する事ができます。
ProxySQL 2.0には他にもいろいろな機能が実装されているので今後本ブログで検証していきたいと思います。
