MySQL: Your password has expired to login you must change it
MySQL: Your password has expired to login you must change it using client that supports expired passwords.
At the time of connecting to MySQL using root username over port 3306 or over any custom MySQL port configured if any, you come across following error
Your password has expired to login you must change it using client that supports expired passwords.
- Stop MySQL
- Add skip-grant-tables at C:\ProgramData\MySQL\MySQL Server 5.7/my.ini under ———————————-[mysqld]skip-grant-tables———————————-
- Start SQL services from services.msc
- Go to C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql.exe -P3306It will log you in without password—————————————————————–C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql.exe -P3306Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 219
Server version: 5.7.8-rc-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
mysql> use mysql
Now as you notice above, since we have ‘skip-grant-tables’ updated in my.ini, it doesn’t allow any commands to be executed. Follow the steps ahead for further remediation.
6. Install any SQL client inside the server. I would recommend Heidi SQL portable would be ideal. With skip grant tables already in place, log into the SQL using
7. Go to user’s icon and create a new user and assign global privileges along with connection access from anywhere (as good as assigning root credentials).
For instance, I create user ‘admin’ with connection access % and access to Global Privileges.
Note: You might be wondering, why I did not create user from command line, considering that skip grant tables would allow me to log in to MySQL db without credentials?
Ans: It will again throw an error at the time of passing following create user command, since skip-grant-tables option is enabled in my.ini
mysql> CREATE USER ‘admin’@’%’ IDENTIFIED BY ‘password’;
mysql> CREATE USER ‘admin’@’%’ IDENTIFIED BY ‘gps@123$win’;
ERROR 1290 (HY000): The MySQL server is running with the –skip-grant-tables option so it cannot execute this statement
8. Now, having created a user ‘admin’ with root like credentials from SQL client. You can remove skip-grant-tables from my.ini and restart SQL services.
9. Now go to C:\Program Files\MySQL\MySQL Server 5.7\bin and login.
For your reference:
C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql.exe -P3306 -uadmin -p
Enter password: ***********
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 612
mysql> use mysql
mysql> ALTER USER ‘root’@’localhost’ PASSWORD EXPIRE NEVER;
mysql> SET PASSWORD FOR ‘root’@’%’ = PASSWORD(‘password’);
This should fix the issue and you should be able to login using root username.
Note: Step no 9 can also be performed for resetting root credentials with Skip-Grant-tables enabled but only from SQL client Heidi SQL or any other SQL client.