haproxy代理MySQL数据库操作实例

# haproxy代理MySQL数据库操作实例

haproxy可以通过**TCP协议**来代理MySQL。但是两个问题必须考虑：

1. 后端MySQL的健康检查问题
2. 如何保证事务的持久性（同一个事务中的语句路由到同一个后端）

—

## 1.1 健康检查问题

haproxy默认已支持MySQL的健康检查：`option mysql-check`

语法：

“`
option mysql-check [ user [ post-41 ] ]
“`

### 自定义健康检查

有时候，仅仅检查MySQL连通性是不够的，例如：
– 检查某个数据库是否存在
– 检查主从复制是否正常
– 检查节点是否read_only
– 检查slave是否严重落后于master

**采用httpchk健康检查方式，检查结果转换为http状态码：**
– 健康：HTTP 200
– 不健康：HTTP 503

—

## 1.1.1 MySQL健康检查脚本示例

检查数据库mytest是否存在，脚本 `/usr/bin/mysqlchk.sh`：

“`bash
#!/bin/bash

mysql_port=3306
mysql_host=”localhost”
chk_mysqluser=”haproxychk”
chk_mysqlpasswd=”haproxychkpassword1!”
mysql=/usr/bin/mysql
to_test_db=mytest

### check
mysql -u${chk_mysqluser} -p${chk_mysqlpasswd} -h${mysql_host} -P${mysql_port} \
-e “show databases;” 2>/dev/null | grep “${to_test_db}” &>/dev/null

### translate the result to report to haproxy
if [ $? -eq 0 ];then
# mysql is healthy, return http 200
/bin/echo -en “HTTP/1.1 200 OK\r\n”
/bin/echo -en “Content-Type: Content-Type: text/plain\r\n”
/bin/echo -en “Connection: close\r\n”
/bin/echo -en “\r\n”
/bin/echo -en “MySQL is running and $to_test_db exists.\r\n”
/bin/echo -en “\r\n”
sleep 0.1
exit 0
else
# mysql is unhealthy, return http 503
/bin/echo -en “HTTP/1.1 503 Service Unavailable\r\n”
/bin/echo -en “Content-Type: Content-Type: text/plain\r\n”
/bin/echo -en “Connection: close\r\n”
/bin/echo -en “\r\n”
/bin/echo -en “MySQL is down or $to_test_db not exists.\r\n”
/bin/echo -en “\r\n”
exit 1
fi
“`

执行权限：

“`bash
chmod +x /usr/bin/mysqlchk.sh
“`

### 配置xinetd管理

“`bash
yum -y install xinetd

cat /etc/xinetd.d/mysqlchk
# default: on
# description: /usr/bin/mysqlchk.sh
service mysqlchk
{
disable = no
flags = REUSE
socket_type = stream
type = UNLISTED
port = 9200
wait = no
user = nobody
server = /usr/bin/mysqlchk.sh
log_on_failure += USERID
only_from = 0.0.0.0/0
per_source = UNLIMITED
}
“`

添加端口映射：

“`bash
cat >>/etc/services<