mysql vip切换未重连问题_MySQL高可用基础之keepalived+双主复制

环境：

MySQL-VIP：192.168.1.3

MySQL-master1：192.168.1.1

MySQL-master2：192.168.1.2

OS版本：CentOS release 6.4 (Final) Linux 2.6.32-358.el6.x86_64

MySQL版本：5.6.14

Keepalived版本：1.2.13

一、MySQL master-master配置

1、修改MySQL配置文件/etc/my.cnf

# Server1配置

[mysqld]

basedir = /usr/local/mysql

datadir = /usr/local/mysql/data

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_UNSIGNED_SUBTRACTION

port = 6603

server_id = 1

lower_case_table_names = 1

character_set_server = utf8

autocommit = off

innodb_flush_log_at_trx_commit = 1

skip_name_resolve = ON

innodb_strict_mode = ON

log_bin = mysql-bin

innodb_data_home_dir = /usr/local/mysql/data

innodb_log_group_home_dir = /usr/local/mysql/data

auto_increment_increment=2

auto_increment_offset=1

# Server2配置

[mysqld]

basedir = /usr/local/mysql

datadir = /usr/local/mysql/data

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_UNSIGNED_SUBTRACTION

port = 6603

server_id = 2

lower_case_table_names = 1

character_set_server = utf8

autocommit = off

innodb_flush_log_at_trx_commit = 1

skip_name_resolve = ON

innodb_strict_mode = ON

log_bin = mysql-bin

innodb_data_home_dir = /usr/local/mysql/data

innodb_log_group_home_dir = /usr/local/mysql/data

auto_increment_increment=2

auto_increment_offset=2

2、将192.168.1.1设为192.168.1.2的主服务器

# 在Server1上执行

grant replication slave on *.* to 'repl'@'%' identified by 'repl';

show master status;

# 在Server2上执行

change master to

master_host='192.168.1.1',

master_port=6603,

master_user='repl',

master_password='repl',

master_log_file='mysql-bin.000027',

master_log_pos=120;

start slave;

show slave status\G

3、将192.168.1.2设为192.168.1.1的主服务器

# 在Server2上执行

grant replication slave on *.* to 'repl'@'%' identified by 'repl';

show master status;

# 在Server1上执行

change master to

master_host='192.168.1.2',

master_port=6603,

master_user='repl',

master_password='repl',

master_log_file='mysql-bin.000010',

master_log_pos=351;

start slave;

show slave status\G

4、MySQL双Master同步测试

二、keepalived安装及配置

1、192.168.1.1服务器上keepalived安装及配置

安装keepalived

# tar -zxvf keepalived-1.2.13.tar.gz

# cd keepalived-1.2.13

# ./configure --prefix=/usr/local/keepalived --with-kernel-dir=/usr/src/kernels/2.6.32-358.el6.x86_64

# make

# make install

配置keepalived

新建一个配置文件，默认情况下keepalived启动时会去/etc/keepalived目录下找配置文件

# mkdir /etc/keepalived

# vi /etc/keepalived/keepalived.conf

# Configuration File for keepalived

global_defs {

router_id MySQL-ha

}

vrrp_instance VI_1 {

state BACKUP   #两台配置此处均是BACKUP

interface eth1

virtual_router_id 51

priority 100   #优先级，另一台改为90

advert_int 1

nopreempt  #不抢占，只在优先级高的机器上设置即可，优先级低的机器不设置

authentication {

auth_type PASS

auth_pass 1111

}

virtual_ipaddress {

192.168.1.3

}

}

virtual_server 192.168.1.3 6603 {

delay_loop 2   #每个2秒检查一次real_server状态

lb_algo wrr   #LVS算法

lb_kind DR    #LVS模式

persistence_timeout 60   #会话保持时间

protocol TCP

real_server 192.168.1.1 6603 {

weight 3

notify_down /usr/local/mysql/bin/failover.sh  #检测到服务down后执行的脚本

TCP_CHECK {

connect_timeout 10    #连接超时时间

nb_get_retry 3       #重连次数

delay_before_retry 3   #重连间隔时间

connect_port 6603   #健康检查端口

}

}

编写检测服务down后所要执行的脚本

# vi /usr/local/mysql/bin/failover.sh

#!/bin/sh

pkill keepalived

# chmod +x /usr/local/mysql/bin/failover.sh

注：此脚本是上面配置文件notify_down选项所用到的，keepalived使用notify_down选项来检查real_server的服务状态，当发现real_server服务故障时，便触发此脚本；我们可以看到，脚本就一个命令，通过pkill keepalived强制杀死keepalived进程，从而实现了MySQL故障自动转移。另外，我们不用担心两个MySQL会同时提供数据更新操作，因为每台MySQL上的keepalived的配置里面只有本机MySQL的IP+VIP，而不是两台MySQL的IP+VIP

启动keepalived

# /usr/local/keepalived/sbin/keepalived -D

# ps aux | grep keepalived

测试

找一台局域网PC，然后去ping MySQL的VIP，这时候MySQL的VIP是可以ping的通的

停止MySQL服务，看keepalived健康检查程序是否会触发我们编写的脚本

keepalived配置成服务并开机启动

# cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/rc.d/init.d/

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

# cp /usr/local/keepalived/sbin/keepalived /usr/sbin/

# chkconfig --add keepalived

# chkconfig --level 345 keepalived on

2、192.168.1.2上keepalived安装及配置

安装keepalived

# tar -zxvf keepalived-1.2.13.tar.gz

# cd keepalived-1.2.13

# ./configure --prefix=/usr/local/keepalived --with-kernel-dir=/usr/src/kernels/2.6.32-358.el6.x86_64

# make

# make install

配置keepalived

这台配置和Server1上基本一样，但有三个地方不同：优先级为90、无抢占设置、real_server为本机IP

# mkdir /etc/keepalived

# vi /etc/keepalived/keepalived.conf

# Configuration File for keepalived

global_defs {

router_id MySQL-ha

}

vrrp_instance VI_1 {

state BACKUP   #两台配置此处均是BACKUP

interface eth1

virtual_router_id 51

priority 100   #优先级，另一台改为90

advert_int 1

nopreempt  #不抢占，只在优先级高的机器上设置即可，优先级低的机器不设置

authentication {

auth_type PASS

auth_pass 1111

}

virtual_ipaddress {

192.168.1.3

}

}

virtual_server 192.168.1.3 6603 {

delay_loop 2   #每个2秒检查一次real_server状态

lb_algo wrr   #LVS算法

lb_kind DR    #LVS模式

persistence_timeout 60   #会话保持时间

protocol TCP

real_server 192.168.1.1 6603 {

weight 3

notify_down /usr/local/mysql/bin/failover.sh  #检测到服务down后执行的脚本

TCP_CHECK {

connect_timeout 10    #连接超时时间

nb_get_retry 3       #重连次数

delay_before_retry 3   #重连间隔时间

connect_port 6603   #健康检查端口

}

}

编写检测服务down后所要执行的脚本

# vi /usr/local/mysql/bin/failover.sh

#!/bin/sh

pkill keepalived

# chmod +x /usr/local/mysql/bin/failover.sh

启动keepalived

# /usr/local/keepalived/sbin/keepalived -D

# ps aux | grep keepalived

测试

停止MySQL服务，看keepalived健康检查程序是否会触发我们编写的脚本

keepalived配置成服务并开机启动

# cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/rc.d/init.d/

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

# cp /usr/local/keepalived/sbin/keepalived /usr/sbin/

# chkconfig --add keepalived

# chkconfig --level 345 keepalived on

三、测试

1、MySQL远程登录测试

使用客户端登录VIP测试

2、keepalived故障转移测试

客户端一直去ping VIP，然后关闭192.168.1.1上的keepalived，正常情况下VIP就会切换到192.168.1.2上面去

开启192.168.1.1上的keepalived，关闭192.168.1.2上的keepalived，看是否能自动切换，正常情况下VIP又会属于192.168.1.1

注：keepalived切换速度还是非常块的，整个切换过程只需1-3秒

3、MySQL故障转移测试

在192.168.1.1上关闭MySQL服务，看VIP是否会切换到192.168.1.2上

开启192.168.1.1上的MySQL和keepalived，然后关闭192.168.1.2上的MySQL，看VIP是否会切换到192.168.1.1上

客户端连接的MySQL的VIP，在切换时执行了一个MySQL查询命令

这个方案可以在一定程度上解决MySQL高可用的问题，即应用访问VIP，当一个MySQL Server出现问题，会自动切换到另一个，切换过程很快，对应用透明。但这种简单配置只能有一台服务器工作，另一个备用，这样无法扩展读写，也没法做负载均衡。目前MySQL负载均衡方案一般是一个HA(keepalived、MHA等)+ 一个负载均衡器(LVS、haproxy等)。

关于LVS+keepalived方案，参考：

http://kb.cnblogs.com/page/83944/

http://blog.chinaunix.net/uid-20639775-id-3337471.html