keepalive+mysql高可用部署

1、部署环境，安装并配置mysql

master1：192.168.0.101
master2：192.168.0.102
虚 拟IP：192.168.0.105

1.1 安装mysql

# wget https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm

安装YUMRepo文件
# yum localinstall mysql80-community-release-el7-1.noarch.rpm -y
安装YUM管理工具包，此包提供了yum-config-manager命令工具
# yum install yum-utils -y
禁用8.0
# yum-config-manager --disable mysql80-community
启用5.7
# yum-config-manager --enable mysql57-community
开始安装MySQL
# yum install -y mysql-community-server
启动
# systemctl start mysqld.service
查看状态
# systemctl status mysqld.service
开机自启动
# systemctl enable mysqld

1.2 配置mysql主从同步

1）主服务器配置vi /etc/my.cnf，在[mysqld]下添加如下配置

server-id=1                       #master端和slave端不可以相同
log-bin=mysql-bin                 ##打开二进制文件
binlog_ignore_db=mysql            #设置要忽略binlog记录的数据库
replicate-do-db=db1               #要复制的数据库

重启数据库服务

# systemctl restart mysqld

2）备服务器配置vi /etc/my.cnf，在[mysqld]下添加如下配置

server-id=2                       #master端和slave端不可以相同
log-bin=mysql-bin                 ##打开二进制文件
binlog_ignore_db=mysql            #设置要忽略binlog记录的数据库
replicate-do-db=db1               #要复制的数据库

重启数据库服务

# system restart mysqld

3）slave连接到master配置方法
在mysql用户下，输入mysql -u用户名 -p密码，进入mysql数据库
以下为主机数据库同步到备机的方法（备机数据同步到主机方法一致）

主服务器端操作：查看master状态：
在mysql命令行：show master status;

备服务器端操作：启动slave线程连接master
在mysql命令行下

中断连接：
mysql> stop slave;
mysql> reset slave;

设置要连接的master：(数值看现场主服务器master的数值配，如第一步图中内容)(ip为对端地址)
mysql> change master to master_host='192.168.0.101', master_user='root', master_password='root',master_port=3306, master_log_file='mysql-bin.000004', master_log_pos=154;

连接到master端(作用)：
mysql> start slave;

备机查看slave状态：
mysql> show slave status\G;

如果做双主配置，需要按照上面操作对另一台服务器做相同操作

2、 配置keepalive

2.1 开始安装keepalived

下载keepalived

[root@ecs0001 ~]# cd /usr/local/src/
[root@ecs0001 src]# wget https://www.keepalived.org/software/keepalived-2.0.17.tar.gz
[root@ecs0001 src]# tar -zxvf keepalived-2.0.17.tar.gz
[root@ecs0001 src]# cd keepalived-2.0.17

yum安装所需依赖

[root@ecs0001 keepalived-2.0.17]# yum install openssl* libnl‐dev* gcc-c++

编译安装

[root@ecs0001 keepalived-2.0.17]# ./configure --prefix=/usr/local/keepalived
[root@ecs0001 keepalived-2.0.17]# make
[root@ecs0001 keepalived-2.0.17]# make install
[root@ecs0001 keepalived-2.0.17]# cd /usr/local/keepalived/sbin/
[root@ecs0001 sbin]# ll
-rwxr-xr-x 1 root root 2382912 Jun 21 11:24 keepalived
[root@ecs0001 sbin]# cp keepalived /usr/sbin/
[root@ecs0001 sbin]# ll /usr/sbin/keepalived
-rwxr-xr-x 1 root root 2382912 Jun 21 11:24 /usr/sbin/keepalived

查看keepalived是否开机自启

[root@ecs0001 sbin]# systemctl list-unit-files | grep keepalived
设置keepalived开机自启
[root@ecs0001 sbin]# systemctl enable keepalived
[root@ecs0001 sbin]# systemctl list-unit-files|grep keepalived

创建keepalived配置目录

[root@ecs0001 sbin]# mkdir /etc/keepalived
[root@ecs0001 sbin]# cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/
[root@ecs0001 sbin]# ll /etc/keepalived/
-rw-r--r-- 1 root root 678 Jun 21 14:32 keepalived.conf

2.2 配置keepailve

Master1配置文件：

[root@ecs0001 sbin]# vim /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {
   router_id db01
}

vrrp_instance VI_1 {
    state BACKUP
    interface eth0
    virtual_router_id 51
    priority 100
    nopreempt
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.0.105
    }
}
virtual_server 192.168.0.105 3306 {
    delay_loop 6
    persistence_timeout 50
    protocol TCP

    real_server 192.168.0.101 3306 {
        notify_down /etc/keepalived/kill_keepalived.sh
        TCP_CHECK {
            connect_timeout 3
            nb_get_retry 3
            connect_port 3306
            delay_before_retry 3
        }
    }
}

配置脚本：

[root@ecs0001 ~]# vim /etc/keepalived/kill_keepalived.sh
#!/bin/bash
kill -9 $(cat /var/run/keepalived.pid)

[root@ecs0001 ~]# chmod +x /etc/keepalived/kill_keepalived.sh
[root@ecs0001 ~]# systemctl start  keepalived
[root@ecs0001 ~]# cat /var/run/keepalived.pid
16287

master2配置文件：(相同做法)

[root@ecs0002 sbin]# vim /etc/keepalived/keepalived.conf

! Configuration File for keepalived

global_defs {
   router_id db02
}

vrrp_instance VI_1 {
    state BACKUP
    interface eth0
    virtual_router_id 51
    priority 90
    nopreempt
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.0.105
    }
}
virtual_server 192.168.0.105 3306 {
    delay_loop 6
    persistence_timeout 50
    protocol TCP

    real_server 192.168.0.102 3306 {
        notify_down /etc/keepalived/kill_keepalived.sh
        TCP_CHECK {
            connect_timeout 3
            nb_get_retry 3
            connect_port 3306
            delay_before_retry 3
        }
    }
}

配置脚本：

[root@ecs0002 ~]# vim /etc/keepalived/kill_keepalived.sh
#!/bin/bash
kill -9 $(cat /var/run/keepalived.pid)

[root@ecs0002 ~]# chmod +x /etc/keepalived/kill_keepalived.sh
[root@ecs0002 ~]# systemctl start  keepalived
[root@ecs0002 ~]# cat /var/run/keepalived.pid
24006

配置简介
global_defs 区域主要是配置故障发生时的通知对象以及机器标识
notification_email_from Alexandre.Cassen@firewall.loc //通知邮件从哪个地址发出
smtp_server 192.168.200.1 //通知邮件的smtp 地址
smtp_connect_timeout 30 //连接smtp 服务器的超时时间
router_id db01 // 标识本节点的字符串， 通常为hostname
vrrp_skip_check_adv_addr
vrrp_strict
vrrp_garp_interval 0
vrrp_gna_interval 0
vrrp_instance VI_1 {//VRRP 实例state MASTER // MASTER 或BACKUP，为了防止脑裂现象，主备均需要设置为backup 模式，master 模式会抢占VIP
interface eth0 //节点固有IP（非VIP）的网卡，用来发VRRP 包
virtual_router_id 51 //取值在0-255 之间，用来区分多个instance 的VRRP 组播priority 100 //优先级，同一个vrrp_instance 的MASTER优先级必须比BACKUP 高nopreempt //非抢占，配合backup,防止切换后，主库服务恢复正常后，IP 漂移过来advert_int 1// MASTER 与BACKUP 负载均衡器之间同步检查的时间间隔，单位为秒。
authentication {
auth_type PASS
auth_pass 1111
virtual_ipaddress { //虚拟ip 地址,可以有多个地址，每个地址占一行，不需要子网掩码
192.168.132.120
virtual_server 192.168.132.120 3306 {
delay_loop 6 //健康检查间隔，单位为秒
persistence_timeout 50 // 会话保持时间，就是把用户请求转发给同一个服务器
protocol TCP //转发协议，有TCP 和UDP 两种，一般用TCP
real_server 192.168.132.121 3306 { //真实服务器，包括IP和端口号
notify_down kill_keepalived.sh
TCP_CHECK { #通过tcpcheck 判断RealServer 的健康状态
connect_timeout 3 #连接超时时间
nb_get_retry 3 #重连次数
delay_before_retry 3 #重连时间间隔
connect_port 3306 #检测端口
keepalived 服务配置
设置不抢占模式（DB1 设置即可）
nopreempt
虚拟路由标识，这个标识是一个数字(1‐255)，在一个VRRP
实例中主备服务器ID 必须一样
virtual_router_id 68
优先级，数字越大优先级越高，在一个实例中主服务器优先级要高于备服务器
priority 100 #从服务器99
设置主备之间同步检查的时间间隔单位秒
advert_int 1

2.3 检查状态

[root@ecs0001 sbin]# systemctl status keepalived

[root@ecs0001 sbin]# ps -ef|grep keepalived

master1 查看：

master2 查看：

3、验证

授权后尝试使用192.168.0.105这个IP连接数据库

[root@ecs0001 sbin]# mysql -h192.168.0.105 -uroot -p

关闭192.168.0.101的mysqld

[root@ecs0001 sbin]# systemctl stop mysqld

查看maste1的ip信息

查看maste2的ip信息

测试访问连接

[root@ecs0002 ~]# mysql -uroot -p -h192.168.0.105
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 310
Server version: 5.7.34-log MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

依然可以访问,双主的高可用配置完成
在master1开启MySQL和keepalive后,虚拟IP并不会抢占回来,是因为设置的是非抢占模式,防止虚拟IP跳动影响性能