For security reasons, database servers always block connections beyond a limit.

In such cases, the quick fix will be flushing the IP block.

But, what if it results in an error mysqladmin flush-hosts access denied?

Usually, insufficient permissions of the MySQL user cause access denied errors in the server.

At Bobcares, we receive too many requests to solve MySQL access denied errors as part of our Server Management Services.

Today, we’ll see reasons for the error mysqladmin flush-hosts access denied and how our Support Engineers solve it.

What is mysqladmin flush-hosts ?

Have you ever thought how an IP address gets blocked by mysqld?

The answer is very simple. The number of interrupted connection requests allowed depends on the value of the max_connect_errors in the MySQL configuration file, my.cnf.

By default, MySQL server blocks a host after 10 unsuccessful connection attempts.

Also, customers get the following error

SQLSTATE[HY000] [1129] Host '1x4.xx.xx.155' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

Immediately, users attempt to unblock the IP address by using the command mysqladmin flush-hosts

How to fix mysqladmin flush-hosts access denied error?

However, from our experience in managing servers, we’ve seen customers facing problems while unblocking IP addresses using mysqladmin utility.

Now, let’s see how our Support Engineers fixed these errors.

Insufficient MySQL user permission

Often, customers may get errors while executing mysqladmin flush-hosts. The major reason for this is the lack of sufficient permissions of the MySQL user to run the command.

In such cases, the error looks like,

error: 'Access denied for user 'root'@'localhost' (using password: NO)'

This means the command mysqladmin does not have the privileges to run directly. Therefore, we login into MySQL shell prompt using MySQL username and password.

mysql -uroot -p

We enter the MySQL root user password. After that, we unblock the IP address using the below command.

flush hosts;

However, the above process becomes tedious as we have to enter the password every time.

Alternatively, our Dedicated Engineers specify the root MySQL username and password in a file ~/.my.cnf. This allows running the MySQL commands mysql, mysqladmin, mysqlcheck, mysqdump, etc without entering the MySQL username and password repeatedly.

Also, it will pick the username and password from the file itself. It can save time too.

For example, to connect as the root user, we add the username and password in a file at /root/.my.cnf.

[client]
user=mysqluser
password=mysqlpass

Thereafter, mysqladmin command works without any errors.

MySQL service restart

The option to flush the IP using mysqladmin utility does not work always. This can be due to corrupted MySQL utility binaries or library files.

Here, we can unblock the IP by restarting the MySQL service. This removes the error. However, this may cause a short downtime on the website that uses the MySQL database. Therefore, our Dedicated Engineers always evaluate the risk and proceed only when the risk is acceptable.

[Having difficulty while running MySQL commands? Our MySQL experts can fix it for you.] 

Conclusion

In short, insufficient permissions of the MySQL user to run the MySQL command causes the error like mysqladmin flush-hosts access denied. Today, we saw how our Support Engineers fixed this error.