MySQL远程连接失败，并且“未知认证方法” [英] MySQL remote connection fails with "unknown authentication method"

问题描述

 我尝试从本地计算机远程连接到MySQL服务器，但我收到以下错误：警告：PDO :: __ construct（）：服务器请求认证
方法对
中的客户端[mysql_old_password]是未知的C：\xampp\htdocs\ticket\terminal\sync.php 
 
 SQLSTATE [HY000] [2054]服务器请求认证方法
已知给客户端
  

我的本​​地MySQL服务器版本是5.5.27，libmysql - mysqlnd 5.0.10
远程MySQL服务器版本是5.5.23，mysqlnd版本没有公开。

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

  $ 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恭喜！ 
} catch（PDOException $ e）{
 echo $ e-> getMessage（）; 
} 
  

解决方案

我发现我的远程MySQL数据库主机仍然使用旧的MySQL密码哈希是16字节，而我的localhost数据库服务器使用41字节密码哈希。我使用以下查询查找密码长度：

  SELECT PASSWORD（'mypass'）
  

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

  SET GLOBAL old_passwords = 1; 
  

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

我发现它是处理认证的PHP，因为我使用 PHP ' MySQL API ，所以我降级到 PHP 5.2.8 ，我可以使远程连接成功。

我希望这有助于某人。

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

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

解决方案

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') 

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

SET GLOBAL old_passwords = 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.

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.

这篇关于MySQL远程连接失败，并且“未知认证方法”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！