远程mySQL连接抛出“无法使用旧的不安全身份验证连接到MySQL 4.1+". XAMPP错误 [英] Remote mySQL connection throws "cannot connect to MySQL 4.1+ using the old insecure authentication" error from XAMPP

问题描述

我正在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 DB，并且在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:\xampp\htdocs\dbtest.php on line 5 
Warning: mysql_connect() [function.mysql-connect]: OK packet 1 bytes shorter than expected in C:\xampp\htdocs\dbtest.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.

这是我放入xampp\htdocs文件夹并进行测试的测试脚本:

Here's the test script I put inside my xampp\htdocs 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的交互式外壳(在服务器上，您可以键入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

现在，我们要更新使您感到悲伤的用户.最终，您可能需要更新所有用户，但是现在，我们只关注引发错误的用户. update user set 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当该用户尝试连接时使用新密码进行身份验证. flush privileges;.

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

你应该很好！

这篇关于远程mySQL连接抛出“无法使用旧的不安全身份验证连接到MySQL 4.1+". XAMPP错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！