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

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值