mysql主从+keepalived实现自动切换

mysql-master  192.168.10.52

mysql-slave   192.168.10.43

VirtualIP     192.168.10.54

软件：Mysql、ipvsadm、keepalived

2.1 安装mysql-server

yum   install mysql-server –y

service   mysqld start

2.2修改配置文件

Mysql-master

log-bin=mysql-bin

relay-log=mysql-relay

server-id=1

sync-binlog=1

auto-increment-increment=1

auto-increment-offset=2

log-bin-trust-function-creators=1

binlog_format=mixed 

slave-skip-errors = all

# binlog-cache-size=10m

Mysql-slave：

log-bin=mysql-bin

relay-log=mysql-relay

server-id=2

sync-binlog=1

auto-increment-increment=2

auto-increment-offset=2 

log-bin-trust-function-creators=1

binlog_format=mixed 

slave-skip-errors = all

2.3 同步权限配置

master授权slave同步：

grant replication slave on *.*    to repuser@'192.168.10.%' identified by 'repuser';

show master status;

slave同步配置

change master to master_host='192.168.10.52',master_user='repuser',master_password='repuser',master_log_file='mysql-bin.000002',master_log_pos=120;

start slave;(reset slave;)

show slave status \G;

    Slave授权master同步，配置master同步slave（步骤同上）

yum   install ipvsadm keepalived –y

service   ipvsadm start

chkconfig   ipvsadm on

service   keepalived start

chkconfig   keepalived on

3.1.1配置VIP

在Master-mysql和slave-mysql服务器上执行如下操作

cd   /etc/sysconfig/network-scripts/

cp   ifcfg-lo ifcfg-lo:0

vi   ifcfg-lo:0

DEVICE=lo:0

IPADDR=10.160.31.232

NETMASK=255.255.255.255

ONBOOT=yes

vi   /etc/rc.local

/sbin/route   add -host 10.160.31.232  dev lo:0

#将访问VIP的数据限制在本地避免通讯混乱

配置PROC参数

vi   /etc/sysctl.conf

net.ipv4.conf.all.arp_ignore   = 1

net.ipv4.conf.all.arp_announce   = 2

net.ipv4.conf.default.arp_ignore   = 1

net.ipv4.conf.default.arp_announce   = 2

net.ipv4.conf.lo.arp_ignore   = 1

net.ipv4.conf.lo.arp_announce   = 2

sysctl   -p

#调整LINUX内核ARP响应参数，阻止更新VIP的MAC地址，避免冲突

3.1.2 Master-mysql配置keepalived.conf：

vi /etc/keepalived/keepalived.conf

!   Configuration File for keepalived

global_defs   {

    router_id mysql1

}

vrrp_script   chk_mysql {

    script   "/etc/keepalived/scripts/mysql_check.sh"

    interval 2

    weight 2

}

vrrp_instance   mysql {

  state MASTER            

  interface eth0        

  virtual_router_id 52

  advert_int 1

  priority 99          

  authentication {

         auth_type PASS

         auth_pass mysql

  }

track_script   {

       chk_mysql

   }

 virtual_ipaddress {

       192.168.10.54  

  }

}

3.1.3 slave-mysql配置keepalived.conf

vi   /etc/keepalived/keepalived.conf

!   Configuration File for keepalived

global_defs   {

    router_id mysql2

}

vrrp_script   chk_mysql {

    script   "/etc/keepalived/scripts/mysql_check.sh"

    interval 2

    weight 2

}

vrrp_instance   mysql {

  state BACKUP             

  interface eth0          

  virtual_router_id 52

  advert_int 1

  priority 50           

  authentication {

         auth_type PASS 

         auth_pass mysql

  }

track_script   {

       chk_mysql

   }

  virtual_ipaddress {

       192.168.10.54

  }

}

mkdir   /etc/keepalived/scripts/

vi   /etc/keepalived/scripts/mysql_check.sh

pidof   mysqld

if [   $? -ne 0 ]

then   service mysqld start &>/dev/null

fi

sleep   10

pidof   mysqld

if [   $? -ne 0 ]

then   service keepalived stop

fi

chmod   +x mysql.sh

重启keepalived

service   keepalived restart

以上两种情况都出现VIP漂移到slave服务器，如图：

在slave执行写操作成功