Last active
July 27, 2018 19:02
-
-
Save lastcoolnameleft/4bd023eba19f171f5c6e76f39521bb88 to your computer and use it in GitHub Desktop.
Centos6 + MHA 0.54 + MySQL
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment