2018.01.10
MySQLの冗長化を試す！～Percona XtraDB Cluster & ProxySQL & Replication～
こんにちは。次世代システム研究室のデータベース・NoSQL・Hadoop 関連を担当している M.K. です。
MySQL の更新可能なマスタ DB をどのように冗長化するか未だに頭を悩ますことが多いですが、今回ずっと気になっていた、Percona XtraDB Cluster (以下 PXC) & ProxySQL の組み合わせを試してみました。
他にも最近 InnoDB Cluster というのも出てきましたが、今回は先ず PXC 。
PXC は MySQL と Galera Cluster の組み合わせの一つで、その Percona 版です。
有名な冗長化ツールに MHA For MySQL がありますが、MHA と PXC は仕組みが全く異なります。
MHA は MySQL の非同期のレプリケーション構成を前提にして、マスター DB が倒れたときにスレーブ DB を一つ選んで自動的に昇格するという仕組みで、VIP を付け替えたりなどは自前で組み込んでやる必要があります。
MHA をより同期に近い形にするために、Semi-Sync Replication (準同期レプリケーション)を併用することもできます。
一方、PXC および Galera Cluster はマルチマスタの同期レプリケーションで、更新するときにノード間で通信して一貫性を担保します。
この PXC に ProxySQL という ロードバランサー を組み合わせると、 ProxySQL の設定でうまく PXC をフェイルオーバーしてくれるため、比較的簡易にフェイルオーバーの仕組みを構築できます。
今回検証したこと
今回はただ PXC & ProxySQL を構築するだけではなく、連続的な更新処理を行っているときに更新ノードを停止してもきちんと処理が続くか、生き残ったノード間でデータ不整合はないか、を見てみる検証を行いました。
サーバー構成も、PXC のクラスタに通常のレプリケーションを張るようにしました。
PXC & ProxySQL はシングルプライマリモードの構成にしています。どのノードも更新 OK にするマルチプライマリモードの場合、かなりクセが強くなります。
同じレコードを別々のノードから更新をかけると、PXC は先勝ちで、後からの更新はエラー（PXC ではデッドロックエラー）になるため、アプリケーション側で制御が必要になります。
それでは簡単に冗長化とはならなくなるので、シングルプライマリモードにしました。
公式のオススメもシングルプライマリモードになっています。
連続的な更新処理をするのには、付属ツールの mysqlslap を使いました。mysqlslap を使って INSERT 文を連続で実行している間、更新ノードを停止してうまくフェイルオーバーするかの検証です。
mysqlslap がエラーなく動き続けるか、残りの PXC ノードおよびスレーブ DB 間でデータに不整合がなかったかをチェックします。
PXC 構築
ということで、先ず PXC を構築してみます。
サーバースペック
検証環境は毎度お馴染みの GMO アプリクラウドのサーバーを使いました。スペックは以下です。
- OS : CentOS 7
- 仮想 CPU : 2
- メモリ容量 : 4GB
- ディスク容量 : 80GB
参考までに、GMOアプリクラウドの技術を応用した Z.com クラウドもあります
このスペックのサーバーを３台使って、PXC のクラスタを構成します。
PXC のインストール
Percona の Redhat 系インストール手順 を参考にしました。
まず、Percona 用の yum レポジトリのインストールを行います。libaio も入ってなければインストールします。
yum install -y http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm yum install -y libaio
今回は PXC のフルバージョンをインストールしてみました。
yum install -y Percona-XtraDB-Cluster-full-57
インストールすると、起動スクリプトが以下のように作られます。ここを覚えておきます。
# 起動スクリプトの場所 /usr/lib/systemd/system/mysql.service /etc/systemd/system/mysqld.service -> /usr/lib/systemd/system/mysql.service /etc/systemd/system/multi-user.target.wants/mysql.service -> /usr/lib/systemd/system/mysql.service
実践的な OS 周りのセットアップ
ここは必要がなければ読み飛ばしてもらっても構いません。本番運用するのにやることが多いであろうセットアップです。
スワップ無効化
echo ' vm.swappiness = 0 ' > /etc/sysctl.d/pxc_os_setting.conf sysctl --system
transparent_hugepage 無効化
transparent_hugepage 無効化をするにあたり、今回は起動スクリプトから呼ばれるスクリプトを修正するやり方にしました。
/usr/lib/systemd/system/mysql.service の中身を見ると、
ExecStartPre=/usr/bin/mysql-systemd start-pre
という箇所で、起動前に必ず、mysql-systemd スクリプトが呼ばれることがわかります。
この mysql-systemd スクリプトに transparent_hugepage 無効化をしかけてみました。
/usr/bin/mysql-systemd をエディターで開き、
fix_thp_setting() {
echo 'never' > /sys/kernel/mm/transparent_hugepage/defrag
echo 'never' > /sys/kernel/mm/transparent_hugepage/enabled
といぅ関数を追記し、以下のように “start-pre” モードだったときのコマンドを修正します。
case $action in
## "start-pre") check_running; install_db ;;
"start-pre") check_running; fix_thp_setting; install_db ;;
オープンファイル制限数とプロセス・スレッド制限数を増やす
/usr/lib/systemd/system/mysql.service をエディターで開き、
[Service] セクションに下記の LimitNOFILE と LimitNPROC の設定を追記します。
[Service] # Sets open_files_limit LimitNOFILE=1006500 LimitNPROC=1006500
設定をリロードします。
systemctl daemon-reload
my.cnf の設定
今回のインストール方法では、MySQL の設定ファイルの my.cnf は、お馴染みの /etc 直下に作成されます。
ただ、/etc/my.cnf の中身は他の場所の設定ファイルをインクルードするだけです。
そこで今回はインクルード対象のディレクトリに作成された cnf ファイルを新しいものに全て置き換えます。
cd /etc/percona-xtradb-cluster.conf.d/ rm -f mysqld.cnf mysqld_safe.cnf wsrep.cnf
今回の検証用に pxc.cnf という名前で、以下のような設定内容にして /etc/percona-xtradb-cluster.conf.d の直下に配置しました。
[mysqld_safe] と [mysqld] のセクション以外は記述してませんので、必要に応じて追記してください。
[mysqld] セクションについては PXC に関わらない項目も実践でよく使いそうなものを設定しました。
[mysqld_safe] socket=/var/data/pxc/mysql.sock log-error=/var/log/mysqld.log nice=0 [mysqld] port=3306 user=mysql datadir=/var/data/pxc tmpdir=/var/data/tmp socket=/var/data/pxc/mysql.sock #----------------------------- # wsrep #----------------------------- wsrep_provider=/usr/lib64/galera3/libgalera_smm.so wsrep_cluster_name=pxc-test wsrep_cluster_address=gcomm://xx.xx.xx.10,xx.xx.xx.20,xx.xx.xx.30 wsrep_sst_method=xtrabackup-v2 wsrep_sst_auth=sstuser:***** wsrep_slave_threads=2 wsrep_log_conflicts wsrep_node_name=pxc1 wsrep_node_address=xx.xx.xx.10 server-id=1 #----------------------------- # レプリケーション関連 #----------------------------- gtid_mode=OFF log-slave-updates #----------------------------- # 機能制御関連 #----------------------------- pxc_strict_mode=ENFORCING # For PXC default_storage_engine=InnoDB # For PXC skip-external-locking skip-name-resolve symbolic-links=0 explicit_defaults_for_timestamp=1 secure_file_priv=/var/data/tmp #----------------------------- # バイナリログ関連 #----------------------------- binlog_format=ROW # For PXC log_timestamps=SYSTEM log-bin=mysql-bin binlog_cache_size=1M sync_binlog=1 #----------------------------- # バッファ/キャッシュ関連 #----------------------------- query_cache_type=OFF sort_buffer_size=8M read_rnd_buffer_size=16M read_buffer_size=2M join_buffer_size=8M max_allowed_packet=256M #----------------------------- # テンポラリ関連 #----------------------------- max_heap_table_size=32M tmp_table_size32M #----------------------------- # キャラクタセット関連 #----------------------------- character-set-server=utf8mb4 collation-server=utf8mb4_general_ci skip-character-set-client-handshake #----------------------------- # トランザクション関連 #----------------------------- transaction-isolation=READ-COMMITTED #----------------------------- # InnoDB関連 #----------------------------- innodb_autoinc_lock_mode=2 # For PXC innodb_file_per_table innodb_data_file_path=ibdata1:2G:autoextend innodb_autoextend_increment=32 innodb_buffer_pool_size=2G innodb_buffer_pool_dump_at_shutdown=ON innodb_buffer_pool_load_at_startup=OFF innodb_log_files_in_group=2 innodb_log_file_size=256M innodb_log_buffer_size=16M innodb_flush_log_at_trx_commit=1 innodb_support_xa=1 innodb_flush_method=O_DIRECT innodb_read_io_threads = 2 innodb_write_io_threads = 4
wsrep ～から始まるパラメータが、PXC 用（Galera Cluster 用）の設定です。
※上記の「*****」と「xx.xx.xx.10」「xx.xx.xx.20」「xx.xx.xx.30」の箇所には実際の値を記入してください。wsrep_cluster_name、wsrep_node_name は任意の名前を。
また、上記で「# For PXC 」というコメントを入れている項目も、PXC のための設定になります。
トランザクション分離レベルに READ-COMMITTED を使う場合や、PXC クラスタを構築する場合、どちらも binlog_format=ROW （行ベースロギング）が必須ということもあり、READ-COMMITTED を今後試したいこともあって、今回はその設定を試しました。
二つ目・三つ目の PXC ノードの my.cnf
二つ目・三つ目の PXC ノードのものは、上記の内容と以下の項目だけ変えて配置します。
wsrep_node_name=pxc2 wsrep_node_address=xx.xx.xx.20 server-id = 2
wsrep_node_name=pxc3 wsrep_node_address=xx.xx.xx.30 server-id = 3
DB のイニシャライズ
編集したばかりの cnf ファイルですが、初期構築のため cnf ファイルの一部の内容を書き換えます。
３台のノードで、/etc/percona-xtradb-cluster.conf.d/pxc.cnf をエディターで開き、以下のような修正またはコメントアウトをします。
wsrep_cluster_address=gcomm:// # wsrep_node_address=xx.xx.xx.XX # comment out # wsrep_sst_auth=sstuser:***** # comment out
そして、mysqld –initialize を１ノードずつ実行します。
/usr/sbin/mysqld --initialize-insecure --datadir=/var/data/pxc --user=mysql
※何故かmysqld –initializeで、ログに root ユーザーの temporary password が書かれない事象に当たってしまって、今回の検証では結局、–initialize-insecure で実行しました。
mysqld を起動し root ユーザーのパスワード変更と sst ユーザーの作成を実施
mysqld を起動、root ユーザーのパスワードをきちんと変更して、PXC のノード間の同期のために必要な sst ユーザーを作成します。３ノードとも行います。
systemctl start mysql
起動したら、mysql クライアントで接続し、以下の SQL コマンドを実施。パスワードは適切なものにしてください。
上記の cnf ファイルの wsrep_sst_auth には<ユーザー名>:<パスワード>の形で設定することになりますが、必ず内容は合わせてください。
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '**********'; mysql> CREATE USER 'sstuser'@'localhost' IDENTIFIED BY '*****'; mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost'; mysql> FLUSH PRIVILEGES;
その後、mysqld を再び停止します。
systemctl stop mysql
プライマリノードを起動
PXC クラスタにおいてプライマリノードとなるノード（今回は pxc1）を最初に起動します。
最初の起動は、先程の通常の mysqld 起動とは異なるので注意が必要です。
pxc1 ノードだけ、systemctl start [email protected] というコマンドを実行します。
pxc1 ノードで /etc/percona-xtradb-cluster.conf.d/pxc.cnf をエディターで開き、wsrep_sst_auth の内容を確認しアンコメントします。
wsrep_sst_auth=sstuser:*****
大きな注意点として、wsrep_cluster_address=gcomm:// は書き戻さずこのままにしておいてください。
それから [email protected] で起動します。
systemctl start [email protected]
残りのノードを起動
残りのノードでは、通常の mysqld のように起動します。
/etc/percona-xtradb-cluster.conf.d/pxc.cnf をエディターで開き、wsrep_cluster_address の書き戻しと、wsrep_node_address、wsrep_sst_auth をアンコメントします。
pxc2 ノード：
wsrep_cluster_address=gcomm://xx.xx.xx.10,xx.xx.xx.20,xx.xx.xx.30 wsrep_node_address=xx.xx.xx.20 wsrep_sst_auth=sstuser:*****
pxc3 ノード：
wsrep_cluster_address=gcomm://xx.xx.xx.10,xx.xx.xx.20,xx.xx.xx.30 wsrep_node_address=xx.xx.xx.30 wsrep_sst_auth=sstuser:*****
その後、各ノードで mysqld を起動すると、PXC クラスタにノードが自動で追加されます。
systemctl start mysql
無事に全ノードが起動したら、PXC クラスタとして上手く動くかどうか確認することをオススメします。
※参考：PXC クラスタの動作確認
プライマリノードの cnf ファイルを修正
プライマリノードだけ [email protected] で起動しましたが、これは最初の一回で良いので、次回から通常の起動コマンドを利用します。
そのときのために cnf ファイルを修正しておきます。
pxc1 ノード：
wsrep_cluster_address=gcomm://xx.xx.xx.10,xx.xx.xx.20,xx.xx.xx.30 wsrep_node_address=xx.xx.xx.10 wsrep_sst_auth=sstuser:*****
ProxySQL 構築
続いて、ProxySQL を構築します。今回は１台を構築します。
Percona の ProxySQL 設定手順を参考にしました。
ProxySQL のインストール
前述の PXC ノード構築と同じサーバースペックでもう１台ノードを追加し、Percona 用の yum レポジトリと libaio をインストールしておきます。
準備が整ったら、ProxySQL と mysql クライアントをインストールします。
yum -y install proxysql yum -y install mysql-client
PXC 上に ProxySQL 用のユーザーを作成
Percona 用の yum レポジトリから ProxySQL をインストールすると、付属の管理ツールの proxysql-admin がついてきます。
proxysql-admin は PXC を色々操作するため、そのための管理ユーザーと、PXC の各ノードの状態を確認する監視ユーザー、ProxySQL を通して実際に処理を行うアプリケーション用ユーザーを予め作成します。
名前は何でも良いです。今回はそれぞれ、pxc_admin、pxc_monitor、app_user としました。
権限は注意が必要で、監視ユーザーは USAGE のみで OK です。アプリケーション用ユーザーは実運用を考えると、本当は特定のデータベース限定が良いのですが、今回の検証で使う mysqlslap の都合で限定していません。
どれか一つの PXC ノードから以下の SQL を実施。
mysql> -- proxysql-admin 用の管理ユーザー mysql> CREATE USER pxc_admin@'xx.xx.xx.%' IDENTIFIED BY '*****'; mysql> GRANT ALL ON *.* TO pxc_admin@'xx.xx.xx.%'; mysql> -- 監視ユーザー mysql> CREATE USER pxc_monitor@'xx.xx.xx.%' IDENTIFIED BY '*****'; mysql> GRANT USAGE ON *.* TO pxc_monitor@'xx.xx.xx.%'; mysql> -- アプリケーション用ユーザー mysql> CREATE USER app_user@'xx.xx.xx.%' IDENTIFIED BY '*****'; mysql> GRANT ALL ON *.* TO app_user@'xx.xx.xx.%'; mysql> FLUSH PRIVILEGES;
ProxySQL の起動
まず、ProxySQL の設定ファイル /etc/proxysql.cnf をエディターで開き、admin_credentials の項目だけ編集します。
これは PXC ではなく ProxySQL の管理ユーザーの認証設定です。ProxySQL ノードで実施します。
この管理ユーザーは今回 proxysql_admin としました。
ProxySQL ノード：
admin_credentials="proxysql_admin:********"
admin_credentials を編集したら、ProxySQL をまず起動します。
systemctl start proxysql
ProxySQL 設定の補足
ProxySQL の設定内容はいつも /etc/proxysql.cnf ファイルを編集というわけではありません。
ProxySQL は管理ユーザー（今回は proxysql-admin）で ProxySQL に入り、SQL で設定を変更することができます。
その後一度でも保存すれば、ローカルの SQLite データベースに内容が保存されます（MySQLでないところがミソ・・）。
SQLite に保存されたあとは、実は /etc/proxysql.cnf ファイルをもう見ることはありません。
このあたりのことは、Multi layer configuration system の記事に書かれていますので参考にしてみてください。
ProxySQL のセットアップ
起動したら、proxysql-admin を利用して ProxySQL をセットアップします。
ProxySQL ノードで /etc/proxysql-admin.cnf をエディターで開き、以下のように設定します。
# proxysql admin interface credentials. export PROXYSQL_USERNAME="proxysql_admin" export PROXYSQL_PASSWORD="********" export PROXYSQL_HOSTNAME="localhost" export PROXYSQL_PORT="6032" # PXC admin credentials for connecting to pxc-cluster-node. export CLUSTER_USERNAME="pxc_admin" export CLUSTER_PASSWORD="*****" export CLUSTER_HOSTNAME="xx.xx.xx.10" export CLUSTER_PORT="3306" # proxysql monitoring user. proxysql admin script will create this user in pxc to monitor pxc-nodes. export MONITOR_USERNAME="pxc_monitor" export MONITOR_PASSWORD="*****" # Application user to connect to pxc-node through proxysql export CLUSTER_APP_USERNAME="app_user" export CLUSTER_APP_PASSWORD="*****" # ProxySQL read/write hostgroup export WRITE_HOSTGROUP_ID="10" export READ_HOSTGROUP_ID="11" # ProxySQL read/write configuration mode. export MODE="singlewrite" # ProxySQL Cluster Node Priority File export HOST_PRIORITY_FILE="/var/lib/proxysql/host_priority.conf"
ProxySQL の管理ユーザーと proxysql-admin 用の管理ユーザーを混同しないように注意してください。
proxysql-admin の設定ができたら、以下のコマンドでセットアップします。
proxysql-admin --config-file=/etc/proxysql-admin.cnf --enable
これで、シングルプライマリモードとして動作するための ProxySQL セットアップが裏で行われます。
proxysql-admin によるセットアップの補足
まず、openssl のバージョンが古かったりすると、以下のエラーで怒られます。
ERROR 2026 (HY000): SSL connection error: SSL_CTX_set_tmp_dh failed
その場合は、以下のように openssl の最新のバージョンをインストールします。
yum install openssl yum install openssl-devel
それから、proxysql-admin ツールは上記 conf ファイルに設定した pxc_monitor と app_user を PXC 上に自動で作ってしまいます。
残念なことに、先に作っておいた pxc_monitor@’xx.xx.xx.%’ と app_user@’xx.xx.xx.%’ を無視して、pxc_monitor@’xx.%’ と app_user@’xx.%’ で作っちゃいます。
ホストが違うと別ユーザー扱いなので、二重にできてしまってイケてないことになりました。
なので、ホストが ‘xx.%’ の方は DROP USER で落としました。
通常のレプリケーション構築
Percona Server のインストール
ProxySQL のセットアップが済んだら、今回はさらに通常のレプリケーションのスレーブ DB を構築します。
注意点として、今の機能では、PXC クラスタ全体を一つのマスター DB のように扱ってレプリケーションを張ることができません。
そこで今回は、pxc2 ノードと pxc3 ノードにそれぞれ個別にレプリケーションを張ることを試しました。
そうしたこともあって、pxc.cnf ファイルに log-slave-updates を設定しています。
まず、前述のサーバースペックと同じサーバーを追加で２台建てました。
はじめに、Percona 用の yum レポジトリと libaio をインストールしておきます。
次にスレーブ用の２台のサーバーでそれぞれ以下をインストールします。今回 PXC は MySQL 5.7 系なので、Percona Server も 5.7系に合わせます。
yum install -y Percona-Server-server-57
それから、スレーブ用の２台のサーバーも PXC ノードと同様に、OS 周りのセットアップを実施します。
OSのセットアップ周りは、何故か mysqld 起動スクリプトが PXC 用と名前が微妙に違ったり、中身がちょっと異なっていたりしたので、前回書いたブログの「OSのセットアップ周り」の章と同じように実施します。
レプリケーション用ユーザーの作成
レプリケーションのための PXC 上のユーザーを作成します。今回は repl_user としました。
どれか一つの PXC ノードから以下の SQL を実施。
mysql> -- レプリケーション用ユーザー mysql> CREATE USER repl_user@'xx.xx.xx.%' IDENTIFIED BY '*****'; mysql> GRANT REPLICATION SLAVE ON *.* TO repl_user@'xx.xx.xx.%'; mysql> FLUSH PRIVILEGES;
スレーブ DB 用の my.cnf
PXC インストールのときと同じく、/etc/my.cnf の中身は他の場所の設定ファイルをインクルードするだけです。
PXC と同じようにインクルード対象のディレクトリに作成された cnf ファイルを新しいものに全て置き換えます。
cd /etc/percona-server.conf.d rm -f mysqld.cnf mysqld_safe.cnf
既に PXC ノード用の pxc.cnf ファイルを作ったので、これを流用して スレーブDB 用の cnf ファイルを作成します。
pxc.cnf ファイルの内容から、wsrep ～から始まるパラメータの項目と、pxc_strict_mode の項目を除外します。
次に server-id を 別の被らない番号に書き換えます。今回は、スレーブ DB 1 を server-id=11、スレーブ DB 2 を server-id=12 にしました。
それから以下のスレーブ DB 用の項目を追記して、/etc/percona-server.conf.d 直下に slave.cnf として保存します。
スレーブ DB1 ：
read_only=ON relay_log_info_repository=TABLE relay_log_recovery=ON
上記はクラッシュセーフなスレーブ DB を作るときの項目も入れています。こちらのこちらの記事が参考になります。
スレーブ DB のイニシャライズと起動
以下を実施します。
/usr/sbin/mysqld --initialize-insecure --datadir=/var/data/pxc --user=mysql systemctl start mysql
PXC からのデータ移行とレプリケーション開始
今回は pxc2 ノードとスレーブ DB 1、pxc3 ノードとスレーブ DB 2 でレプリケーションするので、対応する pxc から mysqldump を使ってデータ移行します。
pxc1 ノード：
mysqldump --all-databases --single-transaction --master-data=2 --default-character-set=utf8mb4 -uroot -p > /var/data/tmp/data_pxc.dmp
スレーブ DB1 ：
mysql -uroot < /var/data/tmp/data_pxc.dmp # CHANGE MASTER 文の取り出し grep '\-\- CHANGE MASTER TO MASTER_LOG_FILE' data_pxc.dmp
上記で取り出した内容を以下の MASTER_LOG_FILE と MASTER_LOG_POS に反映し、CHANGE MASTER 文をスレーブ DB1 で実行します。
mysql> CHANGE MASTER TO MASTER_HOST='xx.xx.xx.20', MASTER_USER='repl_user', MASTER_PASSWORD='*****', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.xxxxxx', MASTER_LOG_POS=xxxxx, MASTER_CONNECT_RETRY=10; mysql> START SLAVE;
上記の作業を スレーブ DB2 でも同じように実施します。
構築でハマった幾つかのこと
今まで構築について書いてきましたが、実はすんなりいかず、ところどころハマってかなり手間取りました。。
大事な点がたくさんあるので整理しようと思います。
ProxySQL 経由で更新処理ができない
一番ハマったのはこれです。このようなエラーが出て フェイルオーバーどころか、ProxySQL から一件の更新処理もできなくて悩みました。
ERROR 9001 (HY000) at line 1: Max connect timeout reached while reaching hostgroup 10 after 10698ms
結論を言うと、２つの原因があって、一つは pxc.cnf ファイルで collation-server=utf8mb4_unicode_520_ci を設定していたこと、もう一つは前述の proxysql-admin ツールを使った ProxySQL のセットアップでは何故か監視ユーザーがうまく登録できていなかったことでした。
ProxySQLは utf8mb4_general_ci の照合順序のみサポート
今回、MySQL 8.0系でこれからデフォルトになる utf8mb4 キャラクタセットにし、知る人ぞ知る「寿司=ビール問題」（詳しくはこちらの記事がとても参考になります）を回避するため、当初は collation-server に utf8mb4_unicode_520_ci を設定していたんですが、これがいけませんでした。
ProxySQL は utf8mb4_general_ciの照合順序のみしかサポートしていませんでした。。
proxysql-admin によるセットアップで監視ユーザーがうまく登録されない
正確には PXC 上には ユーザーができていましたが、ProxySQL 上に登録がありませんでした。
結局、監視ユーザーだけは proxysql-admin ツールで解決するのはやめて、直接 ProxySQL とそのデータベースを更新しました。
まず、PXC ではなく ProxySQL に入ります。mysql クライアントを使っているのでややこしいですが、ProxySQL（ポート：6032）に入っています。
ProxySQL ノード：
mysql -u proxysql_admin -p -h'localhost' -P6032 --protocol=tcp --prompt='ProxySQL> '
パスワードは /etc/proxysql.cnf の admin_credentials や /etc/proxysql-admin.cnf の PROXYSQL_PASSWORD で設定したパスワードです。
ProxySQL による PXC の監視がうまくいっているかどうかは以下のような SQL で確認できます。新しい監視ログが増えていなければ上手く監視できていないことがわかります。
ProxySQL> SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 12;
監視ユーザーの登録のため、直接 ProxySQL に UPDATE して RUNTIME にロードし、その内容をローカルの SQLite データベースに保存。
ProxySQL> -- ProxySQL の監視ユーザー項目の設定内容を確認 ProxySQL> SELECT * FROM global_variables WHERE variable_name LIKE 'mysql-monitor_%'; ProxySQL> -- ProxySQL の監視ユーザーの設定を登録 ProxySQL> UPDATE global_variables SET variable_value='pxc_monitor' WHERE variable_name = 'mysql-monitor_username'; ProxySQL> UPDATE global_variables SET variable_value='*****' WHERE variable_name = 'mysql-monitor_password'; ProxySQL> LOAD MYSQL VARIABLES TO RUNTIME; ProxySQL> SAVE MYSQL VARIABLES TO DISK;
マルチスレッドスレーブ（MTS = Multi-Threaded Slave）で GRANT 文がエラー
PXC のノードに対して通常のレプリケーションも張りましたが、実は当初、最新の 5.7 系ということもあり注目のマルチスレッドスレーブを使ってみました。
しかしこの試みも、GRANT 文で PXC ノード間は問題ないものの、何故かスレーブ DB に伝播したときにレプリケーションが止まるという事象に悩まされました。
そもそも、マルチスレッドスレーブを行うときは GTID の有効化が推奨されているのに対し、PXC (Galera Cluster) では GTID の有効化は推奨されてなかったことに後から気づいたこともあって、マルチスレッドスレーブは今回は見送りました。
それぞれ以下の資料が参考になります。
フェイルオーバー検証
構築が終わったらいよいよフェイルオーバーの検証です。
連続的な更新処理を行うのに、今回は MySQL お馴染みの mysqlslap を利用することにしました。
やりたい更新は、値の整合性が崩れやすそうな auto_increment による採番を利用した INSERT です。
他のカラムにはランダムの数値と文字列を格納します。これらは mysqlslap のオプションを使って実現しました。
mysqlslap を利用
具体的な mysqlslap のオプションは以下のとおりです。6033 は ProxySQL 経由するときのポート番号で、xx.xx.xx.99 の箇所は ProxySQL が起動しているサーバーの IP アドレスを指定します。
time mysqlslap \ -u app_user \ -p \ -h xx.xx.xx.99 \ -P 6033 \ --protocol=tcp \ --no-drop \ --auto-generate-sql \ --auto-generate-sql-add-autoincrement \ --auto-generate-sql-load-type=write \ --auto-generate-sql-write-number=100000 \ --auto-generate-sql-unique-write-number=10000 \ --iterations=1 \ --commit=10
mysqlslap の注意点は、一つのイテレーションで mysqlslap という名前のスキーマとそこにテーブルを作り、指定の処理が終わると削除してしまうところです。
なので同時実行数（–concurrency オプション）を２以上にするときは、タイミングによってはスキーマがなくなって処理がこけることがあります。
今回は負荷試験ではないので同時実行数は１で、スキーマを削除しないオプション（–no-drop）をつけて行いました。
検証と結果
どのように検証したかというと、上記の mysqlslap を実行中に、GMO アプリクラウドのコンパネから、PXC のプライマリノード（pxc1 サーバー）を強制停止、処理がエラーで止まらないか、データがノード間でズレていないかを確認しました。
またデータの確認は、強制停止したプライマリノード以外のノードで、mysqlslap が INSERT したテーブルのデータを TSV ファイルに取り出して全部 diff を取るという方法で行いました。
その結果、途中で更新処理がエラーでコケることもなく、生き残ったノード間のデータ不整合もなく、フェイルオーバーは無事成功しました！
なお、pxc1 ノードの停止中に、他のノードがどのような状態を遷移したかを見たところ、以下のようになりました。
PXC ノードの状態の確認は、ProxySQL に入り、以下の SQL で確認できます。
ProxySQL> SELECT * FROM mysql_servers;
ONLINE → OFFLINE_SOFT → レコードが消える様子と、更新可能なプライマリを決めるホストグループが切り替わっていく様子がわかります。
+--------------+--------------+------+--------+---------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+--------------+------+--------+---------+-------------+-----------------+---------------------+---------+----------------+---------+ | 11 | xx.xx.xx.30 | 3306 | ONLINE | 1000 | 0 | 1000 | 0 | 0 | 0 | READ | | 10 | xx.xx.xx.10 | 3306 | ONLINE | 1000000 | 0 | 1000 | 0 | 0 | 0 | WRITE | | 11 | xx.xx.xx.20 | 3306 | ONLINE | 1000 | 0 | 1000 | 0 | 0 | 0 | READ | +--------------+--------------+------+--------+---------+-------------+-----------------+---------------------+---------+----------------+---------+
+--------------+--------------+------+--------------+---------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+--------------+------+--------------+---------+-------------+-----------------+---------------------+---------+----------------+---------+ | 11 | xx.xx.xx.30 | 3306 | ONLINE | 1000 | 0 | 1000 | 0 | 0 | 0 | READ | | 10 | xx.xx.xx.10 | 3306 | OFFLINE_SOFT | 1000000 | 0 | 1000 | 0 | 0 | 0 | WRITE | | 11 | xx.xx.xx.20 | 3306 | ONLINE | 1000 | 0 | 1000 | 0 | 0 | 0 | READ | +--------------+--------------+------+--------------+---------+-------------+-----------------+---------------------+---------+----------------+---------+
+--------------+--------------+------+--------------+---------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+--------------+------+--------------+---------+-------------+-----------------+---------------------+---------+----------------+---------+ | 10 | xx.xx.xx.30 | 3306 | ONLINE | 1000000 | 0 | 1000 | 0 | 0 | 0 | WRITE | | 11 | xx.xx.xx.10 | 3306 | OFFLINE_SOFT | 1000 | 0 | 1000 | 0 | 0 | 0 | READ | | 11 | xx.xx.xx.20 | 3306 | ONLINE | 1000 | 0 | 1000 | 0 | 0 | 0 | READ | +--------------+--------------+------+--------------+---------+-------------+-----------------+---------------------+---------+----------------+---------+
+--------------+--------------+------+--------+---------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+--------------+------+--------+---------+-------------+-----------------+---------------------+---------+----------------+---------+ | 10 | xx.xx.xx.30 | 3306 | ONLINE | 1000000 | 0 | 1000 | 0 | 0 | 0 | WRITE | | 11 | xx.xx.xx.20 | 3306 | ONLINE | 1000 | 0 | 1000 | 0 | 0 | 0 | READ | +--------------+--------------+------+--------+---------+-------------+-----------------+---------------------+---------+----------------+---------+
まとめ
シングルプライマリモードの PXC & ProxySQL & 通常レプリケーションという構成でフェイルオーバー検証してみましたが、構築に手間取ったものの、構成自体は悪くないんじゃないかと思える結果となりました。
ただ、まだ本番サービスで使うには幾つか課題をクリアにする必要がありそうです。
- ProxySQL はアプリケーションサーバーごとに構築するのが基本線と思いますが、そうするとシングルプライマリモードの監視は別々に行われるため、フェイルオーバーのタイミングがそれぞれバラバラになる
- ProxySQL の様々な設定をやはりもっと知らないといけない
- 今回のレプリケーション構成では、プライマリノード以外が倒れたときにそれに紐づくスレーブ DB の内容が古いまま放置される（レプリケーションを別途監視していれば検知可能）
特に上記１が大きな課題なので、ここは今後も検討していきたいところです。
あとは、今回検証しなかった InnoDB Cluster を今後検証比較して、それぞれのメリット・デメリットを見てみたいと思います。
最後に
次世代システム研究室では、データ分析エンジニアやアーキテクトを募集しています。ビッグデータの解析業務など次世代システム研究室にご興味を持って頂ける方がいらっしゃいましたら、ぜひ 募集職種一覧 からご応募をお願いします。
皆さんのご応募をお待ちしています。
グループ研究開発本部の最新情報をTwitterで配信中です。ぜひフォローください。
Follow @GMO_RD
