host_cache table provides
access to the contents of the host cache, which contains
client host name and IP address information and is used to
avoid Domain Name System (DNS) lookups. The
variable controls the size of the host cache, as well as the
size of the
host_cache table that
exposes the cache contents. For operational and configuration
information about the host cache, see
Section 126.96.36.199, “DNS Lookup Optimization and the Host Cache”.
exposes the contents of the host cache, it can be examined
SELECT statements. This
may help you diagnose the causes of connection problems. The
Performance Schema must be enabled or this table is empty.
host_cache table has these
The IP address of the client that connected to the server, expressed as a string.
The resolved DNS host name for that client IP, or
NULLif the name is unknown.
Whether the IP-to-host name-to-IP DNS resolution was performed successfully for the client IP. If
HOSTcolumn is used as the host name corresponding to the IP so that additional calls to DNS can be avoided. While
NO, DNS resolution is attempted for each connection attempt, until it eventually completes with either a valid result or a permanent error. This information enables the server to avoid caching bad or missing host names during temporary DNS failures, which would negatively affect clients forever.
The number of connection errors that are deemed “blocking” (assessed against the
max_connect_errorssystem variable). Only protocol handshake errors are counted, and only for hosts that passed validation (
HOST_VALIDATED = YES).
SUM_CONNECT_ERRORSfor a given host reaches the value of
max_connect_errors, new connections from that host are blocked. The
SUM_CONNECT_ERRORSvalue can exceed the
max_connect_errorsvalue because multiple connection attempts from a host can occur simultaneously while the host is not blocked. Any or all of them can fail, independently incrementing
SUM_CONNECT_ERRORS, possibly beyond the value of
max_connect_errorsis 200 and
SUM_CONNECT_ERRORSfor a given host is 199. If 10 clients attempt to connect from that host simultaneously, none of them are blocked because
SUM_CONNECT_ERRORShas not reached 200. If blocking errors occur for five of the clients,
SUM_CONNECT_ERRORSis increased by one for each client, for a resulting
SUM_CONNECT_ERRORSvalue of 204. The other five clients succeed and are not blocked because the value of
SUM_CONNECT_ERRORSwhen their connection attempts began had not reached 200. New connections from the host that begin after
SUM_CONNECT_ERRORSreaches 200 are blocked.
The number of connections that were blocked because
SUM_CONNECT_ERRORSexceeded the value of the
The number of transient errors during IP-to-host name DNS resolution.
The number of permanent errors during IP-to-host name DNS resolution.
The number of host name format errors. MySQL does not perform matching of
Hostcolumn values in the
mysql.usersystem table against host names for which one or more of the initial components of the name are entirely numeric, such as
1.2.example.com. The client IP address is used instead. For the rationale why this type of matching does not occur, see Section 6.2.4, “Specifying Account Names”.
The number of transient errors during host name-to-IP reverse DNS resolution.
The number of permanent errors during host name-to-IP reverse DNS resolution.
The number of forward-confirmed reverse DNS errors. These errors occur when IP-to-host name-to-IP DNS resolution produces an IP address that does not match the client originating IP address.
The number of errors that occur because no users are permitted to connect from the client host. In such cases, the server returns
ER_HOST_NOT_PRIVILEGEDand does not even ask for a user name or password.
The number of errors due to requests for an unavailable authentication plugin. A plugin can be unavailable if, for example, it was never loaded or a load attempt failed.
The number of errors reported by authentication plugins.
An authentication plugin can report different error codes to indicate the root cause of a failure. Depending on the type of error, one of these columns is incremented:
COUNT_HANDSHAKE_ERRORS. New return codes are an optional extension to the existing plugin API. Unknown or unexpected plugin errors are counted in the
The number of errors detected at the wire protocol level.
The number of errors detected when proxy user A is proxied to another user B who does not exist.
The number of errors detected when proxy user A is proxied to another user B who does exist but for whom A does not have the
The number of errors caused by failed authentication.
The number of errors due to SSL problems.
The number of errors caused by exceeding per-user connection quotas. See Section 6.3.6, “Setting Account Resource Limits”.
The number of errors caused by exceeding per-user connections-per-hour quotas. See Section 6.3.6, “Setting Account Resource Limits”.
The number of errors related to the default database. For example, the database does not exist or the user has no privileges to access it.
The number of errors caused by execution failures of statements in the
init_connectsystem variable value.
The number of errors local to the server implementation and not related to the network, authentication, or authorization. For example, out-of-memory conditions fall into this category.
The number of other, unknown errors not accounted for by other columns in this table. This column is reserved for future use, in case new error conditions must be reported, and if preserving the backward compatibility and structure of the
host_cachetable is required.
The timestamp of the first connection attempt seen from the client in the
The timestamp of the most recent connection attempt seen from the client in the
The timestamp of the first error seen from the client in the
The timestamp of the most recent error seen from the client in the
host_cache table has these
Primary key on (
Index on (
FLUSH HOSTS statement,
host_cache statement, and mysqladmin
flush-hosts command have the same effect: They clear
the host cache, remove all rows from the
host_cache table that exposes the
cache contents, and unblock any blocked hosts (see
Section B.6.2.5, “Host 'host_name' is blocked”).
HOSTS and mysqladmin flush-hosts
TRUNCATE TABLE requires the
DROP privilege for the