CentOS7 + MySQL Multi Master 検証メモ



# 前提

## これから作るもの

MySQL Multi Masterといっているけれど、実際に作ろうとしているのは **Percona XtraDB Cluster** だ。NDB Clusterとは別物。Galera Clusterの系統だ。
ネットやQiitaを探せばいろんな人がやっている記録が出てくるけれど、自分でやってみることに価値があるってことで。

本来はスプリットブレインを避けるために3台orそれ以上の奇数での構成を推奨しているが、とりあえず動作を見たいだけなので2台で作る。

## 手順について

本手順ではコンソール作業を「$」プロンプトで書いているけれど、 **全部root作業** だと思ってほしい。フォーマットの関係で#だとコメントアウトになってしまって読みにくいので・・・



## 試験環境の構築

とにもかくにもVagrantが楽ちんなのでVagrantで作る。

### Vagrant セットアップ

```bash
$ 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の前に、以下を追加する。
設定値中のメモリサイズとかは各環境で調整してほしい。

```Vagrantfile
  ### 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
```

起動

```bash
$ vagrant up s1
$ vagrant up s2
```

以降はこんな感じでログインできる。(vagrant ssh使ってもいいけど)

```bash
$ ssh 192.168.200.101 -l root
$ ssh 192.168.200.102 -l root

どちらも、パスワードはvagrant
```

疎通NGのときは`vagrant reload s1`してみよう。

# Percona XtraDB Clusterインストール

## 事前準備

入れても入れなくてもいいけど、minimalに含まれていないパッケージを入れる。

```bash
$ yum install net-tools
$ yum install vim
```

検証なのでFWを無効化する。(当然非推奨）

```bash
$ 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
```


## パッケージインストール

[オフィシャルの記載](https://www.percona.com/doc/percona-xtradb-cluster/5.6/installation/yum_repo.html)を参考に進める。

### レポジトリの追加

```bash
$ yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
$ yum search Percona
```

### インストール
サーバーをインストールすれば、依存関係でクライアントもインストールされる。

```bash
$ yum install Percona-XtraDB-Cluster-server-56

```

# 初期設定〜初回起動

## ノード1の初期設定

[オフィシャルドキュメント](https://www.percona.com/doc/percona-xtradb-cluster/5.6/howtos/cenots_howto.html)に基づいて進める。

### 設定ファイル作成 

設定値は色々な考えがあって良いのだが、使用する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`を付与して起動する必要がある。

```bash
$ systemctl start mysql@bootstrap.service
$ journalctl -u mysql
```

## rootユーザへのパスワード設定とユーザー作成

ノード2を作成する前に、オフィシャルにならってレプリケーション用にsstuserを作成する。また、rootユーザーにパスワードを設定しよう。
違和感を覚えるが、sstuserはlocalhostで良いようだ。

```bash
$ 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起動とクラスターへの参加

こちらは単に起動すればいい。

```bash
$ systemctl start mysql
```

自動的に初期同期が行われ、先ほど作ったユーザー情報も同期される。
ただし要注意情報として、これは **初回の動作** であることを覚えておきたい。

基本的にXtraDB Clusterは **InnoDBのみを同期対象としている** ので、MyISAMであるmysql.userテーブルは一部の操作以外は"正確に"同期されない。（DDLであるCREATE, ALTER, GRANT, TRUNCATE, DROPは同期されるが、UPDATE文などは同期されない）
mysql.userテーブルを操作していると、この辺りに「あれ？」って首を傾げることになることに注意だ。


補足：DBエンジンの確認方法

```bash
$ mysql -u root -ppassword

mysql> use information_schema;
mysql> select table_name, engine from tables where table_schema = 'mysql';
```


# 動作確認

期待通り同期されていることを順に確認してみる。

## データベースの作成

ノード1で実施。

```bash
$ 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で確認

```bash
$ 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で実施。

```bash
$ 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でユーザーが複製されていることが確認できる。

```bash
$ 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でパスワードや権限が同期していることを確認。

```bash
$ mysql -u test_user -ppassword
## ローカルホストのtest_userとしてログインできる。


[test_userユーザー]
mysql> show processlist;
## プロセスリストが表示される。

mysql> \q
```

ノード2でユーザーを削除してみる。

```bash
$ mysql -u root -ppassword

[rootユーザー]
mysql> drop user 'test_user'@'localhost';

```

ノード1でつないだままのセッションを使って確認。

```bash
[rootユーザー]
mysql> select user,host,password from mysql.user where user='test_user';
Empty set (0.00 sec)

mysql> \q
```

もちろん改めてログインしようとしてもユーザーは削除されている。

```bash
$ 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`とか)情報量が増える。
詳しい例は[こちら](http://qiita.com/morozumi_h/items/39348f2277f245e31280)を見た方がいい。

### 実行(シングル)

まずはノード1にやってみる。

```bash
$ 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
$ 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で入れる。

```bash
$ yum install haproxy

$ vim /etc/haproxy/haproxy.cfg
```

defaults以下のブロックを以下のように変える。
燦然と輝く **`balance roundrobin`** の1行が、Master-Slave構成ではないことの証明。
この検証ではhaproxyをmysqldノードに相乗りさせているので、haproxyのポートを53306に設定している。


```bash
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
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であること)

```bash
$ 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,
```

ロードバランサの動作確認。

```bash
$ mysql -u test_user -ppassword -h 192.168.200.101 -P 53306 -e'show variables like "server_id";'
```

繋ぐたびに異なるserveridが表示されたらOK。


## 負荷試験

### 試験用ファイル作成

先ほどの試験用ファイルをhaproxyを経由するように編集する。

```bash
$ 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によって両ノードに接続が振られていることを確認する。

```bash
$ watch 'ss -natp | grep 3306'
```


ベンチマーク実行。

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