ProxySQLでInnoDB Clusterもどきを作成する

これはなに？

InnoDB Clusterは、MySQL Router + MySQL Shell + GroupReplication の3つで構成される高可用性ソリューションだが、MySQL RouterとMySQL Shellを使わなくてもInnoDB Clusterと同様のこと (アプリケーション側とMySQL側のフェイルオーバー) を実現することは可能。

今回は、ProxySQL + GroupReplication を使用して、アプリケーション側のフェイルオーバーを行う方法について書こうと思う。

構成

Hyper-V上のUbuntu 18.04にLXCコンテナ (全てUbuntu 18.04) を立てて試してみた。

InnoDB Clusterの場合は、MySQL Routerが mysql_innodb_cluster_metadata スキーマを参照するためMySQL Shellが必須だが、今回の場合は別に必要はない。(ただ、MySQL Shell経由でGroupReplicationを組んだほうが簡単ではある。)

今回は一応、手動でGroupReplicationを組んでみることにする。

ノード	IPアドレス	ホスト名
GroupReplication Primary	10.0.3.11	gr_primary
GroupReplication Secondary1	10.0.3.12	gr_secondary1
GroupReplication Secondary2	10.0.3.13	gr_secondary2
ProxySQL	10.0.3.21	proxysql

準備

GroupReplicationの構築

Primaryノードの設定ファイルは以下のようにした。Secondaryノードに関しては、server_id とreport_host , loose-group_replicaton_local_address 以外は同じため省略する。

[mysqld]
server_id=11   # 各ノードで一意な値を設定
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE
report_host="10.0.3.11"　　# 自身のIPアドレスを指定する
default_authentication_plugin=mysql_native_password

plugin_load=group_replication.so
loose-group_replication_group_name="b73bffe9-5eff-4e32-8bed-fddb45b1999b"
loose-group_replication_start_on_boot=off
loose-group_replication_local_address="10.0.3.11:33061"  # 自身のIPアドレス
loose-group_replication_group_seeds="10.0.3.11:33061,10.0.3.12:33061,10.0.3.13:33061"
loose-group_replication_bootstrap_group=off

また、ProxySQLは caching_sha2_password 認証に対応していないため、default_authentication_plugin の値は mysql_native_password とする必要がある。

ProxySQL doesn't accept connections (frontend connections and connections to Admin) if the client tries to use caching_sha2_password authentication plugin. If clients are using libmysqlclient from MySQL 8.0 (that defaults to caching_sha2_password), they must explicitly use mysql_native_password.

GroupReplicationを構成する各ノードのmysqlに接続して、レプリケーション用のユーザを作成する。

mysql> create user repl@'%' identified by 'password';
mysql> grant replication slave on *.* to repl@'%';
mysql> change master to master_user='repl', master_password='password' for channel 'group_replication_recovery';
mysql> reset master;

次にPrimaryノードとなる最初の一台でのみ以下の作業を行う。Primaryノード以外でも group_replication_bootstrap_group をONにした場合、同じ名前のグループが複数起動されてしまうので注意。

Primary> set global group_replication_bootstrap_group=ON;
Primary> start group_replication;
Primary> set global group_replication_bootstrap_group=OFF;

続いて、SecondaryノードでもGroupReplicatonを開始する。

Secondary> start group_replication;

これで、GroupReplicationの構築が完成。

mysql> select member_host,member_state,member_role from performance_schema.replication_group_members;
+-------------+--------------+-------------+
| member_host | member_state | member_role |
+-------------+--------------+-------------+
| 10.0.3.11   | ONLINE       | PRIMARY     |
| 10.0.3.12   | ONLINE       | SECONDARY   |
| 10.0.3.13   | ONLINE       | SECONDARY   |
+-------------+--------------+-------------+

ProxySQLのインストール

次に、ProxySQLをインストールしていく。ProxySQLには1.4系と2.0系が存在するが、今回は1.4系を使用した。2.0系では色々な機能が追加されているとか (未確認)。

$ wget https://repo.percona.com/apt/percona-release_0.1-10.bionic_all.deb
$ sudo dpkg -i percona-release_0.1-10.bionic_all.deb
$ sudo apt update
$ sudo apt install proxysql
$ proxysql --version
ProxySQL version 1.4.16-percona-1.1, codename Truls

ProxySQLには管理インターフェイスから設定を変更する方法と、設定ファイル (/etc/proxysql.cnf) から設定を変更する方法の2通りが可能だが、今回は管理インターフェイスで設定を行う。両者の違いは、パラメータを動的に変更可能かどうかで、管理インターフェイスから設定する場合は多くのパラメータを動的に変更できる。

sysスキーマに監視用ビューを作成する

lefredさんのgistに公開されてる additional_to_sys_GR.sql を使用してGroupReplicationの状態を監視するビューを作成する。 Primaryノードで以下のコードを実行する。

$ mysql -uroot -p < addition_to_sys_GR.sql

すると、以下のようなビューが作成される。 transactions_behind と transactions_to_cert の値はそれぞれ applier queue と certifier queue にキューイングされるトランザクション数に相当する (ただし、ProxySQLはtransactions_to_cert の値を多分参照してなさそう)。詳しくは公式の Flow Control を参照。

mysql> select * from sys.gr_member_routing_candidate_status;
+------------------+-----------+---------------------+----------------------+
| viable_candidate | read_only | transactions_behind | transactions_to_cert |
+------------------+-----------+---------------------+----------------------+
| YES              | NO        |                   0 |                    0 |
+------------------+-----------+---------------------+----------------------+

また、ProxySQLがこのビューを参照できるように、監視用ユーザーを作成しておく。

mysql> create user 'monitor'@'%' identified by 'monitor';
mysql> grant select on sys.* to 'monitor'@'%';

ProxySQLに接続先サーバー情報を追加する

mysqlクライアントを使ってProxySQLの管理インターフェイスに接続し、GroupReplicationに接続するために必要な設定を行う。

# 管理インターフェイスのポートは6032
$ mysql -uadmin -padmin -h127.0.0.1 -P6032 --prompt='Admin> '

# 書き込みグループを1, 読み込みグループは2とする
Admin> INSERT INTO mysql_replication_hostgroups (writer_hostgroup, reader_hostgroup) VALUES (1, 2);
Admin> INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (1, '10.0.3.11',3306), (1, '10.0.3.12',3306), (1, '10.0.3.13',3306);
Admin> INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (2, '10.0.3.11',3306), (2, '10.0.3.12',3306), (2, '10.0.3.13',3306);

Admin> select hostgroup_id, hostname, status from mysql_servers;
+--------------+-----------+--------+
| hostgroup_id | hostname  | status |
+--------------+-----------+--------+
| 1            | 10.0.3.11 | ONLINE |
| 1            | 10.0.3.12 | ONLINE |
| 1            | 10.0.3.13 | ONLINE |
| 2            | 10.0.3.11 | ONLINE |
| 2            | 10.0.3.12 | ONLINE |
| 2            | 10.0.3.13 | ONLINE |
+--------------+-----------+--------+

# 変更をRUNTIMEに反映させ、DISKに保存する
Admin> LOAD MYSQL SERVERS TO RUNTIME;
Admin> SAVE MYSQL SERVERS TO DISK;

GroupReplicatonの監視設定を行う

ProxySQL1.4から mysql_group_replication_hostgroups というテーブルが追加され、自作スクリプトを用意しなくてもGroupReplicationの状態を監視できるようになった。

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 (1,3,2,4,1,1,0,100);

Admin> LOAD MYSQL SERVERS TO RUNTIME;
Admin> SAVE MYSQL SERVERS TO DISK;

R/W, R/O分割を行う

ProxySQLでは、Read-Write (RW) と Read-Only (RO) の振り分け方として、主に以下の3つの方法が可能。それぞれの方法について説明を行う。

接続ポートを分離する
接続ユーザーを分離する
クエリの種類によって分離する

1. 接続ポートを分離する場合

接続ポートを分離することによってR/W, R/O分割を行う。R/W用ポートは6401, R/O用ポートは6402を使用した。以下の内容はProxySQL Wiki) を参考にした。

接続用ユーザを作成する

ProxySQLからMySQLに接続するためのユーザを作成する。MySQLとProxySQLの両方でユーザ作成を行う必要がある。

# GroupReplicationのPrimaryノードで実行
mysql> CREATE USER 'proxysql'@'%' identified by 'password';
mysql> GRANT ALL ON *.* TO 'proxysql'@'%';

# ProxySQLの管理インターフェイスで実行
Admin> INSERT INTO mysql_users (username, password) VALUES ('proxysql', 'password');
Admin> LOAD MYSQL USERS TO RUNTIME;
Admin> SAVE MYSQL USERS TO DISK;

接続ポートの設定を行う

ProxySQLの mysql-interfaces の値を変更する。ProxySQLは大抵のパラメータを動的に変更可能だが、このパラメータに関しては再起動する必要がある。

Admin> SET mysql-interfaces='0.0.0.0:6401;0.0.0.0:6402';
Admin> SAVE MYSQL VARIABLES TO DISK;
$ sudo service proxysql restart

ルーティングルールを設定する

6401ポートから接続された場合は書き込みグループへ、6402ポートから接続された場合は読み込みグループにルーティングするためのルールを設定する。

Admin> INSERT INTO mysql_query_rules (rule_id,active,proxy_port,destination_hostgroup,apply) VALUES (1,1,6401,1,1), (2,1,6402,2,1);
Admin> LOAD MYSQL QUERY RULES TO RUNTIME;
Admin> SAVE MYSQL QUERY RULES TO DISK;

これで、接続ポートによるR/W, R/O分割が可能。

試してみる

ProxySQLノード上で以下のコマンドを実行する。ちゃんと6401ポート→Primaryノード、6402ポート→Secondaryノードに接続できることが確認できる。

$ mysql -u proxysql -h 127.0.0.1 -P 6401 -p -BNe 'select @@hostname;'
gr_primary

$ mysql -u proxysql -h 127.0.0.1 -P 6402 -p -BNe 'select @@hostname;'
gr_secondary2

2. 接続ユーザを分離する場合

接続ユーザを分離することによってR/W, R/O分割を行う。 R/W用ユーザはwriter、R/O用ユーザはreaderとした。

接続ユーザを作成する

ProxySQLからMySQLに接続するためのユーザを作成する。今回はwriterとreaderユーザを作成する。

# GroupReplicationのPrimaryノードで実行
mysql> CREATE USER 'writer'@'%' IDENTIFIED BY 'password';
mysql> GRANT ALL ON *.* TO 'writer'@'%';
mysql> CREATE USER 'reader'@'%' IDENTIFIED BY 'password';
mysql> GRANT ALL ON *.* TO 'reader'@'%';

# ProxySQLの管理インターフェイスで実行
Admin> INSERT INTO mysql_users (username, password, default_hostgroup) VALUES ('writer', 'password', 1), ('reader', 'password', 2);
Admin> LOAD MYSQL USERS TO RUNTIME;
Admin> SAVE MYSQL USERS TO DISK;

試してみる

ProxySQLノード上で以下のコマンドを実行する。ちゃんとwriterユーザ→Primaryノード、readerユーザ→Secondaryノードに接続できることが確認できる。(ProxySQLのデフォルトの接続ポートは6033)

$ mysql -u writer -h 127.0.0.1 -P 6033 -p -BNe 'select @@hostname;'
gr_primary

$ mysql -u reader -h 127.0.0.1 -P 6033 -p -BNe 'select @@hostname;'
gr_secondary2

3. クエリの種類によって分離する場合

ProxySQLには正規表現によって一致したクエリを特定のグループへルーティングする機能がある。ProxySQL Wikiではこの方法が推奨されていそう。

接続ユーザを作成する

MySQLとProxySQLに接続ユーザを作成する。proxysql ユーザは default_hostgroup を1に設定し、全てのクエリをR/Wノード (Primaryノード) に向けておく。

mysql> CREATE USER 'proxysql'@'%' IDENTIFIED BY 'password';
mysql> GRANT ALL ON *.* TO 'proxysql'@'%';

Admin> INSERT INTO mysql_users (username, password, default_hostgroup) VALUES ('proxysql', 'password', 1);
Admin> LOAD MYSQL USERS TO RUNTIME;
Admin> SAVE MYSQL USERS TO DISK;

ルーティングルールを設定する

ProxySQL Wiki を参考にして、簡単なルーティングルールを設定してみる。

Admin> INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) VALUES (1,1,'^SELECT.*FOR UPDATE$',1,1), (2,1,'^SELECT',2,1);
Admin> LOAD MYSQL QUERY RULES TO RUNTIME;
Admin> SAVE MYSQL QUERY RULES TO DISK;

この場合、SELECT ... FOR UPDATE がR/Wノードに、それ以外のSELECTがR/Oノードにルーティングされる。また、SELECT 以外で始まるすべてのクエリはデフォルトグループ (R/Wノード) にルーティングされる。

試してみる

ProxySQLノード上で以下のコマンドを実行する。SELECTから始まる場合は以下のようにSecondaryノードにルーティングされることが分かる。

$ mysql -u proxysql -h 127.0.0.1 -P 6033 -p -BNe 'select @@hostname;'
gr_secondary1

ためしに、以下のようにtestデータベースを作成してみたところ、ちゃんとPrimaryノードにルーティングされたことが分かる。

$ mysql -u proxysql -h 127.0.0.1 -P 6033 -e 'create database test;' -p

# Primaryノードで確認する
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+

まとめ

ProxySQLを使ってInnoDB Clusterと同じように、アプリケーション側のフェイルオーバーができることが確認できた。ProxySQLはL7プロキシのため、かなり柔軟なルーティングが可能だったりする。ただし、その反面、管理しなければいけないものが増えるため、構成管理ツールとか導入しないと厳しいだろうなぁ、とか感じた。もし、接続ポートをR/WとR/Oで分離したくないならば、MySQL Routerの代わりにProxySQLを使用するのも良いのかもしれない。

2000秒後の私へ

つまり、ただの覚書。

ProxySQLでInnoDB Clusterもどきを作成する

これはなに？

構成

準備

GroupReplicationの構築

ProxySQLのインストール

sysスキーマに監視用ビューを作成する

ProxySQLに接続先サーバー情報を追加する

GroupReplicatonの監視設定を行う

R/W, R/O分割を行う

1. 接続ポートを分離する場合

接続用ユーザを作成する

接続ポートの設定を行う

ルーティングルールを設定する

試してみる

2. 接続ユーザを分離する場合

接続ユーザを作成する

試してみる

3. クエリの種類によって分離する場合

接続ユーザを作成する

ルーティングルールを設定する

試してみる

まとめ