基于keepalived的MySQL高可用

基于keepalived的MySQL高可用

MySQL双主模型，keepalived提供高可用服务。主服务器宕机时，VIP切至备服务器，由备服务提供主服务器的读写任务。原先的主服务器恢复后，角色切换为备服务器，提供热备功能。

架构

M1：192.168.31.140(master)
M2：192.168.31.130(backup)
VIP：192.168.31.150

MySQL MM模型

配置略去，两台MySQL都需要对允许访问的客户端用户地址进行授权

keepalived 配置

安装：两边直接使用rpm安装即可

 [root@baseos-1_192.168.31.140 ~]# yum install -y keepalived

配置：

[root@baseos-1_192.168.31.140 ~]# vim /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 LVS_DEVEL
}
vrrp_script check_mysqld { #调用MYSQL监控脚本的函数
    script "/data/sh/mysqld_check.sh"
    interval 1
    weight 1
}
vrrp_instance VI_1 {
    state BACKUP 			#两台主机都写上BACKUP。
    interface eth0			#vip对外的网络接口，机子上是哪个就写哪个。
    virtual_router_id 10	#标识号。两台主机要统一起来。
    priority 100			#优先级，优先级高的为主机。
    advert_int 1
    nopreempt				#不抢占模式，在优先级高的那台设置此参数，一般在主上设置，主的优先级高。
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    track_script { 
        check_mysqld 
    }
    virtual_ipaddress {
        192.168.31.150/24 dev eth0
    }
}

MySQL监控脚本：

[root@baseos-1_192.168.31.140 ~]# vim /data/sh/mysqld_check.sh 
#!/bin/bash
KeepAliveLog="/data/logs/keepalive_mysql_check.log"
MyAlive=$(/usr/local/mysql/bin/mysqladmin  -uroot -p`cat /data/save/mysql_root` ping 2> /dev/null | grep -i -c alive)
if [ "X$MyAlive" != "X1" ] ; then
    echo "[`date +%F" "%T`] Wanning,mysql is not alive." >> $KeepAliveLog
    /etc/init.d/keepalived stop &> /dev/null
    echo "[`date +%F" "%T`] Service keepalive has being stopd." >> $KeepAliveLog
fi

查看主服务IP地址

[root@baseos-1_192.168.31.140 ~]# ip add
1: lo: <LOOPBACK,UP,LOWER_UP> 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: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 00:0c:29:ef:84:a3 brd ff:ff:ff:ff:ff:ff
    inet 192.168.31.140/24 brd 192.168.31.255 scope global eth0
    inet 192.168.31.150/24 scope global secondary eth0
    inet6 fe80::20c:29ff:feef:84a3/64 scope link 
       valid_lft forever preferred_lft forever

登录验证

[root@baseos-1_192.168.31.140 ~]# mysql -uroot -p4399 -h 192.168.31.150
***
(root@192.168.31.150) [(none)] show variables like '%hostname%';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| hostname      | baseos-1 |
+---------------+----------+
1 row in set (0.01 sec)

主MySQL服务停止，查看从服务器IP地址，并登录验证

# 主MySQL停服
[root@baseos-1_192.168.31.140 ~]# /etc/init.d/mysqld stop
Shutting down MySQL... SUCCESS! 

# 从服务查看IP地址
[root@baseos-2_192.168.31.130  ~]# ip add
1: lo: <LOOPBACK,UP,LOWER_UP> 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: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 00:0c:29:61:96:62 brd ff:ff:ff:ff:ff:ff
    inet 192.168.31.130/24 brd 192.168.31.255 scope global eth0
    inet 192.168.31.150/24 scope global secondary eth0
    inet6 fe80::20c:29ff:fe61:9662/64 scope link 
       valid_lft forever preferred_lft forever

# 授权客户端登录验证
[root@baseos-1_192.168.31.140 ~]# mysql -uroot -p4399 -h 192.168.31.150
***
(root@192.168.31.150) [(none)] show variables like '%hostname%';
+---------------+-------------+
| Variable_name | Value       |
+---------------+-------------+
| hostname      | baseos-2 |
+---------------+-------------+
1 row in set (0.00 sec)