mysql使用keepalive+互为主从,实现高可用,主从分离

http://gaoke.iteye.com/blog/2283890
https://www.2cto.com/database/201411/353346.html

1.主从分离,两个VIP
主库:192.168.0.90 (写库,VIP:192.168.0.199)
从库:192.168.0.91 (读库,VIP:192.168.0.200)
2.主库keepalived.conf:

vrrp_instance VI_1 {   
    state BACKUP   #从库VI_1同为backup
    interface eth0
    virtual_router_id 51  #与VI_2的要不相同
    priority 100    #主库写,这里设置大
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.0.199
    }
}

vrrp_instance VI_2 {
    state BACKUP    #从库读,为MASTER
    interface eth0
    virtual_router_id 44  #与VI_1的要不相同
    priority 50     #从库读,这里设置小
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.0.200
    }
}

virtual_server 192.168.0.199 3306 {
     delay_loop 2
     lb_algo wrr #带有权重的轮询
     lb_kind DR
     persistence_timeout 60 #同一IP的连接60秒内被分配到同一台真实服务器
     protocol TCP
     real_server 192.168.0.90 3306 {
         weight 3 #权重为3
         notify_down /data/keepalived_shutdown.sh #当mysq服down时，执行此脚本，杀死keepalived实现切换, 自杀脚本.
         TCP_CHECK {
             connect_timeout 10
             nb_get_retry 3
             delay_before_retry 3
             connect_port 3306
         }
     }
}

virtual_server 192.168.0.200 3306 {
     delay_loop 2
     lb_algo wrr #带有权重的轮询
     lb_kind DR
     persistence_timeout 60 #同一IP的连接60秒内被分配到同一台真实服务器
     protocol TCP
     real_server 192.168.0.90 3306 {
         weight 3 #权重为3
         notify_down /data/keepalived_shutdown.sh #当mysq服down时，执行此脚本，杀死keepalived实现切换, 自杀脚本.
         TCP_CHECK {
             connect_timeout 10
             nb_get_retry 3
             delay_before_retry 3
             connect_port 3306
         }
     }
}

3.从库keepalived.conf:

vrrp_instance VI_1 {
    state BACKUP   #与主库写VI_1相同
    interface eth0
    virtual_router_id 51   #与主库写VI_1相同,与从库读不同
    priority 50
    advert_int 1
    #nopreempt #不主动抢占资源，设置非抢占模式
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.0.199
    }
}

vrrp_instance VI_2 {
    state MASTER  #从库读,这里设置成master
    interface eth0
    virtual_router_id 44 
    priority 100   #从库读,这里设置成大
    advert_int 1
    #nopreempt #不主动抢占资源，设置非抢占模式
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.0.200
    }
}

virtual_server 192.168.0.199 3306 {
     delay_loop 2 #每2秒检查一次real_server存活
     lb_algo wrr
     lb_kind DR
     persistence_timeout 60
     protocol TCP
     real_server 192.168.0.91 3306 {
         weight 10
         notify_down /data/keepalived_shutdown.sh
         TCP_CHECK {
             connect_timeout 10 #连接超时时间
             nb_get_retry 4 #重连次数
             delay_before_retry 3 #重连间隔时间
             connect_port 3306 #健康检查端口，配置自己mysql服务端口
         }
     }
}


virtual_server 192.168.0.200 3306 {
     delay_loop 2 #每2秒检查一次real_server存活
     lb_algo wrr
     lb_kind DR
     persistence_timeout 60
     protocol TCP
     real_server 192.168.0.91 3306 {
         weight 10
         notify_down /data/keepalived_shutdown.sh
         TCP_CHECK {
             connect_timeout 10 #连接超时时间
             nb_get_retry 4 #重连次数
             delay_before_retry 3 #重连间隔时间
             connect_port 3306 #健康检查端口，配置自己mysql服务端口
         }
     }
}

4.keepalived_shutdown.sh脚本

#!/bin/bash
#kill掉keepalived进程，以防止脑裂问题。
pkill keepalived