MHAとHAproxyとconsulとconsul-templateでマスター切り替え&参照負荷分散

## やること

- 手順
	- MySQLレプリケーション(GTID mode)
	- MHAで冗長化
	- HAproxy,consul,consul-templateでマスター切り替え&負荷分散

## やらないこと

- MHAやconsulなどの説明

※ ただのメモ書きなのです

## 手順

### 環境

- centos 6.6
- mysql 5.6
- haproxy 1.5.2
- consul 0.5.2
- consul-template 0.10.0

### 構成

- mha-manager 1台
- mysql-server 3台
- app-server 数台

![mha-haproxy.png](https://qiita-image-store.s3.amazonaws.com/0/14975/147c5073-bfbe-78b6-cde3-ed151c52bffd.png)


### MySQLレプリケーション(GTID mode)

- mysql-serverすべて

```
yum install -y http://dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm
yum install -y mysql-server mysql

vim /etc/my.cnf
```

``` /etc/my.cnf
server-id = 1 # てきとうに
log-bin = mysqld-bin
log-slave-updates
binlog_format = MIXED
expire_logs_days = 2
gtid-mode=ON
enforce-gtid-consistency
```

```
service mysqld start
chkconfig mysqld on
```

- マスターにする1台にて

```
mysql> grant replication slave on *.* to repl@'192.168.%.%' identified by 'replipass';
mysql> flush privileges;

mysqldump --all-databases --triggers --routines --events > fulldump.sql
```

- スレーブにて

```
mysql < fulldump.sql
mysql> flush privileges;
mysql> change master to master_host = '192.168.151.101', master_port=3306, master_user='repl', master_password='replipass', master_auto_position=1;
mysql> start slave;
```

### MHAで冗長化

- マスターノードで

MHA用管理ユーザー作成(rootでもいいけど

```
mysql> grant all privileges on *.* to mha@'192.168.%.%' identified by 'mhapassword';
mysql> flush privileges;
```

- mha-nodeで

```
wget https://72003f4c60f5cc941cd1c7d448fc3c99e0aebaa8.googledrive.com/host/0B1lu97m8-haWeHdGWXp0YVVUSlk/mha4mysql-node-0.56-0.el6.noarch.rpm
yum localinstall -y mha4mysql-node-0.56-0.el6.noarch.rpm
```

- mha-managerで

```
yum install -y http://dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm epel-release
yum install -y mysql

wget https://72003f4c60f5cc941cd1c7d448fc3c99e0aebaa8.googledrive.com/host/0B1lu97m8-haWeHdGWXp0YVVUSlk/mha4mysql-node-0.56-0.el6.noarch.rpm
wget https://72003f4c60f5cc941cd1c7d448fc3c99e0aebaa8.googledrive.com/host/0B1lu97m8-haWeHdGWXp0YVVUSlk/mha4mysql-manager-0.56-0.el6.noarch.rpm
yum localinstall -y mha4mysql-node-0.56-0.el6.noarch.rpm mha4mysql-manager-0.56-0.el6.noarch.rpm

vim /etc/mha.conf
ln -s /etc/mha.conf /etc/masterha_default.cnf
```

``` /etc/mha.conf
[server default]
user=mha
password=mhapassword
manager_workdir=/var/lib/mha
manager_log=/var/log/mha.log
remote_workdir=/var/lib/mha
repl_user=repl
repl_password=replipass
ssh_user=root
ssh_port=22
#master_ip_failover_script=/var/lib/mha/master_ip_failover_script.sh
#master_ip_online_change_script=/var/lib/mha/master_ip_online_change_script.sh

[server1]
hostname=192.168.151.101
[server2]
hostname=192.168.151.102
[server3]
hostname=192.168.151.103
```

```
ssh-keygen -t rsa -f /root/.ssh/id_rsa -q -N "" -C ""
cat /root/.ssh/id_rsa.pub >> /root/.ssh/authorized_keys

※ 各mysql-serverに配布する

cat <<EOL> /root/.ssh/id_rsa
EOL
echo '' >> /root/.ssh/authorized_keys
chmod 600 /root/.ssh/authorized_keys
chmod 400 /root/.ssh/id_rsa
chmod 700 /root/.ssh/
```

- 接続確認

```
masterha_check_ssh --conf=/etc/mha.conf
masterha_check_repl --conf=/etc/mha.conf
```

- 起動

```
masterha_manager --conf=/etc/mha.conf --ignore_last_failover
```

masterを落とすとslaveが昇格してくるはず。(10秒~くらいだった)

### HAproxy,consul,consul-templateでマスター切り替え&負荷分散

- status-check用のユーザー作成
- マスターノードで

```
mysql> grant select on *.* to haproxy@'192.168.%.%';
mysql> grant select on *.* to haproxy@'127.0.0.1';
```

- 全サーバにconsul入れる

```
cd /usr/local/src/
wget https://dl.bintray.com/mitchellh/consul/0.5.2_linux_amd64.zip
unzip 0.5.2_linux_amd64.zip
mv consul /usr/local/bin/
```

- mha-managerで`consul agent -server `起動

```
mkdir /var/lib/consul
consul agent -server -bootstrap -client=127.0.0.1 -dc=mha -node=`hostname` -data-dir=/var/lib/consul -bind=0.0.0.0
```

- mha-nodeでも`consul agent -server `起動

```
mkdir /var/lib/consul /etc/consul
vim /etc/consul/mysql.json
consul agent -server -dc=mha -node=`hostname` -config-dir=/etc/consul -data-dir=/var/lib/consul -bind=0.0.0.0 -client=127.0.0.1 -join=192.168.151.110
```

``` /etc/consul/mysql.json
{
	"service": {
		"name": "mysql",
		"tags": ["mysql"],
		"port": 3306,
		"check": {
			"script": "mysql -u haproxy -h 127.0.0.1 -P 3306 -e 'select 1' >/dev/null 2>&1",
			"interval": "5s"
		}
	}
}
```

- mha-managerに`master_ip_failover_script`,`master_ip_online_change_script`を仕込む

```
vim /var/lib/mha/master_ip_failover_script.sh
vim /var/lib/mha/master_ip_online_change_script.sh
chmod 700 /var/lib/mha/master_ip_failover_script.sh
chmod 700 /var/lib/mha/master_ip_online_change_script.sh
vim /etc/mha.conf
```

``` /var/lib/mha/master_ip_failover_script.sh
#!/bin/bash -u

OPT=$(getopt -q -o a -l command:,ssh_user:,orig_master_host:,orig_master_ip:,orig_master_port:,new_master_host:,new_master_ip:,new_master_port:,new_master_user:,new_master_password: -- "$@")
if [ $? -ne 0 ]; then
  exit 1
fi

eval set -- "$OPT"
MODE=none
NEW_IP=0.0.0.0
NEW_PORT=0

while true
do
	case "$1" in
	--command)
		if [ "$2" == "start" ]; then
			MODE=$2
		elif [ "$2" == "status" ]; then
			exit 0
		elif [ "$2" == "stop" -o "$2" == "stopssh" ]; then
			exit 0
		else
			exit 0
		fi
		shift 2
		;;
	--new_master_ip)
		if [ $MODE == "start" ]; then
			NEW_IP=$2
		fi
		shift 2
		;;
	--new_master_port)
		if [ $MODE == "start" ]; then
			NEW_PORT=$2
		fi
		shift 2
		;;
	--)
		shift
		break
		;;
	*)
		shift
		;;
	esac
done

case "$MODE" in
start)
	# 新マスター登録処理
	curl -q -XPUT -d "${NEW_IP}" http://127.0.0.1:8500/v1/kv/service/mha/ip
	curl -q -XPUT -d "${NEW_PORT}" http://127.0.0.1:8500/v1/kv/service/mha/port
	exit 0
	;;
*)
	exit 0
	;;
esac
```

``` /var/lib/mha/master_ip_online_change_script.sh
#!/bin/bash -u

OPT=$(getopt -o a -l command:,orig_master_host:,orig_master_ip:,orig_master_port:,orig_master_user:,orig_master_ssh_user:,orig_master_password:,new_master_host:,new_master_ip:,new_master_port:,new_master_user:,new_master_password:,new_master_ssh_user:,orig_master_ssh_port:,new_master_ssh_port:,orig_master_is_new_slave -- "$@")
if [ $? -ne 0 ]; then
  echo $OPT
  exit 1
fi

eval set -- "$OPT"
MODE=none
NEW_IP=0.0.0.0
NEW_PORT=0

while true
do
	case "$1" in
	--command)
		if [ "$2" == "start" ]; then
			MODE=$2
		elif [ "$2" == "stop" -o "$2" == "stopssh" ]; then
			exit 0
		else
			exit 0
		fi
		shift 2
		;;
	--new_master_ip)
		if [ $MODE == "start" ]; then
			NEW_IP=$2
		fi
		shift 2
		;;
	--new_master_port)
		if [ $MODE == "start" ]; then
			NEW_PORT=$2
		fi
		shift 2
		;;
	--)
		shift
		break
		;;
	*)
		shift
		;;
	esac
done

case "$MODE" in
start)
	curl -q -XPUT -d "${NEW_IP}" http://127.0.0.1:8500/v1/kv/service/mha/ip
	curl -q -XPUT -d "${NEW_PORT}" http://127.0.0.1:8500/v1/kv/service/mha/port
	exit 0
	;;
*)
	exit 0
	;;
esac
```

※ `master_ip_failover_script.sh` だけで十分な気がする。何で分けたかは忘れたのでそのまま。

``` /etc/mha.conf
[server default]
user=mha
password=mhapassword
manager_workdir=/var/lib/mha
manager_log=/var/log/mha.log
remote_workdir=/var/lib/mha
repl_user=repl
repl_password=replipass
ssh_user=root
ssh_port=22
master_ip_failover_script=/var/lib/mha/master_ip_failover_script.sh
master_ip_online_change_script=/var/lib/mha/master_ip_online_change_script.sh

[server1]
hostname=192.168.151.101
[server2]
hostname=192.168.151.102
[server3]
hostname=192.168.151.103
```

- mha-manager再起動

```
masterha_manager --conf=/etc/mha.conf --ignore_last_failover
```

- consulのkvsにマスターの情報を初回登録する

```
curl -XPUT -d '192.168.151.101' http://127.0.0.1:8500/v1/kv/service/mha/ip
curl -XPUT -d '3306' http://127.0.0.1:8500/v1/kv/service/mha/port
```

- app-serverにhaproxyいれる

```
yum install haproxy
vim /etc/haproxy/haproxy.cfg
service haproxy start
chkconfig haproxy on
```

``` /etc/haproxy/haproxy.cfg
global
	log		127.0.0.1 local2
	chroot		/var/lib/haproxy
	pidfile		/var/run/haproxy.pid
	maxconn		10000
	user		haproxy
	group		haproxy
	daemon
	stats socket	/var/lib/haproxy/stats

defaults
	mode		tcp
	log		global
	option		tcplog
	retries		3
	timeout connect	10s
	timeout client	1m
	timeout server	1m
```

- app-serverで`consul agent`を起動

```
consul agent -dc=mha -node=`hostname` -data-dir=/var/lib/consul -bind=0.0.0.0 -client=127.0.0.1 -join=192.168.151.110
```

- app-serverに`consul-template`を入れて動かす

```
cd /usr/local/src/
wget https://github.com/hashicorp/consul-template/releases/download/v0.10.0/consul-template_0.10.0_linux_amd64.tar.gz
tar xvzf consul-template_0.10.0_linux_amd64.tar.gz
mv consul-template_0.10.0_linux_amd64/consul-template /usr/local/bin/
chown root: /usr/local/bin/consul-template
vim /etc/haproxy/haproxy.cfg.ctmpl
consul-template -consul=127.0.0.1:8500 -template=/etc/haproxy/haproxy.cfg.ctmpl:/etc/haproxy/haproxy.cfg:"service haproxy reload"
```

``` /etc/haproxy/haproxy.cfg.ctmpl
global
	log		127.0.0.1 local2
	chroot		/var/lib/haproxy
	pidfile		/var/run/haproxy.pid
	maxconn		10000
	user		haproxy
	group		haproxy
	daemon
	stats socket	/var/lib/haproxy/stats

defaults
	mode		tcp
	log		global
	option		tcplog
	retries		3
	timeout connect	10s
	timeout client	1m
	timeout server	1m

listen mysql-master
	bind 127.0.0.1:3306
	mode tcp
	option mysql-check user haproxy
	server master {{key "service/mha/ip"}}:{{key "service/mha/port"}} check port {{key "service/mha/port"}} inter 2000 fall 5

listen mysql-slave
	bind 127.0.0.1:3307
	mode tcp
	option mysql-check user haproxy
	balance leastconn{{range service "mysql"}}
	server {{.Node}} {{.Address}}:{{.Port}} check port {{.Port}} inter 2000 fall 5{{end}}
```

## 完成

- 各app-serverの`127.0.0.1:3306`がマスターノードに接続し、`127.0.0.1:3307`がmysql-server全てに分散するようになっている
- マスターが落ちると別ノードがマスターに昇格し、haproxyでの振り分け先も切り替わる
- スレーブが止まった場合、consulの`status check`で検知されhaproxyから外れる
- mha-managerはSPOF。ちゃんと監視して気づければちょっとぐらい止まってもいいと思ってるので気にしない。(そのうち冗長化するかも
- `# masterha_master_switch --master_state=alive --conf=/etc/mha.conf --interactive=0 --orig_master_is_new_slave` このコマンドで一瞬でマスター切り替わる
- centos7系でも7系のパッケージ使えばちゃんと動いた
- `consul-haproxy`というズバリなものがあるが今回は忘れる(あとから気づいた
	- http://blog.cloudpack.jp/2014/08/14/consul-haproxy-a-little-tips/
	- https://github.com/hashicorp/consul-haproxy