首页 > 代码库 > MySQL高可用基础之keepalived+双主复制【转】
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
http://wenku.baidu.com/link?url=lNERgU80yUEAKmPO0hCFbDH7_xlIG14itOHx4KkjPNsVVRd2GEblXH-HPdOi39Kf8v8Gu9yfQBfkU_WEJ35QM89DOSU_3kGH8LRhfKGKfLO
MySQL高可用基础之keepalived+双主复制【转】