远程 mySQL 连接抛出“无法使用旧的不安全身份验证连接到 MySQL 4.1+"来自 XAMPP 的错误

2022-01-14 00:00:00 xampp mysql remote-access

我正在 XAMPP/WinXP 上运行 WordPress 的本地副本以进行开发，但想保持与远程数据库的连接.无论我尝试什么，我都会不断收到建立数据库连接时出错".

I'm running a local copy of WordPress on XAMPP/WinXP for development, but would like to maintain a connection to the remote database. I keep getting "Error establishing database connection" no matter what I try.

在同一台 PC 上，我可以使用任意数量的 mySQL 客户端连接到远程 mySQL 数据库，并且在 mySQL 端，用户和数据库都设置为接受来自任何通配符域的传入请求.我还可以从我的 PC 轻松 ping 远程数据库服务器(虽然我不知道如何从 WITHIN XAMPP 中执行此操作).

On the same PC, I can connect to the remote mySQL DB using any number of mySQL clients, and on the mySQL side, the both the user and the database are set to accept incoming requests from any wildcard domain. I can also easily ping the remote database server from my PC (though I don't know how to do it from WITHIN XAMPP).

XAMPP 是不是它自己的小宇宙，无法到达外界?还是有什么我明显忽略的东西让我无法连接?

Is XAMPP its own little universe that can't reach through to the outside world? Or is there something I'm clearly overlooking that's not letting me connect?

错误

Warning: mysql_connect() [function.mysql-connect]: Premature end of data (mysqlnd_wireprotocol.c:553) in C:xampphtdocsdbtest.php on line 5 
Warning: mysql_connect() [function.mysql-connect]: OK packet 1 bytes shorter than expected in C:xampphtdocsdbtest.php on line 5 
Warning: mysql_connect() [function.mysql-connect]: mysqlnd cannot connect to MySQL 4.1+ using the old insecure authentication

编辑

感谢@Michael 建议我创建一个简单的连接脚本，以便我可以更好地了解引发的实际错误.这表明它与 mySQL 中的 old_password 设置有关.有关如何解决此问题的完整说明，请参阅下面的答案.

Thanks to @Michael for suggesting I just create a simple connection script so I can get better insight into the actual error that's being thrown. This revealed that it had to do with the old_password setting in mySQL. See my Answer below for a full description of how to resolve this issue.

这是我放在 xampphtdocs 文件夹中并进行测试的测试脚本:

Here's the test script I put inside my xampphtdocs folder and tested out:

<?php

$mysqli = new mysqli('my.server.address', 'user_name', 'password', 'database_name');

if ($mysqli->connect_error){
    die ("Connect error: " . $mysqli->connect_error );
}

推荐答案

我不太清楚为什么这会成为我的 XAMPP 安装的问题，因为我也在服务器的本地机器上运行 PHP 5.3.x 并且在那里没有遇到这些问题.但是，它与运行在旧密码"加密模式下的 mySQL 服务器有关.较新版本的 PHP 不允许此类连接，因此您需要更新您的 mySQL 服务器以使用较新的密码加密.以下是步骤，假设您可以控制 mySQL 服务器.如果你不这样做，那不在我的知识范围内.

I'm not really clear on why this became an issue on my XAMPP installation, since I'm also running PHP 5.3.x on the server's local box and wasn't experiencing those issues there. However, it has to do with my mySQL server running in "old password" encryption mode. Newer versions of PHP won't allow those kinds of connections, so you need to update your mySQL server to use the newer password encryption. Here are the steps, assuming you have control over the mySQL server. If you don't, that falls out of the scope of my knowledge.

  1. 找到名为 my.cnf 的 mysql 服务器的配置文件.我在 /etc/my.cnf 找到了我的.您可以使用 sudo nano/etc/my.cnf

  1. locate the configuration file for the mysql server called my.cnf. I found mine at /etc/my.cnf. You can edit it with sudo nano /etc/my.cnf

查找写有 old_passwords=1 的行并将其更改为 old_passwords=0.您现在已经告诉服务器，下次运行时，它被要求使用 PASSWORD() 命令加密密码，它使用新的 41 个字符加密而不是 16 个字符的旧"样式加密

Look for a line that says old_passwords=1 and change that to old_passwords=0. You have now told the server that the next time it is run, and it is asked to encrypt a password using the PASSWORD() command, it use the new 41-character encryption rather than the 16-character 'old' style encryption

现在你必须重启你的mysql服务器/服务.YMMV，但在 Fedora 上使用 sudo service mysqld restart 很容易完成.检查您的操作系统关于重新启动 mysql 守护程序或服务的说明

Now you have to restart your mysql server / service. YMMV, but on Fedora that was easily done with sudo service mysqld restart. Check your OS' instructions for restarting the mysql daemon or service

现在我们必须在 mysql 中实际编辑我们的 user 表.所以打开mysql的交互式shell(在服务器上你可以输入mysql -uYourRootUsername -pYourRootPassword)

Now we have to actually edit our user table within mysql. So open up an interactive shell to mysql (on the server you can type mysql -uYourRootUsername -pYourRootPassword)

更改为 mysql 数据库.这是包含服务器操作和身份验证的所有好东西的数据库.您必须具有 root 访问权限才能使用此数据库.如果您收到拒绝访问"，您就是 SOL.对不起.use mysql; 会切换到那个数据库

Change to the mysql database. This is the database that holds all the good stuff for server operation and authentication. You must have root access to work with this database. If you get an 'access denied' you're SOL. Sorry. use mysql; will switch to that database

现在我们要更新让你伤心的用户.最终，您可能希望更新所有用户，但目前，我们只关注引发错误的用户.更新用户设置 Password=password('YOUR_PASSWORD') where User='YOUR_USERNAME';

Now we want to update the user that was giving you grief. Ultimately you'll probably want to update all your users, but for now, we're just focusing on the user that threw the error. update user set Password=password('YOUR_PASSWORD') where User='YOUR_USERNAME';

现在您只需要告诉 mysql 在该用户尝试连接时使用新密码进行身份验证.刷新权限；.

Now you just need to tell mysql to use the new password for authentication when that user attempts to connect. flush privileges;.

你应该很高兴！

相关文章