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 数台

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