mysql【双主模式+haproxy+keepalived高可用】


说明

前文仅仅通过mysql双主 + keepalived实现高可用,这种方式在一台出现问题的时候可以切换,但是正常情况下只有一台服务使用,无法实现负载均衡,本文引入haproxy实现正常情况下也能有负载均衡的效果.

配置

  1. 安装haproxy

    yum install haproxy
  2. 修改haproxy配置文件
    默认目录:/etc/haproxy/haproxy.cfg

    #---------------------------------------------------------------------
    # Example configuration for a possible web application.  See the
    # full configuration options online.
    #
    #   http://haproxy.1wt.eu/download/1.4/doc/configuration.txt
    #
    #---------------------------------------------------------------------
    

#———————————————————————

Global settings

#———————————————————————
global
# to have these messages end up in /var/log/haproxy.log you will
# need to:
#
# 1) configure syslog to accept network log events. This is done
# by adding the ‘-r’ option to the SYSLOGD_OPTIONS in
# /etc/sysconfig/syslog
#
# 2) configure local2 events to go to the /var/log/haproxy.log
# file. A line like the following can be added to
# /etc/sysconfig/syslog
#
# local2.* /var/log/haproxy.log
#
log 127.0.0.1 local2

chroot      /var/lib/haproxy
pidfile     /var/run/haproxy.pid
maxconn     4000
user        haproxy
group       haproxy
daemon

# turn on stats unix socket
stats socket /var/lib/haproxy/stats

#———————————————————————

common defaults that all the ‘listen’ and ‘backend’ sections will

use if not designated in their block

#———————————————————————
defaults
mode http
log global
option httplog
option dontlognull
option http-server-close
option forwardfor except 127.0.0.0/8
option redispatch
retries 3
timeout http-request 10s
timeout queue 1m
timeout connect 10s
timeout client 1m
timeout server 1m
timeout http-keep-alive 10s
timeout check 10s
maxconn 3000

######## 监控界面配置 #################
listen admin_status
bind 0.0.0.0:8888
mode http
stats refresh 30s
stats uri /
stats realm welcome login\ Haproxy
stats auth admin:123456
stats hide-version
stats admin if TRUE
########frontend配置##############

######## mysql负载均衡配置 ###############
listen mysql
bind 0.0.0.0:13306
mode tcp
balance roundrobin # 负载均衡策略
#option tcplog # tcp日志
option mysql-check user haproxy # 在mysql中创建无任何权限用户haproxy,且无密码
server mysql_1 192.168.41.141:3306 check weight 1 maxconn 2000
server mysql_2 192.168.41.142:3306 check weight 1 maxconn 2000
option tcpka # 缺少这个配置,可能无法查看到数据库


> `option mysql-check user haproxy`这里是配置健康检查的,需要在mysql中创建无任何权限用户`haproxy`,且无密码.    
> -  `CREATE USER 'haproxy'@'%' IDENTIFIED BY ''; `

3. 修改ulimit配置
`haproxy`要求`ulimit`大于`(maxconn*2 + 15 )`
```bash
#临时修改
ulimit -n 65536

#永久修改,需要修改/etc/security/limits.conf配置文件,文末增加以下内容,然后重新登录就可以生效
* soft nofile 65536
* hard nofile 65536
* soft nproc 65565
* hard nproc 65565
  1. 启动服务并配置自启动

启动后访问8888端口,使用admin/123456登录就可以看到UI界面了。

systemctl start haproxy
systemctl enable haproxy
  1. 修改keepalived配置
    前文配置的keepalived监控的是mysqld进程,需要修改成监控haproxy进程.这样不管哪台机器的mysql或者haproxy或者keepalived出现问题,都不影响使用.
# 修改的配置如下
vrrp_script chk_mysql {
    script "killall -0 haproxy"
    interval 2
    timeout 2
    fall 3
}
  1. 测试
    通过VIP连接数据库,增删改查测试.

文章作者: wuzhiyong
版权声明: 本博客所有文章除特別声明外,均采用 CC BY 4.0 许可协议。转载请注明来源 wuzhiyong !
评论
 上一篇
mysql复制出现问题【GTID模式】 mysql复制出现问题【GTID模式】
非GTID模式下当BIN-LOG里面出现错误导致主从不能同步的时候, -- 错误状态的部分截图 mysql> show slave status\G *************************** 1. row **********
2020-07-29
下一篇 
mysql【双主模式+keepalived高可用】 mysql【双主模式+keepalived高可用】
说明mysql双主模式下,在任意一台mysql上写数据都会同步到另一台上,本章通过keepalived VIP实现如果一台服务挂了会自动切换到另一台mysql上. 配置# 安装keepalived,centos默认安装了 yum insta
2020-07-28
  目录