--- title: "ProxySQL | grasys blog" url: "https://blog.grasys.io/post/dokuma/proxysql" description: "ProxySQL 弊社ではDBバックエンドへの接続にMaxScaleを使っていますが、別のソリューションも欲しかったのでProxySQLを試した時のドキュメントです。何かお役に立てば幸いです。 少し概念がややこしいので、ドキュメントは充実していますので、ちゃんと使いたい人はきちんと読みましょう。 ちなみに、CentO…" --- # ProxySQL - ![](/_astro/noicon.CTHOhNiB_1HMs6A.webp)[dokuma](/authors/dokuma/) - 公開日：2018年6月22日 - カテゴリー：[Tech](/categories/tech/) - タグ：[#proxysql](/tags/proxysql/) ![ProxySQL](/_astro/og-image.DDSiu2Ny_XHpDd.webp) ProxySQL 弊社ではDBバックエンドへの接続に[MaxScale](https://mariadb.com/ja/node/820)を使っていますが、別のソリューションも欲しかったので[ProxySQL](http://www.proxysql.com/)を試した時のドキュメントです。何かお役に立てば幸いです。 少し概念がややこしいので、ドキュメントは充実していますので、ちゃんと使いたい人はきちんと読みましょう。 ちなみに、CentOS7でしか試してないので悪しからず！ ## Install 公式の通りです。`yum`でインストールできるので楽勝ですね。 [Getting started](https://github.com/sysown/proxysql/wiki#red-hat--centos) ### 起動 Plain textcontent\_copy ``` service proxysql start ``` 面白いのは、ProxySQL自体がMySQLのようなインタラクティブShellを持っていて、SQLっぽい構文で命令を実行できます。 ドキュメントでいうと、[この辺](https://github.com/sysown/proxysql/wiki#configuring-proxysql-via-the-admin-interface) インタラクティブShellに接続するためにはMySQL Clientが必要です。入ってなかったらインストールしてね〜 ## Configuration さて、設定ですが、ProxySQLの設定がどのように反映されるかのアーキテクチャを理解していないとわかりにくいかもしれません。 次の図のように設定にレイヤーを持っており、インタラクティブShellから各々命令を発行して設定を反映させます。 [Multi layer configuration system](https://github.com/sysown/proxysql/wiki/Configuring-ProxySQL#multi-layer-configuration-system) Plain textcontent\_copy ``` +-------------------------+ | RUNTIME | +-------------------------+ /|\ | | | [1] | [2] | | \|/ +-------------------------+ | MEMORY | +-------------------------+ _ /|\ | |\ | | \ [3] | [4] | \ [5] | \|/ \ +-------------------------+ +-------------------------+ | DISK | | CONFIG FILE | +-------------------------+ +-------------------------+ ``` コンフィグファイルを修正させてMemoryにロードさせるもよし、再起動時に設定を保持したければDiskに書き込むもよし、インタラクティブに設定をするもよし。 configから各設定をloadする手順は次の5つのコマンドがあります。。。もっとあるかもしれないが気にしない！ - LOAD MYSQL USERS FROM CONFIG - LOAD MYSQL SERVERS FROM CONFIG - LOAD MYSQL QUERY RULES FROM CONFIG - LOAD MYSQL VARIABLES FROM CONFIG - LOAD ADMIN VARIABLES FROM CONFIG 例を挙げると次のような感じ！ Plain textcontent\_copy ``` mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> ' # user, serverの情報をconfigからload Admin> LOAD MYSQL USERS FROM CONFIG; Admin> LOAD MYSQL SERVERS FROM CONFIG; # user, serverの情報をdbにsave Admin> SAVE MYSQL USERS FROM MEMORY; Admin> SAVE MYSQL SERVERS FROM MEMORY; Admin> PROXYSQL RESTART; # user, serverの情報をruntimeにload Admin> LOAD MYSQL USERS TO RUNTIME; Admin> LOAD MYSQL SERVERS TO RUNTIME; ``` ### Backendにmonitor用ユーザ作成 Plain textcontent\_copy ``` # read_only Global variableを変更する必要があるのでSUPERのみ権限付与 GRANT SUPER ON *.* TO proxysql@'%' IDENTIFIED BY 'proxysql'; ``` ### Read/Write Split MaxScaleのようにRead/Writeのクエリをノード別に振り分ける設定をしたい場合、アプリケーション側の実装に依存して次の方法を選択可能となっている！ - ユーザ - ポート - Query Rule MaxScaleと違ってQuery Ruleで分けれるのがポイントで、細かいルールを引けば可能性無限大？ ### Query RuleでRead/Write Split というわけで、Query Ruleで分ける設定を試してみました。 ここからはドキュメントとか見ながら頑張って設定してみてね！ サーバ設定は次の通り。 Plain textcontent\_copy ``` Admin> select * from mysql_servers; +--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 500 | pxc001 | 3306 | ONLINE | 1000 | 0 | 1000 | 0 | 0 | 0 | | | 501 | pxc002 | 3306 | ONLINE | 1000 | 0 | 1000 | 0 | 0 | 0 | | | 501 | pxc003 | 3306 | ONLINE | 1000 | 0 | 1000 | 0 | 0 | 0 | | +--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 3 rows in set (0.00 sec) ``` バックエンドは弊社ではおなじみの[Percona XtraDB Cluster](https://www.percona.com/software/mysql-database/percona-xtradb-cluster)を構成しています。 ユーザの設定は次の通り。 Plain textcontent\_copy ``` Admin> select username,password,active,default_hostgroup,transaction_persistent from mysql_users where username='sysbench'; +----------+----------+--------+-------------------+------------------------+ | username | password | active | default_hostgroup | transaction_persistent | +----------+----------+--------+-------------------+------------------------+ | sysbench | sysbench | 1 | 500 | 0 | +----------+----------+--------+-------------------+------------------------+ 1 row in set (0.00 sec) ``` ユーザ設定のポイントは次の通り。 - `default_hostgroup`をWriteのhostgroupに設定しておく - `transaction_persistent`を`0`に設定しておかないとQuery Ruleが効かない 用意するQuery Rule は次の通り。 Plain textcontent\_copy ``` Admin> select rule_id,active,match_pattern,destination_hostgroup,apply from mysql_query_rules; +---------+--------+------------------------+-----------------------+-------+ | rule_id | active | match_pattern | destination_hostgroup | apply | +---------+--------+------------------------+-----------------------+-------+ | 10 | 1 | ^SELECT .* FOR UPDATE$ | 500 | 1 | | 20 | 1 | ^SELECT | 501 | 1 | +---------+--------+------------------------+-----------------------+-------+ 2 rows in set (0.00 sec) ``` ここが残念ポイントで、上記設定でRead/Write Splitは実現可能だが、Failoverは実現できない。 ### Replication Host Group というわけでFailoverを実現させるためにRHG(Replication Host Group)という概念を利用します。 サーバ設定を次のように設定する。 Plain textcontent\_copy ``` Admin> select * from mysql_servers; +--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 500 | pxc001 | 3306 | ONLINE | 1000 | 0 | 1000 | 0 | 0 | 0 | | | 500 | pxc002 | 3306 | ONLINE | 1000 | 0 | 1000 | 0 | 0 | 0 | | | 500 | pxc003 | 3306 | ONLINE | 1000 | 0 | 1000 | 0 | 0 | 0 | | | 501 | pxc001 | 3306 | ONLINE | 1000 | 0 | 1000 | 0 | 0 | 0 | | | 501 | pxc002 | 3306 | ONLINE | 1000 | 0 | 1000 | 0 | 0 | 0 | | | 501 | pxc003 | 3306 | ONLINE | 1000 | 0 | 1000 | 0 | 0 | 0 | | | 8500 | pxc001 | 3306 | ONLINE | 1000 | 0 | 1000 | 0 | 0 | 0 | | | 8500 | pxc002 | 3306 | ONLINE | 1000 | 0 | 1000 | 0 | 0 | 0 | | | 8500 | pxc003 | 3306 | ONLINE | 1000 | 0 | 1000 | 0 | 0 | 0 | | +--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 9 rows in set (0.00 sec) ``` うわぁ、なんか冗長だしやな感じ〜。でもFailoverさせるためだ、我慢しよう。 RHGを次のように設定! Plain textcontent\_copy ``` Admin> INSERT INTO mysql_replication_hostgroups VALUES(500,501,''); Query OK, 1 row affected (0.00 sec) Admin> select * from mysql_replication_hostgroups; +------------------+------------------+---------+ | writer_hostgroup | reader_hostgroup | comment | +------------------+------------------+---------+ | 500 | 501 | | +------------------+------------------+---------+ 1 row in set (0.00 sec) ``` RHGを設定すると、`read_only`の状態を監視し`ON`なら`writer_hostgroup`のhostをBackendから外してくれます。 上記例でpxc002,pxc003をread\_onlyに設定すると次のような状態になります！ Plain textcontent\_copy ``` Admin> select * from mysql_servers; +--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 500 | pxc001 | 3306 | ONLINE | 1000 | 0 | 1000 | 0 | 0 | 0 | | | 501 | pxc001 | 3306 | ONLINE | 1000 | 0 | 1000 | 0 | 0 | 0 | | | 501 | pxc002 | 3306 | ONLINE | 1000 | 0 | 1000 | 0 | 0 | 0 | | | 501 | pxc003 | 3306 | ONLINE | 1000 | 0 | 1000 | 0 | 0 | 0 | | | 8500 | pxc001 | 3306 | ONLINE | 1000 | 0 | 1000 | 0 | 0 | 0 | | | 8500 | pxc002 | 3306 | ONLINE | 1000 | 0 | 1000 | 0 | 0 | 0 | | | 8500 | pxc003 | 3306 | ONLINE | 1000 | 0 | 1000 | 0 | 0 | 0 | | +--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 7 rows in set (0.00 sec) ``` この状態での挙動は次のようになります。 - pxc001がcrashするとhostgroup 500のホストが存在しなくなりWriteが停滞 - pxc002またはpxc003の`read_only`を`OFF`に設定することでFailover可能 pxc001復帰時に`read_only`を`ON`に設定する必要があるのでmy.cnfでread\_onlyを設定しておくこと！ ### Failoverの自動化 便利なもの作ってくれた人がいました〜！ [galera\_check.pl](https://github.com/Tusamarco/proxy_sql_tools)というツールを利用してFailoverを自動化することができます。。。 これをProxySQLのスケジューラ機能を使って… Plain textcontent\_copy ``` delete from scheduler where id=10; INSERT INTO scheduler (id,active,interval_ms,filename,arg1) VALUES (10,0,2000, "/var/lib/proxysql/galera_check.pl", "-u=admin -p=admin -h=127.0.0.1 -H=500:W,501:R -P=6032 --execution_time=1 --retry_down=2 --retry_up=1 --main_segment=1 --active_failover=1 --debug=0 --log=/var/log/proxysql/galeraLog"); LOAD SCHEDULER TO RUNTIME; SAVE SCHEDULER TO DISK; update scheduler set active=1 where id=10; LOAD SCHEDULER TO RUNTIME; ``` では、バイバイ〜！ ## この記事を書いた人 [![](/_astro/noicon.CTHOhNiB_Z24aAgM.webp)](/authors/dokuma/) ### [dokuma](/authors/dokuma/) dokumaのプロフィールと執筆記事をご覧いただけます。 [プロフィールと記事一覧](/authors/dokuma/)