密码过期，无法连接

我刚刚清空了我的 Mac 然后重新安装了一个 El Capitan。我现在正在努力连接 Mysql。在完成了 Web 服务器设置过程之后，我创建了一个简单的 PHP 测试文件:

<?php
$conn = new mysqli("127.0.0.1", "root", "xxxxxxxx");
if ($conn->connect_error) echo "Connection failed: " . $conn->connect_error;
else echo "Connected successfully";
phpinfo();
?>

当我运行它时，我得到这个错误:

Warning: mysqli::mysqli(): (HY000/1862): Your password has expired. To log in you must change it using a client that supports expired passwords. in /Users/rich/Documents/DESIGN/test/index.php on line 3
Connection failed: Your password has expired. To log in you must change it using a client that supports expired passwords.

我从来没有见过这样的反应，如果我不能连接，我该怎么修复它？

剪辑

在终端我输入了命令:

mysql -u root -p

这要求我输入我的密码(当前密码)。我现在可以访问 mysql 命令，但是我尝试的任何操作都会导致这个错误:

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

如何使用 ALTER USER重置密码？

144073 次浏览
小开

The password expiration is a new feature in MySQL 5.6 or 5.7.

The answer is clear: Use a client which is capable of expired password changing (I think Sequel Pro can do it).

MySQLi library obviously isnt able to change the expired password.

If you have limited access to localhost and you only have a console client, the standard mysql client can do it.

小开

restart MySQL server with --skip-grant-tables option And then set a new root password

$ mysql -u root
mysql> USE mysql;
mysql> UPDATE user SET password=PASSWORD("NEWPASSWORD") WHERE User='root';
mysql> FLUSH PRIVILEGES;
mysql> quit

Now if you need, you can update mysql.user table(field password_expired='N') not to expire the password.

小开
最佳答案

So I finally found the solution myself.

Firstly I went into terminal and typed:

mysql -u root -p

This asked for my current password which I typed in and it gave me access to provide more mysql commands. Anything I tried from here gave this error:

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

This is confusing because I couldn't actually see a way of resetting the password using ALTER USER statement, but I did find another simple solution:

SET PASSWORD = PASSWORD('xxxxxxxx');

小开

I went through the same issue recently while installing mysql on mac os x capitan. I did not find the correct answer here, so adding this answer.

MySql in current versions, generates a temporary password when you install mysql. Use this password to set a new password using the mysqladmin utility as below;

/usr/local/mysql/bin/mysqladmin -u root -p'<your temp password>' password '<your new password>'

Hope it helps you and others.

小开

mysqladmin -u [username] -p password worked for me on OS X El Capitan and MySQL 5.7.12 Community Server. Example: 

$ /usr/local/mysql/bin/mysqladmin -u root -p password
Enter password:
New password:
Confirm new password:
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

This is similar to pavan sachi's answer, but with password prompts.

My error was "#1862 - Your password has expired. To log in you must change it using a client that supports expired passwords." at phpMyAdmin login screen first time.

小开

Just download MySQL workbench to log in. It will prompt you to change the password immediately and automatically.

小开

First, I use:

 mysql -u root -p

Giving my current password for the 'root'. Next:

mysql> ALTER USER `root`@`localhost` IDENTIFIED BY 'new_password',
`root`@`localhost` PASSWORD EXPIRE NEVER;

Change 'new_password' to a new password for the user 'root'.
It solved my problem.

小开

MySQL password expiry

Resetting the password will only solve the problem temporarily. From MySQL 5.7.4 to 5.7.10 (to encourage better security - see MySQL: Password Expiration Policy) the default default_password_lifetime variable value is 360 (1 year-ish). For those versions, if you make no changes to this variable (or to individual user accounts) all passwords expire after 360 days.

So from a script you might get the message: "Your password has expired. To log in you must change it using a client that supports expired passwords."

To stop automatic password expiry, log in as root (mysql -u root -p), then, for clients that automatically connect to the server (e.g. scripts.) change password expiration settings:

ALTER USER 'script'@'localhost' PASSWORD EXPIRE NEVER;

OR you can disable automatic password expiration for all users:

SET GLOBAL default_password_lifetime = 0;

As pointed out by Mertaydin in the comments, to make this permanent add the following line to a my.cnf file MySQL reads on startup, under the [mysqld] group of settings. The location of my.cnf depends on your setup (e.g. Windows, or Homebrew on OS X, or an installer), and whether you want this per-user on Unix or global:

[mysqld] default_password_lifetime = 0 (There may be other settings here too...)

See the MySQL docs on configuration files.

小开

start MYSQL in safe mode

mysqld_safe --skip-grant-tables &

Connect to MYSQL server

mysql -u root

run SQL commands to reset password:

use mysql;
SET GLOBAL default_password_lifetime = 0;
SET PASSWORD = PASSWORD('new_password');

Last step, restart your mysql service

小开

All of these answers are using Linux consoles to access MySQL.

If you are on Windows and are using WAMP, you can start by opening the MySQL console (click WAMP icon->MySQL->MySQL console).

Then it will request you to enter your current password, enter it.

And then type SET PASSWORD = PASSWORD('some_pass');

小开

best easy solution: 

[PATH MYSQL]/bin/mysql -u root
[Enter password]
SET GLOBAL default_password_lifetime = 0;

and then works fine.

小开

Open MySQL console and type SET PASSWORD = 'your password'; and then press ENTER Key which will set your defined password for user root.

You can only write SET PASSWORD = ''; which will set password as blank for root user.

小开

i have faced this issue few days ago. For best solution for 5.7 version of MySQL; login your mysql console and alter your password with the following command:

ALTER USER `root`@`localhost` IDENTIFIED BY 'new_password', `root`@`localhost` PASSWORD EXPIRE NEVER;
小开

This worked for me:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'yourpassword','root'@'localhost' PASSWORD EXPIRE NEVER;
小开

WARNING: this will allow any user to login

I had to try something else. Since my root password expired and altering was not an option because

Column count of mysql.user is wrong. Expected 45, found 46. The table is probably corrupted

temporarly adding skip-grant-tables under [mysqld] in my.cnf and restarting mysql did the trick

小开

On Windows in phpmyadmin look in Variables: default_password_lifetime, and switch it to 0 (instead of 360), enjoy.

Its possible than mySQL take again 360 days, so add in my.ini :

[mysqld]
default_password_lifetime=0

And restart mysql.

小开

This work for me:

Source: https://www.diariodeunprogramador.net/fallo-al-conectar-mysql-your-password-expired/

Login as root:

mysql -u root -p

and then you deactivate the automatic expiration of passwords of all the users:

SET GLOBAL default_password_lifetime = 0;
小开

I did something like this. 

UPDATE mysql.user SET authentication_string = PASSWORD(''), password_expired = 'N' WHERE User = 'root' AND Host = 'localhost';
小开

Just open MySQL Workbench and choose [Instance] Startup/Shutdown and click on start server. It worked for me

小开

On DBeaver, Edit Connection -> Driver Properties -> disable disconnectOnExpiredPasswords: enter image description here

Reconnect and: enter image description here

And execute:

SET PASSWORD FOR 'user-name'@'localhost' = PASSWORD('NEW_USER_PASSWORD');
FLUSH PRIVILEGES;
小开

For MySQL 8.0 +, if you get this error,

    *Your password has expired. To log in you must change it using a client that
supports expired passwords.*

you need use current user , to open mysql terminal, then use

SET PASSWORD ='123456'; To get a new password 123456 for current user;

Don't use SET PASSWORD = PASSWORD('123456'); This one have been deprecated in MySQL 8.0+;


提交答案