Rechercher dans le manuel MySQL
26.12.18.1 The host_cache Table
The 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
host_cache_size
system
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 8.12.4.2, “DNS Lookup Optimization and the Host Cache”.
Because the host_cache
table
exposes the contents of the host cache, it can be examined
using SELECT
statements. This
may help you diagnose the causes of connection problems. The
Performance Schema must be enabled or this table is empty.
The host_cache
table has these
columns:
IP
The IP address of the client that connected to the server, expressed as a string.
HOST
The resolved DNS host name for that client IP, or
NULL
if the name is unknown.HOST_VALIDATED
Whether the IP-to-host name-to-IP DNS resolution was performed successfully for the client IP. If
HOST_VALIDATED
isYES
, theHOST
column is used as the host name corresponding to the IP so that additional calls to DNS can be avoided. WhileHOST_VALIDATED
isNO
, 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.SUM_CONNECT_ERRORS
The number of connection errors that are deemed “blocking” (assessed against the
max_connect_errors
system variable). Only protocol handshake errors are counted, and only for hosts that passed validation (HOST_VALIDATED = YES
).Once
SUM_CONNECT_ERRORS
for a given host reaches the value ofmax_connect_errors
, new connections from that host are blocked. TheSUM_CONNECT_ERRORS
value can exceed themax_connect_errors
value because multiple connection attempts from a host can occur simultaneously while the host is not blocked. Any or all of them can fail, independently incrementingSUM_CONNECT_ERRORS
, possibly beyond the value ofmax_connect_errors
.Suppose that
max_connect_errors
is 200 andSUM_CONNECT_ERRORS
for a given host is 199. If 10 clients attempt to connect from that host simultaneously, none of them are blocked becauseSUM_CONNECT_ERRORS
has not reached 200. If blocking errors occur for five of the clients,SUM_CONNECT_ERRORS
is increased by one for each client, for a resultingSUM_CONNECT_ERRORS
value of 204. The other five clients succeed and are not blocked because the value ofSUM_CONNECT_ERRORS
when their connection attempts began had not reached 200. New connections from the host that begin afterSUM_CONNECT_ERRORS
reaches 200 are blocked.COUNT_HOST_BLOCKED_ERRORS
The number of connections that were blocked because
SUM_CONNECT_ERRORS
exceeded the value of themax_connect_errors
system variable.COUNT_NAMEINFO_TRANSIENT_ERRORS
The number of transient errors during IP-to-host name DNS resolution.
COUNT_NAMEINFO_PERMANENT_ERRORS
The number of permanent errors during IP-to-host name DNS resolution.
COUNT_FORMAT_ERRORS
The number of host name format errors. MySQL does not perform matching of
Host
column values in themysql.user
system table against host names for which one or more of the initial components of the name are entirely numeric, such as1.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”.COUNT_ADDRINFO_TRANSIENT_ERRORS
The number of transient errors during host name-to-IP reverse DNS resolution.
COUNT_ADDRINFO_PERMANENT_ERRORS
The number of permanent errors during host name-to-IP reverse DNS resolution.
COUNT_FCRDNS_ERRORS
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.
COUNT_HOST_ACL_ERRORS
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_PRIVILEGED
and does not even ask for a user name or password.COUNT_NO_AUTH_PLUGIN_ERRORS
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.
COUNT_AUTH_PLUGIN_ERRORS
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_AUTHENTICATION_ERRORS
,COUNT_AUTH_PLUGIN_ERRORS
,COUNT_HANDSHAKE_ERRORS
. New return codes are an optional extension to the existing plugin API. Unknown or unexpected plugin errors are counted in theCOUNT_AUTH_PLUGIN_ERRORS
column.COUNT_HANDSHAKE_ERRORS
The number of errors detected at the wire protocol level.
COUNT_PROXY_USER_ERRORS
The number of errors detected when proxy user A is proxied to another user B who does not exist.
COUNT_PROXY_USER_ACL_ERRORS
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
PROXY
privilege.COUNT_AUTHENTICATION_ERRORS
The number of errors caused by failed authentication.
COUNT_SSL_ERRORS
The number of errors due to SSL problems.
COUNT_MAX_USER_CONNECTIONS_ERRORS
The number of errors caused by exceeding per-user connection quotas. See Section 6.2.20, “Setting Account Resource Limits”.
COUNT_MAX_USER_CONNECTIONS_PER_HOUR_ERRORS
The number of errors caused by exceeding per-user connections-per-hour quotas. See Section 6.2.20, “Setting Account Resource Limits”.
COUNT_DEFAULT_DATABASE_ERRORS
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.
COUNT_INIT_CONNECT_ERRORS
The number of errors caused by execution failures of statements in the
init_connect
system variable value.COUNT_LOCAL_ERRORS
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.
COUNT_UNKNOWN_ERRORS
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_cache
table is required.FIRST_SEEN
The timestamp of the first connection attempt seen from the client in the
IP
column.LAST_SEEN
The timestamp of the most recent connection attempt seen from the client in the
IP
column.FIRST_ERROR_SEEN
The timestamp of the first error seen from the client in the
IP
column.LAST_ERROR_SEEN
The timestamp of the most recent error seen from the client in the
IP
column.
The host_cache
table has these
indexes:
Primary key on (
IP
)Index on (
HOST
)
The FLUSH HOSTS
statement,
TRUNCATE TABLE
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.4.2.5, “Host 'host_name' is blocked”). FLUSH
HOSTS
and mysqladmin flush-hosts
require the RELOAD
privilege.
TRUNCATE TABLE
requires the
DROP
privilege for the
host_cache
table.
Deutsche Übersetzung
Sie haben gebeten, diese Seite auf Deutsch zu besuchen. Momentan ist nur die Oberfläche übersetzt, aber noch nicht der gesamte Inhalt.Wenn Sie mir bei Übersetzungen helfen wollen, ist Ihr Beitrag willkommen. Alles, was Sie tun müssen, ist, sich auf der Website zu registrieren und mir eine Nachricht zu schicken, in der Sie gebeten werden, Sie der Gruppe der Übersetzer hinzuzufügen, die Ihnen die Möglichkeit gibt, die gewünschten Seiten zu übersetzen. Ein Link am Ende jeder übersetzten Seite zeigt an, dass Sie der Übersetzer sind und einen Link zu Ihrem Profil haben.
Vielen Dank im Voraus.
Dokument erstellt 26/06/2006, zuletzt geändert 26/10/2018
Quelle des gedruckten Dokuments:https://www.gaudry.be/de/mysql-rf-host-cache-table.html
Die Infobro ist eine persönliche Seite, deren Inhalt in meiner alleinigen Verantwortung liegt. Der Text ist unter der CreativeCommons-Lizenz (BY-NC-SA) verfügbar. Weitere Informationen auf die Nutzungsbedingungen und dem Autor.
Referenzen
Diese Verweise und Links verweisen auf Dokumente, die während des Schreibens dieser Seite konsultiert wurden, oder die zusätzliche Informationen liefern können, aber die Autoren dieser Quellen können nicht für den Inhalt dieser Seite verantwortlich gemacht werden.
Der Autor Diese Website ist allein dafür verantwortlich, wie die verschiedenen Konzepte und Freiheiten, die mit den Nachschlagewerken gemacht werden, hier dargestellt werden. Denken Sie daran, dass Sie mehrere Quellinformationen austauschen müssen, um das Risiko von Fehlern zu reduzieren.