haproxy代理mysql galera · Linux笔记

[TOC] haproxy可以通过 **TCP协议** 来代理MySQL。 ## 1 安装haproxy 下载 `https://www.haproxy.org/download/2.1/src/haproxy-2.1.4.tar.gz` 解压 `tar zxvf haproxy-2.1.4.tar.gz` 编译 `make TARGET=linux2600 CPU=x86_64 USE_PCRE=1 USE_OPENSSL=1 USE_ZLIB=1 USE_CRYPT_H=1 USE_LIBCRYPT=1` 必须要指定的linux26就是执行的内核版本号是2.6，x86\_64指定架构为x86架构，注：如果是Centos7.2的版本就要： `make TARGET=linux3100 ARCH=x86_64` 安装 `make install PREFIX=/opt/haproxy` ### 报错： #### more undefined references to `clock_gettime' follow src/ev_select.o:/root/haproxy-2.1.4/include/common/time.h:526: more undefined references to `clock_gettime' follow 原因：经过查找发现clock\_gettime在实时库librt（real time）里面，由于链接的时候没有链接这个库导致报错。解决思路：只需在我们运行的Makefile文件里面添加动态链接库librt ( -lrt ) ，重新编译即可。解决方案： 1.查找实时库librt所在路径: ``` [root@localhost haproxy-2.1.4]$ find / -name '*librt*' /usr/lib64/librt.so /lib64/rtkaio/librtkaio-2.12.so /lib64/rtkaio/librt.so.1 /lib64/librt-2.12.so /lib64/librt.so.1 ``` 2.打开haproxy-2.1.4/路径下的Makefile文件： vi /root/haproxy-2.1.4/Makefile ``` 441 #### Build options 442 # Do not change these ones, enable USE_* variables instead. 443 OPTIONS_CFLAGS = 444 OPTIONS_LDFLAGS = /usr/lib64/librt.so #此路径加上librt.so 445 OPTIONS_OBJS = ``` ## 1 健康检查问题 haproxy默认已支持MySQL的健康检查，对应的指令为`option mysql-check`，浏览下该指令语法： ~~~ option mysql-check [ user <username> [ post-41 ] ] ~~~ 其中user是连接MySQL时使用的用户名，post-41是发送一种名为post v4.1的检查包。整个过程只检查haproxy能否连接到MySQL。有时候，仅仅检查MySQL服务的连通性是不够的，例如想检查某个数据库是否存在，检查主从复制是否正常，节点是否read\_only，某个slave是否严重落后于master等等。目前为止，MySQL组复制(MGR)、Galera已经逐渐普及，haproxy更无法检查这两类集群的合理性。这时候需要使用外部脚本，自定义检查内容并将检查结果报告给haproxy。 **因为haproxy对http的支持最完善，因此采用httpchk的健康检查方式，所以使用外部健康检查脚本时，检查结果必须要转换为haproxy能理解的http状态码报告。一般情况下，健康的结果使用http 200状态码表示，不健康的结果使用Http 503来表示** 。例如，要检查数据库mariadb是否存在，外部的脚本(假设脚本名为/usr/bin/mariadbcheck)内容大概如下： ``` #!/bin/bash # # Script to make a proxy (ie HAProxy) capable of monitoring Percona XtraDB Cluster nodes properly # # Documentation and download: https://github.com/olafz/percona-clustercheck # # if [[ $1 == '-h' || $1 == '--help' ]];then echo "Usage: $0 <user> <pass> <available_when_donor=0|1> <log_file> <available_when_readonly=0|1> <defaults_extra_file>" exit fi # if the disabled file is present, return 503. This allows # admins to manually remove a node from a cluster easily. if [ -e "/var/tmp/clustercheck.disabled" ]; then # Shell return-code is 1 echo -en "HTTP/1.1 503 Service Unavailable\r\n" echo -en "Content-Type: text/plain\r\n" echo -en "Connection: close\r\n" echo -en "Content-Length: 51\r\n" echo -en "\r\n" echo -en "Percona XtraDB Cluster Node is manually disabled.\r\n" sleep 0.1 exit 1 fi set -e if [ -f /etc/sysconfig/clustercheck ]; then . /etc/sysconfig/clustercheck fi MYSQL_USERNAME="${MYSQL_USERNAME:=clustercheckuser}" MYSQL_PASSWORD="${MYSQL_PASSWORD-clustercheckpassword!}" AVAILABLE_WHEN_DONOR=${AVAILABLE_WHEN_DONOR:-0} ERR_FILE="${ERR_FILE:-/dev/null}" AVAILABLE_WHEN_READONLY=${AVAILABLE_WHEN_READONLY:-1} DEFAULTS_EXTRA_FILE=${DEFAULTS_EXTRA_FILE:-/etc/my.cnf} #Timeout exists for instances where mysqld may be hung TIMEOUT=10 EXTRA_ARGS="" if [[ -n "$MYSQL_USERNAME" ]]; then EXTRA_ARGS="$EXTRA_ARGS --user=${MYSQL_USERNAME}" fi if [[ -n "$MYSQL_PASSWORD" ]]; then EXTRA_ARGS="$EXTRA_ARGS --password=${MYSQL_PASSWORD}" fi if [[ -r $DEFAULTS_EXTRA_FILE ]];then MYSQL_CMDLINE="mysql --defaults-extra-file=$DEFAULTS_EXTRA_FILE -nNE --connect-timeout=$TIMEOUT \ ${EXTRA_ARGS}" else MYSQL_CMDLINE="mysql -nNE --connect-timeout=$TIMEOUT ${EXTRA_ARGS}" fi # # Perform the query to check the wsrep_local_state # WSREP_STATUS=$($MYSQL_CMDLINE -e "SHOW STATUS LIKE 'wsrep_local_state';" \ 2>${ERR_FILE} | tail -1 2>>${ERR_FILE}) if [[ "${WSREP_STATUS}" == "4" ]] || [[ "${WSREP_STATUS}" == "2" && ${AVAILABLE_WHEN_DONOR} == 1 ]] then # Check only when set to 0 to avoid latency in response. if [[ $AVAILABLE_WHEN_READONLY -eq 0 ]];then READ_ONLY=$($MYSQL_CMDLINE -e "SHOW GLOBAL VARIABLES LIKE 'read_only';" \ 2>${ERR_FILE} | tail -1 2>>${ERR_FILE}) if [[ "${READ_ONLY}" == "ON" ]];then # Percona XtraDB Cluster node local state is 'Synced', but it is in # read-only mode. The variable AVAILABLE_WHEN_READONLY is set to 0. # => return HTTP 503 # Shell return-code is 1 echo -en "HTTP/1.1 503 Service Unavailable\r\n" echo -en "Content-Type: text/plain\r\n" echo -en "Connection: close\r\n" echo -en "Content-Length: 43\r\n" echo -en "\r\n" echo -en "Percona XtraDB Cluster Node is read-only.\r\n" sleep 0.1 exit 1 fi fi # Percona XtraDB Cluster node local state is 'Synced' => return HTTP 200 # Shell return-code is 0 echo -en "HTTP/1.1 200 OK\r\n" echo -en "Content-Type: text/plain\r\n" echo -en "Connection: close\r\n" echo -en "Content-Length: 40\r\n" echo -en "\r\n" echo -en "Percona XtraDB Cluster Node is synced.\r\n" sleep 0.1 exit 0 else # Percona XtraDB Cluster node local state is not 'Synced' => return HTTP 503 # Shell return-code is 1 echo -en "HTTP/1.1 503 Service Unavailable\r\n" echo -en "Content-Type: text/plain\r\n" echo -en "Connection: close\r\n" echo -en "Content-Length: 44\r\n" echo -en "\r\n" echo -en "Percona XtraDB Cluster Node is not synced.\r\n" sleep 0.1 exit 1 fi ``` 执行权限： ~~~ chmod 755 /usr/bin/mariadbcheck ~~~ 然后，将其放进xinetd中进行管理。 ~~~ yum -y install xinetd cat /etc/xinetd.d/mariadbchk # default: on # description: mariadbchk service mariadbchk { disable = no flags = REUSE socket_type = stream port = 9200 wait = no user = mariadb server = /usr/bin/mariadbcheck log_on_failure += USERID only_from = 0.0.0.0/0 per_source = UNLIMITED } ~~~ 然后向/etc/services中添加端口和服务名映射关系： ~~~ cat >> /etc/services <<EOF mariadbchk 9200/tcp #mariadbchk EOF ~~~ 最后重新启动xinetd： ~~~ service xinetd restart ~~~ 添加mariadb检查用户 ~~~ mysql> GRANT PROCESS ON *.* TO 'clustercheckuser'@'localhost' IDENTIFIED BY 'clustercheckpassword!' ~~~ 然后配置haproxy，大概内容如下： ~~~ listen mysql-cluster bind 0.0.0.0:3306 mode tcp balance roundrobin option httpchk # 注意此处是httpchk，不是mysql-check server db01 mariadbnode1:13306 check port 9200 inter 12000 rise 3 fall 3 server db02 mariadbnode2:13306 check port 9200 inter 12000 rise 3 fall 3 server db03 mariadbnode3:13306 check port 9200 inter 12000 rise 3 fall 3 ~~~