Fetch a list of blocked hosts from MySQL server

Question

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.

Dahna

mysql -uUSERNAME -pPASSWORD -e 'SELECT * FROM blocked_list;' do you mean something like this? – Piddock

Something similar to what that would produce, yes, but obviously that table doesn't exist. – Dahna

Answer 1

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

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

Diaz

This table only introduced in ver. 5.6, do you have any idea how to check this table in prior versions, although it's a memory table in prior versions. Thank. – Thou

Answer 2

You can find all blocked hosts by 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

Vedis