Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Can someone tell me how to list the hosts which are blocked by the mysql server due to the reason that they crossed the limit of max_connect_errors. Is there any table in which MySQL server keeps this data. I am using mysql-server-5.1.63

share|improve this question
add comment

1 Answer

Noted in 5.6.5 changelog.

MySQL now provides improved access to the host cache, which contains client host name and IP address information and is used to avoid DNS lookups. These improvements have been implemented:

  • A host_cache Performance Schema table exposes the contents of the host cache so that it can be examined using SELECT statements. The Performance Schema must be enabled or this table is empty.

    If you upgrade to this release of MySQL from an earlier version, you must run mysql_upgrade (and restart the server) to incorporate this change into the performance_schema database.

  • The cache size is configurable using the host_cache_size system variable. Setting the size to 0 disables the host cache.This is similar to starting the server with --skip-host-cache, but host_cache_size is more flexible because it can also be used to resize, enable, or disable the host cache at runtime, not just at server startup. If you start the server with --skip-host-cache to disable the host cache, it cannot be re-enabled at runtime.

  • There are Connection_errors_xxx status variables that provide information about the nature of connection errors and that can help diagnose connection problems.

Improved access to host cache contents makes it possible to answer questions such as how many hosts are cached, or how close hosts are to being blocked (by checking whether the host_cache.SUM_CONNECT_ERRORS column is approaching the value of the max_connect_errors system variable).

share|improve this answer
1  
This is only applicable for MySQL 5.6. There does not appear to be a way to access this information in prior versions. –  Michael - sqlbot Feb 15 '13 at 16:36
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.