MySQL+Keepalived HA

Env

DB1：192.168.242.131
DB1：192.168.242.132
VIP ：192.168.242.133        #注意关闭selinux和iptables

DB1

Vim  /etc/my.cnf
log-bin=mysql-bin
server-id=251  
replicate-do-db=test    >>>    只复制指定（test）的数据库里的内容
binlog-ignore-db=mysql    >>>忽略mysql 数据库的更新操作
binlog-ignore-db=information_schema
auto-increment-increment=2   >>>  自增长每次增长2   这是为了在同步的时候ID不冲突
auto-increment-offset=1      >>>  设置自增长的起始值（第二台机器设为2）
grant replication slave on  *.* to ‘xfell’@’%’  identified by ‘123’;
flush privileges;
重启服务

DB2

Vim  /etc/my.cnf
log-bin=mysql-bin
server-id=132  
replicate-do-db=test    >>>    只复制指定（test）的数据库里的内容
binlog-ignore-db=mysql    >>>忽略mysql 数据库的更新操作
binlog-ignore-db=information_schema
auto-increment-increment=2   >>>  自增长每次增长2   这是为了在同步的时候ID不冲突
auto-increment-offset=2      >>>  设置自增长的起始值（第二台机器设为2）
grant replication slave on  *.* to ‘xfell’@’%’  identified by ‘123’;   >>> 授权用户仅同步
flush privileges;
重启服务

DB1 and DB2

start slave;    
Show slave status\G;   >>>  查看slave开启状态
Slave_IO_Running: Yes  Slave_SQL_Running: Yes    这2项都是yes表示开启成功
同步表内容在这里不过多演示 插入内容即可。

Install Keepalived

yum -y install openssl openssl-devel kernel-devel popt-devel gcc gcc-c++
wget http://www.keepalived.org/software/keepalived-1.2.0.tar.gz
tar zxf keepalived-1.1.20.tar.gz
cd keepalived-1.1.20
./configure --prefix=/usr/local/keepalived --with-kernel-dir=/usr/src/kernels/2.6.32-431.el6.x86_64
make && make install

Copy Seting Configfile

cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/
cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
mkdir /etc/keepalived/
cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/
cp /usr/local/keepalived/sbin/keepalived /usr/sbin/

DB1 Configfile

! Configuration File for keepalived

global_defs {
   router_id MYSQL_HA#标识，双主相同
}
vrrp_script check_run{
script '/usr/local/mysql/mysql_check.sh'
interval 300
}
vrrp_instance VI_1 {
    state BACKUP#两台都设置BACKUP
    interface eth0
    virtual_router_id 51#主备相同
    priority 100#优先级，backup设置90(最关建点之一，同为backup时优先级别越高，就会主动去抢VIP，所以如果不想让这种情况出现加参数nopreempt)
    advert_int 1#主备心跳检查间隔
    nopreempt #不主动抢占资源，只在master这台优先级高的设置，backup不设置(当master恢复时，不会把VIP抢过来)
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.242.133  #VIP
    }
}

virtual_server 192.168.242.133 3306 {
    delay_loop 2
    #lb_algo rr#LVS算法，用不到，我们就关闭了
    #lb_kind NAT
    #nat_mask 255.255.255.0
    persistence_timeout 50#同一IP的连接60秒内被分配到同一台真实服务器
    protocol TCP

    real_server 192.168.242.131 3306 { #检测本地mysql，backup也要写检测本地mysql
        weight 3
    notify_down /usr/local/keepalived/mysql.sh #当mysq服down时，执行此脚本，杀死keepalived实现切换
    TCP_CHECK {
    connect_timeout 3#连接超时
    nb_get_retry 3#重试次数
    delay_before_retry 3#重试间隔时间
    }
}

DB2 Configfile

! 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 MYSQL_HA
   #router_id LVS_DEVEL
}

vrrp_instance VI_1 {
    state BACKUP
    interface eth0
    virtual_router_id 51
    priority 90
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.242.133
    }
}

virtual_server 192.168.242.133 3306 {
    delay_loop 2
    #lb_algo rr
    #lb_kind NAT
    #nat_mask 255.255.255.0
    persistence_timeout 50
    protocol TCP

    real_server 192.168.242.132 3306 {
        weight 3
    notify_down /usr/local/keepalived/mysql.sh
    TCP_CHECK {
    connect_timeout 3
    nb_get_retry 3
    delay_before_retry 3
    }
}

Kill Process Scriipt

Grant for
grant all on *.* to 'admin'@'%' identified by '3306';
flush privileges;
/etc/init.d/keepalived start    >>>开启服务

Test
mysql -uadmin -h192.168.242.133 –p
可以通过 ip a 查看VIP地址飘逸过程 另外mysql客户端不能和主服务器装在一起 不然飘逸时客户端会连接失败。