前提
これから作るもの
MySQL Multi Masterといっているけれど、実際に作ろうとしているのは Percona XtraDB Cluster だ。NDB Clusterとは別物。Galera Clusterの系統だ。
ネットやQiitaを探せばいろんな人がやっている記録が出てくるけれど、自分でやってみることに価値があるってことで。
本来はスプリットブレインを避けるために3台orそれ以上の奇数での構成を推奨しているが、とりあえず動作を見たいだけなので2台で作る。
手順について
本手順ではコンソール作業を「$」プロンプトで書いているけれど、 全部root作業 だと思ってほしい。フォーマットの関係で#だとコメントアウトになってしまって読みにくいので・・・
試験環境の構築
とにもかくにもVagrantが楽ちんなのでVagrantで作る。
Vagrant セットアップ
$ vagrant box add centos7 https://github.com/holms/vagrant-centos7-box/releases/download/7.1.1503.001/CentOS-7.1.1503-x86_64-netboot.box
$ vagrant init centos7
$ vim VagrantFile
最後のendの前に、以下を追加する。
設定値中のメモリサイズとかは各環境で調整してほしい。
### server1の設定内容
config.vm.define "s1" do |server|
server.vm.hostname = config.vm.box + "-s1" + ".vagrant"
server.vm.network "private_network", ip: "192.168.200.101", host: "52001"
config.vm.provider "virtualbox" do |vb|
### virtualbox上で表示される名前
vb.name = config.vm.box + "_s1"
vb.customize ["modifyvm", :id, "--memory", "2048"]
vb.customize ["modifyvm", :id, "--cpus", "2"]
end
end
### server2の設定内容
config.vm.define "s2" do |server|
server.vm.hostname = config.vm.box + "-s2" + ".vagrant"
server.vm.network "private_network", ip: "192.168.200.102", host: "52002"
config.vm.provider "virtualbox" do |vb|
### virtualbox上で表示される名前
vb.name = config.vm.box + "_s2"
vb.customize ["modifyvm", :id, "--memory", "2048"]
vb.customize ["modifyvm", :id, "--cpus", "1"]
end
end
起動
$ vagrant up s1
$ vagrant up s2
以降はこんな感じでログインできる。(vagrant ssh使ってもいいけど)
$ ssh 192.168.200.101 -l root
$ ssh 192.168.200.102 -l root
どちらも、パスワードはvagrant
疎通NGのときはvagrant reload s1してみよう。
Percona XtraDB Clusterインストール
事前準備
入れても入れなくてもいいけど、minimalに含まれていないパッケージを入れる。
$ yum install net-tools
$ yum install vim
検証なのでFWを無効化する。(当然非推奨）
$ firewall-cmd --set-default-zone=trusted
$ firewall-cmd --get-zone-of-interface=eth1
## trustedになっていること
## trustedではない場合
$ firewall-cmd --zone=trusted --change-interface=eth1
$ firewall-cmd --get-zone-of-interface=eth1
パッケージインストール
オフィシャルの記載を参考に進める。
レポジトリの追加
$ yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
$ yum search Percona
インストール
サーバーをインストールすれば、依存関係でクライアントもインストールされる。
$ yum install Percona-XtraDB-Cluster-server-56
初期設定〜初回起動
ノード1の初期設定
オフィシャルドキュメントに基づいて進める。
設定ファイル作成
設定値は色々な考えがあって良いのだが、使用するwsrep_sst_methodの関連で必須パラメータがいくつかある。(まだ記載漏れがあるかもしれない)
パラメータが漏れていてもmysqldは自動的にデフォルト値を使って起動してくれるが、wsrep_sst_methodのxtrabackup-v2などのプログラムが正常動作せずに、2台目の追加時等にエラーを吐くことがある。しかもエラーが読みにくいので注意。私はここで少しハマった。
- server_id
- innodb_locks_unsafe_for_binlog = 1
- innodb_autoinc_lock_mode = 2
- innodb_log_file_size
- datadir
また、これは必須ではなさそうだが、[安全性のために設定するのが良い]
(http://yoku0825.blogspot.jp/2014/07/percona-xtradb-clusterpitr.html)ようだ。
- log_slave_updates
# Template my.cnf for PXC
# Edit to your requirements.
[mysqld]
user = mysql
datadir = /var/lib/mysql
log_bin
default_storage_engine = InnoDB
# In order for Galera to work correctly binlog format should be ROW
binlog_format = ROW
log-bin=/var/lib/mysql/mysqld-bin
expire_logs_days=1
innodb_buffer_pool_size = 100M
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 2
innodb_log_file_size = 64M
innodb_file_per_table = 1
innodb_locks_unsafe_for_binlog = 1
innodb_autoinc_lock_mode = 2
explicit_defaults_for_timestamp = 0
# Cluster
log-slave-updates
wsrep_cluster_name = TestCluster
wsrep_provider = /usr/lib64/galera3/libgalera_smm.so
wsrep_sst_method = xtrabackup-v2
wsrep_slave_threads = 8
wsrep_sst_auth = "sstuser:reppassword"
wsrep_cluster_address = gcomm://192.168.200.101,192.168.200.102
wsrep_node_address = 192.168.200.101
wsrep_node_name = Node1
server_id = 1
[mysqld_safe]
pid-file = /run/mysqld/mysql.pid
syslog
!includedir /etc/my.cnf.d
ノード1初回起動
1台目のサーバーの場合は、参加するクラスターノードがいないので、最初のノード(bootstrap)を意味する@bootstrap.serviceを付与して起動する必要がある。
$ systemctl start mysql@bootstrap.service
$ journalctl -u mysql
rootユーザへのパスワード設定とユーザー作成
ノード2を作成する前に、オフィシャルにならってレプリケーション用にsstuserを作成する。また、rootユーザーにパスワードを設定しよう。
違和感を覚えるが、sstuserはlocalhostで良いようだ。
$ mysql -u root
mysql> UPDATE mysql.user SET password=PASSWORD("password") where user='root';
mysql> CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 'reppassword';
mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost';
mysql> FLUSH PRIVILEGES;
ノード2の初期設定
インストールと設定をする。インストール部分は割愛。
設定ファイル作成
基本的にはノード1から複製すれば良い。その後、以下のパラメータを変える。
- wsrep_node_address
- wsrep_node_name
- server_id
下記にdiff形式で差分を載せる。
@@ -31,9 +31,9 @@
wsrep_slave_threads = 8
wsrep_sst_auth = "sstuser:reppassword"
wsrep_cluster_address = gcomm://192.168.200.101,192.168.200.102
-wsrep_node_address = 192.168.200.101
-wsrep_node_name = Node1
-server_id = 1
+wsrep_node_address = 192.168.200.102
+wsrep_node_name = Node2
+server_id = 2
ノード2起動とクラスターへの参加
こちらは単に起動すればいい。
$ systemctl start mysql
自動的に初期同期が行われ、先ほど作ったユーザー情報も同期される。
ただし要注意情報として、これは 初回の動作 であることを覚えておきたい。
基本的にXtraDB Clusterは InnoDBのみを同期対象としている ので、MyISAMであるmysql.userテーブルは一部の操作以外は"正確に"同期されない。（DDLであるCREATE, ALTER, GRANT, TRUNCATE, DROPは同期されるが、UPDATE文などは同期されない）
mysql.userテーブルを操作していると、この辺りに「あれ？」って首を傾げることになることに注意だ。
補足：DBエンジンの確認方法
$ mysql -u root -ppassword
mysql> use information_schema;
mysql> select table_name, engine from tables where table_schema = 'mysql';
動作確認
期待通り同期されていることを順に確認してみる。
データベースの作成
ノード1で実施。
$ mysql -u root -ppassword
[rootユーザー]
mysql> show databases like 'newdb';
Empty set (0.00 sec)
mysql> create database newdb;
mysql> show databases like 'newdb';
+------------------+
| Database (newdb) |
+------------------+
| newdb |
+------------------+
1 row in set (0.00 sec)
ノード2で確認
$ mysql -u root -ppassword
[rootユーザー]
mysql> show databases like 'newdb';
+------------------+
| Database (newdb) |
+------------------+
| newdb |
+------------------+
1 row in set (0.00 sec)
同期されている。
ユーザー作成と削除
前述のとおり、mysql.userはMyISAMなので注意が必要であるが、CREATE文であれば同期が走る。
ノード1で実施。
$ mysql -u root -ppassword
[rootユーザー]
mysql> create user 'test_user'@'localhost' identified by 'password';
mysql> grant process on *.* to 'test_user'@'localhost';
mysql> flush privileges;
mysql> select user,host,password from mysql.user where user='test_user';
+-----------+-----------+-------------------------------------------+
| user | host | password |
+-----------+-----------+-------------------------------------------+
| test_user | localhost | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
+-----------+-----------+-------------------------------------------+
1 row in set (0.00 sec)
## このままセッションを維持。
前述の通り、CREATE文のためノード2でユーザーが複製されていることが確認できる。
$ mysql -u root -ppassword
[rootユーザー]
mysql> select user,host,password from mysql.user where user='test_user';
+-----------+-----------+-------------------------------------------+
| user | host | password |
+-----------+-----------+-------------------------------------------+
| test_user | localhost | *2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19 |
+-----------+-----------+-------------------------------------------+
1 row in set (0.00 sec)
mysql> \q
ノード2でパスワードや権限が同期していることを確認。
$ mysql -u test_user -ppassword
## ローカルホストのtest_userとしてログインできる。
[test_userユーザー]
mysql> show processlist;
## プロセスリストが表示される。
mysql> \q
ノード2でユーザーを削除してみる。
$ mysql -u root -ppassword
[rootユーザー]
mysql> drop user 'test_user'@'localhost';
ノード1でつないだままのセッションを使って確認。
[rootユーザー]
mysql> select user,host,password from mysql.user where user='test_user';
Empty set (0.00 sec)
mysql> \q
もちろん改めてログインしようとしてもユーザーは削除されている。
$ mysql -u test_user -ppassword
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'test_user'@'localhost' (using password: YES)
mysqlslap
ベンチマークツールのmysqlslapを使って、動作を確認する。
準備
こんな感じでShellスクリプトを作る。
ただのワンライナーなんだけど、繰り返しやすいようにって意味と、コマンドが長すぎるので。
# !/bin/bash
mysqlslap \
--no-defaults \
--concurrency=5 \
--iterations=4 \
--engine=innodb \
--auto-generate-sql \
--auto-generate-sql-add-autoincrement \
--auto-generate-sql-load-type=mixed \
--auto-generate-sql-write-number=1000 \
--number-of-queries=100000 \
--host=localhost \
--port=3306 \
--user=root \
--password=password \
-v
最後の-vの数を増やせば(-vvとか)情報量が増える。
詳しい例はこちらを見た方がいい。
実行(シングル)
まずはノード1にやってみる。
$ bash domysqlslap.sh
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 18.045 seconds
Minimum number of seconds to run all queries: 17.676 seconds
Maximum number of seconds to run all queries: 18.441 seconds
Number of clients running queries: 5
Average number of queries per client: 20000
エラーなくテストが終わった。性能面は気にしないでほしい。
同様に後で起動したノード2でも試してみる。
$ bash domysqlslap.sh
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 16.878 seconds
Minimum number of seconds to run all queries: 16.517 seconds
Maximum number of seconds to run all queries: 17.295 seconds
Number of clients running queries: 5
Average number of queries per client: 20000
この間、topなどを眺めていると、もう片方にも負荷がかかっていることが確認できる。
並行アクセス動作確認
HAProxyの準備
ロードバランサとしてそれぞれのホスト上にhaproxyを準備する。
性能面ではKeepalivedの方が望ましいが、第三者的ホストが必要となってしまうので、手抜きでご勘弁を。
インストールと設定
yumで入れる。
$ yum install haproxy
$ vim /etc/haproxy/haproxy.cfg
defaults以下のブロックを以下のように変える。
燦然と輝く balance roundrobin の1行が、Master-Slave構成ではないことの証明。
この検証ではhaproxyをmysqldノードに相乗りさせているので、haproxyのポートを53306に設定している。
defaults
mode tcp
log global
timeout queue 1m
timeout connect 10s
timeout client 1m
timeout server 1m
timeout check 10s
maxconn 3000
listen MySQL 0.0.0.0:53306
timeout connect 10s
timeout client 1m
timeout server 1m
mode tcp
balance roundrobin
option mysql-check user haproxy
server s1 192.168.200.101:3306 check
server s2 192.168.200.102:3306 check
DBにhaproxy動作確認用ユーザと、mysqlslap用ユーザを作る。(今回はアクセス元を%にしている)
mysql> GRANT USAGE ON *.* TO 'haproxy'@'%';
mysql> CREATE USER 'test_user'@'%' IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'test_user'@'%';
mysql> FLUSH PRIVILEGES;
動作確認
haproxyのステータス確認。（バックエンド指定のs1,s2がUPであること)
$ systemctl start haproxy
$ echo "show stat" | socat stdio /var/lib/haproxy/stats
# pxname,svname,qcur,qmax,scur,smax,slim,stot,bin,bout,dreq,dresp,ereq,econ,eresp,wretr,wredis,status,weight,act,bck,chkfail,chkdown,lastchg,downtime,qlimit,pid,iid,sid,throttle,lbtot,tracked,type,rate,rate_lim,rate_max,check_status,check_code,check_duration,hrsp_1xx,hrsp_2xx,hrsp_3xx,hrsp_4xx,hrsp_5xx,hrsp_other,hanafail,req_rate,req_rate_max,req_tot,cli_abrt,srv_abrt,comp_in,comp_out,comp_byp,comp_rsp,lastsess,last_chk,last_agt,qtime,ctime,rtime,ttime,
MySQL,FRONTEND,,,0,0,3000,0,0,0,0,0,0,,,,,OPEN,,,,,,,,,1,2,0,,,,0,0,0,0,,,,,,,,,,,0,0,0,,,0,0,0,0,,,,,,,,
MySQL,s1,0,0,0,0,,0,0,0,,0,,0,0,0,0,UP,1,1,0,0,0,2,0,,1,2,1,,0,,2,0,,0,L7OK,0,0,,,,,,,0,,,,0,0,,,,,-1,5.6.24-72.2-56-log,,0,0,0,0,
MySQL,s2,0,0,0,0,,0,0,0,,0,,0,0,0,0,UP,1,1,0,0,0,2,0,,1,2,2,,0,,2,0,,0,L7OK,0,1,,,,,,,0,,,,0,0,,,,,-1,5.6.24-72.2-56-log,,0,0,0,0,
MySQL,BACKEND,0,0,0,0,300,0,0,0,0,0,,0,0,0,0,UP,2,2,0,,0,2,0,,1,2,0,,0,,1,0,,0,,,,,,,,,,,,,,0,0,0,0,0,0,-1,,,0,0,0,0,
ロードバランサの動作確認。
$ mysql -u test_user -ppassword -h 192.168.200.101 -P 53306 -e'show variables like "server_id";'
繋ぐたびに異なるserveridが表示されたらOK。
負荷試験
試験用ファイル作成
先ほどの試験用ファイルをhaproxyを経由するように編集する。
$ cp -p domysqlslap.sh domysqlslap_ha.sh
$ vim domysqlslap_ha.sh
## 接続ユーザー情報や接続先を変更する
$ cat domysqlslap_ha.sh
# !/bin/bash
mysqlslap \
--no-defaults \
--concurrency=5 \
--iterations=4 \
--engine=innodb \
--auto-generate-sql \
--auto-generate-sql-add-autoincrement \
--auto-generate-sql-load-type=mixed \
--auto-generate-sql-write-number=1000 \
--number-of-queries=100000 \
--host=192.168.200.101 \
--port=53306 \
--user=test_user \
--password=password \
-v
$ diff -u domysqlslap.sh domysqlslap_ha.sh
--- domysqlslap.sh 2015-08-17 11:28:40.133577790 +0000
+++ domysqlslap_ha.sh 2015-08-18 02:22:48.849237649 +0000
@@ -9,8 +9,8 @@
--auto-generate-sql-load-type=mixed \
--auto-generate-sql-write-number=1000 \
--number-of-queries=100000 \
- --host=localhost \
- --port=3306 \
- --user=root \
- --password=password \
+ --host=192.168.200.101 \
+ --port=53306 \
+ --user=test_user \
+ --password=password \
-v
接続試験
それぞれのノードで以下のコマンドを予め打っておき、ちゃんとhaproxyによって両ノードに接続が振られていることを確認する。
$ watch 'ss -natp | grep 3306'
ベンチマーク実行。
$ do domysqlslap_ha.sh
Benchmark
Running for engine innodb
Average number of seconds to run all queries: 25.393 seconds
Minimum number of seconds to run all queries: 24.943 seconds
Maximum number of seconds to run all queries: 26.290 seconds
Number of clients running queries: 5
Average number of queries per client: 20000
エラーなく実行された。
まとめ
今回は雑にVagrantでCentOS7を起動し、Percona XtraDB Clusterを動かすまでを確認した。本来は3ノード以上の奇数構成とし、ちゃんとクォーラムを意識した構成とすべきだろう。
性能面は置いておいて、Percona XtraDB Clusterは簡単にマルチマスター構成を取れるというのは本当のようだ。