こんにちは、キエンです。
最近、DBサーバーの負荷分散とAuroraの高速フェイルオーバーの対策を調査した時、ProxySQLを検証しましたので、ご紹介します。
ProxySQLとは?
ProxySQLはMySQLおよびfork（Percona ServerやMariaDBなど）用の高性能で高可用性のプロトコル対応プロキシです。ライセンスはGPLv3となっています。以下の主な特徴はあります。
- クエリーキャッシュ
- クエリールーティング
- フェイルオーバーサポート
- ファイアーウォール
ProxySQLの公開サイト
ProxySQLの構成
ProxySQLの構成は複雑ですが、以下のニーズを満たすため使いやすい構成システムがあります。
- 設定を簡単に動的更新を許可する
- ゼロダウンタイムプロビジョニングが必要な大規模なインフラストラクチャでProxySQLを使用できるようになる
- MySQL互換の管理インターフェイスを使用できる
- ProxySQLプロセスの再起動を必要とせずに、できるだけ多くの設定を動的に変更できる
- 無効な設定をを簡単にロールバックできる
Multi layer configuration system と呼ばれる下記の図のような3層構造になっています。
+-------------------------+
| RUNTIME |
+-------------------------+
/|\ |
| |
[1] | [2] |
| \|/
+-------------------------+
| MEMORY |
+-------------------------+ _
/|\ | |\
| | \
[3] | [4] | \ [5]
| \|/ \
+-------------------------+ +-------------------------+
| DISK | | CONFIG FILE |
+-------------------------+ +-------------------------+
- RUNTIME
- インメモリデータ構造を表する
- リクエストを処理するスレッドに使用される
- ランタイム変数には以下が含まれます。
- グローバル変数で定義された値
- ホストグループにグループ化されたバックエンドサーバーのリスト
- プロキシに接続できるMySQLユーザーのリスト
- RUNTIME設定セクションの内容を直接変更できない、常に最下層を通過する必要がある
- MEMORY (MAINとも呼ばれる)
- インメモリデータベースを表す
- MySQL互換のインターフェイスを使用して、さまざまなProxySQL設定テーブル/データベースをクエリできる
- mysql_servers
- ProxySQLが接続するバックエンドサーバーのリスト
- mysql_users
- ProxySQLに接続するユーザーのリスト
- ProxySQLはこちらのユーザーを使用してバックエンドサーバーにも接続する
- mysql_query_rules
- トラフィックをさまざまなバックエンドサーバーにルーティングするときに判別されるクエリルールのリスト
- これらのルールは、クエリを書き換えたり、実行されたクエリの結果をキャッシュすることもできる
- global_variables
- プロキシが使用するように設定され、実行時に調整できるグローバル変数のリスト
- mysql_servers
- DISK
- ディスク上のSQLite3データベースを表す (デフォルトの箇所は$DATADIR/proxysql.db)
- MEMORYの設定を保持する
- 書き込んだものは、次回起動時に自動的に読み込まれる
- 保持されなかったMEMORYの設定は次起動時に失われるので、保持することが重要する
- 設定ファイルは別でこちらの設定のほうが優先される
- CONFIG FILE
- /etc/proxysql.cnfのファイルが読み込まれる
初回起動 (または --initial フラグ)
最初の起動時に、MEMORYとRUNTIMEの設定が設定ファイルから読み込まれます。 その後、設定はProxySQLの埋め込みSQLiteデータベースに保持されます。 --initialフラグを指定してProxySQLを実行すると、初期設定を強制的に再実行できます。
通常起動
通常の起動中に、設定ファイルを読み取り、datadirを決定します。datadir内にデータベースファイルがあるか確認します。
- データベースファイルが見つかった場合、ディスク上のデータベースからインメモリの設定を初期化します。したがって、ディスクはメモリにロードされ、ランタイム設定に向けて伝播される
- データベースファイルが見つからない場合、設定ファイルが存在する場合、設定ファイルが解析され、その内容がインメモリデータベースにロードされます。その後、ディスク上のデータベースに保存され、実行時にロードされる
データベースファイルが見つかった場合、設定ファイルは解析されないことに注意することが重要です。 つまり、通常の起動中に、ProxySQLはディスク上のデータベースからのみからインメモリ設定を初期化します。
リロード(または --reload フラグ)
ProxySqlが--reloadフラグを指定して実行された場合、設定ファイル内の設定をデータベースファイルの内容とマージしようとします。 その後、通常の起動手順を実行します。 コンフリクトする場合、正常にマージできるという保証はありません。ユーザーは、マージが期待どおりに実行されたことを常に検証する必要があります。
レイヤー間で構成を移動するコマンド
設定をディスクに保持するか、設定をランタイムにロードするために、管理インターフェイスを介して実行できるさまざまな管理コマンドのセットがあります。 個人的にはもっとも使用されるコマンドはこちらです。
# MEMORYからRUNTIMEにmysql usersをロードする LOAD MYSQL USERS TO RUNTIME; # MEMORYからDISKにmysql usersを永続化する SAVE MYSQL USERS TO DISK; # MEMORYからRUNTIMEにmysql serversをロードする LOAD MYSQL SERVERS TO RUNTIME; # MEMORYからDISKにmysql serversを永続化する SAVE MYSQL SERVERS TO DISK; # MEMORYからRUNTIMEにmysql query rulesをロードする LOAD MYSQL QUERY RULES TO RUNTIME; # MEMORYからDISKにmysql query rulesを永続化する SAVE MYSQL QUERY RULES TO DISK; # MEMORYからRUNTIMEにmysql variablesをロードする LOAD MYSQL VARIABLES TO RUNTIME; # MEMORYからDISKにmysql variablesを永続化する SAVE MYSQL VARIABLES TO DISK; # MEMORYからRUNTIMEにadmin variablesをロードする LOAD ADMIN VARIABLES TO RUNTIME; # MEMORYからDISKにadmin variableを永続化する SAVE ADMIN VARIABLES TO DISK;
参考 : Configuring ProxySQL · sysown/proxysql Wiki · GitHub
ProxySQLのインストール
今回、Centos6でProxySQL最新版(2.0.6)を試してみました。
Dockerで試す
DockerFileを作成しました。
# DockerFile FROM centos:6 # ProxySQLのyumリポジトリの登録 RUN echo $'[proxysql_repo]\n\ name= ProxySQL YUM repository\n\ baseurl=https://repo.proxysql.com/ProxySQL/proxysql-2.0.x/centos/\$releasever\n\ gpgcheck=1\n\ gpgkey=https://repo.proxysql.com/ProxySQL/repo_pub_key\n' \ >> /etc/yum.repos.d/proxysql.repo RUN yum install -y proxysql mysql # デフォルトadminユーザで管理インターフェイスをアクセスできるため、 # MySQL クライアントをインストールする必要があります。
# docker-compose.yml version: "3.3" services: proxysql: build: context: . dockerfile: ./docker/Dockerfile command: /bin/bash -c "/etc/init.d/proxysql start && tail -f /var/lib/proxysql/proxysql.log" ports: - 6033:6033 volumes: - proxysql:/var/lib/proxysql volumes: proxysql: driver: local
最初の確認
コンテナを立ち上げて、確認しましょう。
$ docker-compose up --build $ docker-compose exec proxysql /bin/bash
ProxySQLバージョン
[root@9efeb635e1b0 /]# proxysql --version ProxySQL version 2.0.6-73-gc746bf7, codename Truls
設定ファイル
ProxySQLのデフォルトの設定ファイルです。admin_variablesでは管理インタフェースのパラメータを定義します。mysql_variablesではMySQLのパラメータを定義します。
#/etc/proxysql.cnf datadir="/var/lib/proxysql" errorlog="/var/lib/proxysql/proxysql.log" admin_variables= { admin_credentials="admin:admin" mysql_ifaces="0.0.0.0:6032" } mysql_variables= { threads=4 max_connections=2048 default_query_delay=0 default_query_timeout=36000000 have_compress=true poll_timeout=2000 interfaces="0.0.0.0:6033" default_schema="information_schema" stacksize=1048576 server_version="5.5.30" connect_timeout_server=3000 monitor_username="monitor" monitor_password="monitor" monitor_history=600000 monitor_connect_interval=60000 monitor_ping_interval=10000 monitor_read_only_interval=1500 monitor_read_only_timeout=500 ping_interval_server_msec=120000 ping_timeout_server=500 commands_stats=true sessions_sort=true connect_retries_on_failure=10 }
デフォルトのadminユーザーはローカルでしか接続できないです。 リモートで接続するには、admin_credentialsでセカンダリユーザーを作成する必要があります。
admin_variables= { # リモート用のユーザを追記 admin_credentials="admin:admin; radminuser:radminpass" mysql_ifaces="0.0.0.0:6032" }
今回の検証では設定ファイルはデフォルトのまま使用します。これからの設定は全て管理インターフェイスでやります。
管理インターフェイスの確認
[root@1833aeb453a7 /]# mysql -u admin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> ' Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 Server version: 5.5.30 (ProxySQL Admin Module) Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. Admin> show databases; +-----+---------------+-------------------------------------+ | seq | name | file | +-----+---------------+-------------------------------------+ | 0 | main | | | 2 | disk | /var/lib/proxysql/proxysql.db | | 3 | stats | | | 4 | monitor | | | 5 | stats_history | /var/lib/proxysql/proxysql_stats.db | +-----+---------------+-------------------------------------+ 5 rows in set (0.00 sec) Admin>
- main
- インメモリ設定データベース
- このデータベースを使用すると、簡単にProxySQLの設定を更新できる
- LOAD MYSQL USERS TO RUNTIME および同様のコマンドを使用して、ここに格納されている設定を、RUNTIMEに反映する
- disk
- 「main」のディスクベースのミラー
- 再起動時、「main」は永続化されず、「disk」データベースまたは設定ファイルからロードされる
- stats
- 収集されたランタイムメトリックが含まれる
- monitor
- ProxySQLが接続するバックエンドサーバーに関連する監視メトリックが含まれる
参考 : The Admin Schemas · sysown/proxysql Wiki · GitHub
主なテーブル
各データベースで複数テーブルがありますが、今回の検証は以下のテーブルがよく使われます。 次の「ProxySQLの設定」を始める前にスキーマの説明をご覧ください。
- main
- monitor
- mysql_server_connect_log
- mysql_server_ping_log
- mysql_server_read_only_log
- mysql_server_replication_lag_log
- stats
ウィキでmonitorスキーマの説明がまだないのでshow create tableでご確認ください。
mysql> show create table monitor.mysql_server_connect_log\G *************************** 1. row *************************** table: mysql_server_connect_log Create Table: CREATE TABLE mysql_server_connect_log ( hostname VARCHAR NOT NULL, port INT NOT NULL DEFAULT 3306, time_start_us INT NOT NULL DEFAULT 0, connect_success_time_us INT DEFAULT 0, connect_error VARCHAR, PRIMARY KEY (hostname, port, time_start_us)) 1 row in set (0.00 sec)
ProxySQLの設定
検証用のAurora準備
事前準備として、検証用のAuroraを作成しました。
- 書き込み用インスタンスエンドポイント(master)
- test-ikorepo-db-cluster-instance-1.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com
- 読み込み用インスタンスエンドポイント(slave)
- test-ikorepo-db-cluster-instance-1-ap-northeast-1c.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com
フェイルオーバーに対応するため、ProxySQLでバックエンドサーバーの設定はクラスターエンドポイントを使用せず、インスタンスエンドポイントを使ってください。
バックエンドサーバーの設定
Admin> show create table mysql_servers\G *************************** 1. row *************************** table: mysql_servers Create Table: CREATE TABLE mysql_servers ( hostgroup_id INT CHECK (hostgroup_id>=0) NOT NULL DEFAULT 0, hostname VARCHAR NOT NULL, port INT CHECK (port >= 0 AND port <= 65535) NOT NULL DEFAULT 3306, gtid_port INT CHECK (gtid_port <> port AND gtid_port >= 0 AND gtid_port <= 65535) NOT NULL DEFAULT 0, status VARCHAR CHECK (UPPER(status) IN ('ONLINE','SHUNNED','OFFLINE_SOFT', 'OFFLINE_HARD')) NOT NULL DEFAULT 'ONLINE', weight INT CHECK (weight >= 0 AND weight <=10000000) NOT NULL DEFAULT 1, compression INT CHECK (compression IN(0,1)) NOT NULL DEFAULT 0, max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 1000, max_replication_lag INT CHECK (max_replication_lag >= 0 AND max_replication_lag <= 126144000) NOT NULL DEFAULT 0, use_ssl INT CHECK (use_ssl IN(0,1)) NOT NULL DEFAULT 0, max_latency_ms INT UNSIGNED CHECK (max_latency_ms>=0) NOT NULL DEFAULT 0, comment VARCHAR NOT NULL DEFAULT '', PRIMARY KEY (hostgroup_id, hostname, port) ) 1 row in set (0.00 sec) Admin> select * from mysql_servers; Empty set (0.00 sec)
この記事の範囲でhostgroup_idは以下となります。
- 1 : 書き込みホストグループ
- 2 : 読み込みホストグループ
# 書き込みホストグループにmasterノードを追加する Admin> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,'test-ikorepo-db-cluster-instance-1.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com',3306); Query OK, 1 row affected (0.00 sec) # 読み込みホストグループにmasterノードとslaveノードの両方を追加する Admin> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (2,'test-ikorepo-db-cluster-instance-1.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com',3306); Query OK, 1 row affected (0.00 sec) Admin> INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (2,'test-ikorepo-db-cluster-instance-1-ap-northeast-1c.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com',3306); Query OK, 1 row affected (0.00 sec) Admin> select * from mysql_servers; +--------------+--------------------------------------------------------------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+--------------------------------------------------------------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 1 | test-ikorepo-db-cluster-instance-1.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 2 | test-ikorepo-db-cluster-instance-1.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 2 | test-ikorepo-db-cluster-instance-1-ap-northeast-1c.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+--------------------------------------------------------------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 3 rows in set (0.00 sec)
登録後、monitorユーザーはアクセスできないエラーログが出てしまいました。バックエンドの監視用のユーザーを設定することが重要です。
proxysql_1 | 2019-09-16 02:27:09 MySQL_Monitor.cpp:700:monitor_connect_thread(): [ERROR] Server test-ikorepo-db-cluster-instance-1.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com:3306 is returning "Access denied" for monitoring user
監視の設定
監視の設定はglobal_variablesテーブルで設定できます。
Admin> select * from global_variables where variable_name like '%monitor%'; +-----------------------------------------------------+----------------+ | variable_name | variable_value | +-----------------------------------------------------+----------------+ | mysql-monitor_enabled | true | | mysql-monitor_connect_timeout | 600 | | mysql-monitor_ping_max_failures | 3 | | mysql-monitor_ping_timeout | 1000 | | mysql-monitor_read_only_max_timeout_count | 3 | | mysql-monitor_replication_lag_interval | 10000 | | mysql-monitor_replication_lag_timeout | 1000 | | mysql-monitor_groupreplication_healthcheck_interval | 5000 | | mysql-monitor_groupreplication_healthcheck_timeout | 800 | | mysql-monitor_galera_healthcheck_interval | 5000 | | mysql-monitor_galera_healthcheck_timeout | 800 | | mysql-monitor_galera_healthcheck_max_timeout_count | 3 | | mysql-monitor_replication_lag_use_percona_heartbeat | | | mysql-monitor_query_interval | 60000 | | mysql-monitor_query_timeout | 100 | | mysql-monitor_slave_lag_when_null | 60 | | mysql-monitor_threads_min | 8 | | mysql-monitor_threads_max | 128 | | mysql-monitor_threads_queue_maxsize | 128 | | mysql-monitor_wait_timeout | true | | mysql-monitor_writer_is_also_reader | true | | mysql-monitor_username | monitor | | mysql-monitor_password | monitor | | mysql-monitor_history | 600000 | | mysql-monitor_connect_interval | 60000 | | mysql-monitor_ping_interval | 10000 | | mysql-monitor_read_only_interval | 1500 | | mysql-monitor_read_only_timeout | 500 | +-----------------------------------------------------+----------------+ 28 rows in set (0.01 sec)
デフォルトの監視用ユーザーとパスワードはmonitorとmonitorです。変更しましょう。 今回、Auroraのユーザーとパスワードを使っています。
Admin> SET mysql-monitor_username='${dbuser}'; Query OK, 1 row affected (0.00 sec) Admin> SET mysql-monitor_password='${dbpassword}'; Query OK, 1 row affected (0.00 sec)
global_variablesテーブルのMySQL Monitorに関連する変更は、「LOAD MYSQL VARIABLES TO RUNTIME」コマンドを実行した後にのみ行われ、「SAVE MYSQL VARIABLES TO DISK」コマンドを実行した後に永続的にディスクに保存されます。
Admin> load mysql variables to runtime; Query OK, 0 rows affected (0.00 sec) Admin> save mysql variables to disk; Query OK, 121 rows affected (0.00 sec)
バックエンドのヘルスチェック
次に、ProxySQLがこれらのホストと通信できるかどうか接続とpingの監視を見てみましょう。
Admin> select * from monitor.mysql_server_connect_log order by time_start_us desc limit 5; +--------------------------------------------------------------------------------------------------+------+------------------+-------------------------+---------------+ | hostname | port | time_start_us | connect_success_time_us | connect_error | +--------------------------------------------------------------------------------------------------+------+------------------+-------------------------+---------------+ | test-ikorepo-db-cluster-instance-1-ap-northeast-1c.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com | 3306 | 1568641840478865 | 284593 | NULL | | test-ikorepo-db-cluster-instance-1.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com | 3306 | 1568641839874743 | 325600 | NULL | | test-ikorepo-db-cluster-instance-1.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com | 3306 | 1568641780630945 | 454056 | NULL | | test-ikorepo-db-cluster-instance-1-ap-northeast-1c.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com | 3306 | 1568641779942122 | 511547 | NULL | | test-ikorepo-db-cluster-instance-1-ap-northeast-1c.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com | 3306 | 1568641720963841 | 313392 | NULL | +--------------------------------------------------------------------------------------------------+------+------------------+-------------------------+---------------+ 5 rows in set (0.00 sec) Admin> select * from monitor.mysql_server_ping_log order by time_start_us desc limit 5; +--------------------------------------------------------------------------------------------------+------+------------------+----------------------+------------+ | hostname | port | time_start_us | ping_success_time_us | ping_error | +--------------------------------------------------------------------------------------------------+------+------------------+----------------------+------------+ | test-ikorepo-db-cluster-instance-1-ap-northeast-1c.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com | 3306 | 1568641920478791 | 9786 | NULL | | test-ikorepo-db-cluster-instance-1.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com | 3306 | 1568641920331803 | 8958 | NULL | | test-ikorepo-db-cluster-instance-1-ap-northeast-1c.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com | 3306 | 1568641910502113 | 10088 | NULL | | test-ikorepo-db-cluster-instance-1.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com | 3306 | 1568641910366108 | 17531 | NULL | | test-ikorepo-db-cluster-instance-1-ap-northeast-1c.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com | 3306 | 1568641900533984 | 11070 | NULL | +--------------------------------------------------------------------------------------------------+------+------------------+----------------------+------------+ 5 rows in set (0.00 sec)
設定されたサーバーはすべてヘルスチェックがOKです。 ここで注意すべき重要な点は、接続とpingの監視が、mysql_serversテーブルの内容に基づいて実行されて、これがRUNTIMEにロードされる前であることです。 このアプローチは意図的なものです。この方法では、本番環境にノードを追加する前に基本的なヘルスチェックを実行できます。
サーバーが正しく監視され、稼働していることがわかったので、それらを有効にしましょう。
Admin> load mysql servers to runtime; Query OK, 0 rows affected (0.00 sec) Admin> select * from mysql_servers; +--------------+--------------------------------------------------------------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+--------------------------------------------------------------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 1 | test-ikorepo-db-cluster-instance-1.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 2 | test-ikorepo-db-cluster-instance-1.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 2 | test-ikorepo-db-cluster-instance-1-ap-northeast-1c.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | +--------------+--------------------------------------------------------------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 3 rows in set (0.00 sec) Admin> select * from stats_mysql_connection_pool; +-----------+--------------------------------------------------------------------------------------------------+----------+--------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+ | hostgroup | srv_host | srv_port | status | ConnUsed | ConnFree | ConnOK | ConnERR | MaxConnUsed | Queries | Queries_GTID_sync | Bytes_data_sent | Bytes_data_recv | Latency_us | +-----------+--------------------------------------------------------------------------------------------------+----------+--------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+ | 1 | test-ikorepo-db-cluster-instance-1.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | 2 | test-ikorepo-db-cluster-instance-1.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | | 2 | test-ikorepo-db-cluster-instance-1-ap-northeast-1c.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | +-----------+--------------------------------------------------------------------------------------------------+----------+--------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+ 3 rows in set (0.00 sec)
MySQL Users
mysql_serversでサーバーを設定した後、mysqlユーザーも設定する必要があります。 これは、mysql_usersテーブルを使用して実行されます。
Admin> show create table mysql_users\G *************************** 1. row *************************** table: mysql_users Create Table: CREATE TABLE mysql_users ( username VARCHAR NOT NULL, password VARCHAR, active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1, use_ssl INT CHECK (use_ssl IN (0,1)) NOT NULL DEFAULT 0, default_hostgroup INT NOT NULL DEFAULT 0, default_schema VARCHAR, schema_locked INT CHECK (schema_locked IN (0,1)) NOT NULL DEFAULT 0, transaction_persistent INT CHECK (transaction_persistent IN (0,1)) NOT NULL DEFAULT 1, fast_forward INT CHECK (fast_forward IN (0,1)) NOT NULL DEFAULT 0, backend INT CHECK (backend IN (0,1)) NOT NULL DEFAULT 1, frontend INT CHECK (frontend IN (0,1)) NOT NULL DEFAULT 1, max_connections INT CHECK (max_connections >=0) NOT NULL DEFAULT 10000, comment VARCHAR NOT NULL DEFAULT '', PRIMARY KEY (username, backend), UNIQUE (username, frontend)) 1 row in set (0.00 sec)
監視用のユーザーと同様に、Auroraのユーザーを使っています。
Admin> INSERT INTO mysql_users(username, password, default_hostgroup) VALUES ('${dbuser}', '${dbpasswd}', 1); Query OK, 1 row affected (0.00 sec)
ほとんどのフィールドはデフォルト値のままにしました。 default_groupはクエリルールの設定で詳しく説明しますが、ちなみに、特定のクエリに一致するクエリルールがない場合に、デフォルトホストグループを使用します。
次に、忘れずmysql usersをRUNTIMEにロードして、DISKに永続化します。
Admin> load mysql users to runtime; Query OK, 0 rows affected (0.00 sec) Admin> save mysql users to disk; Query OK, 0 rows affected (0.01 sec)
これでDockerのホストからProxySQLに接続を確認しましょう。
$ docker ps CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES 33761bdda8b9 proxysql_proxysql "/bin/bash -c '/etc/…" 37 hours ago Up 53 minutes 0.0.0.0:6033->6033/tcp proxysql_proxysql_1 $ mysql -h127.0.0.1 -u${dbuser} -p${dbpasswd} -P6033 -e 'select now(3), @@hostname, @@innodb_read_only' Warning: Using a password on the command line interface can be insecure. +-------------------------+--------------+--------------------+ | now(3) | @@hostname | @@innodb_read_only | +-------------------------+--------------+--------------------+ | 2019-09-17 15:03:20.812 | ip-10-7-3-30 | 0 | +-------------------------+--------------+--------------------+
MYSQL クエリルール
mysql_query_rulesテーブルには多くのフィールドがあり、ProxySQLを通過するトラフィックを制御するための非常に強力な手段です。
Admin> show create table mysql_query_rules\G *************************** 1. row *************************** table: mysql_query_rules Create Table: CREATE TABLE mysql_query_rules ( rule_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 0, username VARCHAR, schemaname VARCHAR, flagIN INT CHECK (flagIN >= 0) NOT NULL DEFAULT 0, client_addr VARCHAR, proxy_addr VARCHAR, proxy_port INT CHECK (proxy_port >= 0 AND proxy_port <= 65535), digest VARCHAR, match_digest VARCHAR, match_pattern VARCHAR, negate_match_pattern INT CHECK (negate_match_pattern IN (0,1)) NOT NULL DEFAULT 0, re_modifiers VARCHAR DEFAULT 'CASELESS', flagOUT INT CHECK (flagOUT >= 0), replace_pattern VARCHAR CHECK(CASE WHEN replace_pattern IS NULL THEN 1 WHEN replace_pattern IS NOT NULL AND match_pattern IS NOT NULL THEN 1 ELSE 0 END), destination_hostgroup INT DEFAULT NULL, cache_ttl INT CHECK(cache_ttl > 0), cache_empty_result INT CHECK (cache_empty_result IN (0,1)) DEFAULT NULL, cache_timeout INT CHECK(cache_timeout >= 0), reconnect INT CHECK (reconnect IN (0,1)) DEFAULT NULL, timeout INT UNSIGNED CHECK (timeout >= 0), retries INT CHECK (retries>=0 AND retries <=1000), delay INT UNSIGNED CHECK (delay >=0), next_query_flagIN INT UNSIGNED, mirror_flagOUT INT UNSIGNED, mirror_hostgroup INT UNSIGNED, error_msg VARCHAR, OK_msg VARCHAR, sticky_conn INT CHECK (sticky_conn IN (0,1)), multiplex INT CHECK (multiplex IN (0,1,2)), gtid_from_hostgroup INT UNSIGNED, log INT CHECK (log IN (0,1)), apply INT CHECK(apply IN (0,1)) NOT NULL DEFAULT 0, comment VARCHAR) 1 row in set (0.01 sec)
では、SELECTのクエリをスレーブに送信し、その他のすべてをマスターに送信できるように設定しましょう。
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); Query OK, 2 rows affected (0.00 sec) Admin> SELECT rule_id,match_digest,destination_hostgroup FROM mysql_query_rules WHERE active = 1 ORDER BY rule_id; +---------+----------------------+-----------------------+ | rule_id | match_digest | destination_hostgroup | +---------+----------------------+-----------------------+ | 1 | ^SELECT.*FOR UPDATE$ | 1 | | 2 | ^SELECT | 2 | +---------+----------------------+-----------------------+ 2 rows in set (0.00 sec)
rule_idの順番により最初のルールが優先にチェックされて、SELECT FOR UPDATEのクエリは書き込みホストグループに送信されます。 SELECT FOR UPDATEのクエリに一致するルールがない場合、次のルールがチェックされます。 SELECTのクエリは読み込みホストグループに送信されます。クエリに一致するルールがない場合、default_hostgroupが適用されます(上記で設定したdefault_hostgroupは1です)。
注意点のは
- クエリルールはrule_id順に処理される
- 設定active = 1のルールのみが処理される
- 正規表現の構文を使用する際、再確認が非常に重要
- apply = 1は、一致する場合、それ以上ルールがチェックされない
Admin> load mysql query rules to runtime; Query OK, 0 rows affected (0.01 sec) Admin> save mysql query rules to disk; Query OK, 0 rows affected (0.01 sec)
第1テスト(DBのベンチマーク、DBサーバーの負荷分散などのテスト)
今回sysbenchを使ってDBのベンチマーク実行します。 sysbenchを使ったことがない方はこちらでご覧ください。
MySQLのベンチマークツール: sysbench の紹介 | スマートスタイル TECH BLOG｜データベース＆クラウドの最新技術情報を配信
まずはprepareでテスト用のテーブルとテスト用データを作成します。
$ sysbench --db-driver=mysql \ > --mysql-user='${dbuser}' \ > --mysql-password='${dbpasswd}' \ > --mysql-host=127.0.0.1 \ > --mysql-port=6033 \ > --mysql-db=test_db \ > oltp_read_write \ > prepare sysbench 1.0.17 (using bundled LuaJIT 2.1.0-beta2) Creating table 'sbtest1'... Inserting 10000 records into 'sbtest1' Creating a secondary index on 'sbtest1'...
次に、ベンチマークを実行します。
$ sysbench --report-interval=5 \ > --threads=4 \ > --time=20 \ > --db-driver=mysql \ > --mysql-user='${dbuser}' \ > --mysql-password='${dbpasswd}' \ > --mysql-host=127.0.0.1 \ > --mysql-port=6033 \ > --mysql-db=test_db \ > oltp_read_write run sysbench 1.0.17 (using bundled LuaJIT 2.1.0-beta2) Running the test with following options: Number of threads: 4 Report intermediate results every 5 second(s) Initializing random number generator from current time Initializing worker threads... Threads started! [ 5s ] thds: 4 tps: 12.79 qps: 262.77 (r/w/o: 185.24/51.16/26.38) lat (ms,95%): 397.39 err/s: 0.00 reconn/s: 0.00 [ 10s ] thds: 4 tps: 16.00 qps: 326.83 (r/w/o: 229.02/65.81/32.00) lat (ms,95%): 287.38 err/s: 0.00 reconn/s: 0.00 [ 15s ] thds: 4 tps: 15.19 qps: 298.43 (r/w/o: 209.08/58.97/30.38) lat (ms,95%): 297.92 err/s: 0.00 reconn/s: 0.00 [ 20s ] thds: 4 tps: 17.41 qps: 345.93 (r/w/o: 241.09/70.03/34.81) lat (ms,95%): 277.21 err/s: 0.00 reconn/s: 0.00 SQL statistics: queries performed: read: 4354 write: 1244 other: 622 total: 6220 transactions: 311 (15.41 per sec.) queries: 6220 (308.15 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.)
stats_mysql_query_digestテーブルでクエリの集計を確認します。 設定した内容とおり、SELECTのクエリは読み込みホストグループに送信されて、その他は書き込みホストグループが適用されました。
Admin> SELECT hostgroup, sum_time, count_star, digest_text FROM stats_mysql_query_digest ORDER BY sum_time DESC; +-----------+---------------------+------------+--------------------------------------------------------------------+ | hostgroup | sum_time | count_star | digest_text | +-----------+---------------------+------------+--------------------------------------------------------------------+ | 1 | 9223372036854775807 | 315 | BEGIN | | 2 | 34059337 | 3114 | SELECT c FROM sbtest1 WHERE id=? | | 1 | 4897722 | 315 | COMMIT | | 2 | 4205295 | 315 | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ? | | 2 | 4128615 | 315 | SELECT c FROM sbtest1 WHERE id BETWEEN ? AND ? ORDER BY c | | 2 | 4096861 | 315 | SELECT DISTINCT c FROM sbtest1 WHERE id BETWEEN ? AND ? ORDER BY c | | 1 | 3771571 | 315 | UPDATE sbtest1 SET k=k+? WHERE id=? | | 1 | 3651740 | 315 | DELETE FROM sbtest1 WHERE id=? | | 1 | 3614642 | 315 | UPDATE sbtest1 SET c=? WHERE id=? | | 2 | 3578745 | 315 | SELECT SUM(k) FROM sbtest1 WHERE id BETWEEN ? AND ? | | 1 | 3492851 | 315 | INSERT INTO sbtest1 (id, k, c, pad) VALUES (?, ?, ?, ?) | +-----------+---------------------+------------+--------------------------------------------------------------------+ 11 rows in set (0.00 sec)
読み込みホストグループは同じweight値でmasterノードとslaveノードを設定しましたですが、実際の結果はどうでしょうか？ stats_mysql_connection_poolテーブルで結果を見ると、両方は接続数とクエリ数はほぼ同じですね。
Admin> select * from stats_mysql_connection_pool; +-----------+--------------------------------------------------------------------------------------------------+----------+--------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+ | hostgroup | srv_host | srv_port | status | ConnUsed | ConnFree | ConnOK | ConnERR | MaxConnUsed | Queries | Queries_GTID_sync | Bytes_data_sent | Bytes_data_recv | Latency_us | +-----------+--------------------------------------------------------------------------------------------------+----------+--------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+ | 1 | test-ikorepo-db-cluster-instance-1.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com | 3306 | ONLINE | 0 | 4 | 4 | 0 | 4 | 1890 | 0 | 132273 | 0 | 11341 | | 2 | test-ikorepo-db-cluster-instance-1.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com | 3306 | ONLINE | 0 | 3 | 3 | 0 | 3 | 2224 | 0 | 58744 | 5923224 | 11341 | | 2 | test-ikorepo-db-cluster-instance-1-ap-northeast-1c.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com | 3306 | ONLINE | 0 | 3 | 3 | 0 | 3 | 2160 | 0 | 57288 | 6228479 | 23272 | +-----------+--------------------------------------------------------------------------------------------------+----------+--------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+ 3 rows in set (0.00 sec)
CloudWatchでもSelect/DML/Commit Throughputを確認してみます。
結果が問題ないですね。読み込む時masterノードとslaveノードのトラフィックが同じことを確認できました。 次はslaveノードをmasterノードよりトラフィックが2倍多くに設定してみます。
Admin> update mysql_servers set weight = 2 where hostgroup_id = 2 and hostname = 'test-ikorepo-db-cluster-instance-1-ap-northeast-1c.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com'; Query OK, 1 row affected (0.00 sec) Admin> select * from mysql_servers; +--------------+--------------------------------------------------------------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+--------------------------------------------------------------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 1 | test-ikorepo-db-cluster-instance-1.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 2 | test-ikorepo-db-cluster-instance-1.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 2 | test-ikorepo-db-cluster-instance-1-ap-northeast-1c.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com | 3306 | 0 | ONLINE | 2 | 0 | 1000 | 0 | 0 | 0 | | +--------------+--------------------------------------------------------------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 3 rows in set (0.00 sec) Admin> load mysql servers to runtime; Query OK, 0 rows affected (0.01 sec)
statsのテーブルをクリアしてから、再度ベンチマークを実行しましょう。結果はこちらです。
Admin> select * from stats_mysql_connection_pool; +-----------+--------------------------------------------------------------------------------------------------+----------+--------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+ | hostgroup | srv_host | srv_port | status | ConnUsed | ConnFree | ConnOK | ConnERR | MaxConnUsed | Queries | Queries_GTID_sync | Bytes_data_sent | Bytes_data_recv | Latency_us | +-----------+--------------------------------------------------------------------------------------------------+----------+--------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+ | 1 | test-ikorepo-db-cluster-instance-1.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com | 3306 | ONLINE | 0 | 4 | 4 | 0 | 4 | 1026 | 0 | 71361 | 0 | 14314 | | 2 | test-ikorepo-db-cluster-instance-1.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com | 3306 | ONLINE | 0 | 2 | 2 | 0 | 2 | 671 | 0 | 17818 | 1799952 | 14314 | | 2 | test-ikorepo-db-cluster-instance-1-ap-northeast-1c.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com | 3306 | ONLINE | 0 | 4 | 4 | 0 | 4 | 1697 | 0 | 45222 | 4725239 | 19491 | +-----------+--------------------------------------------------------------------------------------------------+----------+--------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+ 3 rows in set (0.00 sec)
よおし、これでDBサーバーの負荷分散の確認が完了です。
MySQL replication hostgroups
Auroraはシステム変数のinnodb_read_onlyの値によって、書き込み専用エンドポイントと読み込み専用エンドポイントのロールを判断できます。そのためinnodb_read_onlyを定期的に確認してクエリを書き込み専用エンドポイントにルーティングすることで高速にフェイルオーバーができます。ProxySQLには mysql_replication_hostgroupsテーブルで読み取り専用エンドポイントの自動識別と分離のための設定が含まれています。
Admin> show create table mysql_replication_hostgroups\G *************************** 1. row *************************** table: mysql_replication_hostgroups Create Table: CREATE TABLE mysql_replication_hostgroups ( writer_hostgroup INT CHECK (writer_hostgroup>=0) NOT NULL PRIMARY KEY, reader_hostgroup INT NOT NULL CHECK (reader_hostgroup<>writer_hostgroup AND reader_hostgroup>=0), check_type VARCHAR CHECK (LOWER(check_type) IN ('read_only','innodb_read_only','super_read_only')) NOT NULL DEFAULT 'read_only', comment VARCHAR NOT NULL DEFAULT '', UNIQUE (reader_hostgroup)) 1 row in set (0.00 sec) # デフォルトのcheck_typeはread_onlyですが、Auroraの場合はinnodb_read_onlyを使用してください。 Admin> INSERT INTO mysql_replication_hostgroups VALUES (1,2,'innodb_read_only','aurora-test'); Query OK, 1 row affected (0.00 sec) Admin> select * from mysql_replication_hostgroups; +------------------+------------------+------------------+-------------+ | writer_hostgroup | reader_hostgroup | check_type | comment | +------------------+------------------+------------------+-------------+ | 1 | 2 | innodb_read_only | aurora-test | +------------------+------------------+------------------+-------------+ 1 row in set (0.00 sec)
mysql_serversで各の設定したバックエンドサーバのinnodb_read_onlyの値をチェックして、設定したホストグループが正しいかチェックしてくれます。
| hostname | hostgroup_id | |
|---|---|---|
| master | 1 | 正常 |
| master | 2 | （正常） mysql_serversにmaster-1の設定がなければ、master-1が追加される |
| slave | 1 | （異常） slave-1の設定が不適当なのでmysql_serversから削除されて、新規のslave-2が追加される |
| slave | 2 | 正常 |
設定を有効してからmysql_server_read_only_logのデータを確認します。
Admin> SELECT * FROM monitor.mysql_server_read_only_log ORDER BY time_start_us DESC LIMIT 10; Empty set (0.00 sec) Admin> load mysql servers to runtime; Query OK, 0 rows affected (0.01 sec) Admin> SELECT * FROM monitor.mysql_server_read_only_log ORDER BY time_start_us DESC LIMIT 10; +--------------------------------------------------------------------------------------------------+------+------------------+-----------------+-----------+-------+ | hostname | port | time_start_us | success_time_us | read_only | error | +--------------------------------------------------------------------------------------------------+------+------------------+-----------------+-----------+-------+ | test-ikorepo-db-cluster-instance-1.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com | 3306 | 1568844767468442 | 7935 | 0 | NULL | | test-ikorepo-db-cluster-instance-1-ap-northeast-1c.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com | 3306 | 1568844767442960 | 16219 | 1 | NULL | | test-ikorepo-db-cluster-instance-1-ap-northeast-1c.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com | 3306 | 1568844765963731 | 12476 | 1 | NULL | | test-ikorepo-db-cluster-instance-1.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com | 3306 | 1568844765943039 | 16486 | 0 | NULL | | test-ikorepo-db-cluster-instance-1-ap-northeast-1c.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com | 3306 | 1568844764471497 | 8988 | 1 | NULL | | test-ikorepo-db-cluster-instance-1.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com | 3306 | 1568844764442284 | 8781 | 0 | NULL | | test-ikorepo-db-cluster-instance-1.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com | 3306 | 1568844762968522 | 16112 | 0 | NULL | | test-ikorepo-db-cluster-instance-1-ap-northeast-1c.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com | 3306 | 1568844762942385 | 9608 | 1 | NULL | | test-ikorepo-db-cluster-instance-1-ap-northeast-1c.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com | 3306 | 1568844761467264 | 10395 | 1 | NULL | | test-ikorepo-db-cluster-instance-1.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com | 3306 | 1568844761441101 | 12745 | 0 | NULL | +--------------------------------------------------------------------------------------------------+------+------------------+-----------------+-----------+-------+ 10 rows in set (0.00 sec) Admin> save mysql servers to disk; Query OK, 0 rows affected (0.03 sec)
第2テスト(Auroraフェイルオーバーのテスト)
Auroraの書き込み専用のクラスターエンドの場合
watchで専用のクラスターエンドに接続を確認します。
watch -n 0.5 -d "mysql -htest-ikorepo-db-cluster.cluster-xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com -u${dbuser} -p${dbpasswd} --skip-column-names -e 'select now(3), @@hostname, @@innodb_read_only' 2>&1 | tee -a failover1.log"
次はAWS Consoleでフェイルオーバーを実行しましょう。
# cat failover1.log 2019-09-18 22:32:06.870 ip-10-7-3-30 0 2019-09-18 22:32:07.466 ip-10-7-3-30 0 2019-09-18 22:32:08.072 ip-10-7-3-30 0 2019-09-18 22:32:08.663 ip-10-7-3-30 0 2019-09-18 22:32:09.350 ip-10-7-3-30 0 # ここでフェイルオーバー発生 ERROR 2003 (HY000): Can't connect to MySQL server on 'test-ikorepo-db-cluster.cluster-xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com' (61) ERROR 2003 (HY000): Can't connect to MySQL server on 'test-ikorepo-db-cluster.cluster-xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com' (61) ERROR 2003 (HY000): Can't connect to MySQL server on 'test-ikorepo-db-cluster.cluster-xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com' (61) ERROR 2003 (HY000): Can't connect to MySQL server on 'test-ikorepo-db-cluster.cluster-xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com' (61) ERROR 2003 (HY000): Can't connect to MySQL server on 'test-ikorepo-db-cluster.cluster-xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com' (61) ERROR 2003 (HY000): Can't connect to MySQL server on 'test-ikorepo-db-cluster.cluster-xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com' (61) ERROR 2003 (HY000): Can't connect to MySQL server on 'test-ikorepo-db-cluster.cluster-xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com' (61) ERROR 2003 (HY000): Can't connect to MySQL server on 'test-ikorepo-db-cluster.cluster-xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com' (61) ERROR 2003 (HY000): Can't connect to MySQL server on 'test-ikorepo-db-cluster.cluster-xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com' (61) ERROR 2003 (HY000): Can't connect to MySQL server on 'test-ikorepo-db-cluster.cluster-xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com' (61) ERROR 2003 (HY000): Can't connect to MySQL server on 'test-ikorepo-db-cluster.cluster-xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com' (61) 2019-09-18 22:32:16.225 ip-10-7-3-30 1 2019-09-18 22:32:16.828 ip-10-7-3-30 1 2019-09-18 22:32:17.421 ip-10-7-3-30 1 2019-09-18 22:32:18.019 ip-10-7-3-30 1 2019-09-18 22:32:18.628 ip-10-7-3-30 1 2019-09-18 22:32:19.224 ip-10-7-3-30 1 2019-09-18 22:32:19.895 ip-10-7-3-30 1 2019-09-18 22:32:20.485 ip-10-7-3-30 1 2019-09-18 22:32:21.085 ip-10-7-3-30 1 2019-09-18 22:32:21.668 ip-10-7-3-30 1 2019-09-18 22:32:22.257 ip-10-7-3-30 1 2019-09-18 22:32:22.867 ip-10-7-3-30 1 2019-09-18 22:32:23.577 ip-10-7-3-30 1 2019-09-18 22:32:24.175 ip-10-7-3-30 1 2019-09-18 22:32:24.768 ip-10-7-3-30 1 2019-09-18 22:32:25.375 ip-10-7-3-30 1 2019-09-18 22:32:25.964 ip-10-7-3-30 1 2019-09-18 22:32:26.563 ip-10-7-3-30 1 2019-09-18 22:32:27.169 ip-10-7-3-30 1 2019-09-18 22:32:27.762 ip-10-7-3-30 1 2019-09-18 22:32:28.373 ip-10-7-3-30 1 2019-09-18 22:32:28.978 ip-10-7-3-30 1 2019-09-18 22:32:29.593 ip-10-7-3-30 1 2019-09-18 22:32:30.516 ip-10-7-2-194 0 2019-09-18 22:32:31.110 ip-10-7-2-194 0 2019-09-18 22:32:31.711 ip-10-7-2-194 0 2019-09-18 22:32:32.310 ip-10-7-2-194 0 2019-09-18 22:32:32.895 ip-10-7-2-194 0
結果により書き込み専用のクラスターエンドポイントは切り替えまでも時間が少し遅く、切替時に読み込みエンドポイントへアクセスしてしまうことがあります。
ProxySQLの場合
テスト前の状況は
Admin> select * from mysql_servers; +--------------+--------------------------------------------------------------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+--------------------------------------------------------------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 1 | test-ikorepo-db-cluster-instance-1.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 2 | test-ikorepo-db-cluster-instance-1.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 2 | test-ikorepo-db-cluster-instance-1-ap-northeast-1c.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com | 3306 | 0 | ONLINE | 2 | 0 | 1000 | 0 | 0 | 0 | | +--------------+--------------------------------------------------------------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ Admin> select * from stats_mysql_connection_pool; +-----------+--------------------------------------------------------------------------------------------------+----------+--------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+ | hostgroup | srv_host | srv_port | status | ConnUsed | ConnFree | ConnOK | ConnERR | MaxConnUsed | Queries | Queries_GTID_sync | Bytes_data_sent | Bytes_data_recv | Latency_us | +-----------+--------------------------------------------------------------------------------------------------+----------+--------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+ | 1 | test-ikorepo-db-cluster-instance-1.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 13461 | | 2 | test-ikorepo-db-cluster-instance-1.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 13461 | | 2 | test-ikorepo-db-cluster-instance-1-ap-northeast-1c.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 12908 | +-----------+--------------------------------------------------------------------------------------------------+----------+--------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+
現在、クエリルールの設定で「select now(3), @@hostname, @@innodb_read_only'」で確認すると両方masterノードとslaveノードを向いているので確認やすくため、masterノードのみに向くようにクエリルールを調整しましょう。
Admin> SELECT rule_id, match_digest, destination_hostgroup FROM mysql_query_rules; +---------+----------------------+-----------------------+ | rule_id | match_digest | destination_hostgroup | +---------+----------------------+-----------------------+ | 1 | ^SELECT.*FOR UPDATE$ | 1 | | 2 | ^SELECT | 2 | +---------+----------------------+-----------------------+ 2 rows in set (0.00 sec) Admin> DELETE FROM mysql_query_rules WHERE rule_id in (1,2); Query OK, 2 rows affected (0.00 sec) Admin> INSERT INTO mysql_query_rules (rule_id,active,match_digest,destination_hostgroup,apply) VALUES (1,1,'^SELECT.*innodb_read_only$',1,1), (2,1,'^SELECT.*FOR UPDATE$',1,1), (3,1,'^SELECT',2,1); Query OK, 3 rows affected (0.00 sec) Admin> SELECT rule_id, match_digest, destination_hostgroup FROM mysql_query_rules; +---------+----------------------------+-----------------------+ | rule_id | match_digest | destination_hostgroup | +---------+----------------------------+-----------------------+ | 1 | ^SELECT.*innodb_read_only$ | 1 | | 2 | ^SELECT.*FOR UPDATE$ | 1 | | 3 | ^SELECT | 2 | +---------+----------------------------+-----------------------+ 3 rows in set (0.00 sec) Admin> load mysql query rules to runtime; Query OK, 0 rows affected (0.00 sec)
これでselect @@innodb_read_onlyのクエリは必ずmasterノードのみを使っています。
watchコマンドを実行します。
watch -n 0.5 -d "mysql -h127.0.0.1 -u${dbuser} -p${dbpasswd} -P6033 --skip-column-names -e 'select now(3), @@hostname, @@innodb_read_only' 2>&1 | tee -a failover2.log"
AWS Consoleでフェイルオーバーを実行します。
# cat failover2.log 2019-09-19 00:48:15.379 ip-10-7-3-30 0 2019-09-19 00:48:15.926 ip-10-7-3-30 0 2019-09-19 00:48:16.476 ip-10-7-3-30 0 2019-09-19 00:48:17.023 ip-10-7-3-30 0 2019-09-19 00:48:17.572 ip-10-7-3-30 0 # ここでフェイルオーバー発生 2019-09-19 00:48:18.154 ip-10-7-2-194 0 2019-09-19 00:48:18.699 ip-10-7-2-194 0 2019-09-19 00:48:19.237 ip-10-7-2-194 0 2019-09-19 00:48:19.805 ip-10-7-2-194 0 2019-09-19 00:48:20.352 ip-10-7-2-194 0 2019-09-19 00:48:20.898 ip-10-7-2-194 0
素晴らしいですね、エラーが発生なし、すぐに切り替えることを確認できました。
フェイルオーバー後のmysql_serversを確認します。
Admin> select * from mysql_servers; +--------------+--------------------------------------------------------------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+--------------------------------------------------------------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | 2 | test-ikorepo-db-cluster-instance-1-ap-northeast-1c.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com | 3306 | 0 | ONLINE | 2 | 0 | 1000 | 0 | 0 | 0 | | | 2 | test-ikorepo-db-cluster-instance-1.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com | 3306 | 0 | ONLINE | 1 | 0 | 1000 | 0 | 0 | 0 | | | 1 | test-ikorepo-db-cluster-instance-1-ap-northeast-1c.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com | 3306 | 0 | ONLINE | 2 | 0 | 1000 | 0 | 0 | 0 | | +--------------+--------------------------------------------------------------------------------------------------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 3 rows in set (0.00 sec) Admin> select * from stats_mysql_connection_pool; +-----------+--------------------------------------------------------------------------------------------------+----------+--------------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+ | hostgroup | srv_host | srv_port | status | ConnUsed | ConnFree | ConnOK | ConnERR | MaxConnUsed | Queries | Queries_GTID_sync | Bytes_data_sent | Bytes_data_recv | Latency_us | +-----------+--------------------------------------------------------------------------------------------------+----------+--------------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+ | 1 | test-ikorepo-db-cluster-instance-1.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com | 3306 | OFFLINE_HARD | 0 | 0 | 1 | 2 | 1 | 81 | 0 | 3645 | 3440 | 19365 | | 1 | test-ikorepo-db-cluster-instance-1-ap-northeast-1c.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com | 3306 | ONLINE | 0 | 1 | 1 | 0 | 1 | 17 | 0 | 765 | 748 | 14232 | | 2 | test-ikorepo-db-cluster-instance-1.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 19365 | | 2 | test-ikorepo-db-cluster-instance-1-ap-northeast-1c.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com | 3306 | ONLINE | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 14232 | +-----------+--------------------------------------------------------------------------------------------------+----------+--------------+----------+----------+--------+---------+-------------+---------+-------------------+-----------------+-----------------+------------+ 4 rows in set (0.00 sec)
「MySQL replication hostgroups」で説明したとおりに実際動きはこちらです。
- フェイルオーバー前の「master-1」はフェイルオーバー後、「slave-1」になってしまうのでmysql_serversから削除された
- フェイルオーバー前の「slave-2」はフェイルオーバー後、「master-2」になって、「master-1」がないので新規「master-1」が追加された
最後に
いかがでしょうか。ProxySQLでキャッシュなど他の機能がありますよ。実験してみてください。 さて、アクトインディではエンジニアを募集していますね、ぜひご応募してください。 actindi.net
