MySQL+MHA+HAproxy+consul環境構築ログ
MySQL
MHAで使う万能ユーザを用意します。
grant all privileges on *.* to mha@'10.%' identified by 'mhapassword';
mysqlチェック用ユーザを作成
grant select on *.* to haproxy@'10.%';
]
MHA
準備
mha manager
ssh-keygen -t rsa -f /root/.ssh/id_rsa -q -N "" cat /root/.ssh/id_rsa.pub >> /root/.ssh/authorized_keys
mha node(master + slave db全台)
mkdir /root/.ssh/ vim /root/.ssh/id_rsa vim /root/.ssh/authorized_keys chmod 400 /root/.ssh/id_rsa chmod 600 /root/.ssh/authorized_keys chmod 700 /root/.ssh/
MHA install
manager
apt-get install libdbd-mysql-perl apt-get install libconfig-tiny-perl apt-get install liblog-dispatch-perl apt-get install libparallel-forkmanager-perl wget https://mysql-master-ha.googlecode.com/files/mha4mysql-manager_0.55-0_all.deb wget https://mysql-master-ha.googlecode.com/files/mha4mysql-node_0.54-0_all.deb dpkg -i mha4mysql-node_0.54-0_all.deb dpkg -i mha4mysql-manager_0.55-0_all.deb
node(master + slave db全台)
apt-get install libdbd-mysql-perl wget https://mysql-master-ha.googlecode.com/files/mha4mysql-node_0.54-0_all.deb dpkg -i mha4mysql-node_0.54-0_all.deb
config
コンフィグファイル作成
/etc/mha.conf
[server default] user=mha password=mhapass manager_workdir=/var/lib/mha manager_log=/var/log/mha.log remote_workdir=/var/lib/mha repl_user=repl repl_password=replpass ssh_user=root ssh_port=20022 master_ip_failover_script=/var/lib/mha/master_ip_failover_script.sh [server1] hostname=192.168.0.182 [server2] hostname=192.168.0.76 [server3] hostname=192.168.0.181
/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: -- "$@") 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
確認
SSH接続
masterha_check_ssh --conf=/etc/mha.conf
下記のように表示されればOK
[info] All SSH connection tests passed successfully.
mysql レプリケーション確認
masterha_check_repl --conf=/etc/mha.conf
下記のように表示されればOK
192.168.0.76 (current master) +--192.168.0.182 +--192.168.0.181 Mon Aug 3 08:03:35 2015 - [info] Checking replication health on 192.168.0.182.. Mon Aug 3 08:03:35 2015 - [info] ok. Mon Aug 3 08:03:35 2015 - [info] Checking replication health on 192.168.0.181.. Mon Aug 3 08:03:35 2015 - [info] ok. Mon Aug 3 08:03:35 2015 - [warning] master_ip_failover_script is not defined. Mon Aug 3 08:03:35 2015 - [warning] shutdown_script is not defined. Mon Aug 3 08:03:35 2015 - [info] Got exit code 0 (Not master dead). MySQL Replication Health is OK.
起動
slave追加時
slaveサーバ追加後、/etc/mha.confの[server x]を追加、masterha_managerのプロセスを再起動すればOK
HAproxy
インストール(全APPサーバにて)
apt-get update apt-get install haproxy
config
/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
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/
起動スクリプト
は無いのでupstartに登録しましょう
mhaサーバ(192.168.0.77)
description "consul" author "Your Name <uematsu@kiheitai.co.jp>" start on runlevel [2345] stop on runlevel [016] chdir /var/lib/consul respawn limit 5 60 exec consul agent -server -bootstrap-expect 1 -client=127.0.0.1 -dc=mha -node=`hostname` -data-dir=/var/lib/consul -bind=0.0.0.0 >> /var/log/consul.log 2>&1
mysqlサーバ
description "consul" author "Your Name <uematsu@kiheitai.co.jp>" start on runlevel [2345] stop on runlevel [016] chdir /var/lib/consul respawn limit 5 60 exec 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.0.77 >> /var/log/consul.log 2>&1
appサーバ
description "consul" author "Your Name <uematsu@kiheitai.co.jp>" start on runlevel [2345] stop on runlevel [016] chdir /var/lib/consul respawn limit 5 60 exec consul agent -dc=mha -node=`hostname` -data-dir=/var/lib/^Cnsul -bind=0.0.0.0 -client=127.0.0.1 -join=192.168.0.77
ちなみに一回落とすとleaderを選出する機能がちゃんと働かないので、データディレクトリを削除してからあげ直します。
rm -fr /var/lib/consul/*
mysql チェック
{ "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" } } }
consul-template
インストール(全APPサーバにて)
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
config
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}}
起動
consul-template -consul=127.0.0.1:8500 -template=/etc/haproxy/haproxy.cfg.ctmpl:/etc/haproxy/haproxy.cfg:"service haproxy reload"
起動シーケンス
- 全ノードでconsul起動
- appでconsul-template起動
- mhaノードでmha manager起動
- master-db情報を初回登録する
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