mysql主从+keepalived实现自动切换

举报
水中游 发表于 2019/01/16 11:57:24 2019/01/16
【摘要】 mysql主从+keepalived实现自动切换

1.测试环境信息

mysql-master  192.168.10.52

mysql-slave   192.168.10.43

VirtualIP     192.168.10.54

 

软件:Mysql、ipvsadm、keepalived

 

2.mysql主从安装

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;

                                                                                  spacer.gif

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;

spacer.gif

 

    Slave授权master同步,配置master同步slave(步骤同上)

3.安装ipvsadmkeepalived

yum   install ipvsadm keepalived –y

service   ipvsadm start

chkconfig   ipvsadm on

service   keepalived start

chkconfig   keepalived on

3.1 配置keepalived

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

  }

}

 

3.2 mysql检测脚本:

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

 

 

4、测试

1.master的keepalived停止,VIP漂移到slave服务器

2.master的mysqld停止,10秒后VIP漂移到slave服务器

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

spacer.gif

在slave执行写操作成功

spacer.gif

 

3.master恢复服务后VIP回到master服务器

 

结论:不管master服务器中的keepalived还是mysqld服务,只要有一个暂停,VIP都会漂移到slave服务器,slave服务器会成为主库,允许读写操作,这样就解决了数据库单点故障。


【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息, 否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。