一、haproxy安装时报错pcre.h: No such file or directory及MYSQL主从同步相关
今天在搭建线上新产品的整个服务器环境时遇到点问题:一是在安装haproxy时报错pcre.h: No such file or directory,具体如下:
In file included from include/types/proxy.h:33,
from include/proto/log.h:32,
from include/common/cfgparse.h:29,
from src/haproxy.c:65:
#后面有很多的报错内容,但是这里是报错启点,从这里开始查找
include/common/regex.h:31:18: error: pcre.h: No such file or directory
include/common/regex.h:32:23: error: pcreposix.h: No such file or directory
In file included from include/types/proxy.h:33,
from include/proto/log.h:32,
from include/common/cfgparse.h:29,
from src/haproxy.c:65:
include/common/regex.h:45: error: expected specifier-qualifier-list before ‘pcre’
就是缺少pcre的devel模块,在centos中执行安装pcre-devel即可。
sudo yum -y install pcre-devel
另外今天搭建MYSQL一主四从,把命令文本留在这里做个记录:
change master to master_host='192.168.162.*',master_user='slave_mysql',master_password='getdata_abcd',master_log_file='mysql-bin.000001',master_log_pos=126892;
在创建同步账号一定要记录权限问题,mysql的host中要使用匹配时使用%号,而不是使用*。今天开始使用的*号,在进行start slave命令后发现从库的两个值Slave_IO_Running,Slave_SQL_Running:看Slave_SQL_Running的值是Yes,但是Slave_IO_Running的值是Connecting,是一直在连接。即mysql看的user表的host字段不是用*来匹配(好像很多地方习惯了用*)。
mysql> select user,host,password from mysql.user; +-----------------+---------------+-------------------------------------------+ | user | host | password | +-----------------+---------------+-------------------------------------------+ | slave_mysql | 192.168.162.% | *1C6647080REEW29C44B0000000000000000B09C0 | +-----------------+---------------+-------------------------------------------+
二、haproxy配置mysql负载均衡转发时遇到reading initial communication packet
今天在配置mysql的负载均衡时碰到通过从负载IP连接不上,连接提示错误reading initial communication packet,如下:
[onlinedev@FG-OR-4415 ~]$ sudo mysql -h 192.168.162.14 -u user_ro -P 3306 -p
Enter password:
ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0
检查MYSQL的连接账户,在负载的服务器上使用mysql命令随机连接需要负载的服务器,这个能连的话说明mysql的连接账号权限host限制之类的没有问题。通过检查我这里正常可连。
打开haproxy的监控,查看各后端的服务器的状态,发现后端的服务器的状态都是DOWN,看来从haproxy到MYSQL服务器的连接状态有问题。不过问题出在哪首先排查配置文件。
因为我的配置文件是从其它的服务器拷贝过来的,通过查看有以下这一行比较特殊:
option tcplog
option tcpka
#下面这一行比较特殊,这是用来检查haproxy后端的mysql状态的选项,需要在mysql中创建无任何权限无密码的对用用户haproxy
option mysql-check user haproxy
option mysql-check user haproxy,表示使用haproxy这个用户去检查后端的mysql状态,需要在mysql中创建无任何权限无密码的对用用户haproxy。这就是问题点了,因为我还没有在MYSQL中创建这个用户。进入各后端的MYSQL执行:
mysql> CREATE USER 'haproxy'@'192.168.168.%';
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host,password from mysql.user;
+----------+---------------+-------------------------------------------+
| user | host | password |
+----------+---------------+-------------------------------------------+
| haproxy | 192.168.168.% | |
+----------+---------------+-------------------------------------------+
8 rows in set (0.00 sec)
CREATE USER 'haproxy'@'192.168.168.%';
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
然后haproxy的监控界面中的后端MYSQL陆续状态变更为UP,并且再次通过haproxy连接后端MYSQL成功。