在MySQL數(shù)據(jù)庫中,如果performance_schema.host_cache表中沒有數(shù)據(jù)的話,需要從下面方面進(jìn)行檢查/排查

mysql> select count(*) from performance_schema.host_cache;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.03 sec)

mysql>

1. 檢查變量host_cache_size是否為0

mysql> show variables like '%host_cache_size%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| host_cache_size | 663   |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> 

當(dāng)host_cache_size=0時, performance_schema將停止采集和存儲host_cache表中的監(jiān)控數(shù)據(jù)，導(dǎo)致performance_schema.host_cache表為空.當(dāng)前測試服務(wù)器的變量host_cache_size大小為663,host_cache_size 表示控制主機(jī)緩存的數(shù)量，設(shè)置為0時，禁用主機(jī)緩存，此時每次服務(wù)器連接時，服務(wù)器都會進(jìn)行DNS查找.另外, 此系統(tǒng)變量設(shè)置后立即生效,不用重啟數(shù)據(jù)庫服務(wù)器.

host_cache_size的官方文檔介紹如下所示:

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.

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.

host_cache表提供對主機(jī)緩存內(nèi)容的訪問，其中包含客戶機(jī)主機(jī)名和IP地址信息，用于避免DNS查找。

2. 檢查變量skip_name_resolve

mysql> show variables like 'skip_name_resolve';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| skip_name_resolve | ON    |
+-------------------+-------+
1 row in set (0.00 sec)

mysql>

如上所示,系統(tǒng)變量skip_name_resolve=ON,那么連接MySQL數(shù)據(jù)庫時不用DNS解析,此時它也不會向host_cache表寫入數(shù)據(jù).另外,skip_name_resolve這個變量是一個只讀變量,修改后需要重啟MySQL實例才能生效.

系統(tǒng)變量skip_name_resolve：檢查客戶端連接時是否解析主機(jī)名，變量為off，則mysqld 會在檢查客戶端連接時解析主機(jī)名。若變量為on，mysqld只使用 IP ;在這種情況下，授權(quán)表中的所有列值都必須是IP地址。

另外,在測試過程發(fā)現(xiàn)系統(tǒng)變量skip_name_resolve設(shè)置為OFF后,需要成功連接MySQL數(shù)據(jù)庫后才會向host_cache寫入數(shù)據(jù),如果第一次就是使用錯誤的密碼連接訪問數(shù)據(jù)庫,此時host_cache表并不會寫入一條數(shù)據(jù).

注意:只有非本地主機(jī)的 TCP 連接會使用主機(jī)緩存；使用網(wǎng)絡(luò)回環(huán)地址或 socket 建立的連接不會使用主機(jī)緩存. 所以測試驗證的時候請注意,否則你的實驗結(jié)果可能會不一樣.

其實,早期的MySQL版本,還有一個變量skip-host-cache,它的作用類似于host_cache_size，但mysql在運(yùn)行時無法對該參數(shù)進(jìn)行變更，并且skip-host-cache在之后的版本中已棄用。當(dāng)前測試版本為MySQL 8.0.38,無法測試,遂一筆帶過.

3. 命令清空了host_cache

如果你或其他人執(zhí)行了flush hosts命令, 它會刷新host_cache，刷新后會清除內(nèi)存中的主機(jī)緩存. 不過這個是比較難排查的.

參考資料：

1. https://dev.mysql.com/doc/refman/8.4/en/host-cache.html
2. https://zhuanlan.zhihu.com/p/493702797