基于keepalived的mysql_基于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: 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 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: 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 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)