基于keepalived搭建MySQL热机集群

背景

MySQL的高可用方案一般有如下几种：

keepalived+双主，MHA，MMM，Heartbeat+DRBD，PXC，Galera Cluster

比较常用的是keepalived+双主，MHA和PXC。

对于小公司，一般推荐使用keepalived+双主，便于维护。

环境

mysql双主配置

1. 修改配置文件

master1中有关复制的配置如下：

[mysqld]
log-bin=mysql-bin
server-id=1
log_slave_updates=1

master2中有关复制的配置如下：

[mysqld]
log-bin=mysql-bin
server-id=2
log_slave_updates=1
read_only=1

改完之后把两个数据库都重启了

2. 创建复制用户

master1中创建：

CREATE USER 'repl'@'10.1.80.114' IDENTIFIED BY 'Mysql@2019';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.1.80.114';

master2中创建：

CREATE USER 'repl'@'10.1.80.113' IDENTIFIED BY 'Mysql@2019';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.1.80.113';

3. 执行CHANGE MASTER TO语句

因是从头搭建MySQL主从复制集群，所以不需要获取全局读锁来得到二进制日志文件的位置，直接根据show master status的输出来确认。

master1上执行：

CHANGE MASTER TO
  MASTER_HOST='10.1.80.114',
  MASTER_USER='repl',
  MASTER_PASSWORD='Mysql@2019',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=154;

master2上执行：　　

CHANGE MASTER TO
  MASTER_HOST='10.1.80.113',
  MASTER_USER='repl',
  MASTER_PASSWORD='Mysql@2019',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=154;

4. 分别在两个节点上执行start slave;语句

并通过show slave status\G查看复制是否搭建成功。

出现如下内容说明成功。

 

5、数据同步测试

在两侧插入数据发现正常同步。

 keepalived配置

1、安装依赖包

yum install gcc 
yum install openssl*

2、下载软件，解压编译

#下载
wget   http://www.keepalived.org/software/keepalived-2.0.10.tar.gz
#解压
tar -zxvf keepalived-2.0.10.tar.gz 
#编译
./configure --prefix=/keepalived

3、初始化以及启动

# keepalived启动脚本变量引用文件，默认文件路径是/etc/sysconfig/，也可以不做软链接，直接修改启动脚本中文件路径即可（安装目录下）
[root@localhost /]# cp /keepalived/etc/sysconfig/keepalived  /etc/sysconfig/keepalived 
 
# 将keepalived主程序加入到环境变量（安装目录下）
[root@localhost /]# cp /keepalived/sbin/keepalived /usr/sbin/keepalived
 
# keepalived启动脚本（源码目录下），放到/etc/init.d/目录下就可以使用service命令便捷调用
[root@localhost /]# cp /tmp/keepalived-2.0.10/keepalived/etc/init.d/keepalived  /etc/init.d/keepalived
 
# 将配置文件放到默认路径下
[root@localhost /]# mkdir /etc/keepalived
[root@localhost /]# cp /keepalived/etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf

#加为系统服务：
chkconfig --add keepalived
#开机启动：
chkconfig keepalived on
#查看开机启动的服务：
chkconfig --list
#启动、关闭、重启
service keepalived start|stop|restart

4、修改配置文件

master1

vi /etc/keepalived/keepalived.conf

! Configuration File for keepalived
       
global_defs {
notification_email {
ops@wangshibo.cn
tech@wangshibo.cn
}
       
notification_email_from ops@wangshibo.cn
smtp_server 127.0.0.1 
smtp_connect_timeout 30
router_id MASTER-HA
}
       
vrrp_script chk_mysql_port {            #检测mysql服务是否在运行。有很多方式，比如进程，用脚本检测等等
    script "/keepalived/chk_mysql.sh"   #这里通过脚本监测
    interval 2                          #脚本执行间隔，每2s检测一次
    weight -5                           #脚本结果导致的优先级变更，检测失败（脚本返回非0）则优先级 -5
    fall 2                              #检测连续2次失败才算确定是真失败。会用weight减少优先级（1-255之间）
    rise 1                              #检测1次成功就算成功。但不修改优先级
}
       
vrrp_instance VI_1 {
    state MASTER    
    interface eth2          #指定虚拟ip的网卡接口
    mcast_src_ip 10.1.80.113
    virtual_router_id 51    #路由器标识，MASTER和BACKUP必须是一致的
    priority 101            #数字越大，优先级越高，同一个vrrp_instance下，MASTER的优先级必须大于BACKUP的优先级。这样MASTER故障恢复后，就可以将VIP资源再次抢回来 
    advert_int 1         
    authentication {   
        auth_type PASS 
        auth_pass 1111     
    }
    virtual_ipaddress {    
        10.1.80.119
    }
      
track_script {               
   chk_mysql_port             
}
}

编写切换脚本。KeepAlived做心跳检测，如果Master的MySQL服务挂了(3306端口挂了),那么它就会选择自杀。

Slave的KeepAlived通过心跳检测发现这个情况，就会将VIP的请求接管

vi chk_mysql.sh

#!/bin/bash
counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l)
if [ "${counter}" -eq 0 ]; then
    service keepalived stop
fi

chmod 755 chk_mysql.sh

启动keepalived服务。

master2

vi /etc/keepalived/keepalived.conf

! Configuration File for keepalived
       
global_defs {
notification_email {
ops@wangshibo.cn
tech@wangshibo.cn
}
       
notification_email_from ops@wangshibo.cn
smtp_server 127.0.0.1 
smtp_connect_timeout 30
router_id MASTER-HA
}
       
vrrp_script chk_mysql_port {
    script "/keepalived/chk_mysql.sh"
    interval 2            
    weight -5                 
    fall 2                 
    rise 1               
}
       
vrrp_instance VI_1 {
    state BACKUP
    interface eth2
    mcast_src_ip 10.1.80.114
    virtual_router_id 51    
    priority 99          
    advert_int 1         
    authentication {   
        auth_type PASS 
        auth_pass 1111     
    }
    virtual_ipaddress {    
        10.1.80.119
    }
      
track_script {               
   chk_mysql_port             
}
}

vi chk_mysql.sh

#!/bin/bash
counter=$(netstat -na|grep "LISTEN"|grep "3306"|wc -l)
if [ "${counter}" -eq 0 ]; then
    service keepalived stop
fi

chmod 755 chk_mysql.sh

查看网卡