MySQL Password Expired. Can't Connect

Mysql password expired. Can't connect

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');

cannot login to phpmyadmin error #1862 - Your password has expired

Ok, finally I did not understand what was the reason for this issue, but the following solution worked for me:

1. Enter this in terminal (in /usr/local/mysql/bin/) mysqladmin -u root -p password
2. Enter your password

3. Enter New password

4. Done! I could then login from phpmyadmin too!

Hope it help others who have similar problem,

Azure mySQL Password Expired

In your Application Setting on Azure Add Key = WEBSITE_MYSQL_ARGUMENTS and Value = --default_password_lifetime=0

How to reset the root password in MySQL 8.0.11?

as here says:

This function was removed in MySQL 8.0.11

1.if you in skip-grant-tables mode

in mysqld_safe:

UPDATE mysql.user SET authentication_string=null WHERE User='root';
FLUSH PRIVILEGES;
exit;

and then, in terminal:

mysql -u root

in mysql:

ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'yourpasswd';

2.not in skip-grant-tables mode

just in mysql:

ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'yourpasswd';

Reset mysql root password without mysql client access

https://dev.mysql.com/doc/refman/5.7/en/resetting-permissions.html

B.5.3.2.3 Resetting the Root Password: Generic Instructions

1. Stop the MySQL server if necessary, then restart it with the --skip-grant-tables option. This enables anyone to connect without a password and with all privileges, and disables account-management statements such as ALTER USER and SET PASSWORD. Because this is insecure, you might want to use --skip-grant-tables in conjunction with --skip-networking to prevent remote clients from connecting.

-or-

https://www.codero.com/knowledge-base/content/33/296/en/how-to-reset-your-root-mysql-password.html

$  mysqld --skip-grant-tables --skip-networking &
$  mysql -u root

 ...change your password
 ...reset mysql without --skip-grant-tables and --skip-networking

UPDATE

It likely the mysqld cannot run as root or your system account. See here.

$ mysqld --skip-grant-tables --skip-networking --user=root &

or you can revise the my.cnf file directly after stop the mysql server. I recommend the way.

[mysqld]
...
# add the following two settings to configure variables for reseting password.
skip-grant-tables
skip-networking

After restart mysqld. Using the mysql cli client execute the following sql statement to change your password.

UPDATE mysql.user SET authentication_string = PASSWORD('MyNewPass')
WHERE User = 'root' AND Host = 'localhost';
FLUSH PRIVILEGES;

Finally, stop mysqld, and rollback your my.cnf, then restart mysqld.

---

Related Topics