mysql主从之keepalive+MySQL高可用

目录

• 一 keepalive介绍
  • 1.1 keepalived 是什么
  • 1.2 keepalived 工作原理
  • 1.3 实验环境
•  二 配置keepalive
  • 2.2 配置keepailve
  • 2.3 检查状态
•  三 验证

---

回到顶部

一 keepalive介绍

1.1 keepalived 是什么

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

1.2 keepalived 工作原理

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 for master 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 more updates
           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 for master 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 more updates
           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

[root@master1 sbin]# systemctl enable keepalived

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

[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 for keepalived

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 for keepalived

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

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

[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/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope 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:19 brd ff:ff:ff:ff:ff:ff
    inet 192.168.132.121/24 brd 192.168.132.255 scope global ens33
       valid_lft forever preferred_lft forever
    inet 192.168.132.120/32 scope global ens33          #虚拟IP地址
       valid_lft forever preferred_lft forever
    inet6 fe80::b469:7426:8024:a47c/64 scope 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:23 brd ff:ff:ff:ff:ff:ff
    inet 192.168.43.85/24 brd 192.168.43.255 scope global dynamic ens34
       valid_lft 2810sec preferred_lft 2810sec
    inet 192.168.254.121/24 brd 192.168.254.255 scope global ens34
       valid_lft forever preferred_lft forever
    inet6 fe80::4d8:1809:407a:5c0a/64 scope 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/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope 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:11 brd ff:ff:ff:ff:ff:ff
    inet 192.168.132.122/24 brd 192.168.132.255 scope global ens33
       valid_lft forever preferred_lft forever
    inet6 fe80::5d18:db0b:8907:de34/64 scope 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.255 scope global ens34
       valid_lft forever preferred_lft forever
    inet6 fe80::c3d5:5055:468a:94f8/64 scope 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/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope 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:19 brd ff:ff:ff:ff:ff:ff
inet 192.168.132.121/24 brd 192.168.132.255 scope global ens33
valid_lft forever preferred_lft forever
inet6 fe80::b469:7426:8024:a47c/64 scope 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:23 brd ff:ff:ff:ff:ff:ff
inet 192.168.43.85/24 brd 192.168.43.255 scope global dynamic ens34
valid_lft 3348sec preferred_lft 3348sec
inet 192.168.254.121/24 brd 192.168.254.255 scope global ens34
valid_lft forever preferred_lft forever
inet6 fe80::4d8:1809:407a:5c0a/64 scope link 
valid_lft forever preferred_lft forever

https://www.cnblogs.com/zyxnhr/p/11161365.html

查看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/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope 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:11 brd ff:ff:ff:ff:ff:ff
    inet 192.168.132.122/24 brd 192.168.132.255 scope global ens33
       valid_lft forever preferred_lft forever
    inet 192.168.132.120/32 scope global ens33    #虚拟IP已经转移过来
       valid_lft forever preferred_lft forever
    inet6 fe80::5d18:db0b:8907:de34/64 scope 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.255 scope global ens34
       valid_lft forever preferred_lft forever
    inet6 fe80::c3d5:5055:468a:94f8/64 scope 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跳动影响性能

作者：梦中泪

出处：http://www.cnblogs.com/zyxnhr/

关于作者：云计算，linux，虚拟化，存储

---------------------------------------------------------------------------

个性签名：我以为我很颓废,今天我才知道,原来我早报废了。

如果觉得本篇文章最您有帮助，欢迎转载，且在文章页面明显位置给出原文链接！记得在右下角点个“推荐”，博主在此感谢！

https://www.cnblogs.com/zyxnhr/p/11161365.html

https://www.cnblogs.com/zyxnhr/p/11161365.html