Go to Qiita Advent Calendar 2024 Top
LoginSignup
15
14

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@youyo_in株式会社ヘプタゴン

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

Last updated at Posted at 2015-06-15

やること

  • 手順
    • 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

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というズバリなものがあるが今回は忘れる(あとから気づいた
15
14
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
15
14

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?