5

echo time login in mysql shell it shows:

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

and error log shows: Your password has expired. To log in you must change it using a client that supports expired passwords.

4

You need to enable the Sandbox Mode for your connection. For the mysql CLI there is a parameter to specify it:

mysql -u <user> [other params] --connect-expired-password

You then have to use the SET PASSWORD statement to reset your password (it is allowed to reset it to its current value).

  • thx, can I get rid of SET PASSWORD after restart mysqld ? – lasteye Oct 22 '15 at 8:15
  • @lasteye the message pops up because your password was marked as expired, with the SET PASSWORD you reset it so it should not happen again (unless it is expired again by some command). You should then be able to reconnect normally even without server restart. – jkavalik Oct 22 '15 at 8:19
4

MySQL password expired

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

From MySQL 5.7.4 to 5.7.10, the default value for the default_password_lifetime variable is 360 (a year). For those versions, if you make no changes to this variable or individual user accounts, all user passwords will expire after 360 days.

To prevent automatic password expiry log in as root:

mysql -u root -p

then, for clients that automatically connect to the server (e.g. from scripts.) change the password expiration settings for those clients:

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

or just disable automatic password expiration for all users:

SET GLOBAL default_password_lifetime = 0;

Links and resources:

MySQL: Password Expiration and Sandbox Mode
MySQL: Password Expiration Policy
Password expiration policy in MySQL Server 5.7

  • Caveat: if I use SET GLOBAL… and the script or database changes (say with alembic upgrade… I have to do it again. I have yet to try ALTER USER… for that specific script. – Dave Everitt Jan 11 '17 at 17:40
1

This works for me:

In CMD or in LINUX terminal, 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;.

0

In my.cnf change this:

[mysqld]
default_password_lifetime=180

to this:

[mysqld]
default_password_lifetime=0

And then restart your mysql server: service mysql restart

Changing line default_password_lifetime will affect global setting for all users on server so do that only if it's desired. This solution is advanced by official MySQL 5.6 Password Expiration Policy.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.