CODEWITHSUNDEEP
mysqlnagios
Dan Walker
Dan Walker

Reputation: 474

Fetch a list of blocked hosts from MySQL server

We have 10 or so web servers that share a few MySQL servers. Due to some networking problems, there were too many connection errors and one of the webservers was blocked. I resolved this by flushing the hosts but it took a short while to notice this.

I want to setup a Nagios/Icinga check to fetch a list of blocked hosts - but I can't see how I could do this as MySQL seems to have no option for it (and there's no host_cache table to check from). I could just add a check for every webserver to try and connect, but I'd rather the check be centralised if possible - if not then I'll have to do it that way.

Upvotes: 8

Views: 22230

Answers (2)

anli
anli

Reputation: 805

You can find all blocked hosts by running this connecting to the performance_schema and running this SQL query:

SELECT * FROM `host_cache` where SUM_CONNECT_ERRORS > 0

The documentation for the host_cache table can be found at: https://dev.mysql.com/doc/refman/5.6/en/performance-schema-host-cache-table.html

You can flush the cache executing

mysqladmin flush-hosts

At the server console (maybe you have to add credentials!). The documentation can be found here: https://dev.mysql.com/doc/refman/8.0/en/host-cache.html#host-cache-flushing

Upvotes: 4

Garreth McDaid
Garreth McDaid

Reputation: 2623

There is a host_cache table. It's in the performance_schema database.

That will give you a count on connection errors per host, which you can then set up your Nagios alert for.

Upvotes: 8

Related Questions