MySQL高可用部署-MHA

192.168.1.177	mysql-master
192.168.1.178	mysql-slave-01
192.168.1.179	mysql-slave-02

ssh-keygen -t rsa
ssh-copy IP

yum install epel-release
yum install perl cpan perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager perl-Time-HiRes

yum install epel-release
yum install perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager rrdtool perl-rrdtool rrdtool-devel perl-Params-Validate

rpm -ivh mha4mysql-manager-0.58-0.el7.centos.noarch.rpm

rpm -ivh mha4mysql-node-0.58-0.el7.centos.noarch.rpm

mysql> grant all on *.* to 'mhaadmin'@'192.168.1.%' identified by '123456';
mysql> grant replication slave on *.* to 'repl'@'192.168.1.%' identified by '123456';

mysql> set global read_only=1

mkdir -p /etc/mha
mkdir -p /var/log/mha

vim /etc/mha/mha.cnf

[server default]
manager_log=/var/log/mha/manager.log  
remote_workdir=/apps/data/mysql/		# 设置远端mysql在发生切换时binlog的保存位置 
manager_workdir=/apps/data/mysql/		# MHA工作目录
master_binlog_dir=/apps/data/mysql/		# binlog路径全局配置，如果有节点不一样的话可以单独写在主机标签中
user=mhaadmin					# MHA管理用户，上面创建的mha用户
password=123456					# 管理用户密码
ping_interval=2					# 每2秒检查一次主库状态
repl_user=repl					# 主从复制的用户
repl_password=123456				# 主从复制的密码 
ssh_user=root					# MHA 会使用scp传输日志，所以节点之间做好密钥登录
ssh_port=22                             

[server1]					# 主机标签，数字大小会影响优先级
hostname=192.168.1.103
port=3306
#master_binlog_dir=/data/mysql/binlog		# 单独定义了一个binlog路径
candidate_master=1
 
[server2]
candidate_master=1				# 让该节点成为候选master，即使数据不是最新
#check_repl_delay=0				# 和上面配置配合使用，忽略从库复制延时的问题，否则延迟大于100M的话候选master也不会被选举为master
hostname=192.168.1.104
port=3306
 
[server3]
hostname=192.168.1.108
port=3306
no_master=1					# 不能成为master

#[binlog1]					# 配置一台binlog服务器用来实时备份binlog，防止主库断电导致binlog无法传输，该机器需要有mysql客户端命令
#no_master=1					# 该节点不会被提升为master
#hostname=10.0.0.54
#master_binlog_dir=/data/mysql/binlog

masterha_check_ssh --conf=/etc/mha/mha.cnf
masterha_check_repl --conf=/etc/mha/mha.cnf

vim /etc/mha/mha

#!/bin/bash
mha_log='/var/log/mha/mha.log'
mha_cnf='/etc/mha/mha.cnf'

mha_start () {
        mha_processpid=`ps -ef |grep masterha_manager |grep -cv grep`
        if [ ${mha_processpid} -eq 0 ]; then
                nohup masterha_manager --conf=${mha_cnf} > ${mha_log} < /dev/null &
        fi
}

mha_stop () {
        mha_processpid=`ps -ef |grep masterha_manager |grep -cv grep`
        if [ ${mha_processpid} -eq 1 ]; then
                masterha_stop --conf=${mha_cnf} > /dev/null 2>&1
        fi
}

mha_status () {
        mha_processpid=`ps -ef |grep masterha_manager |grep -cv grep`
        if [ ${mha_processpid} -eq 1 ]; then
                echo -e "\033[32mMHA is running ! \033[0m"
                masterha_check_status --conf=${mha_cnf}
        else
                echo -e "\033[31mMHA has stopped ! \033[0m"
                masterha_check_status --conf=${mha_cnf}
        fi
}

case "$1" in
    start)
    mha_start
    ;;
    stop)
    mha_stop
    ;;
    status)
    mha_status
    ;;
    *)
    echo "USAGE:$0 {start|stop|status}"
esac

chmod +x /etc/mha/mha
/etc/mha/mha start
/etc/mha/mha status

vim /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {
   router_id mysql 			# 两个节点设置一样
}

vrrp_instance VI_1 {
    state BACKUP 			# 两个节点都设置为BACKUP
    interface ens33
    virtual_router_id 58 		# 两个节点设置一样
    priority 100			# 优先级，从设置的要比主小
    advert_int 1
    nopreempt
    authentication {
        auth_type PASS
        auth_pass 1122
    }

    virtual_ipaddress {
        192.168.1.200/24
    }
}

virtual_server 192.168.1.177 3306 { 	# 设置为本机IP
    delay_loop 2
    lb_algo rr
    lb_kind DR
    persistence_timeout 50 
    protocol TCP
    real_server 192.168.1.177 3306 { 
	weight 3
	notify_down /etc/keepalived/chk_mysql.sh
	TCP_CHECK {
	connect_timeout 3
	nb_get_retry 3
	delay_before_retry 3
	}
    }
}

vim /etc/keepalived/chk_mysql.sh

#!/bin/bash
res = `ps -ef | grep -v grep | grep mysql | wc -l`
if [ $res > 0 ]; then
    exit 0
else
   pkill keepalived
fi

chmod +x /etc/keepalived/chk_mysql.sh
systemctl start keepalived

ip a			# 查看虚拟IP

vim /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {
   router_id mysql 			# 两个节点设置一样
}

vrrp_instance VI_1 {
    state BACKUP 			# 两个节点都设置为BACKUP
    interface ens33
    virtual_router_id 58 		# 两个节点设置一样
    priority 100			# 优先级，从设置的要比主小
    advert_int 1
    nopreempt
    authentication {
        auth_type PASS
        auth_pass 1122
    }

    virtual_ipaddress {
        192.168.1.200/24
    }
}

virtual_server 192.168.1.178 3306 { 	# 设置为本机IP
    delay_loop 2
    lb_algo rr
    lb_kind DR
    persistence_timeout 50 
    protocol TCP
    real_server 192.168.1.178 3306 { 
	weight 3
	notify_down /etc/keepalived/chk_mysql.sh
	TCP_CHECK {
	connect_timeout 3
	nb_get_retry 3
	delay_before_retry 3
	}
    }
}

vim /etc/keepalived/chk_mysql.sh

#!/bin/bash
res = `ps -ef | grep -v grep | grep mysql | wc -l`
if [ $res > 0 ]; then
    exit 0
else
   pkill keepalived
fi

chmod +x /etc/keepalived/chk_mysql.sh
systemctl start keepalived

ip a 				# 查看虚拟IP