Block Host Name in Mysql

Question

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 ?

Answer 1

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, [email protected], 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)