【注意】
本記事はベータ版の機能について取り上げていますので、商用や本番環境で使用することはご遠慮ください。
はじめに
ProxySQL – A High Performance Open Source MySQL Proxy
2023/2/5 に Pre-release された ProxySQL 2.5.0 では、リリースノートから幾つかの機能追加が確認できます。
今回はそのうちのひとつ、Group Replication のネイティブサポート実装が追加されたので、試してみました。
Native Group Replication monitoring support for MySQL-8 #4082
これまでの実装
ご存じの方も多いかもしれませんが、ProxySQL の Group Replication への対応は既に v1.4.1 で実装されています。
Group Replication: Native Support for MySQL Group Replication using new configuration table mysql_group_replication_hostgroups , see this blog post
そして、これまでのリリースで度々機能改善や Bug fix が行われてきていました。
具体的な使い方としては、mysql_group_replication_hostgroups テーブルに以下の役割や設定を定義します。
- writer_hostgroup
- backup_writer_hostgroup
- reader_hostgroup
- offline_hostgroup
- max_writers
- writer_is_also_reader
- max_transactions_behind
加えて、この機能を動作させるには、バックエンド DB 側の sys スキーマにユーザーが手動で追加の専用ファンクションとビューを作成する必要がありました。(ProxySQL 用の監視ユーザーに sys への SELECT 権限も付与)
それらの DDL は公式ドキュメントの以下のページの最下部に掲載されています。
Main (runtime tables definition) – ProxySQL
ただし、この公開 DDL には MySQL 8.0 に対応していない Bug がありました。
※ワークアラウンドとして、以下のスレッドで提案されている DDL で作成することで MySQL 8.0 でも正常に動作するようになります。
https://github.com/sysown/proxysql/issues/3406#issuecomment-822145789
v2.5.0 のネイティブサポート
今回の機能改善では、これまでのユーザ介入が必要な部分を排除し、ProxySQL 側でセットアップするだけで利用できるようになりました。
既にある AWS Aurora クラスタサポートの仕組み を MySQL 8.0 Group Replication 用に再利用したそうです。
Monitor ‘Group replication’ rework by JavierJF · Pull Request #4082 · sysown/proxysql · GitHub
また、併せて幾つかの既存の Group Replication 監視機能のバグも修正されているとのことです。
When writer was set as SHUNNED due to replication lag, and ‘mysql_servers’ table was regenerated (via servers reconfiguration or other action). SHUNNED writer wasn’t considered a found writer, triggering an unwanted server reconfiguration.
Since servers present in ‘backup_writer_hostgroup’ where not considered as previously configured writers, everytime the number of available writers exceeded ‘max_writers’ an unwanted server reconfiguration was triggered for each of these ervers at every monitoring action.
セットアップ方法
それでは実際に試してみたいと思います。
公式ドキュメントにはまだ今回の変更に関する説明は追加されていないようですので、多少手探りな部分はありましたがそこまで以前と変わるところはありませんでした。
今回使用する Group Replication 環境の概要は以下の通りです。
- MySQL Server 8.0.30
- Oracle Linux Server release 8.7
- シングルプライマリモード
| hostname | IP adress |
|---|---|
| gr-mysql-e1 | 10.0.2.19 |
| gr-mysql-e2 | 10.0.2.217 |
| gr-mysql-e3 | 10.0.2.72 |
ProxySQL 監視用 DB ユーザーの作成
Group Relication 側に ProxySQL 用の監視ユーザーを事前に作成しておきます。
今回のポイントは、performance_schema スキーマの replication_group_members と replication_group_member_stats への SELECT 権限を付与する必要があることです。
|
1 2 3 4 |
mysql> CREATE USER monitor IDENTIFIED BY "MySQL8.0"; mysql> GRANT REPLICATION CLIENT ON *.* TO monitor; mysql> GRANT SELECT ON performance_schema.replication_group_members TO monitor; mysql> GRANT SELECT ON performance_schema.replication_group_member_stats TO monitor; |
ProxySQL のインストール
今回使用する v2.5.0 は Pre-release 版なので、RPMパッケージを直接インストールします。
|
1 2 3 |
# dnf install -y https://github.com/sysown/proxysql/releases/download/v2.5.0/proxysql-2.5.0-1-centos8.x86_64.rpm # proxysql --version ProxySQL version 2.5.0-95-gd220a42, codename Truls |
各種設定
サービスを起動して、管理インターフェースにログインします。
|
1 2 |
# systemctl start proxysql # mysql -h127.0.0.1 -P6032 -uadmin -padmin --prompt "Admin> " |
監視ユーザなどの設定を行います。
|
1 2 3 4 5 6 7 8 |
Admin> set admin-hash_passwords = 'false'; Admin> LOAD ADMIN VARIABLES TO RUNTIME; Admin> SAVE ADMIN VARIABLES TO DISK; Admin> UPDATE global_variables SET Variable_Value='8.0.30' WHERE Variable_name='mysql-server_version'; Admin> UPDATE global_variables SET variable_value='monitor' WHERE variable_name='mysql-monitor_username'; Admin> UPDATE global_variables SET variable_value='MySQL8.0' WHERE variable_name='mysql-monitor_password'; Admin> LOAD MYSQL VARIABLES TO RUNTIME; Admin> SAVE MYSQL VARIABLES TO DISK; |
mysql_servers テーブルにホスト情報を投入します。
すべてのホストの hostgroup_id はこの時点では後で定義する writer_hostgroup のもの(2)に設定しています。
※特に writer_hostgroup が全て同一である必要性はありませんが、後程このホストグループが本機能によって役割毎に分散される様子を分かり易くするためこのように設定しています。
|
1 2 3 4 5 6 7 |
Admin> INSERT INTO mysql_servers (hostgroup_id,hostname,port,comment) VALUES (2,'10.0.2.19',3306,'gr-mysql-e1'); Admin> INSERT INTO mysql_servers (hostgroup_id,hostname,port,comment) VALUES (2,'10.0.2.217',3306,'gr-mysql-e2'); Admin> INSERT INTO mysql_servers (hostgroup_id,hostname,port,comment) VALUES (2,'10.0.2.72',3306,'gr-mysql-e3'); Admin> LOAD MYSQL SERVERS TO RUNTIME; Admin> SAVE MYSQL SERVERS TO DISK; Admin> SELECT * FROM mysql_servers; |
本機能の肝となる mysql_group_replication_hostgroups テーブルに定義を投入します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
Admin> INSERT INTO mysql_group_replication_hostgroups ( writer_hostgroup, backup_writer_hostgroup, reader_hostgroup, offline_hostgroup, active, max_writers, writer_is_also_reader, max_transactions_behind ) VALUES (2, 4, 3, 1, 1, 1, 1, 100); Admin> LOAD MYSQL SERVERS TO RUNTIME; Admin> SAVE MYSQL SERVERS TO DISK; |
|
1 2 3 4 5 6 7 8 9 10 11 12 |
Admin> SELECT * FROM mysql_group_replication_hostgroups\G *************************** 1. row *************************** writer_hostgroup: 2 backup_writer_hostgroup: 4 reader_hostgroup: 3 offline_hostgroup: 1 active: 1 max_writers: 1 writer_is_also_reader: 1 max_transactions_behind: 100 comment: NULL 1 row in set (0.00 sec) |
Runtime に設定がロードされると、直ちにバックエンドDBへチェックが行われます。
Monitor モジュールの mysql_server_group_replication_log テーブルを確認すると、正しく情報が取得できていれば以下のように表示されます。
|
1 2 3 4 5 6 7 8 9 |
Admin> SELECT * FROM mysql_server_group_replication_log order by time_start_us desc limit 3; +------------+------+------------------+-----------------+------------------+-----------+---------------------+-------+ | hostname | port | time_start_us | success_time_us | viable_candidate | read_only | transactions_behind | error | +------------+------+------------------+-----------------+------------------+-----------+---------------------+-------+ | 10.0.2.217 | 3306 | 1677085497690418 | 790 | YES | YES | 0 | NULL | | 10.0.2.19 | 3306 | 1677085497690297 | 827 | YES | NO | 0 | NULL | | 10.0.2.72 | 3306 | 1677085497690235 | 763 | YES | YES | 0 | NULL | +------------+------+------------------+-----------------+------------------+-----------+---------------------+-------+ 3 rows in set (0.00 sec) |
Runtime の mysql_servers の状態(runtime_mysql_servers) を見てみましょう。
|
1 2 3 4 5 6 7 8 9 10 |
Admin> SELECT hostgroup_id,hostname,port,comment,status FROM runtime_mysql_servers; +--------------+------------+------+-------------+--------+ | hostgroup_id | hostname | port | comment | status | +--------------+------------+------+-------------+--------+ | 2 | 10.0.2.19 | 3306 | gr-mysql-e1 | ONLINE | -- Writer | 3 | 10.0.2.19 | 3306 | gr-mysql-e1 | ONLINE | -- Reader | 3 | 10.0.2.217 | 3306 | gr-mysql-e2 | ONLINE | -- Reader | 3 | 10.0.2.72 | 3306 | gr-mysql-e3 | ONLINE | -- Reader +--------------+------------+------+-------------+--------+ 4 rows in set (0.00 sec) |
それぞれのノードが役割のホストグループに適切に振り分けられているか確認することができます。
ルーティングの挙動確認
それでは、クライアントからの接続ルーティングがこちらで意図した役割通りになるか簡易的に確認してみます。
通常時
以下の R/W Splitting ルールを設定しておきます。
純粋な SELECT クエリは reader_hostgroup へ、それ以外は writer_hostgroup へルーティングされるようになります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
Admin> UPDATE mysql_users SET default_hostgroup=2; Admin> LOAD MYSQL USERS TO RUNTIME; Admin> SAVE MYSQL USERS TO DISK; Admin> INSERT INTO mysql_query_rules ( rule_id, active, match_digest, destination_hostgroup, apply ) VALUES (1, 1, '^SELECT.*FOR UPDATE$', 2, 1), (2, 1, '^SELECT', 3, 1); Admin> LOAD MYSQL QUERY RULES TO RUNTIME; Admin> SAVE MYSQL QUERY RULES TO DISK; |
テスト用のユーザーとテーブルを作成し、一つ目のターミナルでは更新(INSERT)を、二つ目のターミナルは参照(SELECT)を、それぞれ実行します。
-
terminal 1
123456789# while true;domysql --login-path=app_user -h 127.0.0.1 -P 6033 -sNe "BEGIN;INSERT INTO d1.t1 VALUES(null,'TEST','FROM $(uname -n)',NOW());SELECT NOW(),@@hostname,id FROM d1.t1 ORDER BY id DESC LIMIT 1;COMMIT;";sleep 1;done -
terminal 2
123456# while true;domysql --login-path=app_user -h 127.0.0.1 -P 6033 -sNe "SELECT NOW(),@@hostname,id FROM d1.t1 ORDER BY id DESC LIMIT 1;";sleep 1;done
更新処理は writer_hostgroup に属しているプライマリノードのみにルーティングされています。
|
1 2 3 4 5 6 7 8 |
(...) 2023-02-21 12:00:21 gr-mysql-e1 1177 2023-02-21 12:00:22 gr-mysql-e1 1178 2023-02-21 12:00:23 gr-mysql-e1 1179 2023-02-21 12:00:24 gr-mysql-e1 1180 2023-02-21 12:00:25 gr-mysql-e1 1181 2023-02-21 12:00:26 gr-mysql-e1 1182 (...) |
参照処理は reader_hostgroup に分散ルーティングされています。
|
1 2 3 4 5 6 7 8 |
(...) 2023-02-21 12:00:21 gr-mysql-e1 1177 2023-02-21 12:00:22 gr-mysql-e3 1178 2023-02-21 12:00:23 gr-mysql-e2 1179 2023-02-21 12:00:24 gr-mysql-e1 1180 2023-02-21 12:00:25 gr-mysql-e3 1181 2023-02-21 12:00:26 gr-mysql-e1 1182 (...) |
プライマリノードを R/W 可能にしていて(mysql_group_replication_hostgroups.writer_is_also_reader=1)、参照処理のルーティング頻度を下げたい場合は mysql_servers.weight を調整しましょう。
プライマリノードダウン時
つぎに、プライマリノードの mysqld を停止させてみます。
|
1 2 |
[root@gr-mysql-e1 ~]# date '+%F %T'; systemctl stop mysqld 2023-02-21 12:07:13 |
ノード3がプライマリに昇格しました。
|
1 2 3 4 5 6 7 8 |
mysql> select MEMBER_HOST,MEMBER_PORT,MEMBER_STATE,MEMBER_ROLE from performance_schema.replication_group_members; +-------------+-------------+--------------+-------------+ | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | +-------------+-------------+--------------+-------------+ | gr-mysql-e3 | 3306 | ONLINE | PRIMARY | | gr-mysql-e2 | 3306 | ONLINE | SECONDARY | +-------------+-------------+--------------+-------------+ 2 rows in set (0.00 sec) |
更新処理は、プライマリノードのシャットダウン中、接続断でエラーとなり、ノード3がプライマリ昇格後、処理再開・ルーティングされています。
|
1 2 3 4 5 6 7 8 9 10 |
(...) 2023-02-21 12:07:11 gr-mysql-e1 1268 2023-02-21 12:07:12 gr-mysql-e1 1269 2023-02-21 12:07:13 gr-mysql-e1 1270 ERROR 2013 (HY000) at line 5: Lost connection to MySQL server during query 2023-02-21 12:07:18 gr-mysql-e3 1270 2023-02-21 12:07:19 gr-mysql-e3 1271 2023-02-21 12:07:20 gr-mysql-e3 1272 2023-02-21 12:07:21 gr-mysql-e3 1273 (...) |
参照処理は、プライマリノードのシャットダウン中、データアクセスは継続され、シャットダウン後は生存メンバーの reader_hostgroup でルーティングされています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
(...) 2023-02-21 12:07:11 gr-mysql-e1 1267 2023-02-21 12:07:12 gr-mysql-e1 1268 2023-02-21 12:07:13 gr-mysql-e1 1269 -- プライマリ mysqld シャットダウン開始 2023-02-21 12:07:14 gr-mysql-e2 1269 2023-02-21 12:07:15 gr-mysql-e1 1269 2023-02-21 12:07:16 gr-mysql-e3 1269 2023-02-21 12:07:17 gr-mysql-e3 1269 2023-02-21 12:07:18 gr-mysql-e3 1269 -- このあたりでノード3がプライマリに昇格 2023-02-21 12:07:19 gr-mysql-e2 1270 2023-02-21 12:07:20 gr-mysql-e3 1271 2023-02-21 12:07:21 gr-mysql-e3 1272 (...) |
ダウンした旧プライマリノードは offline_hostgroup には移されるものの、ステータスは OFFLINE ではなく、SHUNNED に移行されています。
SHUNNED ステータスとは、一時的なエラーで使用不可状態(敬遠)と言う意味です。接続中のセッションは切断され、そのノードへは以降ルーティングされなくなります。また、ヘルスチェックなども無効となります。
OFFLINE_HARD と異なるのは、一時的なエラー扱いなので復帰の可能性を見込んでいるということです。OFFLINE_HARD はサーバー自体を接続先から削除してしまうことと同等になります。
|
1 2 3 4 5 6 7 8 9 10 |
Admin> SELECT hostgroup_id,hostname,port,comment,status FROM runtime_mysql_servers; +--------------+------------+------+-------------+---------+ | hostgroup_id | hostname | port | comment | status | +--------------+------------+------+-------------+---------+ | 1 | 10.0.2.19 | 3306 | gr-mysql-e1 | SHUNNED | -- Offline グループ | 2 | 10.0.2.72 | 3306 | gr-mysql-e3 | ONLINE | | 3 | 10.0.2.217 | 3306 | gr-mysql-e2 | ONLINE | | 3 | 10.0.2.72 | 3306 | gr-mysql-e3 | ONLINE | +--------------+------------+------+-------------+---------+ 4 rows in set (0.00 sec) |
対象ノードが、read_only=1 かつ mysql-monitor_groupreplication_max_transactions_behind_count (デフォルト:3) の回数、transactions_behind のチェックに失敗すると SHUNNED に変更されます。
実際に mysql_server_group_replication_log テーブルを確認してみるとその通りの挙動となっています。
|
1 2 3 4 5 6 7 8 9 |
Admin> SELECT * FROM mysql_server_group_replication_log order by time_start_us desc limit 3; +-----------+------+------------------+-----------------+------------------+-----------+---------------------+-------------------------------------------------------------------------------------------------+ | hostname | port | time_start_us | success_time_us | viable_candidate | read_only | transactions_behind | error | +-----------+------+------------------+-----------------+------------------+-----------+---------------------+-------------------------------------------------------------------------------------------------+ | 10.0.2.19 | 3306 | 1677159329641217 | 0 | NO | YES | -1 | timeout or error in creating new connection: Can't connect to MySQL server on '10.0.2.19' (115) | | 10.0.2.19 | 3306 | 1677159319641133 | 0 | NO | YES | -1 | timeout or error in creating new connection: Can't connect to MySQL server on '10.0.2.19' (115) | | 10.0.2.19 | 3306 | 1677159309640963 | 0 | NO | YES | -1 | timeout or error in creating new connection: Can't connect to MySQL server on '10.0.2.19' (115) | +-----------+------+------------------+-----------------+------------------+-----------+---------------------+-------------------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec) |
Group Replication 用 Monitor パラメータはヘルスチェック間隔含め幾つか用意されていて、調整が可能です。
各パラメータの説明は以下のページをご確認ください。
MySQL Monitor Variables – ProxySQL
ダウンノードの復帰時
最後に、先ほど停止させた旧プライマリノード(ノード1)の mysqld を再び起動し、Group Replication に自動再参加させます。
|
1 2 |
[root@gr-mysql-e1 ~]# date '+%F %T'; systemctl start mysqld 2023-02-21 13:09:36 |
|
1 2 3 4 5 6 7 8 9 |
mysql> select MEMBER_HOST,MEMBER_PORT,MEMBER_STATE,MEMBER_ROLE from performance_schema.replication_group_members; +-------------+-------------+--------------+-------------+ | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | +-------------+-------------+--------------+-------------+ | gr-mysql-e3 | 3306 | ONLINE | PRIMARY | | gr-mysql-e1 | 3306 | ONLINE | SECONDARY | -- セカンダリメンバーとして復帰 | gr-mysql-e2 | 3306 | ONLINE | SECONDARY | +-------------+-------------+--------------+-------------+ 3 rows in set (0.00 sec) |
復帰ノードは直ちに reader_hostgroup に再度振り分けられました。
|
1 2 3 4 5 6 7 8 9 10 |
Admin> SELECT hostgroup_id,hostname,port,comment,status FROM runtime_mysql_servers; +--------------+------------+------+-------------+--------+ | hostgroup_id | hostname | port | comment | status | +--------------+------------+------+-------------+--------+ | 2 | 10.0.2.72 | 3306 | gr-mysql-e3 | ONLINE | | 3 | 10.0.2.19 | 3306 | gr-mysql-e1 | ONLINE | -- Reader ホストグループとして戻った | 3 | 10.0.2.217 | 3306 | gr-mysql-e2 | ONLINE | | 3 | 10.0.2.72 | 3306 | gr-mysql-e3 | ONLINE | +--------------+------------+------+-------------+--------+ 4 rows in set (0.00 sec) |
参照処理のルーティングも行われるようになりました。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
(...) 2023-02-21 13:09:48 gr-mysql-e3 1308 2023-02-21 13:09:49 gr-mysql-e2 1309 2023-02-21 13:09:50 gr-mysql-e2 1310 2023-02-21 13:09:51 gr-mysql-e1 1311 -- ルーティング先に組み込まれた 2023-02-21 13:09:52 gr-mysql-e3 1312 2023-02-21 13:09:53 gr-mysql-e3 1313 2023-02-21 13:09:54 gr-mysql-e1 1314 2023-02-21 13:09:55 gr-mysql-e3 1315 2023-02-21 13:09:56 gr-mysql-e3 1316 2023-02-21 13:09:57 gr-mysql-e3 1317 2023-02-21 13:09:58 gr-mysql-e3 1318 2023-02-21 13:09:59 gr-mysql-e2 1319 2023-02-21 13:10:00 gr-mysql-e1 1320 (...) |
まとめ
以前のバージョンから Group Replication には対応していたので、既に利用しているユーザーも多いのではないでしょうか。
今回の改善で MySQL 8.0 に対応し、さらに ProxySQL 側のみでセットアップできるようになってより導入のし易さが向上したと言えます。
ProxySQL は自身のプロセスを再起動不要で設定変更が可能であることが強みで、あたかもネットワークスイッチのようにルーティングを柔軟に制御できます。
また、クエリルールを駆使した多彩な機能を使えるようになり、ファイアウォールやSQLインジェクション検出、監査ログといったセキュリティ面の機能も充実しています。
ProxySQL と Group Replication を組み合わせることで、望んでいた運用や現在抱えている課題の解決が実現するかもしれません。
冒頭で注意を述べました通り、本記事の機能は執筆時点ではまだ Pre-release 扱いのバージョンですので、遠くない正式リリースの時を楽しみに待ちたいと思います。
