"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 .."

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      >>>  设置自增长的起始值(第二台机器设为2grant replication slave on  *.* to ‘xfell’@’%’  identified by123’;
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

Vim /usr/local/keepalived/mysql.sh
#!/bin/bash
/usr/bin/pkill  keepalived

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客户端不能和主服务器装在一起 不然飘逸时客户端会连接失败。

  • B3log

    B3log 是一个开源组织,名字来源于“Bulletin Board Blog”缩写,目标是将独立博客与论坛结合,形成一种新的网络社区体验,详细请看 B3log 构思。目前 B3log 已经开源了多款产品:PipeSoloSymWide 等,欢迎大家加入,贡献开源。

    2405 引用 • 3795 回帖 • 623 关注
  • 数据库

    据说 99% 的性能瓶颈都在数据库。

    231 引用 • 494 回帖
感谢    关注    收藏    赞同    反对    举报    分享
回帖    
请输入回帖内容...