ProxySQL と Percona XtraDB Cluster でロードバランシングと高可用性を実現する

2017年12月13日

この記事は最終更新から7年以上経過しています。内容が古くなっている可能性があります。

今回は、MySQLのマルチマスター構成を実現し高い可用性を持つ Percona XtraDB Cluster（以下：PXC）とハイパフォーマンスなSQLプロキシのProxySQLの組み合わせてロードバランシングと高可用性を実現するための方法を検証します。

※ PXCは「Galera Cluster」をベースとした製品です。

ProxySQLとは？

ProxySQLは、ハイパフォーマンスなMySQLのSQLプロキシです。MySQLのフォークであるPercona Server や MariaDB だけでなく、Galera Cluster にも対応しています。
クエリールールによるルーティングなど柔軟な設定変更が可能で、またリスタートを行わずに設定を反映できるなど、高い可用性も持ち合わせています。

主な特徴

クエリーキャッシュ
クエリールーティング
フェイルオーバー連携
ファイアーウォール

参考
ProxySQL 公式サイト
 ProxySQL – Github

Percona XtraDB Cluster とは？

Percona XtraDB Cluster は Percona社が開発するマルチマスター構成の同期型レプリケーションで、全てのノードに対して書き込み、読み込みが可能で、単一障害点が無い高い可用性と、自動死活監視、自動ノードプロビジョニングなど運用容易性を持ったクラスタリングソリューションです。

参考
Percona XtraDB Cluster
弊社ブログ関連記事

構成図

今回の検証での構成はProxySQLが1台なので、ここが単一障害点となってしまいますが、通常の運用では多重化したり、複数あるアプリケーションサーバーと同居させることで可用性を担保します。

sysbench 1.0
ProxySQL 1.4.3
Percona XtraDB Cluster 5.7.19-29.22

node1: 172.26.31.202
node2: 172.26.31.203
node3: 172.26.31.204

インスタンスは全てEC2
OSは全て CentOS 7.3

PXCのセットアップ

PXCのセットアップは下記のURLなどを参考にセットアップを行なってください。

Installing Percona XtraDB Cluster on Red Hat Enterprise Linux and CentOS

ProxySQLのセットアップ

ここでは Percona のレポジトリを使ってインストールする方法で行います。

レポジトリの追加を行います。

$ sudo yum install -y http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm

1	$ sudo yum install -y http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm

ProxySQLのインストールします。

$ sudo yum install -y proxysql

1	$ sudo yum install -y proxysql

PXCの監視やProxySQLのSQL管理インターフェースでログインするために MySQL Client もインストールします。

$ sudo yum install -y Percona-Server-client-57

1	$ sudo yum install -y Percona-Server-client-57

サービスをスタートします。

$ sudo service proxysql start

1	$ sudo service proxysql start

ProxySQLの設定

PerconaのパッケージからProxySQLをインストールすると、初期設定が簡単に行える proxysql-admin というツールが同梱されていますが、今回はこのツールを使用せずに設定を行います。

参考：Load balancing with ProxySQL

ProxySQLの設定方法は、同様のSQLプロキシである、MySQL Router や MaxScale のように設定ファイルによる設定方法だけで無く、SQLによる管理インターフェースによって設定することができます。
また、ProxySQLの設定は Multi layer configuration system と呼ばれる下記の図のような3層構造になっています。

RUNTIME
ここに反映されている設定でProxySQLが動作します。
MEMORY
DISKやCONFIG FILEの設定が反映された状態。mysqlコマンドでログインしてSQLで更新することができます。
DISK
MEMORYの設定をディスクに保存します。設定ファイルとは別でこちらの設定のほうが優先されます。
CONFIG FILE
/etc/proxysql.cnfのファイルが読み込まれます。

参考：Multi layer configuration system · sysown/proxysql Wiki · GitHub

設定ファイルによる初期設定

/etc/proxysql.cnf にProxySQLの設定ファイルが配置されるので必要に応じてこのファイルを編集してください。
今回の検証ではデフォルトのまま使用します。

SQLによる管理インターフェースによる設定

ProxySQLのSQL管理インターフェースは通常の mysql コマンドを使ってログインすることができます。
（管理インターフェースのポートはデフォルト6032です）

$ mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '
Admin> show databases;
+-----+---------+-------------------------------+
| seq | name    | file                          |
+-----+---------+-------------------------------+
| 0   | main    |                               |
| 2   | disk    | /var/lib/proxysql/proxysql.db |
| 3   | stats   |                               |
| 4   | monitor |                               |
+-----+---------+-------------------------------+
4 rows in set (0.00 sec)

$ mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '

Admin> show databases;

+-----+---------+-------------------------------+

| seq | name | file |

+-----+---------+-------------------------------+

| 0 | main | |

| 2 | disk | /var/lib/proxysql/proxysql.db |

| 3 | stats | |

| 4 | monitor | |

+-----+---------+-------------------------------+

4 rows in set (0.00 sec)

見た目はMySQLと同じように見えますが、実際のデータベースはSQLite3が使われています。

PXC の監視ユーザーを作成

PXCへの監視用ユーザーは mysql_variables の以下の項目に設定されているので必要に応じて設定してください。

mysql_variables=
{
...省略...
        monitor_username="monitor"
        monitor_password="monitor"
...省略...
}

mysql_variables=

{

...省略...

monitor_username="monitor"

monitor_password="monitor"

...省略...

}

今回はデフォルトの監視用ユーザーをPXCに作成します。必要な権限は USAGE なので、権限付与は不要です。
（PXCなので、どれかのノードでユーザーを作成すれば、全ノードに反映されます）

$ mysql -u root -p
mysql> CREATE USER `monitor`@`%` IDENTIFIED BY 'monitor';

1 2	$ mysql -u root -p mysql> CREATE USER `monitor`@`%` IDENTIFIED BY 'monitor';

ロードバランシング

PXCではマルチマスターのため、全てのノードに対して書き込みが可能で、どのノードのデータも同一になります。
ただし、PXCの仕様として同じレコードに対する更新トランザクションが別々のノードで同時に行われた場合、後から更新されたトランザクションはロールバックされエラーとなります。この場合、アプリケーション側でエラー処理としてリトライなどの処理を追加する必要があります。

また、更新データが他のノードに伝播してから反映されるのに若干のラグが発生する場合があります。

これらの制約を理解した上で、ロードバランシングの方法を決定します。

シングルプライマリ

非常に厳格なデータ整合性が必要な場合や、マスタースレーブ構成で運用中の既存のアプリケーションをPXCに載せ替えたい場合は、更新処理を1ノードに限定することで従来のマスタースレーブ構成と同等の動きを実現できます。

アプリケーションからの接続ポートに応じて、接続するノードを切り替えることもできますが、今回は接続ユーザーでノードを切り替える設定を行います。

ProxySQLでシングルプライマリの設定をする場合

まず、バックエンドのMySQLの設定は mysql_servers テーブルを操作します。

hostgroup_id フィールドはバックエンドのMySQLをグルーピングすることができ、IDは自由に付与することができます。今回は更新系は0、参照系は1とします。
PXCの場合、どのノードからも更新も参照もできるため、3つのノードはどちらのグループにも所属させるようにします。

status はバックエンドのMySQLの状態です。
更新については1ノードに限定し、該当ノードがダウンしたら別のノードに切り替えたいのでグループ0のstatus はノード1を除いてSOFT_OFFLINEの設定にします。
参照については全ノード分散させたいので、全てONLINEに設定します。

設定は以下のようになります。

Admin> INSERT INTO mysql_servers (hostgroup_id, hostname, status) VALUES (0, '172.26.31.202', 'ONLINE'), (0, '172.26.31.203', 'OFFLINE_SOFT'), (0, '172.26.31.204', 'OFFLINE_SOFT');
Admin> INSERT INTO mysql_servers (hostgroup_id, hostname, status) VALUES (1, '172.26.31.202', 'ONLINE'), (1, '172.26.31.203', 'ONLINE'), (1, '172.26.31.204', 'ONLINE');
Admin> SELECT hostgroup_id, hostname, status FROM mysql_servers;
+--------------+---------------+--------------+
| hostgroup_id | hostname      | status       |
+--------------+---------------+--------------+
| 0            | 172.26.31.202 | ONLINE       |
| 0            | 172.26.31.203 | OFFLINE_SOFT |
| 0            | 172.26.31.204 | OFFLINE_SOFT |
| 1            | 172.26.31.202 | ONLINE       |
| 1            | 172.26.31.203 | ONLINE       |
| 1            | 172.26.31.204 | ONLINE       |
+--------------+---------------+--------------+

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

Admin> INSERT INTO mysql_servers (hostgroup_id, hostname, status) VALUES (0, '172.26.31.202', 'ONLINE'), (0, '172.26.31.203', 'OFFLINE_SOFT'), (0, '172.26.31.204', 'OFFLINE_SOFT');

Admin> INSERT INTO mysql_servers (hostgroup_id, hostname, status) VALUES (1, '172.26.31.202', 'ONLINE'), (1, '172.26.31.203', 'ONLINE'), (1, '172.26.31.204', 'ONLINE');

Admin> SELECT hostgroup_id, hostname, status FROM mysql_servers;

+--------------+---------------+--------------+

| hostgroup_id | hostname | status |

+--------------+---------------+--------------+

| 0 | 172.26.31.202 | ONLINE |

| 0 | 172.26.31.203 | OFFLINE_SOFT |

| 0 | 172.26.31.204 | OFFLINE_SOFT |

| 1 | 172.26.31.202 | ONLINE |

| 1 | 172.26.31.203 | ONLINE |

| 1 | 172.26.31.204 | ONLINE |

+--------------+---------------+--------------+

Admin> LOAD MYSQL SERVERS TO RUNTIME;

Admin> SAVE MYSQL SERVERS TO DISK;

次にPXC側に書き込み用と読み込み用のユーザーを作成します。

$ mysql -u root -p
mysql> CREATE USER `writer`@`%` IDENTIFIED BY 'Password1!';
mysql> GRANT ALL ON *.* TO `writer`@`%`;
mysql> CREATE USER `reader`@`%` IDENTIFIED BY 'Password1!';
mysql> GRANT ALL ON *.* TO `reader`@`%`;

$ mysql -u root -p

mysql> CREATE USER `writer`@`%` IDENTIFIED BY 'Password1!';

mysql> GRANT ALL ON *.* TO `writer`@`%`;

mysql> CREATE USER `reader`@`%` IDENTIFIED BY 'Password1!';

mysql> GRANT ALL ON *.* TO `reader`@`%`;

ProxySQL側では書き込み用ユーザーは更新系グループの0へ、読み込み用ユーザーは参照系グループの1に接続が向くように設定します。

Admin> INSERT INTO mysql_users (username, password, default_hostgroup) VALUES ('writer', 'Password1!', 0);
Admin> INSERT INTO mysql_users (username, password, default_hostgroup) VALUES ('reader', 'Password1!', 1);
Admin> LOAD MYSQL USERS TO RUNTIME;
Admin> SAVE MYSQL USERS TO DISK;

Admin> INSERT INTO mysql_users (username, password, default_hostgroup) VALUES ('writer', 'Password1!', 0);

Admin> INSERT INTO mysql_users (username, password, default_hostgroup) VALUES ('reader', 'Password1!', 1);

Admin> LOAD MYSQL USERS TO RUNTIME;

Admin> SAVE MYSQL USERS TO DISK;

PXCの監視用スクリプトを有効にします。

Admin> INSERT INTO scheduler (id, active, interval_ms, filename, arg1, arg2, arg3, arg4, arg5) VALUES (1, 1, 10000, '/usr/bin/proxysql_galera_checker', 0, 1, 1, 1, '/var/lib/proxysql/proxysql_galera_checker.log');
Admin> LOAD SCHEDULER TO RUNTIME;
Admin> SAVE SCHEDULER TO DISK;

Admin> INSERT INTO scheduler (id, active, interval_ms, filename, arg1, arg2, arg3, arg4, arg5) VALUES (1, 1, 10000, '/usr/bin/proxysql_galera_checker', 0, 1, 1, 1, '/var/lib/proxysql/proxysql_galera_checker.log');

Admin> LOAD SCHEDULER TO RUNTIME;

Admin> SAVE SCHEDULER TO DISK;

スクリプトに渡す引数の意味は以下の通りです。

引数	説明
arg1	書き込みノードの hostgroup ID を指定
arg2	読み込みノードの hostgroup ID を指定
arg3	書き込みノードの最大数
arg4	0に設定すると、書き込みノードとしてONLINEになると、読み込みノードではONLINEになりません
arg5	ログファイル

ProxySQL経由でバックエンドのノードに接続します。（デフォルトのポートは6033です）
書き込みユーザーで接続する

$ mysql -u writer -pPassword1! -h [ProxySQLのIPアドレス] -P6033

1	$ mysql -u writer -pPassword1! -h [ProxySQLのIPアドレス] -P6033

読み込みユーザーで接続する

$ mysql -u reader -pPassword1! -h [ProxySQLのIPアドレス] -P6033

1	$ mysql -u reader -pPassword1! -h [ProxySQLのIPアドレス] -P6033

マルチプライマリ

書き込み、読み込み共に全てのノードに分散されるのでシンプルでわかりやすい構成です。

ProxySQLでマルチプライマリの設定をする場合

マルチプライマリの場合は hostgroup_id を更新系と参照系に分ける必要がないため、一つのグループに全てのノードが所属するよう設定します。

status についても全てのノードに更新も参照も分散させるため、全てONLINEとなります。

マルチプライマリの設定は以下になります。

Admin> INSERT INTO mysql_servers (hostgroup_id, hostname, status) VALUES (0, '172.26.31.202', 'ONLINE'), (0, '172.26.31.203', 'ONLINE'), (0, '172.26.31.204', 'ONLINE');
Admin> SELECT hostgroup_id, hostname, status FROM mysql_servers;
+--------------+---------------+---------+
| hostgroup_id | hostname      | status  |
+--------------+---------------+---------+
| 0            | 172.26.31.202 | ONLINE  |
| 0            | 172.26.31.203 | ONLINE  |
| 0            | 172.26.31.204 | ONLINE  |
+--------------+---------------+---------+

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

Admin> INSERT INTO mysql_servers (hostgroup_id, hostname, status) VALUES (0, '172.26.31.202', 'ONLINE'), (0, '172.26.31.203', 'ONLINE'), (0, '172.26.31.204', 'ONLINE');

Admin> SELECT hostgroup_id, hostname, status FROM mysql_servers;

+--------------+---------------+---------+

| hostgroup_id | hostname | status |

+--------------+---------------+---------+

| 0 | 172.26.31.202 | ONLINE |

| 0 | 172.26.31.203 | ONLINE |

| 0 | 172.26.31.204 | ONLINE |

+--------------+---------------+---------+

Admin> LOAD MYSQL SERVERS TO RUNTIME;

Admin> SAVE MYSQL SERVERS TO DISK;

PXC側のユーザーを作成します。
（シングルプライマリのように書き込み用と読み込み用でユーザーを分ける必要はありません）

$ mysql -u root -p
mysql> CREATE USER `app`@`%` IDENTIFIED BY 'Password1!';
mysql> GRANT ALL ON *.* TO `app`@`%`;

$ mysql -u root -p

mysql> CREATE USER `app`@`%` IDENTIFIED BY 'Password1!';

mysql> GRANT ALL ON *.* TO `app`@`%`;

ProxySQL側は以下のような設定になります。

Admin> INSERT INTO mysql_users (username, password, default_hostgroup) VALUES ('app', 'Password1!', 0);
Admin> LOAD MYSQL USERS TO RUNTIME;
Admin> SAVE MYSQL USERS TO DISK;

Admin> INSERT INTO mysql_users (username, password, default_hostgroup) VALUES ('app', 'Password1!', 0);

Admin> LOAD MYSQL USERS TO RUNTIME;

Admin> SAVE MYSQL USERS TO DISK;

ProxySQL経由でバックエンドのノードに接続します。

$ mysql -u app -pPassword1! -h [ProxySQLのIPアドレス] -P6033

1	$ mysql -u app -pPassword1! -h [ProxySQLのIPアドレス] -P6033

障害発生時の挙動

PXCでは1ノードがダウンした場合でも、残りの2ノードで継続してサービスを継続することができます。

例えば、従来のマスタースレーブ構成のような、マスターがダウンした場合、スレーブをマスターに昇格させるようなフェイルオーバーの処理も必要ありません。
また、PXCでは更新トランザクションは全てのノードに伝播しているため、従来の非同期レプリケーションのようにデータロスも発生しません。

そのため、PXCでノードがダウンしても、ProxySQL側ではダウンしたノードを単純に切り離すだけでフェイルオーバーは完了するというシンプルなものとなります。

シングルプライマリで書き込みノードがダウンした場合

書き込みノードがダウンした場合、ProxySQLは障害を検知して、残り2ノードのうち、どちらかを新しい書き込みノードに変更してサービスを継続させます。

Admin> SELECT hostgroup_id, hostname, status FROM mysql_servers;
+--------------+---------------+--------------+
| hostgroup_id | hostname      | status       |
+--------------+---------------+--------------+
| 0            | 172.26.31.203 | ONLINE       |
| 0            | 172.26.31.204 | OFFLINE_SOFT |
| 1            | 172.26.31.203 | ONLINE       |
| 1            | 172.26.31.204 | ONLINE       |
+--------------+---------------+--------------+

Admin> SELECT hostgroup_id, hostname, status FROM mysql_servers;

+--------------+---------------+--------------+

| hostgroup_id | hostname | status |

+--------------+---------------+--------------+

| 0 | 172.26.31.203 | ONLINE |

| 0 | 172.26.31.204 | OFFLINE_SOFT |

| 1 | 172.26.31.203 | ONLINE |

| 1 | 172.26.31.204 | ONLINE |

+--------------+---------------+--------------+

マルチプライマリで1ノードがダウンした場合

1ノードがダウンした場合、単純にダウンしたノードが切り離され、サービスは継続されます。

Admin> SELECT hostgroup_id, hostname, status FROM mysql_servers;
+--------------+---------------+--------------+
| hostgroup_id | hostname      | status       |
+--------------+---------------+--------------+
| 0            | 172.26.31.203 | ONLINE       |
| 0            | 172.26.31.204 | ONLINE       |
+--------------+---------------+--------------+

Admin> SELECT hostgroup_id, hostname, status FROM mysql_servers;

+--------------+---------------+--------------+

| hostgroup_id | hostname | status |

+--------------+---------------+--------------+

| 0 | 172.26.31.203 | ONLINE |

| 0 | 172.26.31.204 | ONLINE |

+--------------+---------------+--------------+

まとめ

PXCとProxySQLを組み合わせることで、運用の手間を軽減しつつ、高い可用性を実現できます。
PXCはSQLレベルでMySQLと同じため、シングルプライマリにすれば従来のマスタースレーブレプリケーションと同じように使えるので既存のアプリケーションからの乗り換えも可能です。
ProxySQLの設定方法は他のSQLプロキシと比較しても特殊なので少し慣れが必要かもしれません。

よかったらシェアしてね！

URLをコピーしました！

ProxySQL と Percona XtraDB Cluster でロードバランシングと高可用性を実現する

ProxySQLとは？

主な特徴

Percona XtraDB Cluster とは？

構成図

PXCのセットアップ

ProxySQLのセットアップ

ProxySQLの設定

設定ファイルによる初期設定

SQLによる管理インターフェースによる設定

PXC の監視ユーザーを作成

ロードバランシング

シングルプライマリ

ProxySQLでシングルプライマリの設定をする場合

マルチプライマリ

ProxySQLでマルチプライマリの設定をする場合

障害発生時の挙動

シングルプライマリで書き込みノードがダウンした場合

マルチプライマリで1ノードがダウンした場合

まとめ

スマートスタイルTECHブログについて

関連記事