keepalived实现mysql双主架构

5.1 架构介绍

企业级Mysql集群具备高可用、可扩展、以管理、低成本的特点。下面将介绍企业环境中经常应用的一个解决方案，即Mysql的双主互备架构，主要设计思想是通过Mysql复制技术将两台Mysql服务器互相将对方作为自己的主服务器，自己又同时作为对方的从服务器来进行复制。这样就实现了高可用架构中的数据同步功能，同时，将采用keepalived来实现mysql的自动故障切换。在这个架构中，虽然两台Mysql服务器互为主从，但同一时刻只有一个Mysql服务器可读写，另一个Mysql服务器只能进行读操作，这样可保证数据的一致性。整个架构如下：

如上图，DB1和DB2互为主从，这样就保证了两台Mysql服务器的数据始终是同步的，同时在DB1和DB2上还需要安装高可用软件keepalived。在正常情况下，Web服务器主机仅从DB1进行数据的读写操作，DB2主负责从DB1同步数据。而Keepalived维护一个VIP，此IP用来对外部提供链接服务。同时，keepalived还负责监控DB1和DB2上Mysql数据库的运行状态。当DB1主机出现故障或者Mysql运行异常时，自动将VIP地址和Mysql服务切换到DB2上，此时Web服务器主机继续从DB2进行数据的读写操作。通过Keepalived保持了数据库服务的连续性，整个切换过程非常快，并且对前端Web服务器主机是透明的

5.2 主主互备模式配置

(略)

5.3 配置Keepalived实现Mysql双主高可用

5.3.1 安装keepalived

参考《企业级web集群——keepalived》

安装软件

wget https://www.keepalived.org/software/keepalived-2.0.1.tar.gz
?
[root@bogon keepalived-2.0.1]# tar -xf keepalived-2.0.1.tar.gz -C /usr/local/src/
[root@bogon keepalived-2.0.1]# mkdir -p /data/keepalived
[root@bogon keepalived-2.0.1]# yum install openssl-devel gcc gcc-c++ make
[root@bogon keepalived-2.0.1]# ./configure --prefix=/data/keepalived/
make 
make install

配置规范启动

[root@bogon etc]# pwd
/usr/local/src/keepalived-2.0.1/keepalived/etc
[root@bogon etc]# cp -R init /data/keepalived/ 
[root@bogon etc]# cp -R init /data/keepalived/etc/ 
[root@bogon etc]# cp -R init.d /data/keepalived/etc/
[root@bogon etc]# ll /data/keepalived/etc/
总用量 0
drwxr-xr-x. 2 root root  86 4月  20 12:03 init
drwxr-xr-x. 2 root root 135 4月  20 12:03 init.d
drwxr-xr-x. 3 root root  44 4月  20 11:00 keepalived
drwxr-xr-x. 2 root root  24 4月  20 11:00 sysconfig

[root@bogon etc]# cp /data/keepalived/etc/init.d/keepalived /etc/init.d/
[root@bogon etc]# cp /data/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
[root@bogon etc]# mkdir /etc/keepalived
[root@bogon etc]# cp /data/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/
[root@bogon etc]# cp /data/keepalived/sbin/keepalived /usr/sbin/

启动

[root@bogon etc]# /etc/init.d/keepalived start
Starting keepalived (via systemctl):                       [  确定  ]
[root@bogon etc]# ps -ef |grep keepalived
root     14858     1  0 12:07 ?        00:00:00 /data/keepalived/sbin/keepalived -D
root     14859 14858  0 12:07 ?        00:00:00 /data/keepalived/sbin/keepalived -D
root     14860 14858  0 12:07 ?        00:00:00 /data/keepalived/sbin/keepalived -D
root     14875  1702  0 12:09 pts/1    00:00:00 grep --color=auto keepalived

5.3.2 keepalived配置

在ser01上：

[root@localhost ~]# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived
?
global_defs {
    notification_email {
     acassen@firewall.loc
     failover@firewall.loc
     sysadmin@firewall.loc
    }
    notification_email_from Alexandre.Cassen@firewall.loc
    smtp_server 192.168.200.1
    smtp_connect_timeout 30
    router_id HA_MYSQL01
}
?
vrrp_script check_mysqld {
    script "/etc/keepalived/check_mysqld.sh"
    interval 2
}
?
vrrp_instance HA_1 {
    state MASTER
    interface eth0
    virtual_router_id 51
    priority 150
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
       192.168.42.201
    }
?
    track_script {
       check_mysqld
    }
}

在ser02上：

! Configuration File for keepalived
?
global_defs {
    notification_email {
     acassen@firewall.loc
     failover@firewall.loc
     sysadmin@firewall.loc
    }
    notification_email_from Alexandre.Cassen@firewall.loc
    smtp_server 192.168.200.1
    smtp_connect_timeout 30
    router_id HA_MYSQL01
}
?
vrrp_script check_mysqld {
    script "/etc/keepalived/check_mysqld.sh"
    interval 2
}
?
vrrp_instance HA_1 {
    state BACKUP  
    interface eth0
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.42.201
    }
?
    track_script {
        check_mysqld
    }
}

监控脚本：

[root@localhost ~]# cat /etc/keepalived/check_mysqld.sh 
#!/bin/bash
user=root
pass=Cloudbu@123
/usr/local/mysql/bin/mysql -u$user -p$pass -e "show status;" > /dev/null 2>&1
if [ $? -eq 0 ];
   then
   MYSQL_STATUS=0
   else
       MYSQL_STATUS=1
fi
exit $MYSQL_STATUS
[root@localhost ~]# chmod +x /etc/keepalived/check_mysqld.sh

按照以上的配置，虽然能够完成故障的切换，但是在数据库的应用场景中，我们不建议数据库的频繁切换，因此，我们可将其配置为keepalived的VIP不抢占模式。具体配置如下：

在ser01上：

[root@localhost keepalived]# cat keepalived.conf
! Configuration File for keepalived
?
global_defs {
    notification_email {
     acassen@firewall.loc
     failover@firewall.loc
     sysadmin@firewall.loc
    }
    notification_email_from Alexandre.Cassen@firewall.loc
    smtp_server 192.168.200.1
    smtp_connect_timeout 30
    router_id HA_MYSQL01
}
?
vrrp_script check_mysqld {
    script "/etc/keepalived/check_mysqld.sh"
    interval 2
}
?
vrrp_instance HA_1 {
    state BACKUP      #在ser01和ser02上均配置为backup
    interface eth0
    virtual_router_id 51
    priority 150
    advert_int 1
    nopreempt   #不抢占模式，只在优先级高的机器上设置即可，优先级低的机器上不设置。
    authentication {
        auth_type PASS
        auth_pass 1111
    }
?
    virtual_ipaddress {
        192.168.42.201
    }
?
    track_script {
        check_mysqld
    }
}

在ser02上：

[root@localhost keepalived]# cat keepalived.conf 
! Configuration File for keepalived
?
global_defs {
    notification_email {
     acassen@firewall.loc
     failover@firewall.loc
     sysadmin@firewall.loc
    }
    notification_email_from Alexandre.Cassen@firewall.loc
    smtp_server 192.168.200.1
    smtp_connect_timeout 30
    router_id HA_MYSQL01
}
?
vrrp_script check_mysqld {
    script "/etc/keepalived/check_mysqld.sh"
    interval 2
}
?
vrrp_instance HA_1 {
    state BACKUP      #在ser01和ser02上均配置为backup
    interface eth0
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.42.201
    }
?
    track_script {
        check_mysqld
    }
}

在优先级比较高的节点上配置成非抢占模式，使得我们故障切换后，保持在原节点上，这种场景在mysql数据库高可用方案中非常实用。

5.4 Mysql配置账号测试

5.4.1 授权账号

mysql> create user ‘keepalived‘@‘%‘ identified by ‘Cloudbu@123‘;
mysql> ALTER USER ‘keepalived‘@‘%‘ IDENTIFIED WITH mysql_native_password BY ‘Cloudbu@123‘;
mysql> flush privileges;

5.4.2 结果测试

在测试节点上用192.168.42.201，即浮动ip测试。

 mysql -h192.168.42.201 -ukeepalived -pCloudbu@123

当我们ser01失败后，ser02能够实现数据的读写；

当ser01恢复后，能够实现数据同步，vip并没有漂移到ser01，而是保持在ser02上；

当ser02故障后，VIP自然漂移到了ser01上。

keepalived实现mysql双主架构

标签：contain   参考   规范   instance   tps   usr   nss   tor   sql数据库