MGR和HAProxy实现高可靠性MySQL集群

来自Linux78|wiki
Bob（讨论 | 贡献）2019年12月16日 (一) 15:31的版本
(差异) ←上一版本 | 最后版本 (差异) | 下一版本→ (差异)

前文提到的高可用环境的几个要求，包括自动同步无数据丢失、可扩展性、自动宕切等，MySQL组复制已经能很好地解决了。但是仍然还有几个问题没法单独通过MySQL 组复制来达成，就是应用层自动宕切和负载均衡。当主节点宕机之后，应用层要能自动访问新的主节点，并且只读负载要均衡地分布在各个可用节点之间。

这个时候HAProxy就闪亮登场了。其提供高可用性、负载均衡，以及基于TCP和HTTP的应用程序代理，完整的HAProxy的实现原理和配置方式超出了本文的范围，仅仅简单描述一下利用HAPxoxy实现MySQL 组复制环境的宕切和负载均衡的配置。整体架构图如下图所示：

Mgr-5.png

HAProxy的配置文件如下 

   /etc/haproxy/haproxy.cfg

   frontend mysql-gr-front_write
   bind *:3307
   mode tcp
   default_backend mysql-gr-back_write

   backend mysql-gr-back_write
   mode tcp
   balance leastconn
   option httpchk
   server mysql1 10.206.131.63:3306 check port 6446 inter 1000 rise 1 fall 2 on-marked-up shutdown-backup-sessions
   server mysql2 10.206.131.132:3306 check port 6446 inter 1000 rise 1 fall 2 backup
   server mysql3 10.206.131.156:3306 check port 6446 inter 1000 rise 1 fall 2 backup

   frontend mysql-gr-front_read
   bind *:3308
   mode tcp
   default_backend mysql-gr-back_read

   backend mysql-gr-back_read
   mode tcp
   balance leastconn
   option httpchk
   server mysql1 10.206.131.63:3306 check port 6447 inter 1000 rise 1 fall 2
   server mysql2 10.206.131.132:3306 check port 6447 inter 1000 rise 1 fall 2
   server mysql3 10.206.131.156:3306 check port 6447 inter 1000 rise 1 fall 2

为了实现上述HAPxoxy检查，需要实现两个不同的xinetd服务(注意inetd服务需要部署在每个MySQL节点上，而HAProxy是部署在一台单独的服务器上)，用于检查MySQL组成员的状况，并决定那个节点是可读写节点，哪些节点是只读节点。完整的check脚本请参考[github](https://github.com/lefred/mysql_gr_routing_check \”github\”)

下面是对xinetd服务的设定 

/etc/xinetd.d/mysql_gr_routing_check_write

# default: on
# description: check to see if the node is a viable routing candidate
service mysql_gr_routing_check_write
{
disable = no
flags = REUSE
socket_type = stream
port = 6446
wait = no
user = mysql
server = /usr/local/bin/mysql_gr_routing_check.sh
server_args = 100 write
log_on_failure += USERID
only_from = localhost 10.206.131.0/24
per_source = UNLIMITED
}

/etc/xinetd.d/mysql_gr_routing_check_read

# default: on
# description: check to see if the node is a viable routing candidate
service mysql_gr_routing_check_read
{
disable = no
flags = REUSE
socket_type = stream
port = 6447
wait = no
user = mysql
server = /usr/local/bin/mysql_gr_routing_check.sh
server_args = 100 read
log_on_failure += USERID
only_from = localhost 10.206.131.0/24
per_source = UNLIMITED
}


最后还要更新/etc/services中的设定 

mysql_gr_routing_check_write 6446/tcp # MySQL Group Replication
mysql_gr_routing_check_read  6447/tcp  # MySQL Group Replication

紧接着可以通过命令行来验证一下xinetd服务配置是否正确

检查可读写服务： 

[root@mysql1 bin]# telnet 10.206.131.63 6446
Trying 10.206.131.63...
Connected to 10.206.131.63.
Escape character is \'^]\'.
HTTP/1.1 200 OK
Content-Type: text/plain
Connection: close
Content-Length: 40
 
Group Replication member is a viable routing candidate for write.
Connection closed by foreign host.

检查只读服务： 

[root@mysql1 bin]# telnet 10.206.131.63 6447
Trying 10.206.131.63...
Connected to 10.206.131.63.
Escape character is \'^]\'.
HTTP/1.1 200 OK
Content-Type: text/plain
Connection: close
Content-Length: 40
 
Group Replication member is a viable routing candidate for read.
Connection closed by foreign host.

至此，HAProxy宕切和负载均衡都已经设定完毕，下面可以来从应用层面检查一下

1. 首先用mysql链接HAProxy节点 

mysql -h10.206.131.83 -P3307 -uroot -p

2. 查看当前所连接的主机 

mysql> select @@hostname;

可以看到该脚本一直返回同一个可读写节点

3. 再次连接到HAproxy的只读端口，并查看所连接的主机 

mysql -h10.206.131.83 -P3308 -uroot -p
mysql> select @@hostname;

可以看到所连接的主机在node1，node2和node3之间变化，达到了负载均衡的效果。

MySQL高可用架构搭建过程中遇到的问题

Ubuntu 仓库发布的MySQL 5.7.19没有组复制功能，连动态库group_replication.so都没有，也无法启用组复制plugin 集群中的每个节点，最好要有唯一的hostname，并且在/etc/hosts中配置上每个几点的IP hostname的映射关系。否则导致的问题新加入的节点一直处于recovering状态 集群中的表结构必须都要有主键，如果没有主键，数据是无法插入的，会提示下面的错误 ERROR 3098 (HY000) at line 557: The table does not comply with the requirements by an external plugin Ubuntu14.04 官方的HaProxy无法正常启动，请参考 “[Installing HAProxy on Ubuntu 14.04](https://www.vultr.com/docs/installing-and-configuring-haproxy-on-ubuntu-14-04 \”Installing HAProxy on Ubuntu 14.04\”)” 进行安装 修改密码操作必须设置binlog不记录，执行后再打开，否则会引起START GROUP_REPLICATION执行报错。设定方法 mysql> SET SQL_LOG_BIN=0; 某一个节点因为某种原因下线之后，在重新上线之前，一定不能执行写操作，否则会报错，并导致该节点无法加入到集群中： The member contains transactions not present in the group. The member will now exit the group xinetd中要允许HAProxy所在的机器访问，否则HAProxy无法确认MySQL的状态 为什么没有选择MySQL Router作为负载均衡或者其他方案，而要选择HAProxy？ 因为MySQL Router没有区分可写节点和只读节点。 HAProxy本身是一个业界成熟的解决方案，支持万级并发 为什么没有选择MySQL Cluster或者其他方案？ MySQL Cluster虽然也是一个高可用数据库集群环境，但是配置相对比较复杂一些，而且在某些情况下因为事务执行慢而导致同步问题 MySQL connector/J为什么不行？MySQL connector/J类似于MySQL Router，对单个节点的故障有一定程度的容忍性，但是并没有负载均衡 是否有考虑过HAProxy自身的灾备？

HAProxy本身是支持双机热备的，也支持虚拟IP，不过HAProxy本身支持上万级并发，暂时没有考虑这个功能 读节点加入组的时候，start group_replication抛出了下面的错误。 

2017-02-20T07:56:30.064556Z 0 [ERROR] Plugin group_replication reported: 'This member has more executed transactions than those present in the group. Local transactions: 89328c79-f730-11e6-ab63-782bcb377193:1-2 > Group transactions: 7c744904-f730-11e6-a72d-782bcb377193:1-4'
2017-02-20T07:56:30.064580Z 0 [ERROR] Plugin group_replication reported: 'The member contains transactions not present in the group. The member will now exit the group.'
2017-02-20T07:56:30.064587Z 0 [Note] Plugin group_replication reported: 'To force this member into the group you can use the group_replication_allow_local_disjoint_gtids_join option'

可以很明显看到日志中已经提示了，需要设置参数,也就是兼容加入组。group_replication_allow_local_disjoint_gtids_join设置完成后运行start group_replication即可。

补充：HAProxy自动route的原理是：通过mysql的函数自动获取集群主备状态，写到一个mysql view中，然后xinetd服务执行一个shell脚本去读取mysql view中的可读可写状态，返回给haproxy，haproxy根据返回的状态判断可以route到哪些节点。所以集群环境搭建好了之后，一定要注意执行[github](https://github.com/lefred/mysql_gr_routing_check \”github\”)上的sql脚本，以创建视图，保证视图中可以读取主备状态。

补充新踩的两个坑：

1. 因为上面mysql view是创建系统用户下面的，缺省情况下普通用户是没有权限select的，所以/usr/local/bin/mysql_gr_routing_check.sh这个脚本中配置的mysql用户必须是有权限的用户，否则执行该脚本一直输出为空

2. /usr/local/bin/mysql_gr_routing_check.sh脚本的执行已经能按照期望输出了，但是通过telnet确认服务是否正常的时候，telnet连上但是没有任何返回。检查tail -f /var/log/syslog发现如下错误 

execv( /usr/local/bin/mysql_gr_routing_check.sh ) failed: Exec format error (errno = 8)

其实这个错误的原因非常简单，就是要求脚本的第一行，必须是 

#!/bin/bash
取自“http://www.linux78.com/index.php?title=MGR和HAProxy实现高可靠性MySQL集群&oldid=1200”