MySQL 远程连接因“未知身份验证方法”而失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14612551/
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
MySQL remote connection fails with "unknown authentication method"
提问by Chibuzo
I am trying to remotely connect to MySQL server online from my local machine, but I am getting the following error:
我正在尝试从本地计算机远程连接到 MySQL 服务器，但出现以下错误：
Warning: PDO::__construct(): The server requested authentication
method unknown to the client [mysql_old_password] in
C:\xampp\htdocs\ticket\terminal\sync.php
SQLSTATE[HY000] [2054] The server requested authentication method
umknown to the client
My local MySQL server version is 5.5.27, libmysql - mysqlnd 5.0.10 The remote MySQL server version is 5.5.23, the mysqlnd version isn't exposed.
我的本地 MySQL 服务器版本是 5.5.27，libmysql - mysqlnd 5.0.10 远程 MySQL 服务器版本是 5.5.23，mysqlnd 版本没有暴露。
I guess it's an incompatible password hash issue, but I do not know how to resolve it. Below is part of my connection code
我想这是一个不兼容的密码哈希问题，但我不知道如何解决它。下面是我的连接代码的一部分
$dsn = 'mysql:host=184.173.209.193;dbname=my_db_name';
$options = array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
);
try {
$online_dbh = new PDO($dsn, 'myusername', 'mypassword', $options);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Congratulations!";
} catch (PDOException $e) {
echo $e->getMessage();
}
采纳答案by Chibuzo
I overcame the challenge. I found out that my remote MySQL database host still uses the old MySQL password hash which is 16-byte, while my localhost database server uses 41-byte password hash. I used the following query to find the password length:
我克服了挑战。我发现我的远程 MySQL 数据库主机仍然使用 16 字节的旧 MySQL 密码哈希，而我的 localhost 数据库服务器使用 41 字节的密码哈希。我使用以下查询来查找密码长度：
SELECT PASSWORD('mypass')
I changed my localhost database server password hash to 16-byte by running the following query
我通过运行以下查询将我的 localhost 数据库服务器密码哈希更改为 16 字节
SET GLOBAL old_passwords = 1;
Then I edited my.inifile, and set the old_password=1to ensure that when the server restarts, it won't revert to the new password system. But that didn't solve my problem.
然后我编辑了my.ini文件，并设置了old_password=1以确保当服务器重新启动时，它不会恢复到新的密码系统。但这并没有解决我的问题。
I figured out that it was PHP that handles the authentication, since I was using PHP's MySQLAPI, so I downgraded to PHP 5.2.8and I was able to make the remote connection successfully.
我发现是 PHP 处理身份验证，因为我使用PHP的是's MySQLAPI，所以我降级到PHP 5.2.8并且我能够成功建立远程连接。
I hope this helps someone.
我希望这可以帮助别人。
回答by Michael Robinson
Assuming you're using PHP 5.3+, you could be experiencing one of the Backward Incompatibility Changes:
假设您使用的是 PHP 5.3+，您可能会遇到向后不兼容更改之一：
The new mysqlnd library necessitates the use of MySQL 4.1's newer 41-byte password format. Continued use of the old 16-byte passwords will cause mysql_connect() and similar functions to emit the error, "mysqlnd cannot connect to MySQL 4.1+ using old authentication."
新的 mysqlnd 库需要使用 MySQL 4.1 较新的 41 字节密码格式。继续使用旧的 16 字节密码将导致 mysql_connect() 和类似函数发出错误“mysqlnd 无法使用旧身份验证连接到 MySQL 4.1+”。
If so, see https://stackoverflow.com/a/1340538/187954for information on updating your password.
如果是这样，请参阅https://stackoverflow.com/a/1340538/187954以获取有关更新密码的信息。
回答by Rob
This may help someone with this issue. This is how I fixed it in my situation. From the MySQL PHP API (PDO_MYSQL)website
这可能会帮助解决此问题的人。这就是我在我的情况下修复它的方式。来自MySQL PHP API (PDO_MYSQL)网站
When running a PHP version before 7.1.16, or PHP 7.2 before 7.2.4, set MySQL 8 Server's default password plugin to mysql_native_password or else you will see errors similar to The server requested authentication method unknown to the client [caching_sha2_password] even when caching_sha2_password is not used.
This is because MySQL 8 defaults to caching_sha2_password, a plugin that is not recognized by the older PHP (mysqlnd) releases. Instead, change it by setting default_authentication_plugin=mysql_native_password in my.cnf. The caching_sha2_password plugin will be supported in a future PHP release. In the meantime, the mysql_xdevapiextension does support it.
当运行 7.1.16 之前的 PHP 版本或 7.2.4 之前的 PHP 7.2 时，将 MySQL 8 Server 的默认密码插件设置为 mysql_native_password 否则您将看到类似于 The server requests authentication method unknown to the client [caching_sha2_password] 的错误，即使在 caching_sha2_password 时未使用。
这是因为 MySQL 8 默认使用 caching_sha2_password，这是旧 PHP (mysqlnd) 版本无法识别的插件。相反，通过在 my.cnf 中设置 default_authentication_plugin=mysql_native_password 来更改它。未来的 PHP 版本将支持 caching_sha2_password 插件。同时，mysql_xdevapi扩展确实支持它。
回答by Wouter
I ran into this same problem and figured out the problem had indeed to do with PHP.
我遇到了同样的问题，并发现问题确实与 PHP 有关。
The solution was simple for me: switch to mysqli instead of PDO. When using Zend_Db, this is as easy as changing 1 line:
解决方案对我来说很简单：切换到 mysqli 而不是 PDO。使用 Zend_Db 时，这就像更改 1 行一样简单：
$db = Zend_Db::factory('pdo_mysql',array('host' => MYSQL_HOST, 'username' => MYSQL_USER, 'password' => MYSQL_PASS, 'dbname' => MYSQL_SCHEMA));
Becomes
成为
$db = Zend_Db::factory('mysqli',array('host' => MYSQL_HOST, 'username' => MYSQL_USER, 'password' => MYSQL_PASS, 'dbname' => MYSQL_SCHEMA));
But if you application is not using the Zend_Db abstraction (or any other) layer, you could be in trouble.
但是如果您的应用程序没有使用 Zend_Db 抽象（或任何其他）层，您可能会遇到麻烦。
回答by Adrián Prieto
alter user 'username'@'localhost' identified with mysql_native_password by 'password'; would fix it.
更改用户 'username'@'localhost' 用 mysql_native_password 标识的 'password'；会修复它。
回答by metatron
I had this problem on a shared hosting service (bluehost.com). I just reset the borking MySql user's password via the web interface provided by Bluehost. I re-used the old password, retyped it at the interface and saved and everything was fine again.
我在共享托管服务 (bluehost.com) 上遇到了这个问题。我只是通过 Bluehost 提供的 Web 界面重置了 borking MySql 用户的密码。我重新使用了旧密码，在界面上重新输入并保存，一切又正常了。
回答by Yevgeniy Afanasyev
I got this problem when running bitbucket pipelines with laravel 5.6 and default mysql database (set is services:).
我在使用 laravel 5.6 和默认 mysql 数据库（设置为服务：）运行 bitbucket 管道时遇到了这个问题。
solution was to use older version of mysql
解决方案是使用旧版本的mysql
definitions:
services:
mysql:
image: mysql:5.6
PS: it was PHP 7.1
PS：它是 PHP 7.1
