未经身份验证的用户在 MYSQL 中是什么意思？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/983400/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
what does unauthenticated user mean in MYSQL?
提问by omg
mysql> show full processlist;
+-----+----------------------+--------------------+------+---------+------+----------------------------------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+----------------------+--------------------+------+---------+------+----------------------------------+-----------------------+
| 1 | system user | | NULL | Connect | 623 | Waiting for master to send event | NULL |
| 2 | system user | | NULL | Connect | 0 | Reading event from the relay log | NULL |
| 400 | root | localhost | v3 | Sleep | 68 | | NULL |
| 585 | root | localhost | v3 | Query | 0 | NULL | show full processlist |
| 748 | unauthenticated user | 172.19.0.27:32833 | NULL | Connect | NULL | login | NULL |
| 749 | unauthenticated user | 172.19.0.27:32836 | NULL | Connect | NULL | login | NULL |
| 750 | unauthenticated user | 172.19.0.27:32838 | NULL | Connect | NULL | login | NULL |
| 751 | unauthenticated user | 172.19.0.27:32841 | NULL | Connect | NULL | login | NULL |
| 752 | unauthenticated user | 172.19.0.27:32844 | NULL | Connect | NULL | login | NULL |
| 753 | unauthenticated user | 172.19.0.27:32846 | NULL | Connect | NULL | login | NULL |
| 754 | unauthenticated user | 172.19.0.27:32848 | NULL | Connect | NULL | login | NULL |
| 755 | unauthenticated user | 172.19.0.139:46827 | NULL | Connect | NULL | login | NULL |
| 756 | unauthenticated user | 172.19.0.139:46830 | NULL | Connect | NULL | login | NULL |
| 757 | unauthenticated user | 172.19.0.139:46831 | NULL | Connect | NULL | login | NULL |
| 758 | unauthenticated user | 172.19.0.27:32857 | NULL | Connect | NULL | login | NULL |
| 759 | unauthenticated user | 172.19.0.27:32858 | NULL | Connect | NULL | login | NULL |
| 760 | unauthenticated user | 172.19.0.27:32859 | NULL | Connect | NULL | login | NULL |
| 761 | unauthenticated user | 172.19.0.27:32863 | NULL | Connect | NULL | login | NULL |
| 762 | unauthenticated user | 172.19.0.27:32864 | NULL | Connect | NULL | login | NULL |
| 763 | unauthenticated user | 172.19.0.27:32866 | NULL | Connect | NULL | login | NULL |
| 764 | unauthenticated user | 172.19.0.27:32870 | NULL | Connect | NULL | login | NULL |
| 765 | unauthenticated user | 172.19.0.27:32871 | NULL | Connect | NULL | login | NULL |
| 766 | unauthenticated user | 172.19.0.139:46833 | NULL | Connect | NULL | login | NULL |
| 767 | unauthenticated user | 172.19.0.27:32878 | NULL | Connect | NULL | login | NULL |
| 768 | unauthenticated user | 172.19.0.27:32881 | NULL | Connect | NULL | login | NULL |
| 769 | unauthenticated user | 172.19.0.27:32885 | NULL | Connect | NULL | login | NULL |
| 770 | unauthenticated user | 172.19.0.139:46835 | NULL | Connect | NULL | login | NULL |
| 771 | unauthenticated user | 172.19.0.27:32888 | NULL | Connect | NULL | login | NULL |
| 772 | unauthenticated user | 172.19.0.27:32890 | NULL | Connect | NULL | login | NULL |
| 773 | unauthenticated user | 172.19.0.139:46837 | NULL | Connect | NULL | login | NULL |
| 774 | unauthenticated user | 172.19.0.139:46839 | NULL | Connect | NULL | login | NULL |
| 775 | unauthenticated user | 172.19.0.139:46841 | NULL | Connect | NULL | login | NULL |
| 776 | unauthenticated user | 172.19.0.139:46844 | NULL | Connect | NULL | login | NULL |
| 777 | unauthenticated user | 172.19.0.139:46845 | NULL | Connect | NULL | login | NULL |
| 778 | unauthenticated user | 172.19.0.139:46847 | NULL | Connect | NULL | login | NULL |
| 779 | unauthenticated user | 172.19.0.27:32898 | NULL | Connect | NULL | login | NULL |
| 780 | unauthenticated user | 172.19.0.27:32900 | NULL | Connect | NULL | login | NULL |
| 781 | unauthenticated user | 172.19.0.139:46850 | NULL | Connect | NULL | login | NULL |
| 782 | unauthenticated user | 172.19.0.139:46852 | NULL | Connect | NULL | login | NULL |
| 783 | unauthenticated user | 172.19.0.139:46854 | NULL | Connect | NULL | login | NULL |
| 784 | unauthenticated user | 172.19.0.139:46857 | NULL | Connect | NULL | login | NULL |
| 785 | unauthenticated user | 172.19.0.139:46859 | NULL | Connect | NULL | login | NULL |
| 786 | unauthenticated user | 172.19.0.27:32903 | NULL | Connect | NULL | login | NULL |
| 787 | unauthenticated user | 172.19.0.139:46862 | NULL | Connect | NULL | login | NULL |
| 788 | unauthenticated user | 172.19.0.139:46865 | NULL | Connect | NULL | login | NULL |
| 789 | unauthenticated user | 172.19.0.139:46866 | NULL | Connect | NULL | login | NULL |
| 790 | unauthenticated user | 172.19.0.139:46868 | NULL | Connect | NULL | login | NULL |
| 791 | unauthenticated user | 172.19.0.139:46871 | NULL | Connect | NULL | login | NULL |
| 792 | unauthenticated user | 172.19.0.139:46873 | NULL | Connect | NULL | login | NULL |
| 793 | unauthenticated user | 172.19.0.27:32907 | NULL | Connect | NULL | login | NULL |
| 794 | unauthenticated user | 172.19.0.27:32909 | NULL | Connect | NULL | login | NULL |
| 795 | unauthenticated user | 172.19.0.27:32911 | NULL | Connect | NULL | login | NULL |
| 796 | unauthenticated user | 172.19.0.139:46875 | NULL | Connect | NULL | login | NULL |
| 797 | unauthenticated user | 172.19.0.27:32914 | NULL | Connect | NULL | login | NULL |
| 798 | unauthenticated user | 172.19.0.27:32916 | NULL | Connect | NULL | login | NULL |
| 799 | unauthenticated user | 172.19.0.139:46877 | NULL | Connect | NULL | login | NULL |
| 800 | unauthenticated user | 172.19.0.139:46879 | NULL | Connect | NULL | login | NULL |
+-----+----------------------+--------------------+------+---------+------+----------------------------------+-----------------------+
57 rows in set (0.00 sec)
回答by Artem Russakovskii
A connection with "unauthenticated user" in the User column has initiated a connection but hasn't sent his/her credentials yet, so the server doesn't know who exactly is connecting.
与“用户”列中“未经身份验证的用户”的连接已启动连接但尚未发送他/她的凭据，因此服务器不知道究竟是谁在连接。
If such connections only showed up in the list when they were authenticated, it could potentially run the server out of available sockets and you wouldn't even know why.
如果此类连接仅在通过身份验证时出现在列表中，则可能会导致服务器耗尽可用套接字，而您甚至不知道为什么。
Most often stuck connections are due to DNS not resolving properly, which the skip-name-resolve option will help with.
大多数情况下卡住的连接是由于DNS 没有正确解析，跳过名称解析选项将有助于解决.
More infoat the Pythian blog.
更多信息请访问 Pythian 博客。
回答by RedScourge
you may need to use skip-name-resolve, as mysql is trying to resolve the mysql host address, or add the hostname being connected to in the file /etc/hosts, or both. bonus: fix any problems with your DNS that are causing the lookups to time out in the first place
您可能需要使用 skip-name-resolve，因为 mysql 正在尝试解析 mysql 主机地址，或在文件 /etc/hosts 中添加要连接的主机名，或两者兼而有之。奖励：首先解决导致查找超时的 DNS 问题
回答by Brian Frantz
We had this problem on our production database. It turned out to be SSL on the connections to the database. We were opening a lot of connections to the database due to high web traffic (no connection pooling in Django) - guess the server got bogged down with the initial SSL handshakes. When we turned off SSL to the database things started flying through.
我们在生产数据库上遇到了这个问题。结果证明是连接到数据库的 SSL。由于高网络流量（Django 中没有连接池），我们打开了大量到数据库的连接 - 猜测服务器因初始 SSL 握手而陷入困境。当我们关闭数据库的 SSL 时，事情开始飞速发展。
回答by mazgalici
You have a low thread_cache_size (number of mysql start threads) or max_connexion and mysql can't make a new thread is in the process of creating new threads to handle those connextions
您的 thread_cache_size（mysql 启动线程数）或 max_connexion 较低，并且 mysql 无法创建新线程正在创建新线程来处理这些连接
回答by Nicolas Blattmann
Indeed, the skip-name-resolvecan resolve your problem but it wasn't my case.
事实上，skip-name-resolve可以解决您的问题，但这不是我的情况。
This message : "unauthenticated user" on Usercolumn with "show processlist" can mean that you are using old encryption password.
此消息：“未认证用户”的用户用“SHOW PROCESSLIST”列可能意味着你正在使用旧加密密码。
In my case, I recreated my users with new encryption format, like that :
就我而言，我使用新的加密格式重新创建了我的用户，如下所示：
CREATE USER 'myuser'@'IP' IDENTIFIED BY PASSWORD '*DA7CC43A56959FE78FC336FB9D7185AAACCC44DD';
or simply with it if you don't have used "old password" in your configuration.
或者如果您没有在配置中使用“旧密码”，则只需使用它。
CREATE USER 'myuser'@'IP' IDENTIFIED BY 'mypassword';
