The MySQL server maintains an in-memory host cache that contains
information about clients: IP address, host name, and error
information. The Performance Schema
host_cache
table exposes the
contents of the host cache so that it can be examined using
SELECT
statements. This may help
you diagnose the causes of connection problems. See
Section 29.12.22.3, “The host_cache Table”.
The following sections discuss how the host cache works, as well as other topics such as how to configure and monitor the cache.
The server uses the host cache only for non-localhost TCP
connections. It does not use the cache for TCP connections
established using a loopback interface address (for example,
127.0.0.1
or ::1
), or
for connections established using a Unix socket file, named
pipe, or shared memory.
The server uses the host cache for several purposes:
By caching the results of IP-to-host name lookups, the server avoids doing a Domain Name System (DNS) lookup for each client connection. Instead, for a given host, it needs to perform a lookup only for the first connection from that host.
The cache contains information about errors that occur during the client connection process. Some errors are considered “blocking.” If too many of these occur successively from a given host without a successful connection, the server blocks further connections from that host. The
max_connect_errors
system variable determines the permitted number of successive errors before blocking occurs.
For each applicable new client connection, the server uses the client IP address to check whether the client host name is in the host cache. If so, the server refuses or continues to process the connection request depending on whether or not the host is blocked. If the host is not in the cache, the server attempts to resolve the host name. First, it resolves the IP address to a host name and resolves that host name back to an IP address. Then it compares the result to the original IP address to ensure that they are the same. The server stores information about the result of this operation in the host cache. If the cache is full, the least recently used entry is discarded.
The server performs host name resolution using the
getaddrinfo()
system call.
The server handles entries in the host cache like this:
When the first TCP client connection reaches the server from a given IP address, a new cache entry is created to record the client IP, host name, and client lookup validation flag. Initially, the host name is set to
NULL
and the flag is false. This entry is also used for subsequent client TCP connections from the same originating IP.If the validation flag for the client IP entry is false, the server attempts an IP-to-host name-to-IP DNS resolution. If that is successful, the host name is updated with the resolved host name and the validation flag is set to true. If resolution is unsuccessful, the action taken depends on whether the error is permanent or transient. For permanent failures, the host name remains
NULL
and the validation flag is set to true. For transient failures, the host name and validation flag remain unchanged. (In this case, another DNS resolution attempt occurs the next time a client connects from this IP.)If an error occurs while processing an incoming client connection from a given IP address, the server updates the corresponding error counters in the entry for that IP. For a description of the errors recorded, see Section 29.12.22.3, “The host_cache Table”.
To unblock blocked hosts, flush the host cache; see Dealing with Blocked Hosts.
It is possible for a blocked host to become unblocked even without flushing the host cache if activity from other hosts occurs:
If the cache is full when a connection arrives from a client IP not in the cache, the server discards the least recently used cache entry to make room for the new entry.
If the discarded entry is for a blocked host, that host becomes unblocked.
Some connection errors are not associated with TCP
connections, occur very early in the connection process (even
before an IP address is known), or are not specific to any
particular IP address (such as out-of-memory conditions). For
information about these errors, check the
Connection_errors_
status variables (see
Section 7.1.10, “Server Status Variables”).
xxx
The host cache is enabled by default. The
host_cache_size
system
variable controls its size, as well as the size of the
Performance Schema host_cache
table that exposes the cache contents. The cache size can be
set at server startup and changed at runtime. For example, to
set the size to 100 at startup, put these lines in the server
my.cnf
file:
[mysqld]
host_cache_size=200
To change the size to 300 at runtime, do this:
SET GLOBAL host_cache_size=300;
Setting host_cache_size
to 0, either at
server startup or at runtime, disables the host cache. With
the cache disabled, the server performs a DNS lookup every
time a client connects.
Changing the cache size at runtime causes an implicit host
cache flushing operation that clears the host cache, truncates
the host_cache
table, and
unblocks any blocked hosts; see
Flushing the Host Cache.
To disable DNS host name lookups, start the server with the
skip_name_resolve
system
variable enabled. In this case, the server uses only IP
addresses and not host names to match connecting hosts to rows
in the MySQL grant tables. Only accounts specified in those
tables using IP addresses can be used. (A client may not be
able to connect if no account exists that specifies the client
IP address.)
If you have a very slow DNS and many hosts, you might be able
to improve performance either by enabling
skip_name_resolve
to disable
DNS lookups, or by increasing the value of
host_cache_size
to make the
host cache larger.
To disallow TCP/IP connections entirely, start the server with
the skip_networking
system
variable enabled.
To adjust the permitted number of successive connection errors
before host blocking occurs, set the
max_connect_errors
system
variable. For example, to set the value at startup put these
lines in the server my.cnf
file:
[mysqld]
max_connect_errors=10000
To change the value at runtime, do this:
SET GLOBAL max_connect_errors=10000;
The Performance Schema host_cache
table exposes the contents of the host cache. This table can
be examined using SELECT
statements, which may help you diagnose the causes of
connection problems. For information about this table, see
Section 29.12.22.3, “The host_cache Table”.
Flushing the host cache might be advisable or desirable under these conditions:
Some of your client hosts change IP address.
The error message
Host '
occurs for connections from legitimate hosts. (See Dealing with Blocked Hosts.)host_name
' is blocked
Flushing the host cache has these effects:
It clears the in-memory host cache.
It removes all rows from the Performance Schema
host_cache
table that exposes the cache contents.It unblocks any blocked hosts. This enables further connection attempts from those hosts.
To flush the host cache, use any of these methods:
Change the value of the
host_cache_size
system variable. This requires theSYSTEM_VARIABLES_ADMIN
privilege (or the deprecatedSUPER
privilege).Execute a
TRUNCATE TABLE
statement that truncates the Performance Schemahost_cache
table. This requires theDROP
privilege for the table.Execute a mysqladmin flush-hosts command. This requires the
DROP
privilege for the Performance Schemahost_cache
table or theRELOAD
privilege.
The server uses the host cache to track errors that occur during the client connection process. If the following error occurs, it means that mysqld has received many connection requests from the given host that were interrupted in the middle:
Host 'host_name' is blocked because of many connection errors.
Unblock with 'mysqladmin flush-hosts'
The value of the
max_connect_errors
system
variable determines how many successive interrupted connection
requests the server permits before blocking a host. After
max_connect_errors
failed
requests without a successful connection, the server assumes
that something is wrong (for example, that someone is trying
to break in), and blocks the host from further connection
requests.
To unblock blocked hosts, flush the host cache; see Flushing the Host Cache.
Alternatively, to avoid having the error message occur, set
max_connect_errors
as
described in Configuring the Host Cache. The
default value of
max_connect_errors
is 100.
Increasing max_connect_errors
to a large value makes it less likely that a host reaches the
threshold and becomes blocked. However, if the Host
'
error message occurs, first verify that there is nothing wrong
with TCP/IP connections from the blocked hosts. It does no
good to increase the value of
host_name
' is blockedmax_connect_errors
if there
are network problems.