思享工具箱

mysql主从之keepalive+MySQL高可用

摘要：
一keepalive介绍1.1keepalived是什么keepalived是集群管理中保证集群高可用的一个服务软件，用来防止单点故障。keepalived主要有三个模块，分别是core、check和vrrp。core模块为keepalived的核心，负责主进程的启动、维护以及全局配置文件的加载和解析。check负责健康检查，包括常见的各种检查方式。vrrp模块是来实现VRRP协议的。

一 keepalive介绍

1.1 keepalived 是什么

keepalived 是集群管理中保证集群高可用的一个服务软件，用来防止单点故障。

1.2 keepalived 工作原理

mysql主从之keepalive+MySQL高可用第1张

keepalived 是以VRRP 协议为实现基础的，VRRP 全称VirtualRouter Redundancy Protocol，即虚拟路由冗余协议。虚拟路由冗余协议，可以认为是实现路由器高可用的协议，即将N 台提供相同功能的路由器组成一个路由器组，这个组里面有一个master 和多个backup，master 上面有一个对外提供服务的vip（该路由器所在局域网内其他机器的默认路由为该vip），master 会发组播，当backup 收不到vrrp 包时就认为master 宕掉了，这时就需要根据VRRP 的优先级来选举一个backup 当master。这样的话就可以保证路由器的高可用了。

keepalived 主要有三个模块，分别是core、check 和vrrp。

core 模块为keepalived 的核心，负责主进程的启动、维护以及全局配置文件的加载和解析。

check 负责健康检查，包括常见的各种检查方式。

vrrp 模块是来实现VRRP 协议的。

1.3 实验环境

master1:192.168.132.121

master2:192.168.132.122

虚拟IP:192.168.132.120

双主环境

master1

master1
mysql>show slave statusG;
*************************** 1. row ***************************
               Slave_IO_State: Waiting formaster to send event
                  Master_Host: 192.168.132.122
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000003
          Read_Master_Log_Pos: 4975
               Relay_Log_File: relay-log.000003
                Relay_Log_Pos: 913
        Relay_Master_Log_File: master-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 4975
              Relay_Log_Space: 2618
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 2
                  Master_UUID: 662134df-a196-11e9-b432-000c2963fd11
             Master_Info_File: /data/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for moreupdates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 662134df-a196-11e9-b432-000c2963fd11:1-7
            Executed_Gtid_Set: 63a7f26f-a196-11e9-a2b2-000c2991dd19:1-18,
662134df-a196-11e9-b432-000c2963fd11:1-7
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name:

master2

mysql>show slave statusG;
*************************** 1. row ***************************
               Slave_IO_State: Waiting formaster to send event
                  Master_Host: 192.168.132.121
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000008
          Read_Master_Log_Pos: 650
               Relay_Log_File: relay-log.000008
                Relay_Log_Pos: 865
        Relay_Master_Log_File: master-bin.000008
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 650
              Relay_Log_Space: 2098
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: 63a7f26f-a196-11e9-a2b2-000c2991dd19
             Master_Info_File: /data/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for moreupdates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 63a7f26f-a196-11e9-a2b2-000c2991dd19:1-18
            Executed_Gtid_Set: 63a7f26f-a196-11e9-a2b2-000c2991dd19:1-18,
662134df-a196-11e9-b432-000c2963fd11:1-7
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version:

二 配置keepalive

2.1 开始安装keepalived

[root@master1 ~]# cd /usr/local/src/

[root@master1 src]# wget https://www.keepalived.org/software/keepalived-2.0.17.tar.gz

[root@master1 src]# tar -xf keepalived-2.0.17.tar.gz

[root@master1 src]# cd keepalived-2.0.17

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

[root@master1 keepalived-2.0.17]# ./configure --prefix=/usr/local/keepalived

[root@master1 keepalived-2.0.17]# make

[root@master1 keepalived-2.0.17]# make install

[root@master1 keepalived-2.0.17]# cd /usr/local/keepalived/sbin/

[root@master1 sbin]# ll

-rwxr-xr-x. 1 root root 2381368 Jul 9 11:59 keepalived

[root@master1 sbin]# cp keepalived /usr/sbin/

[root@master1 sbin]# ll /usr/sbin/keepalived

-rwxr-xr-x. 1 root root 2381368 Jul 9 12:01 /usr/sbin/keepalived

[root@master1 sbin]# systemctl list-unit-files|grep keepalived

mysql主从之keepalive+MySQL高可用第2张

[root@master1 sbin]# systemctl enable keepalived

[root@master1 sbin]# systemctl list-unit-files|grep keepalived

mysql主从之keepalive+MySQL高可用第3张

[root@master1 sbin]# mkdir /etc/keepalived

[root@master1 sbin]# cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/

[root@master1 sbin]# ll /etc/keepalived/

-rw-r--r--. 1 root root 3550 Jul 9 12:06 keepalived.conf

2.2 配置keepailve

root@master1 keepalived-2.0.17]# vim /etc/keepalived/keepalived.conf

! Configuration File forkeepalived
global_defs {
   router_id db01
}
vrrp_instance VI_1 {
    state BACKUP
    interface ens33
    virtual_router_id 51
    priority 100
    nopreempt
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.132.120
    }
}
virtual_server 192.168.132.120 3306{
    delay_loop 6
    persistence_timeout 50
    protocol TCP
    real_server 192.168.132.121 3306{
        notify_down /etc/keepalived/kill_keepalived.sh
        TCP_CHECK {
            connect_timeout 3
            nb_get_retry 3
            connect_port 3306
            delay_before_retry 3
        }
    }
}

配置简介

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.200.16 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

[root@master1 keepalived-2.0.17]# vim /etc/keepalived/kill_keepalived.sh

[root@master1 keepalived-2.0.17]# chmod +x /etc/keepalived/kill_keepalived.sh

#!/bin/bash
kill -9 $(cat /var/run/keepalived.pid)

[root@master1 keepalived-2.0.17]# systemctl start keepalived

[root@master1 keepalived-2.0.17]# cat /var/run/keepalived.pid
16287

master2相同做法
配置文件

[root@master2 sbin]# cat /etc/keepalived/keepalived.conf 

[root@master2 sbin]# cat /etc/keepalived/keepalived.conf 
! Configuration File forkeepalived
bal_defs {
   router_id db02
}
vrrp_instance VI_1 {
    state BACKUP
    interface ens33
    virtual_router_id 51
    priority 90
    nopreempt
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.132.120
    }
}
virtual_server 192.168.132.120 3306{
    delay_loop 6
    persistence_timeout 50
    protocol TCP
    real_server 192.168.132.122 3306{
        notify_down /etc/keepalived/kill_keepalived.sh
        TCP_CHECK {
            connect_timeout 3
            nb_get_retry 3
            connect_port 3306
            delay_before_retry 3
        }
    }
}

2.3 检查状态

[root@master2 ~]# systemctl status keepalived

mysql主从之keepalive+MySQL高可用第4张

[root@master2 ~]# ps -ef|grep keepalived

mysql主从之keepalive+MySQL高可用第5张

[root@master1 ~]# ip addr

1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN qlen 1
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128scope host 
       valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 00:0c:29:91:dd:19brd ff:ff:ff:ff:ff:ff
    inet 192.168.132.121/24 brd 192.168.132.255scope global ens33
       valid_lft forever preferred_lft forever
    inet 192.168.132.120/32scope global ens33          #虚拟IP地址
       valid_lft forever preferred_lft forever
    inet6 fe80::b469:7426:8024:a47c/64scope link 
       valid_lft forever preferred_lft forever
3: ens34: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 00:0c:29:91:dd:23brd ff:ff:ff:ff:ff:ff
    inet 192.168.43.85/24 brd 192.168.43.255scope global dynamic ens34
       valid_lft 2810sec preferred_lft 2810sec
    inet 192.168.254.121/24 brd 192.168.254.255scope global ens34
       valid_lft forever preferred_lft forever
    inet6 fe80::4d8:1809:407a:5c0a/64scope link 
       valid_lft forever preferred_lft forever

master2查看

1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN qlen 1
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128scope host 
       valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 00:0c:29:63:fd:11brd ff:ff:ff:ff:ff:ff
    inet 192.168.132.122/24 brd 192.168.132.255scope global ens33
       valid_lft forever preferred_lft forever
    inet6 fe80::5d18:db0b:8907:de34/64scope link 
       valid_lft forever preferred_lft forever
3: ens34: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 00:0c:29:63:fd:1b brd ff:ff:ff:ff:ff:ff
    inet 192.168.254.122/24 brd 192.168.254.255scope global ens34
       valid_lft forever preferred_lft forever
    inet6 fe80::c3d5:5055:468a:94f8/64scope link 
       valid_lft forever preferred_lft forever

三 验证

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

mysql> grant all on *.*  to root@192.168.132.123 identified by '1234567';
mysql>flush privileges;

[root@slave ~]# mysql -ureplication -h192.168.132.120 -p1234567

mysql>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| master1            |
| master2            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

关闭192.168.132.121的mysqld

[root@master1 ~]# systemctl stop mysqld

[root@master1 ~]# ip addr

1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN qlen 1
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128scope host 
valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 00:0c:29:91:dd:19brd ff:ff:ff:ff:ff:ff
inet 192.168.132.121/24 brd 192.168.132.255scope global ens33
valid_lft forever preferred_lft forever
inet6 fe80::b469:7426:8024:a47c/64scope link 
valid_lft forever preferred_lft forever
3: ens34: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
link/ether 00:0c:29:91:dd:23brd ff:ff:ff:ff:ff:ff
inet 192.168.43.85/24 brd 192.168.43.255scope global dynamic ens34
valid_lft 3348sec preferred_lft 3348sec
inet 192.168.254.121/24 brd 192.168.254.255scope global ens34
valid_lft forever preferred_lft forever
inet6 fe80::4d8:1809:407a:5c0a/64scope link 
valid_lft forever preferred_lft forever

查看master2

[root@master2 ~]# ip addr

1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN qlen 1
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128scope host 
       valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 00:0c:29:63:fd:11brd ff:ff:ff:ff:ff:ff
    inet 192.168.132.122/24 brd 192.168.132.255scope global ens33
       valid_lft forever preferred_lft forever
    inet 192.168.132.120/32scope global ens33    #虚拟IP已经转移过来
       valid_lft forever preferred_lft forever
    inet6 fe80::5d18:db0b:8907:de34/64scope link 
       valid_lft forever preferred_lft forever
3: ens34: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 00:0c:29:63:fd:1b brd ff:ff:ff:ff:ff:ff
    inet 192.168.254.122/24 brd 192.168.254.255scope global ens34
       valid_lft forever preferred_lft forever
    inet6 fe80::c3d5:5055:468a:94f8/64scope link 
       valid_lft forever preferred_lft forever

使用slave连接

[root@slave ~]# mysql -uroot -h192.168.132.120 -p1234567
mysql>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| master1            |
| master2            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

依然可以访问,双主的高可用配置完成

在master1开启MySQL和keepalive后,虚拟IP并不会抢占回来,是因为设置的是非抢占模式,防止虚拟IP跳动影响性能

标签：#keepalived#mysql主从配置#mysql集群#last#mysql#loopback#host#master#brd | 浏览：302 | 发布日期：

免责声明：文章转载自《mysql主从之keepalive+MySQL高可用》仅用于学习参考。如对内容有疑问，请及时联系本站处理。

上篇阅读这篇文章，假设你不知道的傅里叶变换，然后来掐死我RMAN 增量备份级别说明下篇

宿迁高防，2C2G15M，22元/月；香港BGP，2C5G5M，25元/月 雨云优惠码：MjYwNzM=

相关文章

USB复合设备与组合设备的区别

Composite USB devices  组合设备 Compound USB devices  复合设备 Composite USB devices expose multiple functions to a host byusing multiple interfaces.A compound device is an external hub t...

docker+ceph实现私网云盘

目录 ceph+docker方式搭建owncloud实现私网云盘 项目背景 项目架构图 部署过程 准备过程 1、所有节点配置静态IP地址（要求能上公网）（ceph1举例） 2、所有节点主机名配置在/etc/hosts文件中 3、所有节点关闭centos7的firewalld房防火墙，iptables规则清空 4、所有节点关闭selinux 5、同步...

Mac上安装MySQL后在终端连接不上数据库Command not found

Mac上安装好MySQL要想在终端输入mysql命令，但是报Command not found，解决办法 打开终端输入如下命令：  /usr/local/mysql/bin/mysql -u root -p 其中root为用户名。这时会出现如下命令：Enter password:  123456 用文本编辑器打开.bash_profile，加入PATH=$...

MySQL会话控制限制登录次数

MySQL 5.7 以后提供了Connection-Control插件用来控制客户端在登录操作连续失败一定次数后的响应的延迟。 该插件可有效的防止客户端暴力登录的风险(攻击)。该插件包含以下两个组件： connection_control：控制失败次数以及延迟时间 connection_control_failed_login_attempts：将登录失...

MySQL 5.6表空间传输

在MySQL 5.6 Oracle引入了一个可移动表空间的特征（复制的表空间到另一个服务器）和Percona Server采用部分备份，这意味着你现在可以备份单个数据库或表；由于Percona Server 5.6的出现，innodb_import_table_from_xtrabackup是过时的Percona Server实现Oracle MySQL的...

lunix常用命令

lunix  查看端口 　　netstat -nltp查看进程 　ps -f | grep elasticsearch关闭进程 kill -9 pid号 批量创建文件夹 mkdir -p mysql/{logs,data,conf}   创建mysql 文件夹，并在mysql目录下创建三个目录...

最新文章

随机推荐

思享工具箱导航