Lost connection to MySQL server错误

本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> Lost connection to MySQL server错误

Lost connection to MySQL server错误

一套MySQL的数据库,之前一直可以远程访问,但最近从笔记本,使用Navicat工具访问,提示错误,无法连接,

Lost connection to MySQL server错误

ping和telnet端口均正常,关于2013错误,

Lost connection to MySQL server

There are three likely causes for this error message.

Usually it indicates network connectivity trouble and you should check the condition of your network if this error occurs frequently. If the error message includes “during query,” this is probably the case you are experiencing. Sometimes the “during query” form happens when millions of rows are being sent as part of one or more queries. If you know that this is happening, you should try increasing net_read_timeout from its default of 30 seconds to 60 seconds or longer, sufficient for the data transfer to complete.

More rarely, it can happen when the client is attempting the initial connection to the server. In this case, if your connect_timeout value is set to only a few seconds, you may be able to resolve the problem by increasing it to ten seconds, perhaps more if you have a very long distance or slow connection. You can determine whether you are experiencing this more uncommon cause by using SHOW GLOBAL STATUS LIKE 'Aborted_connects'. It will increase by one for each initial connection attempt that the server aborts. You may see “reading authorization packet” as part of the error message; if so, that also suggests that this is the solution that you need.

If the cause is none of those just described, you may be experiencing a problem with BLOB values that are larger than max_allowed_packet, which can cause this error with some clients. Sometime you may see an ER_NET_PACKET_TOO_LARGE error, and that confirms that you need to increase max_allowed_packet.

一一排查,貌似不是上述这些错误,但是使用DOS,通过命令行方式访问,可以正常登陆,

mysql -hIP地址 -u用户名 -p

Lost connection to MySQL server错误

看下账号状态,

Lost connection to MySQL server错误

看一下my.cnf配置文件,有个参数skip-name-resolve,之前没怎么见过,不知道什么时候配置的,

[mysqld] ... skip-name-resolve

关于这参数解释

Lost connection to MySQL server错误

skip-name-resolve引用如下,

Do not resolve host names when checking client connections. Use only IP addresses. If you use this option, all Host column values in the grant tables must be IP addresses. See Section 8.12.5.2, “DNS Lookup Optimization and the Host Cache”.

Depending on the network configuration of your system and the Host values for your accounts, clients may need to connect using an explicit --host option, such as --host=127.0.0.1 or --host=::1.

An attempt to connect to the host 127.0.0.1 normally resolves to the localhost account. However, this fails if the server is run with the --skip-name-resolve option, so make sure that an account exists that can accept a connection. For example, to be able to connect as root using --host=127.0.0.1 or --host=::1, create these accounts: CREATE USER 'root'@'127.0.0.1' IDENTIFIED BY 'root-password'; CREATE USER 'root'@'::1' IDENTIFIED BY 'root-password';

设置了这个参数,就仅能使用IP地址,处理客户端连接,不会处理主机名,这也能解释为何DOS下可以连接,Navicat工具可能受了这参数影响。

修改my.cnf,注释或直接删除skip-name-resolve,重启MySQL服务,使用Navicat工具就可以正常连接了。

如果您觉得本文有帮助,欢迎关注转发:bisal的个人杂货铺,

Lost connection to MySQL server错误 Lost connection to MySQL server错误
本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> Lost connection to MySQL server错误


 上一篇
操作分区表提示ORA-01502 操作分区表提示ORA-01502
最近生产环境,有两张普通表,由于检索性能的问题,需要转为分区表,而且是时间间隔interval分区,一张表2000万数据,一张表1.5亿数据,有俩方案, 方案1:在线重定义 方案2:新建分区表,用程序重新导入数据 由于这两张表对应
下一篇 
MySQL表名大小写问题 MySQL表名大小写问题
在Navicat中执行检索MySQL表的操作,提示此表不存在,但库中有此表, [SQL]select * from tbl;[Err] 1146 - Table ‘bisal.tbl’ doesn’t exist 问题其实不难