How can I make HAProxy with the mysql-check option working?

Question

I entered a HAProxy connection to connect to a MySQL database. It is for reading purposes only as of now.

 

I used the following method for its setup:

frontend db_read_port33517

mode    tcp

option  tcplog

bind *:33517

default_backend db_slave_con

backend db_slave_con

option  mysql-check user haproxy post-41

   server database1   1.2.3.4:3307 check port 3307

   server database2   1.2.3.5:3307 check port 3307 backup

It is MySQL 5.7, and I have added a basic user to MySQL:

CREATE USER 'haproxy'@'1.0.0.0/255.0.0.0' IDENTIFIED BY 'some_password';

But stats report is saying that it can't connect to either database. I am very new to try out the mysql-check option, but I think I have missed something, but I am unable to figure it out. Please help me to figure it out.

Answer 1

It seems that I was right, as soon as I removed the password, HAProxy could see the databases. I've gone through the notes for HAProxy, and there doesn't appear to be any way to specify a password.

Whilst I appreciate it is only using that user to connect / disconnect this still seems like a bit of a security issue, as I now have a user in my database with no password. The best I can do for now I think is lock down the IP Address for the user as tightly as possible.

If you want to get more insights into SQL, checkout this SQL Course from Intellipaat.