MySQL高可用之双主+keepalived

 2024/09/04 

双主+keepalive是一种简单通用的mysql高可用架构
优点就是简单
缺点是扩展性差，如果出现错误需要手动进行恢复

双主配置

双主在主从同步的基础上进行，binlog配置及rep用户配置略过

用户repuser@%
密码repuser123
权限replication、slave

环境：
mysql5.7.20
192.168.10.125 mysql-1
192.168.10.126 mysql-2

主：
修改my.cnf
#replicate-do-db=xxx 需要同步的数据库，不写就是全部同步
binlog-ignore-db=mysql
auto_increment_increment=2  #有几台主就写几个
auto_increment_offset=1
systemctl restart mysqld.service

备：
修改my.cnf
#replicate-do-db=xxx
binlog-ignore-db=mysql
auto_increment_increment=2 
auto_increment_offset=2
systemctl restart mysqld.service

主：开启把备当主库的连接
stop slave;
reset slave;
change master to
master_host='192.168.10.126',
master_user='repuser',
master_password='repuser123',
master_log_file='bin.000010',
master_log_pos=234;
start slave;
show slave status\G

主2:同理
stop slave;
reset slave;
change master to
master_host='192.168.10.125',
master_user='repuser',
master_password='repuser123',
master_log_file='bin.000011',
master_log_pos=234;
start slave;
show slave status\G

查看IO线程与SQL线程是否已经成功

双主+keepalive配置

keepalive安装与部署

主配置

#直接用yum装,两台
yum -y install keepalived

cat >/etc/keepalived/keepalived.conf<<EOF
! Configuration File for keepalived
global_defs {
   router_id mysql-1
}
vrrp_script check_mysqld {
		script "/etc/keepalived/check_mysqld.sh"
		interval 2
		weight 2
}

vrrp_instance VI_1 {
    state MASTER
    interface ens18
    virtual_router_id 51
    priority 150
    advert_int 1
    nopreempt
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    track_script {
	    check_mysqld
    }
    virtual_ipaddress {
        192.168.10.129/24 dev ens18
    }
}
EOF
systemctl restart keepalived.service

备配置

yum -y install keepalived
cat>/etc/keepalived/keepalived.conf<<EOF
! Configuration File for keepalived
global_defs {
   router_id mysql-2
}
vrrp_script check_mysqld {
		script "/etc/keepalived/check_mysqld.sh"
		interval 2
		weight 2
}
vrrp_instance VI_1 {
    state BACKUP
    interface ens18
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.10.129/24 dev ens18
    }
}
EOF
systemctl restart keepalived.service

测试部分

测试VIP

VIP为192.168.10.129

mysql -uroot -proot -h 192.168.10.129 -e "show variables like 'server_id';"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| server_id     | 253306 |
+---------------+--------+

测试keepalive VIP切换

当前状态
主：
 ip a | grep -w inet | grep 192
    inet 192.168.10.125/24 brd 192.168.10.255 scope global noprefixroute ens18
    inet 192.168.10.129/24 scope global secondary ens18
备
ip a | grep -w inet | grep 192
    inet 192.168.10.126/24 brd 192.168.10.255 scope global noprefixroute ens18

主关闭keepalive服务
systemctl stop keepalived.service
备查看
ip a | grep -w inet | grep 192
    inet 192.168.10.126/24 brd 192.168.10.255 scope global noprefixroute ens18
    inet 192.168.10.129/24 scope global secondary ens18

主恢复
systemctl start keepalived.service
主查看
ip a | grep -w inet | grep 192
    inet 192.168.10.125/24 brd 192.168.10.255 scope global noprefixroute ens18
    inet 192.168.10.129/24 scope global secondary ens18

检测脚本配置

cat >/etc/keepalived/check_mysqld.sh<<'EOF'
#!/bin/bash
if [ -f ~/.mailrc ];then
	echo "mail 已安装"
else
	echo "mail 未安装"
	yum -y install mailx 2&>/dev/null
	echo"
set from=alertwarning@163.com #发送的邮箱
set smtp=smtp.163.com:25 #使用的协议
set smtp-auth-user=alertwarning@163.com #邮箱账号
set smtp-auth-password=xxx #邮箱授权码
set smtp-auth=login #自动登录
set ssl-verify-server-cert=no #不使用SSL证书" > /root/.mailrc
fi

count=$(ps -ef | grep -c mysql)
if [ $count -le 1 ];then
		echo "mysql服务已停止，尝试拉活"
		systemctl start mysqld
		sleep 3
		count=$(ps -ef | grep -c mysql)
		if [ $count -le 1 ];then
			echo "拉活失败,准备关闭keepalived"
			systemctl stop keepalived
			echo "$(hostname) VIP 已切换" | mail -s "vip切换告警" alertwarning@163.com
		else
			echo "拉活成功";fi
fi
EOF

chmod +x /etc/keepalived/check_mysqld.sh
systemctl restart keepalived