1

I want to block a remote server to access MySQL and want to get this error "blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'" .

How do i block user in such manner in MySQL ? DO i need to hit the server for connection from remote server several times until it gets blocked ? Or what can be done.

Also which parameter in MySQL database does the field change when user is blocked in such case ?

Can anyone help ?

Vivek
  • 11
  • 4

1 Answers1

0

You could revoke the privileges of your users to connect from specific IPs.

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'USERNAME'@'123.123.123.123';

OR

DELETE FROM mysql.user WHERE User='USERNAME' AND Host NOT IN ('localhost', '127.0.0.1', '::1');

If you reduce the privileges to USERNAME@localhost, only local connections are possible. Just make sure you actually have at least one local entry (username@localhost, username@127.0.0.1, username@[local IPv6]) left, or you wont be able to login again.

make sure to apply the privileges after you change them by calling

FLUSH PRIVILEGES;

To restrict the amount of connections per user@host combination, you may add additional settings to the GRANT command:

GRANT ALL ON customer.* TO 'USERNAME'@'123.123.123.123'
     WITH MAX_QUERIES_PER_HOUR 0
          MAX_UPDATES_PER_HOUR 0
          MAX_CONNECTIONS_PER_HOUR 0
          MAX_USER_CONNECTIONS 0;

More information can be found on the mysql webpage (Setting Account Resource Limits and GRANT Syntax)

dube
  • 4,898
  • 2
  • 23
  • 41
  • Thanks for reply . I had tried the revoke earlier. But that din't help. It gave me this error "Access denied for user 'Username'@'IP Address' to database 'databaseName'" while i was trying to connect the remote server mysql. I want the user to get this error : "blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'" – Vivek Aug 06 '14 at 07:42
  • I updated the answer with the settings you want: MAX_USER_CONNECTIONS. However, I wonder why you would want to do that, it solves the task with the wrong tool – dube Aug 06 '14 at 09:03