5

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.

jaredk
  • 986
  • 5
  • 21
  • 37
Dan Walker
  • 434
  • 1
  • 5
  • 15

2 Answers2

6

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.

Laurel
  • 5,965
  • 14
  • 31
  • 57
Garreth McDaid
  • 2,427
  • 22
  • 34
  • 8
    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. – Arfeen Apr 22 '15 at 05:37
0

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 console of the server (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

anli
  • 515
  • 6
  • 9