php 错误：mysqlnd 无法使用旧的不安全身份验证连接到 MySQL 4.1+

Error: mysqlnd cannot connect to MySQL 4.1+ using the old insecure authentication

提问by user982853

I am getting the following error:

我收到以下错误：

Database connection failed: mysqlnd cannot connect to MySQL 4.1+ using the old insecure authentication. Please use an administration tool to reset your password with the command SET PASSWORD = PASSWORD('your_existing_password').

This will store a new, and more secure, hash value in mysql.user. If this user is used in other scripts executed by PHP 5.2 or earlier you might need to remove the old-passwords flag from your my.cnffile

数据库连接失败：mysqlnd 无法使用旧的不安全身份验证连接到 MySQL 4.1+。请使用管理工具通过命令重置您的密码SET PASSWORD = PASSWORD('your_existing_password')。

这将在mysql.user. 如果此用户用于其他由 PHP 5.2 或更早版本执行的脚本，您可能需要从my.cnf文件中删除旧密码标志

I using PHP 5.3.8 and MySQL 5.5.16 on my local machine and my host (media temple) is running PHP 5.3 MySQL 5.0.51a. The version on the live server is older than the one on my machine.

我在本地机器上使用 PHP 5.3.8 和 MySQL 5.5.16，我的主机（媒体寺）正在运行 PHP 5.3 MySQL 5.0.51a。实时服务器上的版本比我机器上的版本旧。

How do I fix this error and connect to MySQL from my local machine?

如何修复此错误并从本地计算机连接到 MySQL？

I know there are similar posts to this one but I have tried them all and none are working.

我知道有与此类似的帖子，但我已经尝试了所有帖子，但没有一个有效。

回答by Ramil Amerzyanov

• Remove or comment old_passwords = 1 in my.cnf

• 在 my.cnf 中删除或注释 old_passwords = 1

Restart MySQL. If you don't, MySQL will keep using the old password format, which will mean that you cannot upgrade the passwords using the builtin PASSWORD() hashing function.

重启 MySQL。如果不这样做，MySQL 将继续使用旧密码格式，这意味着您无法使用内置 PASSWORD() 散列函数升级密码。

The old password hashes are 16 characters, the new ones are 41 characters.

旧密码哈希为 16 个字符，新密码为 41 个字符。

• Connect to the database, and run the following query:

  SELECT user, Length(`Password`) FROM  `mysql`.`user`;
  

• 连接到数据库，并运行以下查询：

  SELECT user, Length(`Password`) FROM  `mysql`.`user`;
  

This will show you which passwords are in the old format, e.g.:

这将显示哪些密码是旧格式，例如：

+----------+--------------------+
| user     | Length(`Password`) |
+----------+--------------------+
| root     |                 41 |
| root     |                 16 |
| user2    |                 16 |
| user2    |                 16 |
+----------+--------------------+

Notice here that each user can have multiple rows (one for each different host specification).

请注意，每个用户可以有多行（每个不同的主机规范一个）。

To update the password for each user, run the following:

要更新每个用户的密码，请运行以下命令：

UPDATE mysql.user SET Password = PASSWORD('password') WHERE user = 'username';

Finally, flush privileges:

最后，刷新权限：

FLUSH PRIVILEGES;

Source: How to fix "mysqlnd cannot connect to MySQL 4.1+ using old authentication" on PHP5.3

来源：如何在 PHP5.3 上修复“mysqlnd 无法使用旧身份验证连接到 MySQL 4.1+”

回答by aqm

I had a issue where the old passwords had been enable by the server by default, so a simple SET PASSWORD FOR 'some-user'@'%' = PASSWORD ('XXXX'); wouldn't work(for reason due to old software and legacy which I won't go into....)

我有一个问题，服务器默认启用旧密码，所以一个简单的 SET PASSWORD FOR 'some-user'@'%' = PASSWORD ('XXXX'); 不起作用（由于旧软件和遗留问题，我不会讨论......）

Solution :

解决方案 ：

SET old_passwords = 0;
SET PASSWORD FOR 'some-user'@'%' = PASSWORD ('XXXX');
FLUSH PRIVILEGES;

Details :

细节 ：

Doing this as the logged in user

以登录用户身份执行此操作

SET Password = PASSWORD('password')

Simply didn't work

根本没用

Eg testable here

例如可在这里测试

SHOW CREATE TABLE `mysql`.`user`;

The password wouldn't shift to from

密码不会从

| HOST | USER | PASS |

OLD PASSWORD

旧密码

| % | some-user | 7fa559aa33d844b4 |

WHAT I WANTED, EG NEW PASSWORD

我想要什么，例如新密码

| % | some-user | *CF04AECA892176E6C0C8206F965C03374D12F93E |

So I looked up the variables for old passwords

所以我查找旧密码的变量

SHOW VARIABLES;

...
old_passwords = ON
...

So basically I had to set the mysql server var first to old_password=OFF, eg this worked for me

所以基本上我必须首先将 mysql 服务器 var 设置为 old_password=OFF，例如这对我有用

SET old_passwords = 0;
SET PASSWORD FOR 'some-user'@'%' = PASSWORD ('XXXX');
FLUSH PRIVILEGES;

回答by rkosegi

Configure target Mysql server to allow old insecure auth.

配置目标 Mysql 服务器以允许旧的不安全身份验证。

http://dev.mysql.com/doc/refman/5.0/en/server-options.html#option_mysqld_old-passwords

http://dev.mysql.com/doc/refman/5.0/en/server-options.html#option_mysqld_old-passwords

Simply in the my.cnf file on target Mysqld server comment out the old_passwords.

只需在目标 Mysqld 服务器上的 my.cnf 文件中注释掉 old_passwords。

Maybe there is way to obtain PHP build (or build it yourself) which uses compatible (old) auth mode.

也许有办法获得使用兼容（旧）身份验证模式的 PHP 构建（或自己构建）。

回答by Joe K

I had the same issue trying to connect to mediatemple external-db through IIS on my local Windows machine. Updating my password for the specific db user solved the problem connecting to the database.

我在尝试通过本地 Windows 机器上的 IIS 连接到 mediatemple external-db 时遇到了同样的问题。更新特定 db 用户的密码解决了连接到数据库的问题。

Within (mt) Account center, simply update the password. I used the same password and it solved all my problems.

在 (mt) 帐户中心内，只需更新密码。我使用了相同的密码，它解决了我所有的问题。

回答by littledynamo

I had this issue when I imported a database from an older version of MySQL. The biggest problem - it was preventing me from connecting to MySQL with my root user, so I had to reset the root passwordby following the instructions below:

当我从旧版本的 MySQL 导入数据库时​​遇到了这个问题。最大的问题 - 它阻止我使用 root 用户连接到 MySQL，所以我必须按照reset the root password以下说明进行操作：

• How to Reset the Root Password

• 如何重置根密码

After this I was able to apply the fix given above.

在此之后，我能够应用上面给出的修复程序。

E.g. by:

例如通过：

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPass');

回答by INSITE MOBILE

I ran into this error for the first time with a Media Temple db on a grid server.

我第一次在网格服务器上使用 Media Temple 数据库时遇到此错误。

The issue was caused because I used a password for a database user that was still in their old password format. The new format calls for 10+ characters, special characters, a number etc...

这个问题是因为我使用的数据库用户密码仍然是旧密码格式。新格式需要 10 个以上的字符、特殊字符、数字等...

I created a new database user and password in the new format and it worked fine.

我以新格式创建了一个新的数据库用户和密码，并且运行良好。

回答by abdelaziz barda

i have the same issue and this is all steps i've done to solve this porbleme :

我有同样的问题，这是我为解决这个问题所做的所有步骤：

1. Check if you use the correct credential
2. use this command to be sure that you change your pass : use characters ,numbers and letters (more than 10)

   UPDATE mysql.user SET Password = PASSWORD('NewPassword') WHERE user = 'username';

3. be sure that you comment oldpassword value in /etc/my.cnf
4. restart mysql service to avoid any other issues

1. 检查您是否使用了正确的凭据
2. 使用此命令确保您更改通行证：使用字符、数字和字母（超过 10 个）

   更新 mysql.user SET Password = PASSWORD('NewPassword') WHERE user = 'username';

3. 确保在 /etc/my.cnf 中注释 oldpassword 值
4. 重新启动 mysql 服务以避免任何其他问题