MySQL远程连接失败，“身份验证方法未知"

2021-11-20 00:00:00 database-connection mysql pdo remote-access

我正在尝试从本地计算机远程连接到 MySQL 服务器，但出现以下错误:

I am trying to remotely connect to MySQL server online from my local machine, but I am getting the following error:

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

我本地的 MySQL 服务器版本是 5.5.27，libmysql - mysqlnd 5.0.10远程 MySQL 服务器版本为 5.5.23，mysqlnd 版本未公开.

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.

我猜这是一个不兼容的密码哈希问题，但我不知道如何解决它.下面是我的连接代码的一部分

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();
}

推荐答案

我克服了挑战.我发现我的远程 MySQL 数据库主机仍然使用 16 字节的旧 MySQL 密码哈希，而我的 localhost 数据库服务器使用 41 字节的密码哈希.我使用以下查询来查找密码长度:

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:

SELECT PASSWORD('mypass')

我通过运行以下查询将我的 localhost 数据库服务器密码哈希更改为 16 字节

I changed my localhost database server password hash to 16-byte by running the following query

SET GLOBAL old_passwords = 1;

然后我编辑了 my.ini 文件，并设置了 old_password=1 以确保服务器重新启动时不会恢复到新密码系统.但这并没有解决我的问题.

Then I edited my.ini file, and set the old_password=1 to ensure that when the server restarts, it won't revert to the new password system. But that didn't solve my problem.

我发现是 PHP 处理身份验证，因为我使用的是 PHP 的 MySQL API，所以我降级为PHP 5.2.8 并且我能够成功建立远程连接.

I figured out that it was PHP that handles the authentication, since I was using PHP's MySQL API, so I downgraded to PHP 5.2.8 and I was able to make the remote connection successfully.

我希望这对某人有所帮助.

I hope this helps someone.

相关文章