1、环境
服务器A mysql master:192.168.1.46
服务器B mysql slave 192.168.1.49
VIP 192.168.1.199
2、mysql主从备份
2.1 安装
mysql安装查看http://www.jianshu.com/p/1f77b90e718c
2.2 编辑服务器A 配置文件 /etc/my.cnf
vim /etc/my.cnf
log-bin=/data/mysql/data/binlog/mysql-bin #开启二进制日志功能,binlog数据位置
server-id=1 #服务端ID,用来高可用时做区分
binlog_cache_size=1M #二进制日志缓冲大小
binlog-do-db=mycat_test #需要备份数据库
expire_logs_days=7 #二进制文件超时天数
binlog-ignore-db=information_schema #忽略备份库
binlog-ignore-db=mysql
创建二进制文件目录并授权
cd /data/mysql/data/
mkdir binlog
cd binlog
mkdir mysql-bin
cd ../
chown -R mysql:mysql ./binlog
2.3服务器A上建立 账户并授权
# mysql -uroot -pwbool
mysql>GRANT REPLICATION SLAVE ON *.* to 'slave'@'192.168.1.49' identified by '123456';
mysql>flush privileges;
2.4 服务器B 配置文件/etc/my.cnf
server-id=2 #比master server-id 小
binlog_cache_size=1M
binlog-ignore-db=information_schema
binlog-ignore-db=mysql
binlog-do-db=mycat_test
expire_logs_days=7
slave-skip-errors=1062
slave-net-time=60
replicate-do-db=mycat_test
replicate-ignore-db=information_schema
replicate-ignore-db=mysql
relay_log=/data/mysql/data/relaylog/mysql-relay-bin
创建二进制文件目录并授权
cd /data/mysql/data/
mkdir relaylog
cd relaylog
mkdir mysql-relay-bin
cd ../
chown -R mysql:mysql ./relaylog
2.5 配置服务器B(slave)
mysql>stop slave;
mysql>change master to master_host='192.168.1.46',master_user='slave',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=409;
mysql>start slave;
mysql>show slave status \G;
图如下所示
同时出现两个Yes则配置成功
其中:
master_user :master授权用户
master_password : 授权用户密码
master_log_file : 二进制文件名
master_log_pos: 二进制文件当前节点
查询服务器A master状态
show master status;
2.6 测试
往master库新增一条数据
查看slave
这里id由于前面测试 mycat分库分表(全局全局序列号)所致
3、keepalived 高可用配置
3.1 、下载安装
#这里用的是yum安装
yum install keepalived
配置keepalived.conf配置文件
vim /etc/keepalived/keepalived.conf
! 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 LVS_DEVEL #主备须一致
vrrp_skip_check_adv_addr
vrrp_strict
vrrp_garp_interval 0
vrrp_gna_interval 0
}
vrrp_instance VI_1 {
state MASTER #本实例启动状态:MASTER/BACKUP
interface ens33 #监控的网络接口
virtual_router_id 51 #vrrp实例(同一个组主备服务器设置一样)
priority 100 #优先级高的为master,不能超过255。(BACKUP可设置为50)
advert_int 1 #均衡器检测间隔1秒(服务器设置都一样)
authentication { #验证类型及密码(服务器设置都一样)
auth_type PASS #认证方式,PASS或AH
auth_pass 1111 #认证密码
}
virtual_ipaddress { #虚拟ip地址virtual_ipaddress,可以定义多个
192.168.1.199
}
}
virtual_server 192.168.1.199 3306 {
delay_loop 3 #健康检查时间间隔,3秒
lb_algo rr #负载均衡调度算法:rr|wrr|lc|wlc|sh|dh|lblc
lb_kind NAT #负载均衡转发规则:NAT|DR|TUN
persistence_timeout 50 #回话保持时间50秒,动态服务建议开启
protocol TCP #转发协议protocol,一般有tcp和udp两种
real_server 192.168.1.46 3306 { # 指定real server1的IP地址
weight 3 # 配置节点权值,数字越大权重越高
notify_down /usr/local/mysql/bin/mysql.sh #检测3306端口为down状态就执行此脚本(只有keepalived关闭,VIP才漂移 )
TCP_CHECK { #健康检查方式:HTTP_GET|SSL_GET|TCP_CHECK|SMTP_CHECK|MISC_CHECK
connect_timeout 10
nb_get_retry 3
delay_before_retry 3
connect_port 3306
}
}
}
编写LVS启动脚本/etc/init.d/realserver
#!/bin/sh
VIP=192.168.1.199
. /etc/rc.d/init.d/functions
case "$1" in
# 禁用本地的ARP请求、绑定本地回环地址
start)
/sbin/ifconfig lo down
/sbin/ifconfig lo up
echo "1" >/proc/sys/net/ipv4/conf/lo/arp_ignore
echo "2" >/proc/sys/net/ipv4/conf/lo/arp_announce
echo "1" >/proc/sys/net/ipv4/conf/all/arp_ignore
echo "2" >/proc/sys/net/ipv4/conf/all/arp_announce
/sbin/sysctl -p >/dev/null 2>&1
/sbin/ifconfig lo:0 $VIP netmask 255.255.255.255 broadcast $VIP up #在回环地址上绑定VIP,设定掩码,与Direct Server(自身)上的IP保持通信
/sbin/route add -host $VIP dev lo:0
echo "LVS-DR real server starts successfully.\n"
;;
stop)
/sbin/ifconfig lo:0 down
/sbin/route del $VIP >/dev/null 2>&1
echo "1" >/proc/sys/net/ipv4/conf/lo/arp_ignore
echo "2" >/proc/sys/net/ipv4/conf/lo/arp_announce
echo "1" >/proc/sys/net/ipv4/conf/all/arp_ignore
echo "2" >/proc/sys/net/ipv4/conf/all/arp_announce
echo "LVS-DR real server stopped.\n"
;;
status)
isLoOn=`/sbin/ifconfig lo:0 | grep "$VIP"`
isRoOn=`/bin/netstat -rn | grep "$VIP"`
if [ "$isLoON" == "" -a "$isRoOn" == "" ]; then
echo "LVS-DR real server has run yet."
else
echo "LVS-DR real server is running."
fi
exit 3
;;
*)
echo "Usage: $0 {start|stop|status}"
exit 1
esac
exit 0
编写关闭keepalived脚本
cd /usr/local/mysql/bin/mysql.sh
#!/bin/bash
pkill keepalived
赋于mysql.sh可执行权限
chmod 755 mysql:mysql /usr/local/mysql/bin/mysql.sh
为realserver授权
chmod +x /etc/init.d/realserver
chmod +x /etc/rc.d/init.d/functions
查看lvs状态
yum install -y ipvsadm
ipvsadm -ln
启动LVS keepalived
service realserver start
service keepalived start
服务器A 、B都执行以上步骤
不同点 服务器B keepalived.conf中 state 设置为BACKUP priority 设置为 50(比master小就可以)
4、测试故障转移
用windows SQLyog连接VIP 192.168.1.199
此时连接的是服务器A 主服务器
当手动将服务器A 192.168.1.46 mysql 停掉