在MySQL数据库中,如果performance_schema.host_cache表中没有数据的话,需要从下面方面进行检查/排查- mysql> select count(*) from performance_schema.host_cache;
- +----------+
- | count(*) |
- +----------+
- | 0 |
- +----------+
- 1 row in set (0.03 sec)
- mysql>
- mysql> show variables like '%host_cache_size%';
- +-----------------+-------+
- | Variable_name | Value |
- +-----------------+-------+
- | host_cache_size | 663 |
- +-----------------+-------+
- 1 row in set (0.00 sec)
- mysql>
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.
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>
系统变量skip_name_resolve：检查客户端连接时是否解析主机名，变量为off，则mysqld 会在检查客户端连接时解析主机名。若变量为on，mysqld只使用 IP ;在这种情况下，授权表中的所有列值都必须是IP地址。
另外,在测试过程发现系统变量skip_name_resolve设置为OFF后,需要成功连接MySQL数据库后才会向host_cache写入数据,如果第一次就是使用错误的密码连接访问数据库,此时host_cache表并不会写入一条数据.
注意:只有非本地主机的 TCP 连接会使用主机缓存；使用网络回环地址或 socket 建立的连接不会使用主机缓存. 所以测试验证的时候请注意,否则你的实验结果可能会不一样.
其实,早期的MySQL版本,还有一个变量skip-host-cache,它的作用类似于host_cache_size，但mysql在运行时无法对该参数进行变更，并且skip-host-cache在之后的版本中已弃用。当前测试版本为MySQL 8.0.38,无法测试,遂一笔带过.
3. 命令清空了host_cache
如果你或其他人执行了flush hosts命令, 它会刷新host_cache，刷新后会清除内存中的主机缓存. 不过这个是比较难排查的.
参考资料：
- https://dev.mysql.com/doc/refman/8.4/en/host-cache.html
- https://zhuanlan.zhihu.com/p/493702797
扫描上面二维码关注我如果你真心觉得文章写得不错，而且对你有所帮助，那就不妨帮忙“推荐"一下，您的“推荐”和”打赏“将是我最大的写作动力！本文版权归作者所有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接.
来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！ |
提升卡
置顶卡
沉默卡
喧嚣卡
变色卡
千斤顶
照妖镜
