lastcoolnameleft
7/26/2018 - 3:54 PM
Centos6 + MHA 0.54 + MySQL
Centos6 + MHA 0.54 + MySQL
#!/bin/bash
set -e
set -o xtrace
# https://stackoverflow.com/questions/8473121/execute-command-without-keeping-it-in-history
export HISTCONTROL=ignorespace
# https://www.percona.com/blog/2016/09/02/mha-quickstart-guide/
# HA Proxy
# export MYSQL_MASTER_IP=`docker inspect mysql-master | jq '.[0].NetworkSettings.Networks.bridge.IPAddress' -r` && echo $MYSQL_MASTER_IP
# export MYSQL_SLAVE1_IP=`docker inspect slave-1 | jq '.[0].NetworkSettings.Networks.bridge.IPAddress' -r` && echo $MYSQL_SLAVE1_IP
# export MYSQL_SLAVE2_IP=`docker inspect slave-2 | jq '.[0].NetworkSettings.Networks.bridge.IPAddress' -r` && echo $MYSQL_SLAVE2_IP
# docker run -v $HOME/tmp/ps:/tmp/ps -it --name haproxy -e MYSQL_SLAVE1_IP=$MYSQL_SLAVE1_IP -e MYSQL_SLAVE2_IP=$MYSQL_SLAVE2_IP -e MYSQL_MASTER_IP=$MYSQL_MASTER_IP --rm centos:centos6 bash
# cd /tmp/ps && ./haproxy.sh
# TODO: Change before using in prod
MYSQL_PASSWORD=${MYSQL_PASSWORD:=password}
FETCH_RPM=${FETCH_RPM:=0}
OS_USER=${OS_USER:=root}
MYSQL_REPL_USER=${MYSQL_REPL_USER:=repluser}
MYSQL_REPL_PASS=${MYSQL_REPL_PASS:=replpass}
HAPROXY_IP=${HAPROXY_IP:=10.0.0.9} # 10.0.0.9
MYSQL_MASTER_IP=${MYSQL_MASTER_IP:=172.17.0.3} # 10.0.0.10
MYSQL_SLAVE1_IP=${MYSQL_SLAVE1_IP:=172.17.0.4} # 10.0.0.11
MYSQL_SLAVE2_IP=${MYSQL_SLAVE2_IP:=172.17.0.5} # 10.0.0.12
# Just used for testing when mounting volume with these existing files
if [[ $FETCH_RPM -eq 1 ]];then
wget http://dl.fedoraproject.org/pub/epel/6/i386/epel-release-6-8.noarch.rpm
wget http://repo.mysql.com/mysql-community-release-el6-5.noarch.rpm
wget https://storage.googleapis.com/google-code-archive-downloads/v2/code.google.com/mysql-master-ha/mha4mysql-manager-0.54-0.el6.noarch.rpm
wget https://storage.googleapis.com/google-code-archive-downloads/v2/code.google.com/mysql-master-ha/mha4mysql-node-0.54-0.el6.noarch.rpm
wget http://www.haproxy.org/download/1.6/src/haproxy-1.6.3.tar.gz
tar zxvf haproxy-1.6.3.tar.gz
fi
# install dependencies repo
yum install -y epel-release-6-8.noarch.rpm
# install mysql-server
rpm -Uvh mysql-community-release-el6-5.noarch.rpm
yum install -y mysql
#configure mha manager
mkdir -p /var/log/masterha/app1
mkdir -p /script/masterha
cat > /etc/app1.cnf <<EOF
[server default]
# mysql user and password
user=$MYSQL_REPL_USER
password=$MYSQL_REPL_USER
ssh_user=$OS_USER
master_binlog_dir= /var/log/mysql
# working directory on the manager
manager_workdir=/var/log/masterha/app1
# manager log file
manager_log=/var/log/masterha/app1/app1.log
# working directory on MySQL servers
remote_workdir=/var/log/masterha/app1
secondary_check_script= masterha_secondary_check -s $MYSQL_SLAVE1_IP -s $MYSQL_SLAVE2_IP
ping_interval=1
#master_ip_failover_script=/script/masterha/master_ip_failover
#shutdown_script= /script/masterha/power_manager
#report_script= /script/masterha/send_report
[server1]
hostname=$MYSQL_MASTER_IP
[server2]
hostname=$MYSQL_SLAVE1_IP
candidate_master=1
[server3]
hostname=$MYSQL_SLAVE2_IP
no_master=1
EOF
#install haproxy
cd haproxy-1.6.3
yum install -y make gcc
make TARGET=linux2628 PREFIX=/usr/local/haproxy
make install PREFIX=/usr/local/haproxy
cd -
cat > /usr/local/haproxy/haproxy.cfg <<EOF
global
maxconn 4096
chroot /usr/local/haproxy
uid 99
gid 99
daemon
pidfile /usr/local/haproxy/haproxy.pid
defaults
log global
log 127.0.0.1 local3
mode tcp
option tcplog
option dontlognull
option httpclose
retries 3
option redispatch
maxconn 2000
timeout connect 5000
timeout client 50000
timeout server 50000
frontend mysqlwrite-in
bind *:3306
mode tcp
option tcplog
log global
default_backend mysqlwritepool
frontend mysqlread-in
bind *:3307
mode tcp
option tcplog
log global
default_backend mysqlreadpool
backend mysqlwritepool
balance roundrobin
server mysql01 $MYSQL_MASTER_IP:3306 weight 5 check inter 2000 rise 2 fall 3
backend mysqlreadpool
balance roundrobin
server $MYSQL_SLAVE1_IP $MYSQL_SLAVE1_IP:3306 weight 5 check inter 2000 rise 2 fall 3
server $MYSQL_SLAVE2_IP $MYSQL_SLAVE2_IP:3306 weight 5 check inter 2000 rise 2 fall 3
EOF
cat > /etc/my-repl.cnf <<EOF
[mysql]
user=$MYSQL_REPL_USER
password=$MYSQL_REPL_PASS
EOF
#master ip check script
cat > /usr/local/haproxy/master_ip_check.sh <<EOF
#!/usr/bin/env bash
haproxyDir=/usr/local/haproxy
haproxyConFile=/usr/local/haproxy/haproxy.cfg
haproxyPidFile=/usr/local/haproxy/haproxy.pid
masterIP=\$1
candiMasterIP=\$2
usage() {
echo "execution method: \`basename \$0\` masterip candimasterip
Most import thing is the 1st parameter should be the mysql running master ip address; the 2nd parameter is the candidate master ip address
"
}
if [[ \$# -ne 2 ]] ;then
usage
exit
fi
while :
do
result=\`mysql --defaults-extra-file=/etc/my-repl.cnf -h \$candiMasterIP -e "show slave status\G"\`
if [[ -n \$result ]];then #the candidate master is still slave
slaveRunningNumber=\`mysql --defaults-extra-file=/etc/my-repl.cnf -h \$candiMasterIP -e "show slave status\G" |grep -i "running: Yes" |wc -l\`
if [[ \$slaveRunningNumber -ne 2 ]];then #master-slave has problem
echo "checked at \$(date +"%F %H:%M:%S"):candidate master \$candiMasterIP slave status is not OK, please check ! "
fi
else #the candidate master is no longer slave
sed -i "s/\$masterIP:3306/\$candiMasterIP:3306/" \$haproxyConFile #master ip failover
\$haproxyDir/sbin/haproxy -f \$haproxyConFile -st \`cat \$haproxyPidFile\` #reload
if [[ \$? -eq 0 ]];then
echo "at \$(date +"%F %H:%M:%S"): master ip failover, \$candiMasterIP becomes the master, reloaded haproxy process. "
exit #quit with mha manager the same time
else
echo "at \$(date +"%F %H:%M:%S"): reloaded haproxy process failed. Please check haproxy ! quit the shell script ! "
exit
fi
fi
sleep 1
done
EOF
#slave ip check script
cat > /usr/local/haproxy/slave_ip_check.sh <<EOF
#!/usr/bin/env bash
haproxyDir=/usr/local/haproxy
haproxyConFile=/usr/local/haproxy/haproxy.cfg
haproxyPidFile=/usr/local/haproxy/haproxy.pid
mysqlBackendReadPool=mysqlreadpool
MYSQL_REPL_USER=${MYSQL_REPL_USER:=repluser}
MYSQL_REPL_PASS=${MYSQL_REPL_PASS:=replpass}
usage() {
echo "execution method: \`basename \$0\` slaveip1 slaveip2 ...
"
}
restartHaproxy() {
\$haproxyDir/sbin/haproxy -f \$haproxyConFile -st \`cat \$haproxyPidFile\` #reload
if [[ \$? -eq 0 ]];then
echo "at \$(date +"%F %H:%M:%S"): reloaded haproxy process. "
else
echo "at \$(date +"%F %H:%M:%S"): reloaded haproxy process failed. Please check haproxy ! "
fi
}
if [[ \$# -eq 0 ]] ;then
usage
exit
fi
while :
do
for slaveIP in "\$@"
do
result=\`mysql --defaults-extra-file=/etc/my-repl.cnf -h \$slaveIP -e "show slave status\G"\`
slaveResult=\`sed -n "/^backend.*\$mysqlBackendReadPool/,/^backend/{/\$slaveIP/p}" \$haproxyConFile\`
if [[ -n \$result ]];then #the slave is still slave
slaveRunningNumber=\`mysql --defaults-extra-file=/etc/my-repl.cnf -h \$slaveIP -e "show slave status\G" |grep -i "running: Yes" |wc -l\`
if [[ \$slaveRunningNumber -ne 2 ]];then #master-slave has problem
echo "checked at \$(date +"%F %H:%M:%S"):MySQL \$slaveIP slave status is not OK, please check ! "
elif [[ -z \$slaveResult ]];then #slave is not in the read pool
#add the slave in the read pool
sed -i "/^backend.*\$mysqlBackendReadPool/,/^backend/{:a;N;s/balance.*/&\n server \$slaveIP \$slaveIP:3306 weight 5 check inter 2000 rise 2 fall 3/}" \$haproxyConFile
restartHaproxy
fi
else #the slave is no longer slave
sed -i "/^backend.*\$mysqlBackendReadPool/,/^backend/{/\$slaveIP/d}" \$haproxyConFile #remove from the read pool
restartHaproxy
exit #quit with mha manager the same time
fi
sleep 1
done
done
EOF
# Carry-over from https://github.com/Azure/azure-quickstart-templates/blob/master/mysql-mha-haproxy-ubuntu/scripts/haproxy.sh#L218-L219
# Oh boy, do we still need this?
#sed -i '/my \$msg = \$args{message};$/a $msg = \"\" unless($msg);' /usr/share/perl5/vendor_perl/MHA/ManagerConst.pm
#sed -i 's/^\($msg = "" unless($msg);\)$/ \1/' /usr/share/perl5/vendor_perl/MHA/ManagerConst.pm
chown ${OS_USER}:${OS_USER} /etc/app1.cnf
chmod 600 /etc/app1.cnf
chmod 600 /etc/my-repl.cnf
chown ${OS_USER}:${OS_USER} -R /var/log/masterha/
chown ${OS_USER}:${OS_USER} /usr/local/haproxy/master_ip_check.sh
chown ${OS_USER}:${OS_USER} /usr/local/haproxy/slave_ip_check.sh
chmod 700 /usr/local/haproxy/master_ip_check.sh
chmod 700 /usr/local/haproxy/slave_ip_check.sh
yum install -y mha4mysql-node-0.54-0.el6.noarch.rpm
yum install -y mha4mysql-manager-0.54-0.el6.noarch.rpm
# Testing
# /usr/local/haproxy/master_ip_check.sh $MYSQL_MASTER_IP $MYSQL_SLAVE1_IP
# /usr/local/haproxy/slave_ip_check.sh $MYSQL_SLAVE1_IP $MYSQL_SLAVE2_IP#!/bin/bash
set -e
# Master:
# docker run -v $HOME/tmp/ps:/tmp/ps -it --name mysql-master --rm centos:centos6 bash
# cd /tmp/ps && MYSQL_SERVER_ID=1 ./mysql.sh
# Slave 1:
# export MYSQL_MASTER_IP=`docker inspect mysql-master | jq '.[0].NetworkSettings.Networks.bridge.IPAddress' -r` && echo $MYSQL_MASTER_IP
# docker run -v $HOME/tmp/ps:/tmp/ps -it --name slave-1 -e MYSQL_MASTER_IP=$MYSQL_MASTER_IP --rm centos:centos6 bash
# cd /tmp/ps && MYSQL_SERVER_ID=2 ./mysql.sh
# Slave 2:
# export MYSQL_MASTER_IP=`docker inspect mysql-master | jq '.[0].NetworkSettings.Networks.bridge.IPAddress' -r` && echo $MYSQL_MASTER_IP
# docker run -v $HOME/tmp/ps:/tmp/ps -it --name slave-2 -e MYSQL_MASTER_IP=$MYSQL_MASTER_IP --rm centos:centos6 bash
# cd /tmp/ps && MYSQL_SERVER_ID=3 ./mysql.sh
MYSQL_CONF_FILE=${MYSQL_CONF_FILE:=/etc/my.cnf}
MYSQL_SERVER_ID=${MYSQL_SERVER_ID:=0}
MYSQL_REPL_USER=${MYSQL_REPL_USER:=repluser}
MYSQL_REPL_PASS=${MYSQL_REPL_PASS:=replpass}
MYSQL_MASTER_IP=${MYSQL_MASTER_IP:=172.17.0.3}
OS_USER=${OS_USER:=root}
# TODO: Change before using in prod
MYSQL_PASSWORD=${MYSQL_PASSWORD:=password}
MYSQL_COMMAND=${MYSQL_COMMAND:=mysql -uroot}
#MYSQL_COMMAND=${MYSQL_COMMAND:=mysql -uroot -p$MYSQL_PASSWORD}
FETCH_RPM=${FETCH_RPM:=1}
yum -y update
yum install -y wget telnet
# Just used for testing when mounting volume with these existing files
if [[ $FETCH_RPM -eq 1 ]];then
wget http://dl.fedoraproject.org/pub/epel/6/i386/epel-release-6-8.noarch.rpm
wget http://repo.mysql.com/mysql-community-release-el6-5.noarch.rpm
wget https://storage.googleapis.com/google-code-archive-downloads/v2/code.google.com/mysql-master-ha/mha4mysql-node-0.54-0.el6.noarch.rpm
fi
# install dependencies repo
yum install -y epel-release-6-8.noarch.rpm
# install mysql-server
rpm -Uvh mysql-community-release-el6-5.noarch.rpm
yum install -y mysql mysql-server
service mysqld start
#configure mysql
#sed -i 's/^bind-address/#bind-address/' $MYSQL_CONF_FILE
#sed -i 's/^#server-id/server-id/' $MYSQL_CONF_FILE
sed -i 's/# log_bin/log_bin=mysqld-bin/' $MYSQL_CONF_FILE
sed -i "/\[mysqld\]/a server-id=${MYSQL_SERVER_ID}" $MYSQL_CONF_FILE
sed -i '/\[mysqld\]/a replicate-ignore-db=mysql' $MYSQL_CONF_FILE #ignore db mysql replication
service mysqld restart
#master-slave replication
if [[ $MYSQL_SERVER_ID -gt 0 ]];then
$MYSQL_COMMAND -e "change master to master_host='$MYSQL_MASTER_IP',master_user='$MYSQL_REPL_USER',master_password='$MYSQL_REPL_PASS';start slave;"
SLAVE_STATUS=`$MYSQL_COMMAND -e "show slave status\G" |grep -i "Running: Yes"|wc -l`
# Test MySQL Slave status
if [[ $SLAVE_STATUS -ne 2 ]];then
echo "master-slave replication issue!"
exit
fi
else
$MYSQL_COMMAND -e "grant replication slave, replication client on *.* to '$MYSQL_REPL_USER'@'%' identified by '$MYSQL_REPL_PASS';flush privileges;"
fi
# TODO: https://github.com/yoshinorim/mha4mysql-manager/issues/60
# Install MHA https://code.google.com/archive/p/mysql-master-ha/downloads?page=1
yum install -y mha4mysql-node-0.54-0.el6.noarch.rpm