keepalived 实现mysql主从自动切换

3.1安装KEEPALIVED软件：
wget http://www.keepalived.org/software/keepalived-1.2.7.tar.gz
tar zxvf keepalived-1.2.7.tar.gz 
cd keepalived-1.2.7
./configure --prefix=/usr/local/keepalived --with-kernel-dir=/usr/src/kernels/2.6.18-194.el5-x86_64

  3.2设置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 keepalived on

  3.3编写主从库上的配置文件：
mkdir /etc/keepalived
vi /etc/keepalived/keepalived.conf
数据库角色	配置文件内容



192.168.137.212 VIP
 
192.168.137.3  master

192.168.137.4  slave

主	! Configuration File for keepalived
global_defs {
   router_id MySQL-ha
}

#global_defs {
#notification_email {
#xxxx@126.com
}
#当主、备份设备发生改变时，通过邮件通知
#notification_email_from lzyangel@126.com
#smtp_server stmp.126.com
#smtp_connect_timeout 30
#router_id MySQL-ha
#}

vrrp_instance VI_1{
# 在初始化状态下定义为主设备
state BACKUP
# 注意网卡接口
interface eth0
virtual_router_id 51
# 优先级，另一台改为90
priority 100
advert_int 1
# 不主动抢占资源
nopreempt
authentication {
# 认证方式，可以是PASS或AH两种认证方式
auth_type PASS
# 认证密码
auth_pass 1111
}

virtual_ipaddress {
# 虚拟IP地址，随着state的变化而增加删除
192.168.137.212
}
}

virtual_server 192.168.137.212 3306 {
# 每个2秒检查一次real_server状态
delay_loop 2
# LVS算法
lb_algo wrr
# LVS模式
lb_kind DR
# 会话保持时间
persistence_timeout 60
protocol TCP

real_server 192.168.137.3 3306 {
# 权重
weight 3
# 检测到服务down后执行的脚本
notify_down /etc/keepalived/keepalived.sh
TCP_CHECK {
# 连接超时时间
connect_timeout 10
# 重连次数
nb_get_retry 3
# 重连间隔时间
delay_before_retry 3
# 健康检查端口
connect_port 3306
}
}
} 


从! Configuration File for keepalived

global_defs {
   router_id MySQL-ha
}

vrrp_instance VI_1{
# 在初始化状态下定义为主设备
state BACKUP
# 注意网卡接口
interface eth0
virtual_router_id 51
# 优先级，另一台改为90
priority 90
advert_int 1
# 不主动抢占资源
nopreempt
authentication {
# 认证方式，可以是PASS或AH两种认证方式
auth_type PASS
# 认证密码
auth_pass 1111
}

virtual_ipaddress {
# 虚拟IP地址，随着state的变化而增加删除
192.168.137.212
}
notify_master /etc/keepalived/modifyreadonly.sh
}

virtual_server 192.168.137.212 3306 {
# 每个2秒检查一次real_server状态
delay_loop 2
# LVS算法
lb_algo wrr
# LVS模式
lb_kind DR
# 会话保持时间
persistence_timeout 60
protocol TCP

real_server 192.168.137.4 3306 {
# 权重
weight 3
# 检测到服务down后执行的脚本
notify_down /etc/keepalived/keepalived.sh
TCP_CHECK {
# 连接超时时间
connect_timeout 10
# 重连次数
nb_get_retry 3
# 重连间隔时间
delay_before_retry 3
# 健康检查端口
connect_port 3306
}
}
}


/etc/keepalived/keepalived.sh 内容:
/get_ssh.exp 192.168.137.4 xxxx "/apps/svr/mysql5.6/bin/mysql -uroot -pxxxxxxx -S /apps/dbdat/mysql_3306data/mysql_3306.sock -e 'set global read_only=0;'"



/etc/keepalived/modifyreadonly.sh 内容:
/usr/local/mysql/bin/mysql -uroot -pxxxxx -S /data/mysqldata/3306/mysql.sock -e 'set global read_only=0;'



192.168.137.212 

192.168.137.3 主

192.168.137.4 从


  3.4配置检测主库DOWN后执行的脚本：
脚本一	#!/bin/sh

/etc/init.d/keepalived stop 
#在停止KEEPALIVED后远程将第二节点改为可写
/get_ssh.exp 192.168.137.4 xxxx "/apps/svr/mysql5.6/bin/mysql -uroot -pxxxxxxx -S /apps/dbdat/mysql_3306data/mysql_3306.sock -e 'set global read_only=0;'"



脚本二	get_ssh.exp 的内容如下： 要先安装软件：expect
#!/usr/bin/expect -f
set timeout -1
if { [llength $argv] < 3} {
  puts "usage: $argv0 ip pass cmd"
    exit 1
    }


set ip [lindex $argv 0 ]
set password [lindex $argv 1 ]
set cmd [lindex $argv 2 ]

spawn ssh $ip $cmd
expect {  
 "*yes/no" { send "yes
"; exp_continue}  
 "*password:" { send "$password
" }  
 }  
 expect eof




脚本三	当从库的状态变为MASTER后所要执行的脚本：
#!/bin/bash
/usr/local/mysql/bin/mysql -uroot -pxxxxx -S /data/mysqldata/3306/mysql.sock -e 'set global read_only=0;'