0

We can create users in MySQL which are allowed from a particular IP or range of IPs. For e.g, CREATE USER 'username'@'IP' IDENTIFIED BY ... Here if I give a particular IP, it means users from that IP only can access MySQL. Now there is a need of a load balancer (HAProxy) on top of many MySQL nodes behind it. The issue is : When a request comes from HAProxy to MySQL, it is the HAProxy's IP which comes to MySQL. So the way I want to use the IP while creating a user, doesn't work. My question is particularly with this USE-CASE only and I would like to know is there any solution for it ?

siddhusingh
  • 1,832
  • 4
  • 25
  • 30

1 Answers1

0

You have to create a user with the haproxy IP since DB traffic will be coming from there.

Instead of specific IPs, you can also opt for wildcard IPs e.g. user@10.10.10.%. Users can access MySQL from machines with IPs starting from 10.10.10. If both the DB and haproxy machines are on the same network (10.10.10.x), you only need to create one account.

Explore more options in the documentation: https://dev.mysql.com/doc/refman/5.7/en/account-names.html

If you decide to fully transition the users to use the proxy to access mysql, you can change the host of they user account as mentioned here: https://stackoverflow.com/a/12045483/255523

Community
  • 1
  • 1
Ianthe the Duke of Nukem
  • 1,721
  • 2
  • 20
  • 37
  • doesn't solve my problem as I have to create specific users for specific IP(s). – siddhusingh May 28 '16 at 09:00
  • You have no choice but to specify IPs of where you expect users to connect. In your case, it's haproxy. What you can do is minimize the amount of work you need to transition the users e.g. perms, names etc. Check this out: http://stackoverflow.com/a/12045483/255523 – Ianthe the Duke of Nukem May 28 '16 at 10:38