利用Keepalived实现MySQL高可用

最新推荐文章于 2021-02-02 15:30:55 发布

cpseh68048

最新推荐文章于 2021-02-02 15:30:55 发布

阅读量91

点赞数

使用MySQL双master+keepalived是一种很好的HA解决方案，在MySQL-HA环境中，MySQL互为主从关系，这样就保证了两台MySQL数据的一致性，然后用keepalived实现虚拟IP，通过keepalived自带的服务监控功能来实现MySQL故障时自动切换。

MySQL双主配置

master1 : 192.168.90.128
master2 : 192.168.90.129
mysql-vip: 192.168.90.200

keepalived安装及配置

安装依赖包
shell> rpm -q openssl openssl-devel popt 
openssl-1.0.0-20.el6.i686
openssl-devel-1.0.0-20.el6.i686
popt-1.13-7.el6.i686

安装keepalived
shell> tar -zxvf keepalived-1.2.14
shell> cd keepalived-1.2.14/

shell> ./configure --prefix=/usr/local/keepalived 
·
·
Keepalived configuration
------------------------
Keepalived version       : 1.2.14
Compiler                 : gcc
Compiler flags           : -g -O2
Extra Lib                : -lssl -lcrypto -lcrypt 
Use IPVS Framework       : Yes
IPVS sync daemon support : Yes
IPVS use libnl           : No
fwmark socket support    : Yes
Use VRRP Framework       : Yes
Use VRRP VMAC            : Yes
SNMP support             : No
SHA1 support             : No
Use Debug flags          : No

shell> make && make install

shell> ln -s /usr/local/keepalived/sbin/keepalived /usr/local/bin/keepalived

配置keepalived
shell> mkdir -p /etc/keepalived/
shell> vi /etc/keepalived/keepalived.conf
=========================================================================
#全局配置
global_defs {
  #表示keepalived在发生诸如切换操作时发送Email给哪些地址，邮件地址可以多个，每行一个
  notification_email {
    test@admin.com
  }

  #表示发送通知邮件时邮件源地址是谁
  notification_email_from admin@aidmin.com
  #表示发送email时使用的smtp服务器地址，这里可以用本地的sendmail来实现
  smtp_server 127.0.0.1
  #连接smtp连接超时时间
  smtp_connect_timeout 30
  #机器标识
  router_id MySQL-ha
}

vrrp_instance VI_1 {
  state BACKUP   #两台配置此处均是BACKUP
  interface eth0
  virtual_router_id 51
  priority 100   #优先级，另一台改为90
  #检查间隔，默认为1秒
  advert_int 1
  nopreempt  #不抢占，只在优先级高的机器上设置即可，优先级低的机器不设置
  authentication {
    auth_type PASS
    auth_pass 1111
  }

  #这里设置的就是VIP，也就是虚拟IP地址
  virtual_ipaddress {
    192.168.90.200
  }
}

virtual_server 192.168.90.200 3306 {
  delay_loop 2   #每个2秒检查一次real_server状态
  lb_algo wrr   #LVS算法
  lb_kind DR    #LVS模式
  persistence_timeout 60   #会话保持时间
  protocol TCP
  real_server 192.168.90.130 3306 {
    weight 3
    notify_down /db/mysql/mysql.sh  #检测到服务down后执行的脚本
    TCP_CHECK {
      connect_timeout 10     #连接超时时间
      nb_get_retry 3         #重连次数
      delay_before_retry 3   #重连间隔时间
      connect_port 3306      #健康检查端口
    }
  }
}

=========================================================================
主负载均衡器与备负载均衡器配置文件的差异：
vrrp_instance的优先级 priority、virtual_server的 real_server、vrrp_instance的 nopreempt

shell> vi /db/mysql/mysql.sh
=========================================================================
#!/usr/bin/env bash
pkill keepalived
/etc/init.d/network restart
=========================================================================

shell> chmod u+x /db/mysql/mysql.sh

shell> keepalived –D  
查看keepalived日志
shell> tail -f /var/log/messages
Feb  5 23:14:32 rac1 kernel: IPVS: [wrr] scheduler registered.
Feb  5 23:14:32 rac1 Keepalived_healthcheckers[3931]: Using LinkWatch kernel netlink reflector...
Feb  5 23:14:32 rac1 Keepalived_healthcheckers[3931]: Activating healthchecker for service [192.168.90.128]:3306
Feb  5 23:14:35 rac1 Keepalived_vrrp[3932]: VRRP_Instance(VI_1) Transition to MASTER STATE
Feb  5 23:14:36 rac1 Keepalived_vrrp[3932]: VRRP_Instance(VI_1) Entering MASTER STATE
Feb  5 23:14:36 rac1 Keepalived_vrrp[3932]: VRRP_Instance(VI_1) setting protocol VIPs.
Feb  5 23:14:36 rac1 Keepalived_vrrp[3932]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth0 for 192.168.90.200
Feb  5 23:14:36 rac1 avahi-daemon[1314]: Registering new address record for 192.168.90.200 on eth0.IPv4.
Feb  5 23:14:36 rac1 Keepalived_healthcheckers[3931]: Netlink reflector reports IP 192.168.90.200 added
Feb  5 23:14:41 rac1 Keepalived_vrrp[3932]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth0 for 192.168.90.200

测试验证

远程通过VIP登录测试

shell> mysql -h 192.168.90.200 -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 556
Server version: 5.6.19-log Source distribution

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

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>

keepalived故障转移测试

shell> ps -ef |grep keepalived
root      3930     1  0 23:14 ?        00:00:00 keepalived -D
root      3931  3930  0 23:14 ?        00:00:00 keepalived -D
root      3932  3930  0 23:14 ?        00:00:00 keepalived -D
root      4069  4019  0 23:46 pts/2    00:00:00 grep keepalived
shell> pkill keepalived
shell> ps -ef |grep keepalived
root      4073  4019  0 23:46 pts/2    00:00:00 grep keepalived

【db1】
Feb  5 23:46:24 rac1 Keepalived[3930]: Stopping Keepalived v1.2.14 (02/05,2015)
Feb  5 23:46:24 rac1 Keepalived_vrrp[3932]: VRRP_Instance(VI_1) sending 0 priority
Feb  5 23:46:24 rac1 Keepalived_vrrp[3932]: VRRP_Instance(VI_1) removing protocol VIPs.
Feb  5 23:46:24 rac1 Keepalived_healthcheckers[3931]: Removing service [192.168.90.128]:3306 from VS [192.168.1.200]:3306
Feb  5 23:46:24 rac1 avahi-daemon[1314]: Withdrawing address record for 192.168.90.200 on eth0.

【db2】
Feb  5 23:46:20 rac2 Keepalived_vrrp[2243]: VRRP_Instance(VI_1) Transition to MASTER STATE
Feb  5 23:46:21 rac2 Keepalived_vrrp[2243]: VRRP_Instance(VI_1) Entering MASTER STATE
Feb  5 23:46:21 rac2 Keepalived_vrrp[2243]: VRRP_Instance(VI_1) setting protocol VIPs.
Feb  5 23:46:21 rac2 Keepalived_vrrp[2243]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth0 for 192.168.90.200
Feb  5 23:46:21 rac2 avahi-daemon[1322]: Registering new address record for 192.168.90.200 on eth0.IPv4.
Feb  5 23:46:21 rac2 Keepalived_healthcheckers[2242]: Netlink reflector reports IP 192.168.90.200 added
Feb  5 23:46:26 rac2 Keepalived_vrrp[2243]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth0 for 192.168.90.200

可以看出VIP可以自动切换，keepalived切换速度还是非常块的

MySQL故障转移测试

【db1】
手动关闭db1数据库，模拟宕机
shell> mysqladmin -u root -p shutdown
shell>  tail -f /var/log/messages
Feb  6 01:05:59 rac2 Keepalived_healthcheckers[2538]: TCP connection to [192.168.90.129]:3306 failed !!!
Feb  6 01:05:59 rac2 Keepalived_healthcheckers[2538]: Removing service [192.168.90.129]:3306 from VS [192.168.90.200]:3306
Feb  6 01:05:59 rac2 Keepalived_healthcheckers[2538]: Executing [/db/mysql/mysql.sh] for service 
[192.168.90.129]:3306 in VS [192.168.90.200]:3306
Feb  6 01:05:59 rac2 Keepalived_healthcheckers[2538]: Lost quorum 1-0=1 > 0 for VS [192.168.90.200]:3306
Feb  6 01:05:59 rac2 Keepalived_healthcheckers[2538]: Remote SMTP server [127.0.0.1]:25 connected.
Feb  6 01:05:59 rac2 Keepalived[2537]: Stopping Keepalived v1.2.13 (08/07,2014)
Feb  6 01:05:59 rac2 Keepalived_vrrp[2539]: VRRP_Instance(VI_1) sending 0 priority
Feb  6 01:05:59 rac2 Keepalived_vrrp[2539]: VRRP_Instance(VI_1) removing protocol VIPs.
Feb  6 01:05:59 rac2 avahi-daemon[1322]: Withdrawing address record for 192.168.90.200 on eth0.
Feb  6 01:05:59 rac2 avahi-daemon[1322]: Withdrawing address record for 192.168.90.129 on eth0.
Feb  6 01:05:59 rac2 avahi-daemon[1322]: Leaving mDNS multicast group on interface eth0.IPv4 with address 192.168.90.129.
Feb  6 01:06:00 rac2 avahi-daemon[1322]: Interface eth0.IPv4 no longer relevant for mDNS.
Feb  6 01:06:00 rac2 avahi-daemon[1322]: Withdrawing address record for fe80::20c:29ff:fe34:8dbe on eth0.
Feb  6 01:06:01 rac2 kernel: lo: Disabled Privacy Extensions
Feb  6 01:06:01 rac2 kernel: eth0: link up
Feb  6 01:06:04 rac2 avahi-daemon[1322]: Registering new address record for fe80::20c:29ff:fe34:8dbe on eth0.*.
Feb  6 01:06:04 rac2 avahi-daemon[1322]: Joining mDNS multicast group on interface eth0.IPv4 with address 192.168.90.129.
Feb  6 01:06:05 rac2 avahi-daemon[1322]: New relevant interface eth0.IPv4 for mDNS.
Feb  6 01:06:05 rac2 avahi-daemon[1322]: Registering new address record for 192.168.90.129 on eth0.IPv4.
150206 01:06:10 mysqld_safe mysqld from pid file /mysql/mysql/rac2.pid ended

shell> ps -ef |grep mysql |grep -v grep
root      2316  2296  0 Feb05 pts/2    00:00:00 mysql -u root -p
shell> ps -ef |grep keepalived |grep -v grep

【db2】
shell>  tail -f /var/log/messages
Feb  6 01:06:04 rac1 Keepalived_vrrp[5184]: VRRP_Instance(VI_1) Transition to MASTER STATE
Feb  6 01:06:05 rac1 Keepalived_vrrp[5184]: VRRP_Instance(VI_1) Entering MASTER STATE
Feb  6 01:06:05 rac1 Keepalived_vrrp[5184]: VRRP_Instance(VI_1) setting protocol VIPs.
Feb  6 01:06:05 rac1 Keepalived_vrrp[5184]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth0 for 192.168.90.200
Feb  6 01:06:05 rac1 avahi-daemon[1314]: Registering new address record for 192.168.90.200 on eth0.IPv4.
Feb  6 01:06:05 rac1 Keepalived_healthcheckers[5183]: Netlink reflector reports IP 192.168.90.200 added
Feb  6 01:06:10 rac1 Keepalived_vrrp[5184]: VRRP_Instance(VI_1) Sending gratuitous ARPs on eth0 for 192.168.90.200

shell> ip a
1: lo:  mtu 16436 qdisc noqueue state UNKNOWN 
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
2: eth0:  mtu 1500 qdisc pfifo_fast state UNKNOWN qlen 1000
    link/ether 00:0c:29:2d:c1:03 brd ff:ff:ff:ff:ff:ff
    inet 192.168.90.128/24 brd 192.168.90.255 scope global eth0
    inet 192.168.90.200/32 scope global eth0
    inet6 fe80::20c:29ff:fe2d:c103/64 scope link 
       valid_lft forever preferred_lft forever

keepalived只能做到对数据库端口的健康检查，但是做不到比如像MySQL复制中的slave-SQL、slave-IO进程的检查。所以要想做到一些细致的健康检查，还得需要借助额外的监控工具，比如nagios，然后用nagios实现短信、邮件报警，从而能够有效地解决问题。