3

Is there a way to whitelist an IP address, so that max_connect_errors doesn't apply?

The IP address for my office was blocked today due to too many connection errors to our new development MySQL server. I had to FLUSH HOSTS to allow connections again.

Is there a way that I can just whitelist our IP address, so that the max_connect_errors can still stay in effect for all other connections?

Improve this question
2
  • You really don't want to do this. The next round of connection errors could be malicious.
    – Michael Hampton
    Mar 29, 2013 at 23:12
  • From my office? I was hoping to just whitelist that IP address, so I could leave the max_connect_errors setting in place for other attempts but prevent the office from getting blocked again.
    – balleyne
    Mar 30, 2013 at 3:32

2 Answers 2

Reset to default
1

No, there is no way of doing that, max_connect_errors is a global setting, not a per user/session variable.

Improve this answer
3
  • Other servers have global settings and whitelists (thinking of cPanel, Postfix...), i.e. global settings to counter brute force attacks, but a whitelist of hosts exempt from that rate limiting. So, being a global setting doesn't seem to preempt the possibility of a whitelist... But, I guess MySQL just doesn't have one? =\
    – balleyne
    Mar 30, 2013 at 3:35
  • Mysql doesn't do any whitelisting. You either have global settings, per session settings or per user settings. max_connections is an eaxmple of a global setting, which can be overridden per user. max_connect_errors cannot be overridden.
    – Dennis Kaarsemaker
    Mar 30, 2013 at 8:25
  • But you can set limits on the number of connections from different sources on most firewalls including Linux iptables).
    – symcbean
    Mar 30, 2013 at 23:55
1

You can modify global setting to high number then use different users for local and other connections like this;

grant usage on *.* to outsideuser@'%' with max_user_connections 10;

grant usage on *.* to localuser@'%' with max_user_connections 1000;

You can try same user with different host too, im not sure about it but you can try like this;

grant usage on *.* to user@'%' with max_user_connections 10;

grant usage on *.* to user@'localip' with max_user_connections 1000;

You can also do these for users;

 GRANT OPTION
  | MAX_QUERIES_PER_HOUR count
  | MAX_UPDATES_PER_HOUR count
  | MAX_CONNECTIONS_PER_HOUR count
  | MAX_USER_CONNECTIONS count
Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .