MySQL高可用之keepalived方案的案例分析

发布时间：2021-12-25 07:47:49 所属栏目：MySql教程来源：互联网

导读：这篇文章主要为大家展示了MySQL高可用之keepalived方案的示例分析，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下MySQL高可用之keepalived方案的示例分析这篇文章吧。实验环境 mysql master : 192.168.111.52

这篇文章主要为大家展示了“MySQL高可用之keepalived方案的示例分析”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下“MySQL高可用之keepalived方案的示例分析”这篇文章吧。

实验环境
    mysql master   : 192.168.111.52
    mysql slave     : 192.168.111.53
    keepalived vip : 192.168.111.60
、搭建过程
    1. mysql双主的构建
        ① 互相 change master 即可，此处省略该过程，着重讲下keepalived

    2. keepalived相关
        ① yum -y install keepalived 安装keepalived
        ② root@192.168.111.52:~# keepalived -v
            Keepalived v1.2.7 (02/21,2013)

        ③ 编辑 /etc/keepalived/keepalived.conf

点击(此处)折叠或打开

root@192.168.111.52:keepalived# cat keepalived.conf
! Configuration File for keepalived
global_defs {##全局配置
   notification_email {
     horand_gc@163.com                    #####邮件接收者
   }
   notification_email_from dba@163.com    #####邮件发送者
   smtp_server smtp.163.com               #####SMTP服务器
   smtp_connect_timeout 30
   router_id haMySQL                      #####routerID 同一组keepalived设置为相同
}
vrrp_script chk_mysql {##健康检测相关配置
    script "/etc/keepalived/chk_mysql.sh" #####设置脚本或者直命令返回 0 则表示成功 >0 则表示失败，详情可以后面的脚本内容
    interval 1                            #####检测间隔
    weight 2                              #####检测返回失败之后优先级会减少 2 (如果 master优先级100 ， slave 优先级 99 ,master检测失败则优先级为100-2 < 99 ，则slave会提升为主)
}
vrrp_instance VI_1 {
    state MASTER                          ##### 设为master
    interface eth2                        ##### 网卡设定
    virtual_router_id 51                  ##### 针对该 instance的虚拟id , 同一组 instance设置相同
    priority 100                          ##### 优先级设定
    advert_int 1                          ##### 检测时间间隔
    authentication {
        auth_type PASS                    ##### 同一组instance之间的认证方式为 PASS ,pass 为7777 ，必须相同(防止有用户恶意伪造 vrrp)
        auth_pass 7777
    }
    virtual_ipaddress {
        192.168.111.60                    ##### 设置虚拟ip ,可以设置多个
    }

    track_script {
    chk_mysql                             ##### 表示该instance 使用chk_mysql进行相关检测
    }
  ##### 以下配置在该实例转换为 master,slave，或者出错的时候执行的脚本(可以设置邮件通知，或者处理一些其他问题)
   # notify_master "/etc/keepalived/change_master.sh"
   # notify_slave "/etc/keepalived/change_slave.sh"
   # notify_fault "/etc/keepalived/change_fault.sh"
}
root@192.168.111.53:keepalived# cat keepalived.conf
! Configuration File for keepalived
global_defs {
   notification_email {
     horand_gc@163.com
   }
   notification_email_from dba@163.com
   smtp_server smtp.163.com
   smtp_connect_timeout 30
   router_id haMySQL
}
vrrp_script chk_mysql {
        script "/etc/keepalived/chk_mysql.sh"
        interval 1
        weight 2
}
vrrp_instance VI_1 {
    state BACKUP                            ##### 该主机作为备机 BACKUP
    interface eth2
    virtual_router_id 51
    priority 99                             ##### 优先级设置小于 master
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 7777
    }
    virtual_ipaddress {
        192.168.111.60
    }

    track_script {
        chk_mysql
    }
}
root@192.168.111.52:keepalived# cat chk_mysql.sh
#!/bin/bash
num=`ps -ef |grep mysqld | grep -v grep | wc -l`        ##### 查看mysqld进程数量，正常情况有一个root起的mysqld_safe守护进程，还有一个属于mysql用户的mysqld进程
[[ $num -eq 2 ]] && exit 0 || exit 1
    3. 故障模拟
       ① 启动 keepalived

点击(此处)折叠或打开

root@192.168.111.52:keepalived# tail /var/log/messages
Apr 29 13:45:24 localhost Keepalived_healthcheckers[24184]: Registering Kernel netlink command channel
Apr 29 13:45:24 localhost Keepalived_healthcheckers[24184]: Opening file '/etc/keepalived/keepalived.conf'.
Apr 29 13:45:24 localhost Keepalived_healthcheckers[24184]: Configuration is using : 7417 Bytes
Apr 29 13:45:24 localhost Keepalived_vrrp[24185]: Opening file '/etc/keepalived/keepalived.conf'.
Apr 29 13:45:24 localhost Keepalived_vrrp[24185]: Configuration is using : 65552 Bytes
Apr 29 13:45:24 localhost Keepalived_vrrp[24185]: Using LinkWatch kernel netlink reflector...
Apr 29 13:45:24 localhost Keepalived_healthcheckers[24184]: Using LinkWatch kernel netlink reflector...
Apr 29 13:45:25 localhost Keepalived_vrrp[24185]: VRRP_Script(chk_mysql) succeeded
Apr 29 13:45:25 localhost Keepalived_vrrp[24185]: VRRP_Instance(VI_1) Transition to MASTER STATE
Apr 29 13:45:26 localhost Keepalived_vrrp[24185]: VRRP_Instance(VI_1) Entering MASTER STATE
root@192.168.111.53:keepalived# tailf /var/log/messages
Apr 29 13:49:27 localhost Keepalived_healthcheckers[32569]: Registering Kernel netlink reflector
Apr 29 13:49:27 localhost Keepalived_healthcheckers[32569]: Registering Kernel netlink command channel
Apr 29 13:49:27 localhost Keepalived_vrrp[32570]: Opening file '/etc/keepalived/keepalived.conf'.
Apr 29 13:49:27 localhost Keepalived_vrrp[32570]: Configuration is using : 65550 Bytes
Apr 29 13:49:27 localhost Keepalived_vrrp[32570]: Using LinkWatch kernel netlink reflector...
Apr 29 13:49:27 localhost Keepalived_healthcheckers[32569]: Opening file '/etc/keepalived/keepalived.conf'.
Apr 29 13:49:27 localhost Keepalived_healthcheckers[32569]: Configuration is using : 7415 Bytes
Apr 29 13:49:27 localhost Keepalived_vrrp[32570]: VRRP_Instance(VI_1) Entering BACKUP STATE
Apr 29 13:49:27 localhost Keepalived_healthcheckers[32569]: Using LinkWatch kernel netlink reflector...
Apr 29 13:49:27 localhost Keepalived_vrrp[32570]: VRRP_Script(chk_mysql) succeeded
    ip a 可以查看到 vip 192.168.111.60 在 192.168.111.52(master)上

    ② 关闭 111.52上面的mysql

点击(此处)折叠或打开

root@192.168.111.52:keepalived# mysqladmin shutdown
root@192.168.111.52:keepalived# tailf /var/log/messages
Apr 29 14:19:30 localhost Keepalived_vrrp[24862]: VRRP_Script(chk_mysql) failed
Apr 29 14:19:32 localhost Keepalived_vrrp[24862]: VRRP_Instance(VI_1) Received higher prio advert
Apr 29 14:19:32 localhost Keepalived_vrrp[24862]: VRRP_Instance(VI_1) Entering BACKUP STATE
root@192.168.111.53:keepalived# tailf /var/log/messages
Apr 29 14:19:55 localhost Keepalived_vrrp[566]: VRRP_Instance(VI_1) forcing a new MASTER election
Apr 29 14:19:56 localhost Keepalived_vrrp[566]: VRRP_Instance(VI_1) Transition to MASTER STATE
Apr 29 14:19:57 localhost Keepalived_vrrp[566]: VRRP_Instance(VI_1) Entering MASTER STATE
    ip a 可以查看到 vip 192.168.111.60 在 192.168.111.53(master)上

    ③ 启动111.52上面的mysql

点击(此处)折叠或打开

root@192.168.111.52:keepalived# mysqld_safe & ; tailf /var/log/messages
Apr 29 14:24:21 localhost Keepalived_vrrp[24862]: VRRP_Script(chk_mysql) succeeded
Apr 29 14:24:22 localhost Keepalived_vrrp[24862]: VRRP_Instance(VI_1) forcing a new MASTER election
Apr 29 14:24:23 localhost Keepalived_vrrp[24862]: VRRP_Instance(VI_1) Transition to MASTER STATE
Apr 29 14:24:24 localhost Keepalived_vrrp[24862]: VRRP_Instance(VI_1) Entering MASTER STATE
root@192.168.111.53:keepalived# tailf /var/log/messages
Apr 29 14:24:45 localhost Keepalived_vrrp[566]: VRRP_Instance(VI_1) Received higher prio advert
Apr 29 14:24:45 localhost Keepalived_vrrp[566]: VRRP_Instance(VI_1) Entering BACKUP STATE
    ip a 可以查看到 vip 192.168.111.60 在 192.168.111.52(master)上，也就是说111.52会持续通过track_script的脚本检查，若成功的话会恢复原来的优先级100 ，便把vip抢过来了(若不希望优先级高的直接上来直接抢占vip的话需要再instance 里面配置 nopreempt ,backup无需设置)

虚拟server
    以上是实验是通过 vrrp_script以及trace_script 实现优先级变换来实现故障转移的，现在看下通过虚拟server怎么实现mysql的高可用
    ① 配置

点击(此处)折叠或打开

root@192.168.111.52:keepalived# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
   notification_email {
     tab@taomee.com
   }
   notification_email_from dba@taomee.com
   smtp_server smtp.shidc.taomee.com
   smtp_connect_timeout 30
   router_id haMySQL
}
vrrp_script chk_mysql {
    script "/etc/keepalived/chk_mysql.sh"
    interval 1
    weight 2
}
vrrp_instance VI_1 {
    state MASTER
    interface eth2
    virtual_router_id 51
    priority 100
    nopreempt
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 7777
    }
    virtual_ipaddress {
        192.168.111.60
    }

#    track_script {
#    chk_mysql
#    }
}
virtual_server 192.168.111.60 3306 {
    delay_loop 6
    persistence_timeout 300
    protocol TCP
    real_server 192.168.111.52 3306 {
        weight 1
        notify_down /etc/keepalived/kill_self.sh
        TCP_CHECK {
            tcp_port 3306
            connect_timeout 3
        }
    }
}
root@192.168.111.53:keepalived# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
   notification_email {
     tab@taomee.com
   }
   notification_email_from dba@taomee.com
   smtp_server smtp.shidc.taomee.com
   smtp_connect_timeout 30
   router_id haMySQL
}
vrrp_script chk_mysql {
        script "/etc/keepalived/chk_mysql.sh"
        interval 1
        weight 2
}
vrrp_instance VI_1 {
    state BACKUP
    interface eth2
    virtual_router_id 51
    priority 99
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 7777
    }
    virtual_ipaddress {
        192.168.111.60
    }

#    track_script {    #########这里先注释掉通过追踪脚本的检查
#        chk_mysql
#    }
}
virtual_server 192.168.111.60 3306 {
    delay_loop 6
    persistence_timeout 300
    protocol TCP
    real_server 192.168.111.53 3306 {### 真实服务
        weight 1                                    #### 权重，用来多真实服务均衡使用
        notify_down /etc/keepalived/kill_self.sh    ####在检查该服务不可用时执行该脚本(用来杀死 keepalived 实现 vip 飘逸)
        TCP_CHECK {
                tcp_port 3306                       #### 检查端口继承 real_server 192.168.111.53 3306 {### 真实服务
            connect_timeout 3                       #### tcp超时时间
        }
    }
}
root@192.168.111.53:keepalived# cat /etc/keepalived/kill_self.sh
#!/bin/bash
killall keepalived

    ②启动keepalived

点击(此处)折叠或打开

root@192.168.111.52:keepalived# keepalived
root@192.168.111.52:keepalived# tailf /var/log/messages
Apr 29 14:48:22 localhost Keepalived_vrrp[20482]: Opening file '/etc/keepalived/keepalived.conf'.
Apr 29 14:48:22 localhost Keepalived_vrrp[20482]: Configuration is using : 64590 Bytes
Apr 29 14:48:22 localhost Keepalived_vrrp[20482]: Using LinkWatch kernel netlink reflector...
Apr 29 14:48:22 localhost Keepalived_healthcheckers[20481]: IPVS: Scheduler not found
Apr 29 14:48:22 localhost kernel: IPVS: Scheduler module ip_vs_ not found
Apr 29 14:48:22 localhost Keepalived_healthcheckers[20481]: IPVS: Service not defined
Apr 29 14:48:22 localhost Keepalived_healthcheckers[20481]: Using LinkWatch kernel netlink reflector...
Apr 29 14:48:22 localhost Keepalived_healthcheckers[20481]: Activating healthchecker for service [192.168.111.52]:3306
Apr 29 14:48:23 localhost Keepalived_vrrp[20482]: VRRP_Instance(VI_1) Transition to MASTER STATE
Apr 29 14:48:24 localhost Keepalived_vrrp[20482]: VRRP_Instance(VI_1) Entering MASTER STATE
root@192.168.111.53:keepalived# keepalived
root@192.168.111.53:keepalived# tailf /var/log/messages
Apr 29 14:48:51 localhost Keepalived_healthcheckers[25092]: Configuration is using : 11673 Bytes
Apr 29 14:48:51 localhost Keepalived_vrrp[25093]: Opening file '/etc/keepalived/keepalived.conf'.
Apr 29 14:48:51 localhost Keepalived_vrrp[25093]: Configuration is using : 64568 Bytes
Apr 29 14:48:51 localhost Keepalived_vrrp[25093]: Using LinkWatch kernel netlink reflector...
Apr 29 14:48:51 localhost Keepalived_vrrp[25093]: VRRP_Instance(VI_1) Entering BACKUP STATE
Apr 29 14:48:51 localhost kernel: IPVS: Scheduler module ip_vs_ not found
Apr 29 14:48:51 localhost Keepalived_healthcheckers[25092]: IPVS: Scheduler not found
Apr 29 14:48:51 localhost Keepalived_healthcheckers[25092]: IPVS: Service not defined
Apr 29 14:48:51 localhost Keepalived_healthcheckers[25092]: Using LinkWatch kernel netlink reflector...
Apr 29 14:48:51 localhost Keepalived_healthcheckers[25092]: Activating healthchecker for service [192.168.111.53]:3306
    此时 ip a 命令可以查看虚拟ip 111.60 在111.52(master) 上

    ③关闭 111.52上的mysql

点击(此处)折叠或打开

root@192.168.111.52:keepalived# mysqladmin shutdown
2017-04-29T07:07:38.121123Z mysqld_safe mysqld from pid file /opt/mysql/mysqld.pid ended
[1]+ Done                    mysqld_safe
root@192.168.111.52:keepalived# tailf /var/log/messages
Apr 29 15:07:31 localhost Keepalived_healthcheckers[23405]: TCP connection to [192.168.111.52]:3306 failed !!!
Apr 29 15:07:31 localhost Keepalived_healthcheckers[23405]: Removing service [192.168.111.52]:3306 from VS [192.168.111.60]:3306
Apr 29 15:07:31 localhost Keepalived_healthcheckers[23405]: IPVS: Service not defined
Apr 29 15:07:31 localhost Keepalived_healthcheckers[23405]: Executing [/etc/keepalived/kill_self.sh] for service [192.168.111.52]:3306 in VS [192.168.111.60]:3306
Apr 29 15:07:31 localhost Keepalived_healthcheckers[23405]: Lost quorum 1-0=1 > 0 for VS [192.168.111.60]:3306
Apr 29 15:07:31 localhost Keepalived_healthcheckers[23405]: Remote SMTP server [0.0.0.0]:25 connected.
Apr 29 15:07:31 localhost Keepalived[23404]: Stopping Keepalived v1.2.7 (02/21,2013)
Apr 29 15:07:31 localhost Keepalived_healthcheckers[23405]: IPVS: No such service
Apr 29 15:07:31 localhost Keepalived_vrrp[23406]: VRRP_Instance(VI_1) sending 0 priority
root@192.168.111.53:keepalived# tailf /var/log/messages
Apr 29 15:07:32 localhost Keepalived_vrrp[26815]: VRRP_Instance(VI_1) Transition to MASTER STATE
Apr 29 15:07:33 localhost Keepalived_vrrp[26815]: VRRP_Instance(VI_1) Entering MASTER STATE
    此时 ip a 命令可以看到虚拟ip 111.60在111.53(新master)上

以上是“MySQL高可用之keepalived方案的示例分析”这篇文章的所有内容，感谢各位的阅读！

（编辑：PHP编程网 - 黄冈站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

数据库中间件ProxySQL	zabbix 一次悲惨的分区
mysql如何实现数据备份	建立数据表主键外键