查看MySQL 5.7 + MHA + Ke<unk>ived 高可用的例子

发布时间：2023-10-13 13:30:45 所属栏目：MySql教程来源：

导读：这篇文章主要介绍了MySQL5.7+MHA+Keepalived高可用配置的示例分析，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。数据库架构：一主两从master：

这篇文章主要介绍了MySQL5.7+MHA+Keepalived高可用配置的示例分析，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。

数据库架构：一主两从

master：192.168.8.57

slave1：192.168.8.58

slave2：192.168.8.59

manager：192.168.8.60

MHA工具包：

mha4mysql-manager-0.58.tar.gz

mha4mysql-node-0.58.tar.gz

keepalived-1.4.5.tar.gz

一、主从复制环境和MHA安装如下

http://blog.itpub.net/30135314/viewspace-2217566/

二、配置MHA相关文件

master_ip_failover

my $vip = '192.168.8.88/24';

my $key = '1';

my $ssh_start_vip = "systemctl start keepalived";

my $ssh_stop_vip = "systemctl stop keepalived";

master_ip_online_change

my $vip = '192.168.8.88/24';

my $key = '1';

my $ssh_start_vip = "systemctl start keepalived";

my $ssh_stop_vip = "systemctl stop keepalived";

my $orig_master_ssh_port = 22;

my $new_master_ssh_port = 22;

my $ssh_user = "root";

my $new_master_password='mysql';

my $orig_master_password='mysql';

三、添加非本机 IP 邦定支持

echo "net.ipv4.ip_nonlocal_bind=1" >> /etc/sysctl.conf

sysctl -p

四、安装配置keepalived

mount /dev/cdrom /mnt

yum install kernel-devel openssl-devel popt-devel -y

tar zxvf keepalived-1.4.5.tar.gz

cd keepalived-1.4.5

./configure --prefix=/usr/local/keepalived/

make && make install

设置 keepalived 开机自动启动

systemctl enable keepalived

配置keepalived

192.168.8.57

mkdir /etc/keepalived

# vi /etc/keepalived/keepalived.conf

! Configuration file for keepalived

global_defs {

notification_email {

zdd5503@163.com

}

router_id 8.57

notification_email_from zdd5503@163.com

smtp_server stmp.163.com

smtp_connect_timeout 30

}

vrrp_instance v_mysql {

state BACKUP

interface enp0s3

virtual_router_id 200

priority 100

advert_int 1

nopreempt

authentication {

auth_type PASS

auth_pass mysql

}

virtual_ipaddress {

192.168.8.88/24

}

virtual_server 192.168.8.57 3306 {

delay_loop 2

lb_algo wrr

lb_kind DR

persistence_timeout 60

protocol TCP

real_server 192.168.8.57 3306 {

weight 3

notify_down /etc/keepalived/keepalived_stop.sh

TCP_CHECK {

connect_timeout 10

nb_get_retry 3

delay_before_retry 3

connect_port 3306

}

echo "#!/bin/bash" >/etc/keepalived/keepalived_stop.sh

echo "pkill keepalived" >>/etc/keepalived/keepalived_stop.sh

chmod u+x /etc/keepalived/keepalived_stop.sh

192.168.8.58

mkdir /etc/keepalived

# vi /etc/keepalived/keepalived.conf

! Configuration file for keepalived

global_defs {

notification_email {

zdd5503@163.com

}

router_id 8.58

notification_email_from zdd5503@163.com

smtp_server stmp.163.com

smtp_connect_timeout 30

}

vrrp_instance v_mysql {

state BACKUP

interface enp0s3

virtual_router_id 200

priority 90

advert_int 1

nopreempt

authentication {

auth_type PASS

auth_pass mysql

}

virtual_ipaddress {

192.168.8.88/24

}

virtual_server 192.168.8.58 3306 {

delay_loop 2

lb_algo wrr

lb_kind DR

persistence_timeout 60

protocol TCP

real_server 192.168.8.58 3306 {

weight 3

notify_down /etc/keepalived/keepalived_stop.sh

TCP_CHECK {

connect_timeout 10

nb_get_retry 3

delay_before_retry 3

connect_port 3306

}

echo "#!/bin/bash" >/etc/keepalived/keepalived_stop.sh

echo "pkill keepalived" >>/etc/keepalived/keepalived_stop.sh

chmod u+x /etc/keepalived/keepalived_stop.sh

五、启动并查看keepalived

192.168.8.57和192.168.8.58

systemctl daemon-reload

systemctl start keepalived

systemctl enable keepalived

ps -ef |grep keepalived

看到如下进程，即启动正常，否则，启动失败

#ps -ef|grep keepalived

root 16916 1 0 14:53 ? 00:00:00 /usr/local/keepalived/sbin/keepalived -D

root 16917 16916 0 14:53 ? 00:00:00 /usr/local/keepalived/sbin/keepalived -D

root 16918 16916 0 14:53 ? 00:00:00 /usr/local/keepalived/sbin/keepalived -D

root 17772 9992 0 16:17 pts/2 00:00:00 grep --color=auto keepalived

查看VIP

192.168.8.57

# ip a|grep 88

inet 192.168.8.88/24 scope global secondary enp0s3

192.168.8.58看不到192.168.8.88/24

六、测试keepalived可用性（测试之前关闭MHA进程，避免故障切换造成干扰）

MySQL连接测试

查到目前VIP192.168.8.88在192.168.8.57这个机器

# mysql -uroot -pmysql -h292.168.8.88 -e "show variables like 'server_id'"

mysql: [Warning] Using a password on the command line interface can be insecure.

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| server_id | 57 |

+---------------+-------+

关闭keepalived测试

关闭192.168.8.57上keepalived进程，此时在此节点已查不到VIP

ip a

1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000

link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00

inet 127.0.0.1/8 scope host lo