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则配置成功

image.png

QQ截图20171030165452.png

2.6 测试

往master库新增一条数据

image.png

查看slave

image.png

这里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

image.png

启动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 主服务器

image.png

当手动将服务器A 192.168.1.46 mysql 停掉

image.png