目次
はじめに
一般に知られている通り、MySQLにおける通常のレプリケーション機能はマスタが1台の「シングルマスタ」構成を前提としています（マルチソースレプリケーションを除く）。
そのため、マスタを2台用意して双方向にレプリケーションを行う「デュアルマスタ」構成は、更新イベントの競合などが多発し正常に動作しない可能性が高いです。そこで実際にはアクティブ-スタンバイ型の構成にするパターンが多いのですが、その場合アクティブからスタンバイの切り替えに時間がかかるなどの問題を抱えてしまうケースもあります。
私は上記のような場合は、Percona XtraDB Cluster（以下、PXC）の導入を推奨しています。しかし、中には諸々の事情からサーバが2台しか用意できない方もいらっしゃいます（PXCの最小構成は3台～）。
そこで本記事でご紹介するのは、PXC＋Galera Arbitrator を用いた「デュアルマスタ」構成です。
※ Galera Arbitratorについてはこちらの記事を参照してください
構成について
Galera Arbitratorの利点は、データを保持しないことです。そのため、PXCノードとの通信が適切にできれば、必ずしもサーバのスペックをDB側に合わせる必要はありません（性能については後述）。そこで、この構成ではGalera Arbitrator をWEBサーバに同居させることで、PXCのノード数を削減しています。
本記事では以下のような検証サーバを使用します。プロキシソフトウェアにはHAProxyを使用しています。
[code lang=text]
DBサーバ1： 192.168.100.10 / 192.168.200.10 – Percona XtraDB Cluster
DBサーバ2： 192.168.100.20 / 192.168.200.20 – Percona XtraDB Cluster
WEBサーバ： 192.168.100.30 / 192.168.200.30 – ProxySQL, Percona-client, Galera Arbitrator
[/code]
※ 全サーバ CentOS 7.3 を利用しています
※ NWはアプリ通信用とGaleraレプリケーション用の2つに分けています。
構築手順
構築手順に関しては、公式マニュアルの手順を参考に進めていきましょう。
1. PXCインストール、初期設定
まずはPXCのNode1を構築します。PXCをインストールして、初期パスワードの変更を行います。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
[vagrant@server1 ~]$ sudo setenforce 0 [vagrant@server1 ~]$ sudo yum install http://www.percona.com/downloads/percona-release/redhat/0.1-6/percona-release-0.1-6.noarch.rpm [vagrant@server1 ~]$ sudo yum install Percona-XtraDB-Cluster-57 [vagrant@server1 ~]$ sudo service mysql start [vagrant@server1 ~]$ sudo grep 'temporary password' /var/log/mysqld.log 2018-10-23T03:13:01.539990Z 1 [Note] A temporary password is generated for root@localhost: AykWXksGg3.c [vagrant@server1 ~]$ mysql -u root -p mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MySQL5.7'; mysql> exit [vagrant@server1 ~]$ sudo service mysql stop |
2. Galeraの設定
設定するパラメータは、環境に合わせて随時変更してください（buffer_pool_sizeやIPアドレスなど）。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
[vagrant@server1 ~]$ sudo tee /etc/my.cnf << EOT [mysqld] ## MySQL settings server_id = 100 log_timestamps = SYSTEM log_error = /var/log/mysqld.log log_bin log_slave_updates binlog_format = ROW default_storage_engine = InnoDB innodb_autoinc_lock_mode = 2 innodb_buffer_pool_size = 8G ## Galera settings wsrep_provider = /usr/lib64/galera3/libgalera_smm.so wsrep_cluster_name = pxc-cluster wsrep_cluster_address = gcomm://192.168.200.10,192.168.200.20,192.168.200.30 wsrep_node_name = pxc1 wsrep_node_address = 192.168.200.10 wsrep_sst_method = xtrabackup-v2 wsrep_sst_auth = sstuser:passw0rd wsrep_slave_threads = 4 pxc_strict_mode = ENFORCING EOT |
3. Node1の起動
Node1を起動して、SST用のユーザを作成します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
[vagrant@server1 ~]$ sudo systemctl start mysql@bootstrap.service [vagrant@server1 ~]$ mysql -u root -pMySQL5.7 mysql> CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 'passw0rd'; mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost'; mysql> FLUSH PRIVILEGES; ``` ### 4. Node2の構築・起動 続けてNode2を起動します。データはNode1からSSTで同期するため、インストール・設定・起動の手順だけで十分です。パラメータについては以下2つの設定だけ変える点に注意してください。 ``` [vagrant@server1 ~]$ sudo setenforce 0 [vagrant@server1 ~]$ sudo yum install http://www.percona.com/downloads/percona-release/redhat/0.1-6/percona-release-0.1-6.noarch.rpm [vagrant@server1 ~]$ sudo yum install Percona-XtraDB-Cluster-57 [vagrant@server2 ~]$ sudo tee /etc/my.cnf << EOT [mysqld] ... wsrep_node_name = pxc2 wsrep_node_address = 192.168.200.20 ... EOT [vagrant@server2 ~]$ sudo systemctl start mysql |
5. Galera Arbitratorの構築・起動
Galera Arbitratorのインストールも公式マニュアルでも説明されています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
[vagrant@server3 ~]$ sudo setenforce 0 [vagrant@server3 ~]$ sudo yum install http://www.percona.com/downloads/percona-release/redhat/0.1-6/percona-release-0.1-6.noarch.rpm [vagrant@server3 ~]$ sudo yum install Percona-XtraDB-Cluster-garbd-57 [vagrant@server3 ~]$ sudo tee /etc/sysconfig/garb << EOT # Copyright (C) 2012 Codership Oy # This config file is to be sourced by garb service script. # A comma-separated list of node addresses (address[:port]) in the cluster GALERA_NODES="192.168.200.10,192.168.200.20,192.168.200.30" # Galera cluster name, should be the same as on the rest of the nodes. GALERA_GROUP="pxc-cluster" # Optional Galera internal options string (e.g. SSL settings) # see http://galeracluster.com/documentation-webpages/galeraparameters.html # GALERA_OPTIONS="" # Log file for garbd. Optional, by default logs to syslog # Deprecated for CentOS7, use journalctl to query the log for garbd LOG_FILE="/tmp/garbd.log" EOT [vagrant@server3 ~]$ sudo systemctl start garb |
※ 上記のコメントの通り、Arbitratorのログはデフォルトでsyslogに出力されます
ただし、CentOS7の場合は journalctlを使うことが推奨されるようです。
6. HAProxyの構築・起動
HAProxyは有名なプロキシソフトウェアの一つですが、PXCとの相性も良くマニュアルにも設定手順が記載されています。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 |
[vagrant@server3 ~]$ sudo yum install haproxy Percona-Server-client-57 [vagrant@server3 ~]$ sudo tee /etc/haproxy/haproxy.cfg << EOT global log 127.0.0.1 local0 log 127.0.0.1 local1 notice maxconn 4096 chroot /usr/share/haproxy user haproxy group haproxy daemon defaults log global mode http option tcplog option dontlognull retries 3 option redispatch frontend pxc-onenode-front bind *:3306 mode tcp default_backend pxc-onenode-back frontend pxc-front bind *:3307 mode tcp default_backend pxc-back backend pxc-onenode-back mode tcp balance leastconn option httpchk server node1 192.168.100.10:3306 check port 9200 inter 12000 rise 3 fall 3 server node2 192.168.100.20:3306 check port 9200 inter 12000 rise 3 fall 3 backup backend pxc-back mode tcp balance leastconn option httpchk server node1 192.168.100.10:3306 check port 9200 inter 12000 rise 3 fall 3 server node2 192.168.100.20:3306 check port 9200 inter 12000 rise 3 fall 3 EOT |
なおPXCはステータス情報などがMySQLと異なるため、ノードの死活監視には基本的にPXCに付属しているclustercheckスクリプトを使います。設定方法は以下の通りです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
[vagrant@server1 ~]$ mysql -u root -pMySQL5.7 mysql> CREATE USER 'clustercheckuser'@'localhost' identified by 'clustercheckpassword!'; mysql> GRANT PROCESS ON *.* TO 'clustercheckuser'@'localhost'; mysql> FLUSH PRIVILEGES; mysql> exit [vagrant@server1 ~]$ sudo yum install xinetd [vagrant@server1 ~]$ sudo tee /etc/xinetd.d/mysqlchk << EOT # default: on # description: mysqlchk service mysqlchk { # this is a config for xinetd, place it in /etc/xinetd.d/ disable = no flags = REUSE socket_type = stream port = 9200 wait = no user = nobody server = /usr/bin/clustercheck log_on_failure += USERID only_from = 0.0.0.0/0 # recommended to put the IPs that need # to connect exclusively (security purposes) per_source = UNLIMITED } EOT [vagrant@server1 ~]$ sudo vi /etc/services 以下を末尾に追記 mysqlchk 9200/tcp # mysqlchk [vagrant@server1 ~]$ sudo systemctl restart xinetd [vagrant@server2 ~]$ sudo yum install xinetd [vagrant@server2 ~]$ sudo tee /etc/xinetd.d/mysqlchk << EOT ...（上と同じ）... EOT [vagrant@server2 ~]$ sudo vi /etc/services 以下を末尾に追記 mysqlchk 9200/tcp # mysqlchk [vagrant@server2 ~]$ sudo systemctl restart xinetd |
※ ユーザ名、パスワードを変更した場合は /usr/bin/clustercheck に記載されている認証情報も変更してください
利用方法
ここまでの手順で環境はできました。それでは実際にクエリを実行してみましょう。
HAProxyローカル上の3306ポートは Node1 にのみクエリをルーティングします。Node1がダウンした場合は、Node2に切り替えます。更新クエリの実行先として使用しましょう。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
[vagrant@server1 ~]$ mysql -u root -pMySQL5.7 mysql> CREATE USER `testuser`@`192.168.100.30` IDENTIFIED BY "testpass!"; mysql> GRANT ALL ON *.* TO `testuser`@`192.168.100.30`; mysql> FLUSH PRIVILEGES; [vagrant@server3 ~]$ sudo systemctl start haproxy [vagrant@server3 ~]$ mysql -u testuser -ptestpass! -h 127.0.0.1 -P 3306 -e "SELECT @@hostname" mysql: [Warning] Using a password on the command line interface can be insecure. +------------+ | @@hostname | +------------+ | server1 | +------------+ [vagrant@server3 ~]$ mysql -u testuser -ptestpass! -h 127.0.0.1 -P 3306 -e "SELECT @@hostname" mysql: [Warning] Using a password on the command line interface can be insecure. +------------+ | @@hostname | +------------+ | server1 | +------------+ [vagrant@server3 ~]$ mysql -u testuser -ptestpass! -h 127.0.0.1 -P 3306 -e "SELECT @@hostname" mysql: [Warning] Using a password on the command line interface can be insecure. +------------+ | @@hostname | +------------+ | server1 | +------------+ |
対して3307ポートはアクセス数に応じてNode1とNode2両方にクエリを実行します。こちらは参照クエリの向き先として使用しましょう。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
[vagrant@server3 ~]$ mysql -u testuser -ptestpass! -h 127.0.0.1 -P 3307 -e "SELECT @@hostname" mysql: [Warning] Using a password on the command line interface can be insecure. +------------+ | @@hostname | +------------+ | server1 | +------------+ [vagrant@server3 ~]$ mysql -u testuser -ptestpass! -h 127.0.0.1 -P 3307 -e "SELECT @@hostname" mysql: [Warning] Using a password on the command line interface can be insecure. +------------+ | @@hostname | +------------+ | server2 | +------------+ [vagrant@server3 ~]$ mysql -u testuser -ptestpass! -h 127.0.0.1 -P 3307 -e "SELECT @@hostname" mysql: [Warning] Using a password on the command line interface can be insecure. +------------+ | @@hostname | +------------+ | server1 | +------------+ [vagrant@server3 ~]$ mysql -u testuser -ptestpass! -h 127.0.0.1 -P 3307 -e "SELECT @@hostname" mysql: [Warning] Using a password on the command line interface can be insecure. +------------+ | @@hostname | +------------+ | server2 | +------------+ |
障害試験
mysqlslapコマンドを使って簡単な障害試験を実施します。以下のコマンド実行中に Node1 をkillコマンドで強制終了させた時の挙動を確認します。
|
1 2 3 4 5 6 7 |
[vagrant@server3 ~]$ mysqlslap -utestuser -ptestpass! -h 127.0.0.1 -P 3306 --auto-generate-sql --iterations=10000 --concurrency=1 --auto-generate-sql-guid-primary [vagrant@server1 ~]$ ps aux | grep mysqld root 7197 0.0 0.0 15204 1792 ? Ss 05:31 0:00 /bin/sh /usr/bin/mysqld_safe --basedir=/usr --wsrep-new-cluster mysql 7559 0.7 9.6 2934976 368672 ? Sl 05:31 0:40 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --wsrep-provider=/usr/lib64/galera3/libgalera_smm.so --wsrep-new-cluster --log-error=/var/log/mysqld.log --pid-file=server1.pid --wsrep_start_position=98edcbab-d74a-11e8-a590-d72ee24cf801:1 vagrant 10528 0.0 0.0 12452 940 pts/0 S+ 07:01 0:00 grep --color=auto mysqld [vagrant@server1 ~]$ sudo kill -9 7559; sudo kill -9 7197; |
【実行結果】
|
1 2 3 4 5 6 7 8 9 10 11 |
[vagrant@server3 ~]$ mysqlslap -utestuser -ptestpass! -h 127.0.0.1 -P 3306 --auto-generate-sql --iterations=10000 --concurrency=1 --auto-generate-sql-g uid-primary mysqlslap: [Warning] Using a password on the command line interface can be insecure. mysqlslap: Cannot run query INSERT INTO t1 VALUES (uuid(),1348361729,'i8X2EnycNH7sDHMltxcILtQE0ZPoPq9zyg24J0hiAgQNpg8jedtrWK5WtXIALR9B03FJ4ou6TCTAtWtN7fETzBzkiAmvTv6LrEZn2RtNfMaOkJfjytCp54ZfEJbb7Z') ERROR : Lost connection to MySQL server during query [vagrant@server3 ~]$ mysql -u testuser -ptestpass! -h 127.0.0.1 -P 3306 -e "SELECT @@hostname" mysql: [Warning] Using a password on the command line interface can be insecure. +------------+ | @@hostname | +------------+ | server2 | +------------+ |
SQL実行中にNode1がダウンするため、mysqlslapにはエラーが返されます。ただし、その後のアクセスは全てNode2に向けられるため、エラーとなった処理をリトライしてサービスは継続稼働することができます。
また、Node2およびGalera Arbitratorがダウンした場合はNode1の処理に影響はありません。
性能試験
最後に性能試験を行います。今回はAWS EC2を利用して以下のような試験環境を用意しました。
DBサーバ1： Percona XtraDB Cluster
DBサーバ2： Percona XtraDB Cluster
仲裁サーバ： Galera Arbitrator
benchサーバ： sysbench, HAProxy
測定するのは以下の3ケースです。
① 全サーバ同じインスタンスタイプ（m5.xlarge）
② 仲裁サーバのみスペックの低いインスタンスタイプ（m5.xlarge → t2.micro）
③ ①の環境 + tcコマンドで10msのNW遅延を起こす（平常時は0.1ms程度）
※ m5.xlarge = CPU: 4core / RAM: 16GB
※ t2.micro = CPU: 1core / RAM: 1GB
※ I/O性能の安定性のため、ディスクは汎用SSD（1TB）にしています
※ ベンチマーク用とレプリケーション用でNICは分けています
試験手順
- 上記の手順の通り、PXC2ノード, Galera Arbitrator, HAProxyをセットアップ
- sysbenchをインストール（Perconaリポジトリに含まれています）
- テストデータをロード
- sysbenchのベンチマーク実行
- sysbench の cleanup 実行
- mysqldの停止、メモリのクリア
- 手順3.に戻る
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
bench$ sudo yum install http://www.percona.com/downloads/percona-release/redhat/0.1-6/percona-release-0.1-6.noarch.rpm bench$ sudo yum install sysbench bench$ mysql -u testuser -ptestpass! -h 127.0.0.1 -P 3306 -e "CREATE DATABASE sbtest" bench$ sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=mysql --mysql-user=testuser --mysql-password=testpass! --mysql-host=127.0.0.1 --mysql-port=3306 --tables=8 --table_size=2500000 prepare ### 上記のオプションでおよそ4.6GBのデータが作られます bench$ sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=mysql --mysql-user=testuser --mysql-password=testpass! --mysql-host=127.0.0.1 --mysql-port=3306 --tables=8 --table_size=2500000 --threads=16 --time=1200 run 2>&1 | tee ./sysbench_result_`date "+%m%d_%H%M"`.log bench$ sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=mysql --mysql-user=testuser --mysql-password=testpass! --mysql-host=127.0.0.1 --mysql-port=3306 --tables=8 cleanup node2$ sudo systemctl stop mysql node1$ sudo systemctl stop mysql@bootstrap.service all-servers$ sudo sync all-servers$ sudo sysctl -w vm.drop_caches=3 node1$ sudo systemctl start mysql@bootstrap.service node2$ sudo systemctl start mysql ★ → sysbench /usr/share/sysbench/oltp_read_write.lua ... prepare に戻る ### 試験③のNW遅延は以下のコマンドで行う arbitrator$ sudo tc qdisc add dev ens6 root netem delay 100ms ### ens6はreplication用インターフェース arbitrator$ sudo tc qdisc del dev ens6 root |
※sysbenchについては以前のブログ記事も参照してください
試験結果
sysbenchの結果は以下のようになりました。Arbitratorサーバのスペックを落としても、sysbenchのパフォーマンスに大きな影響はないことが分かります。一方で、ArbitratorサーバのNWが遅延している場合はスコアが半分近くまで落ちています。すなわち、Arbitratorのみを遠隔地に配置するなどした場合は注意が必要となります。
| TPS | QPS | Latency (avg) | |
|---|---|---|---|
| ①同一スペック（1回目） | 1565.02 | 31300.60 | 10.22 ms |
| ①同一スペック（2回目） | 1416.18 | 28323.77 | 10.22 ms |
| ②仲裁サーバースペック低(1回目) | 1596.48 | 31929.78 | 10.02ms |
| ②仲裁サーバースペック低(2回目) | 1403.25 | 28065.18 | 11.40ms |
| ③10ms NW遅延(1回目) | 792.51 | 15850.26 | 20.19ms |
| ③10ms NW遅延(2回目) | 849.52 | 16990.50 | 18.83ms |
まとめ
PXC2ノード + Galera Arbitrator という構成は、公式で推奨されているものではなく少々トリッキーな構成です。しかし、通常のMySQLでは困難な「デュアルマスタ」という構成が実現できるため、必要に応じて検討してみてはいかがでしょうか？
