跳转至

HAProxy + Percona XtraDB Cluster 部署

环境说明

假定Percona XtraDB Cluster(以下简称PXC)有4个节点,分别为:

  • 192.168.1.75
  • 192.168.1.77
  • 192.168.1.78
  • 192.168.1.81

HAProxy 部署在192.168.1.97节点上。

配置haproxy.cfg

192.168.1.97上安装HAProxy后,需要配置/etc/haproxy/haproxy.cfg文件,内容如下:

global
log 127.0.0.1    local0
log 127.0.0.1    local1 notice
maxconn     4096
user        haproxy
group        haproxy
daemon
#debug

defaults
log            global
mode        http
option        tcplog
option        dontlognull
option        redispatch
retries        3
maxconn        2000
contimeout    5000
clitimeout    50000
srvtimeout    50000

frontend    pxc-front
bind        *:33060       # 对外开放的MySQL连接端口
mode        tcp
default_backend    pxc-back

frontend    stats-front
bind        *:8080       # 查看HAProxy的状态端口
mode        http
default_backend    stats-back

backend        pxc-back
mode        tcp
balance        roundrobin
option         httpchk

server    db01    192.168.1.75:3306    check     port 9200 inter 5000  rise 2 fall 2
server    db02    192.168.1.77:3306    check     port 9200 inter 5000  rise 2 fall 2
server    db03    192.168.1.78:3306    check     port 9200 inter 5000  rise 2 fall 2
server    db04    192.168.1.81:3306    check     port 9200 inter 5000  rise 2 fall 2 backup

backend        stats-back
mode        http
balance        roundrobin
stats        uri /haproxy/stats
stats        auth pxcstats:secret    # 查看状态的认证账户号和密码

配置数据库检测脚本

需要在每个PXC节点上配置数据库检测脚本,该脚本的状态以HTTP的方式发布出去。 在上述四个PXC节点上,需要安装下面的两个脚本:

  • mysqlchk
  • clustercheck

mysqlchk是一个xinetd守护进程配置文件,默认位置为/etc/xinetd.d目录,内容如下:

 # default: on
description: mysqlchk
service mysqlchk
{
    this is a config for xinetd, place it in /etc/xinetd.d/
    disable = no
    flags           = REUSE
    socket_type     = stream
    port            = 9200
    wait            = no
    user            = nobody
    server_args     = clustercheck clustercheckpassword 1 /var/log/clustercheck.log 0 /etc/my.cnf
    server          = /usr/local/sbin/clustercheck
    log_on_failure  += USERID
    only_from       = 192.168.1.0/24
    #
    # Passing arguments to clustercheck
    # <user> <pass> <available_when_donor=0|1> <log_file> <available_when_readonly=0|1> <defaults_extra_file>"
    # Recommended: server_args   = user pass 1 /var/log/log-file 0 /etc/my.cnf.local"
    # Compatibility: server_args = user pass 1 /var/log/log-file 1 /etc/my.cnf.local"
    # 55-to-56 upgrade: server_args = user pass 1 /var/log/log-file 0 /etc/my.cnf.extra"
    #
    # recommended to put the IPs that need
    # to connect exclusively (security purposes)
    per_source      = UNLIMITED
}

创建/var/log/clustercheck.log文件,并设置属主为上述配置文件中指定的nobody

/etc/services文件增加对9200端口的服务描述,类似下面一行: mysqlcheck 9200/tcp #MySQL Status Check

上述配置文件中提到的/usr/local/sbin/clustercheck文件,来源于

在任意一个PXC节点上(比如是192.168.1.75)创建上述配置文件提供的数据库状态检测账号

mysql> grant process on *.* to 'clustercheck'@'localhost' identified by 'clustercheckpassword!';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

在PXC四个节点上,启动xinetd服务:

service xinetd start

查看9200端口是否已经打开。

启动HAProxy

上述过程完成后,就可以启动HAProxy了:
service haproxy start

如果没有报错,则可以通过浏览器访问 http://192.168.1.97:8080/haproxy/stats,输入配置文件中设置账号密码,就可以看到HAProxy的状态了。