前提

これから作るもの

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

/etc/my.cnf

# 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形式で差分を載せる。

/etc/my.cnf

@@ -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スクリプトを作る。
ただのワンライナーなんだけど、繰り返しやすいようにって意味と、コマンドが長すぎるので。

domysqlslap.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=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は簡単にマルチマスター構成を取れるというのは本当のようだ。