HAProxyでPercona XtraDB Clusterへのアクセスを分散する


## はじめに

HAProxyでPercona XtraDB Clusterへのアクセスを分散する方法を記述します。

## 環境

* CentOS 6.7
* Percona XtraDB Cluster 5.6
* HAProxy 1.5.4

## サーバ構成

|ホスト名|IPアドレス|役割|
|:-:|:-:|:-:|
|pxclient|192.168.33.11|client/proxy|
|percona01|192.168.33.21|cluster node1|
|percona02|192.168.33.22|cluster node2|
|percona03|192.168.33.23|cluster node3|

## Percona XtraDB Clusterの構築

[CentOS6.7にPercona XtraDB Clusterをインストールする](http://qiita.com/toshiro3/items/67da351a7ea1764c7997)をご参照ください

## clustercheckの設定

* node1

clustercheck用のユーザ作成

```shell
$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.6.24-72.2-56 Percona XtraDB Cluster (GPL), Release rel72.2, Revision 1, WSREP version 25.11, wsrep_25.11

Copyright (c) 2009-2015 Percona LLC and/or its affiliates
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> grant process on *.* to 'clustercheckuser'@'localhost' identified by 'clustercheckpassword!';
Query OK, 0 rows affected (0.06 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.03 sec)

mysql> exit
Bye
```

clustercheckの動作確認

```shell
$ clustercheck
HTTP/1.1 200 OK
Content-Type: text/plain
Connection: close
Content-Length: 40

Percona XtraDB Cluster Node is synced.
```

* node1 〜 node3

xinetdのインストール

```shell
$ sudo yum install xinetd
読み込んだプラグイン:fastestmirror, security
インストール処理の設定をしています
Loading mirror speeds from cached hostfile
epel/metalink                                                                                                                         | 4.9 kB     00:00
 * base: ftp.riken.jp
 * epel: ftp.riken.jp
 * extras: ftp.riken.jp
 * updates: ftp.riken.jp
base                                                                                                                                  | 3.7 kB     00:00
extras                                                                                                                                | 3.4 kB     00:00
percona-release-noarch                                                                                                                |  951 B     00:00
percona-release-noarch/primary                                                                                                        | 5.0 kB     00:00
percona-release-noarch                                                                                                                                 33/33
percona-release-x86_64                                                                                                                |  951 B     00:00
updates                                                                                                                               | 3.4 kB     00:00
依存性の解決をしています
--> トランザクションの確認を実行しています。
---> Package xinetd.x86_64 2:2.3.14-39.el6_4 will be インストール
--> 依存性解決を終了しました。

依存性を解決しました

=============================================================================================================================================================
 パッケージ                         アーキテクチャ                     バージョン                                     リポジトリー                      容量
=============================================================================================================================================================
インストールしています:
 xinetd                             x86_64                             2:2.3.14-39.el6_4                              base                             121 k

トランザクションの要約
=============================================================================================================================================================
インストール         1 パッケージ

総ダウンロード容量: 121 k
インストール済み容量: 259 k
これでいいですか? [y/N]y
パッケージをダウンロードしています:
xinetd-2.3.14-39.el6_4.x86_64.rpm                                                                                                     | 121 kB     00:00
rpm_check_debug を実行しています
トランザクションのテストを実行しています
トランザクションのテストを成功しました
トランザクションを実行しています
  インストールしています  : 2:xinetd-2.3.14-39.el6_4.x86_64                                                                                              1/1
  Verifying               : 2:xinetd-2.3.14-39.el6_4.x86_64                                                                                              1/1

インストール:
  xinetd.x86_64 2:2.3.14-39.el6_4

完了しました!
```

/etc/xinetd.d/mysqlchk

```conf:/etc/xinetd.d/mysqlchk
# 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
        type            = UNLISTED
        port            = 9200
        wait            = no
        user            = nobody
        server          = /usr/bin/clustercheck
        log_on_failure  += USERID
        only_from       = 0.0.0.0/0
        #
        # Passing arguments to clustercheck
        # <user> <pass> <available_when_donor=0|1> <log_file> <available_when_readonly=0|1> <defaults_extra_file>"
        # Recommended: server_args   = user pass 1 /var/log/log-file 0 /etc/my.cnf.local"
        # Compatibility: server_args = user pass 1 /var/log/log-file 1 /etc/my.cnf.local"
        # 55-to-56 upgrade: server_args = user pass 1 /var/log/log-file 0 /etc/my.cnf.extra"
        #
        # recommended to put the IPs that need
        # to connect exclusively (security purposes)
        per_source      = UNLIMITED
}
```

xinetdの起動

```shell
$ sudo service xinetd start
```

xinetd経由のclustercheckの動作確認

```shell
$ curl http://127.0.0.1:9200/
Percona XtraDB Cluster Node is synced.
```

mysqlchkをserviceへ登録

```shell
$ sudo vim /etc/services
(追記)
mysqlchk 9200/tcp # mysqlchk

$ sudo vim /etc/services
[vagrant@percona01 ~]$ sudo service xinetd restart
xinetd を停止中:                                           [  OK  ]
xinetd を起動中:                                           [  OK  ]
```

## HAProxyの設定

* HAProxyのインストール

```shell
$ sudo yum install haproxy
読み込んだプラグイン:fastestmirror
インストール処理の設定をしています
Loading mirror speeds from cached hostfile
 * base: www.ftp.ne.jp
 * epel: ftp.riken.jp
 * extras: ftp.nara.wide.ad.jp
 * updates: www.ftp.ne.jp
依存性の解決をしています
--> トランザクションの確認を実行しています。
---> Package haproxy.x86_64 0:1.5.4-2.el6_7.1 will be インストール
--> 依存性解決を終了しました。

依存性を解決しました

=============================================================================================================================================================
 パッケージ                         アーキテクチャ                    バージョン                                    リポジトリー                        容量
=============================================================================================================================================================
インストールしています:
 haproxy                            x86_64                            1.5.4-2.el6_7.1                               updates                            792 k

トランザクションの要約
=============================================================================================================================================================
インストール         1 パッケージ

総ダウンロード容量: 792 k
インストール済み容量: 2.4 M
これでいいですか? [y/N]y
パッケージをダウンロードしています:
haproxy-1.5.4-2.el6_7.1.x86_64.rpm                                                                                                    | 792 kB     00:00
rpm_check_debug を実行しています
トランザクションのテストを実行しています
トランザクションのテストを成功しました
トランザクションを実行しています
  インストールしています  : haproxy-1.5.4-2.el6_7.1.x86_64                                                                                               1/1
  Verifying               : haproxy-1.5.4-2.el6_7.1.x86_64                                                                                               1/1

インストール:
  haproxy.x86_64 0:1.5.4-2.el6_7.1

完了しました!
```

* socatのインストール

```shell
$ sudo yum instal socat
読み込んだプラグイン:fastestmirror
インストール処理の設定をしています
Loading mirror speeds from cached hostfile
 * base: www.ftp.ne.jp
 * epel: ftp.riken.jp
 * extras: ftp.nara.wide.ad.jp
 * updates: www.ftp.ne.jp
依存性の解決をしています
--> トランザクションの確認を実行しています。
---> Package socat.x86_64 0:1.7.2.3-1.el6 will be インストール
--> 依存性の処理をしています: libreadline.so.5()(64bit) のパッケージ: socat-1.7.2.3-1.el6.x86_64
--> トランザクションの確認を実行しています。
---> Package compat-readline5.x86_64 0:5.2-17.1.el6 will be インストール
--> 依存性解決を終了しました。



依存性を解決しました

=============================================================================================================================================================
 パッケージ                                 アーキテクチャ                   バージョン                                 リポジトリー                    容量


global
=============================================================================================================================================================
インストールしています:
 socat                                      x86_64                           1.7.2.3-1.el6                              epel                           246 k
依存性関連でのインストールをします。:
 compat-readline5                           x86_64                           5.2-17.1.el6                               base                           130 k

トランザクションの要約
=============================================================================================================================================================
インストール         2 パッケージ

総ダウンロード容量: 375 k
インストール済み容量: 1.2 M
これでいいですか? [y/N]y
パッケージをダウンロードしています:
(1/2): compat-readline5-5.2-17.1.el6.x86_64.rpm                                                                                       | 130 kB     00:00
(2/2): socat-1.7.2.3-1.el6.x86_64.rpm                                                                                                 | 246 kB     00:00
-------------------------------------------------------------------------------------------------------------------------------------------------------------
合計                                                                                                                         454 kB/s | 375 kB     00:00
rpm_check_debug を実行しています
トランザクションのテストを実行しています
トランザクションのテストを成功しました
トランザクションを実行しています
  インストールしています  : compat-readline5-5.2-17.1.el6.x86_64                                                                                         1/2
  インストールしています  : socat-1.7.2.3-1.el6.x86_64                                                                                                   2/2
  Verifying               : socat-1.7.2.3-1.el6.x86_64                                                                                                   1/2
  Verifying               : compat-readline5-5.2-17.1.el6.x86_64                                                                                         2/2

インストール:
  socat.x86_64 0:1.7.2.3-1.el6



global
依存性関連をインストールしました:
  compat-readline5.x86_64 0:5.2-17.1.el6

完了しました!
```

* /etc/haproxy/haproxy.cfg

```conf:/etc/haproxy/haproxy.cfg
global
log 127.0.0.1 local0
log 127.0.0.1 local1 notice
maxconn 4096
chroot /usr/share/haproxy
stats socket /var/run/haproxy.sock user root group wheel level admin
user haproxy
group haproxy
daemon

defaults
log global
mode http
option tcplog
option dontlognull
retries 3
option redispatch
maxconn 2000
timeout connect 5000
timeout client 50000
timeout server 50000

frontend pxc-front
bind *:3307
mode tcp
default_backend pxc-back

frontend stats-front
bind *:80
mode http
default_backend stats-back

frontend pxc-onenode-front
bind *:3306
mode tcp
default_backend pxc-onenode-back

backend pxc-back
mode tcp
balance leastconn
option httpchk
server c1 192.168.33.21:3306 check port 9200 inter 12000 rise 3 fall 3
server c2 192.168.33.22:3306 check port 9200 inter 12000 rise 3 fall 3
server c3 192.168.33.23:3306 check port 9200 inter 12000 rise 3 fall 3

backend stats-back
mode http
balance roundrobin
stats uri /haproxy/stats
stats auth pxcstats:secret

backend pxc-onenode-back
mode tcp
balance leastconn
option httpchk
server c1 192.168.33.21:3306 check port 9200 inter 12000 rise 3 fall 3
server c2 192.168.33.22:3306 check port 9200 inter 12000 rise 3 fall 3 backup
server c3 192.168.33.23:3306 check port 9200 inter 12000 rise 3 fall 3 backup
```

* HAProxyの起動

```shell
$ sudo service haproxy start
haproxy を起動中:                                          [  OK  ]
```

* HAProxyの状態確認

```shell
$ sudo sh -c 'echo "show stat" | socat unix-connect:/var/run/haproxy.sock stdio'
# 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,
pxc-front,FRONTEND,,,0,0,2000,0,0,0,0,0,0,,,,,OPEN,,,,,,,,,1,2,0,,,,0,0,0,0,,,,,,,,,,,0,0,0,,,0,0,0,0,,,,,,,,
stats-front,FRONTEND,,,0,0,2000,0,0,0,0,0,0,,,,,OPEN,,,,,,,,,1,3,0,,,,0,0,0,0,,,,0,0,0,0,0,0,,0,0,0,,,0,0,0,0,,,,,,,,
pxc-onenode-front,FRONTEND,,,0,0,2000,0,0,0,0,0,0,,,,,OPEN,,,,,,,,,1,4,0,,,,0,0,0,0,,,,,,,,,,,0,0,0,,,0,0,0,0,,,,,,,,
pxc-back,c1,0,0,0,0,,0,0,0,,0,,0,0,0,0,UP,1,1,0,0,0,520,0,,1,5,1,,0,,2,0,,0,L7OK,200,19,,,,,,,0,,,,0,0,,,,,-1,OK,,0,0,0,0,
pxc-back,c2,0,0,0,0,,0,0,0,,0,,0,0,0,0,UP,1,1,0,1,1,47,468,,1,5,2,,0,,2,0,,0,L7OK,200,15,,,,,,,0,,,,0,0,,,,,-1,OK,,0,0,0,0,
pxc-back,c3,0,0,0,0,,0,0,0,,0,,0,0,0,0,UP,1,1,0,1,1,15,498,,1,5,3,,0,,2,0,,0,L7OK,200,34,,,,,,,0,,,,0,0,,,,,-1,OK,,0,0,0,0,
pxc-back,BACKEND,0,0,0,0,200,0,0,0,0,0,,0,0,0,0,UP,3,3,0,,0,520,0,,1,5,0,,0,,1,0,,0,,,,,,,,,,,,,,0,0,0,0,0,0,-1,,,0,0,0,0,
stats-back,BACKEND,0,0,0,0,200,0,0,0,0,0,,0,0,0,0,UP,0,0,0,,0,520,0,,1,6,0,,0,,1,0,,0,,,,0,0,0,0,0,0,,,,,0,0,0,0,0,0,-1,,,0,0,0,0,
pxc-onenode-back,c1,0,0,0,0,,0,0,0,,0,,0,0,0,0,UP,1,1,0,0,0,520,0,,1,7,1,,0,,2,0,,0,L7OK,200,20,,,,,,,0,,,,0,0,,,,,-1,OK,,0,0,0,0,
pxc-onenode-back,c2,0,0,0,0,,0,0,0,,0,,0,0,0,0,UP,1,0,1,1,1,46,466,,1,7,2,,0,,2,0,,0,L7OK,200,17,,,,,,,0,,,,0,0,,,,,-1,OK,,0,0,0,0,
pxc-onenode-back,c3,0,0,0,0,,0,0,0,,0,,0,0,0,0,UP,1,0,1,1,1,15,495,,1,7,3,,0,,2,0,,0,L7OK,200,36,,,,,,,0,,,,0,0,,,,,-1,OK,,0,0,0,0,
pxc-onenode-back,BACKEND,0,0,0,0,200,0,0,0,0,0,,0,0,0,0,UP,1,1,2,,0,520,0,,1,7,0,,0,,1,0,,0,,,,,,,,,,,,,,0,0,0,0,0,0,-1,,,0,0,0,0,
```

## 動作確認

* pxclientからHAProxy経由でアクセス

```shell
$ mysql -u pxclient -p -P 3307 -h 127.0.0.1 -e 'show variables like "%hostname%"'
Enter password:
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| hostname      | percona01 |
+---------------+-----------+

$ mysql -u pxclient -p -P 3307 -h 127.0.0.1 -e 'show variables like "%hostname%"'
Enter password:
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| hostname      | percona02 |
+---------------+-----------+

$ mysql -u pxclient -p -P 3307 -h 127.0.0.1 -e 'show variables like "%hostname%"'
Enter password:
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| hostname      | percona03 |
+---------------+-----------+
```

## 参考

* [Setting up PXC reference architecture with HAProxy](https://www.percona.com/doc/percona-xtradb-cluster/5.6/howtos/virt_sandbox.html)