3

I have my HAProxy running on VIP:192.168.61.32, also I've checked netstat -tulnp|grep 3306

 tcp        0      0 192.168.61.32:3306      0.0.0.0:*               LISTEN      7895/haproxy

But when I run mysql -h 192.168.61.32 -u root -p:

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

My Backend Mysql Nodes are at 192.168.61.33 & 192.168.61.34, I'm able to Connect to them directly using mysql -h 192.168.61.33 -u root -p

 mysql -h 192.168.61.34 -u root -p
karthik v
  • 131

2 Answers2

1

You may need to check if there are any connection errors between haproxy and mysql. If you run systemctl status haproxy or tail /var/log/haproxy-traffic.log, you may find the error similar to the one below

Server mysql_farm/galera03 is DOWN, reason: Layer7 wrong status, code: 0, info: "Host 'xx.xx.xx.xx' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'", check duration: 0ms. 2 active and 0 backup servers left. 0 sessions active, 0 requeued, 0 remaining in queue.t

In that case you need to run the command mysqladmin flush-hosts to clear blocked IPs from mysqld so that haproxy can connect again.

You may need to run the command as mysqladmin -uroot -p flush-hosts

See docs below for further reference in how to resolve the error. https://dev.mysql.com/doc/refman/5.7/en/host-cache.html#blocked-host

wwmwabini
  • 11
0

I think you can recheck bind-address and Edit the global my.ini file, in the [mysqld] section:

[mysqld]

-- various other settings

port = 3306
bind-address = 127.0.0.1

Save this file, and then restart service mysql with command:

service mysql restart

Edit

To grant a user access from a remote IP, run this command from the mysql> shell when logged in with the MySQL root user:

GRANT ALL on somedb.* to someuser@192.168.61.32 identified by 'somepassword';

If you want to grant access to someuser from any remote IP:

GRANT ALL on somedb.* to someuser@% identified by 'somepassword';

After those steps, make sure to restart the MySQL server so it will read the changes in the configuration.

Kotler
  • 609