mysql双机热备keepalive_基于主主复制的mysql双机热备+keepalived实现高可用性

1、环境

Master1 192.168.1.135

Master2 192.168.1.136

Client 192.168.1.137

2、Master1操作授权

mysql->grant replication slave on *.* to 'admin'@'192.168.1.136' identified by 'aixocm'; 授权操作

mysql->flush privileges; 刷新权限

mysql->show master status\G; 查看此时的binlog日志文件名和pos号

3、Master2操作授权

mysql->grant replication slave on *.* to 'backup'@'192.168.1.135' identified by 'aixocm'; 授权操作

mysql->flush privileges; 同上

mysql->show master status\G; 同上

4、主1-135上操作

mysql->stop slave;

mysql->change

master to master_host='192.168.1.136', master_user='backup',

master_password='aixocm',

master_log_file='mysql-bin.000006',master_log_pos=411;

这里的文件名和pos号就是上面Master1查看的具体内容

mysql->start slave;

mysql->show slave status\G;

开启复制功能之后，如果你能看到Slave_IO_Runing:和slave_SQL_Running:这两个都显示yes，表示复制功能开启正常，缺一不可

5、主2-136上操作

mysql->stop slave;

mysql->change

master to master_host='192.168.1.135', master_user='admin',

master_password='aixocm',

master_log_file='mysql-bin.000002',master_log_pos=242;

mysql->start slave;

mysql->show slave status\G;

这里和上面的原理一致，就不累述了

6、主135 和136同时安装keepalived

这是keepalived的官方网站:www.keepalived.org

# tar -xvf keepalived-1.1.20.tar.gz

# cd keepalived-1.1.20

#./configure --prefix=/usr/local/keepalived

#yum install openssl* 如果没有安装openssl请安装

# make && make install

# cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/rc.d/init.d/ 移动启动脚本

# cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/

# mkdir /etc/keepalived

# cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/ 复制配置文件

# cp /usr/local/keepalived/sbin/keepalived /usr/sbin/ 修改可执行二进制文件路径

7、在Master上对keepalived配置文件进行相关配置

! 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 master #此处填本机的主机名

}

vrrp_script check_mysqld {

script "/etc/keepalived/mysqlcheck/check_mysql.sh" #检查MYSQL 复制状态脚本

interval 2

weight 2

}

vrrp_instance VI_1 {

state BACKUP #这里主135和主136都写上BACKUP

interface eth0 #vip对外的网络接口

virtual_router_id 80 #标识号，主主要统一起来

priority 100 #优先级，越大优先级越高，主136上改成90

advert_int 2

nopreempt #不抢占模式，在优先级高的那台设置此参数

authentication {

auth_type PASS

auth_pass 1111

}

track_script {

check_mysqld #调用MYSQL脚本的函数

}

virtual_ipaddress {

192.168.1.200 dev eth0 ；设置vip，即虚拟ip地址，我的是在eth0网卡上

}

}

当Master1 down了，Master2接管了，Master1再次起来了，不会再成为master,也就是说不会再次抢占VIP。

否则master恢复了再接管的话,如果出现问题了，则切换两次对业务来说并不好。

解决方法是:

state 都设置为Backup,在优先级高的那台设置参数nopreempt.

同样，还要在在Maste2配置该文件，需要修改主机名，优先级等参数。

8、监控MYSQL 复制状态的脚本，在Master1上，脚本位置在keepalived配置文件上有记录。

#!/bin/bash

#function:监视mysql主从服务器状态

#written by:alec

user=admin

host=192.168.1.135

password=aixocm

check_mysql() {

IO=`/usr/local/mysql/bin/mysql

-u$user -p$password -h$host -e 'show slave status\G'|grep IO_Running|tr

-s ' '|awk '{print $2}'|grep Yes|wc -l`

SQL=`/usr/local/mysql/bin/mysql

-u$user -p$password -h$host -e 'show slave status\G'|grep

SQL_Running|tr -s ' '|sed -n '1p'|awk '{print $2}'|grep Yes|wc -l`

if [ "$IO" -eq 1 ]&&[ "$SQL" -eq 1 ]

then

service keepalived status|grep pid

if [ $? -eq 0 ]

then

echo "服务已经启动"

else

service keepalived start

if [ $? -eq 0 ]

then

echo "服务启动成功"

else

echo "服务启动失败"

fi

fi

else

service keepalived status|grep pid

if [ $? -eq 0 ]

then

service keepalived stop

if [ $? -eq 0 ]

then

echo "服务关闭成功"

else

echo "服务关闭失败"

fi

else

echo "服务已经关闭"

fi

fi

}

main(){

check_mysql

}

main;

同样，在Master2上也需要该监控脚本，监控脚本需要能够登陆到本地mysql的用户名和密码，在Master2上只需修改用户名，主机名和密码。需要注意的是，需要给脚本加上可执行的权限。

9、在Master1和Master2上远程授权VIP登陆用户

>grant all on *.* to test@'%' identified by 'aixocm';

拥有VIP地址的主机才是真正对外提供服务主机，远程用户通过VIP连接到真实的服务器上来

/etc/init.d/keepalived start ；两台主机都启动keepalived这个服务

VIP会首先出现在优先级高的Master1上，如下图所示:

10 最后测试

用客户机登录服务器，让拥有VIP的那台服务器也就是Master1宕机，我们这里只需要停止slave复制功能即可。

# mysql –utest –paixocm –h192.168.1.200 –P 3306

# stop slave;

# quit;

然后此时在查看ip地址，VIP这时候应该是没有了的，跑到Master2下查看VIP地址，如果能够看到VIP地址，说明你成功了，如下图: