HAProxy load balancing issue

Question

I have the following configurations for load balancing a precona cluster.

haproxy.cfg

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

defaults
        log     global
        mode    http
        option  tcplog
        option  dontlognull
        retries 3
        maxconn 2000
        timeout connect 3000
        timeout server 5000
        timeout client 5000

listen mysql-cluster
    bind 127.0.0.1:3306
    mode tcp
    balance roundrobin
    option mysql-check user root

    server db01 192.168.54.158:3306 check
    server db02 192.168.54.140:3306 check

I followed this tutorial to configure haproxy. However I'm unable to connect to the cluster from the haproxy instance.

mysql -u root -proot -h 127.0.0.1 --port=3306 -e "show variables like 'server_id'"

Throw the following error.

ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0

I tried to debug the issue by looking at the haproxy logs. However there wasn't any logs present in /var/log/syslog or there's no such file haproxy.log in /var/log/

I even tried updating /etc/rsyslog.d/49-haproxy.conf to the following and restarting rsyslog service

$ModLoad imudp
$UDPServerRun 514

local0.*                        -/var/log/haproxy-0.log
local1.*                        -/var/log/haproxy-1.log
&~

I'm using haproxy version 1.5.15 on ubuntu 14.04.1 LTS. I can directly connect to the mysql cluster from the haproxy node. What would have configured wrong here? How can I get the logging enabled properly in haproxy?

Answer 1

 Allow mysql server for remote login -:
 /etc/mysql/my.cnf    
127.0.0.1 to 0.0.0.0 

   Note – This command run on both mysql server 

root@mysql-1# mysql -u root -p -e "INSERT INTO mysql.user (Host,User) values ('haproxy server ip ','haproxy_check'); FLUSH PRIVILEGES;"

 root@mysql-1# mysql -u root -p -e "INSERT INTO mysql.user (Host,User) values ('haproxy server ip','haproxy_root'); FLUSH PRIVILEGES;"

   A MySQL user is needed with root privileges when accessing the MySQL cluster from HAProxy. The default root user on all the servers are allowed to login only locally. While this can be fixed by granting additional privileges to the root user, it is better to have a separate user with root privileges.

Note – This command run on both mysql server

root@mysql-1# mysql -u root -p -e "GRANT ALL PRIVILEGES ON *.* TO 'haproxy_root'@'haproxy server ip' IDENTIFIED BY 'password' WITH GRANT OPTION; FLUSH PRIVILEGES"

 # sudo service mysql restart
 Then run below cmnd -:

  sudo mysql -h haproxyserverIP  -u haproxy_root -p -e "show variables like 'server_id'"