Mysql password expired. Can't connect
PhpMysqlMacosOsx ElcapitanPhp Problem Overview
I just wiped my Mac and did a fresh install of El Capitan. I'm struggling to connect to Mysql now. Having gone through a web server setup process, I've created a simple PHP test file:
<?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();
?>
When I run it, I get this error:
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.
I've never seen that response from a connection before. How do I fix it if I can't connect?
EDIT
In terminal I entered the command:
mysql -u root -p
This asked me for my password (current one) which I put in. I now have access to mysql commands but anything I try results in this error:
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
How do I reset the password using ALTER USER?
Php Solutions
Solution 1 - Php
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');
Solution 2 - Php
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.
Solution 3 - Php
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.
Solution 4 - Php
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.
Solution 5 - Php
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.
Solution 6 - Php
Just download MySQL workbench to log in. It will prompt you to change the password immediately and automatically.
Solution 7 - Php
This worked for me:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'yourpassword','root'@'localhost' PASSWORD EXPIRE NEVER;
Solution 8 - Php
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.
Solution 9 - Php
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
Solution 10 - Php
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;
Solution 11 - Php
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
Solution 12 - Php
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');
Solution 13 - Php
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.
Solution 14 - Php
best easy solution:
[PATH MYSQL]/bin/mysql -u root
[Enter password]
SET GLOBAL default_password_lifetime = 0;
and then works fine.
Solution 15 - Php
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;
Solution 16 - Php
On DBeaver, Edit Connection -> Driver Properties -> disable disconnectOnExpiredPasswords:
Reconnect and:
And execute:
SET PASSWORD FOR 'user-name'@'localhost' = PASSWORD('NEW_USER_PASSWORD');
FLUSH PRIVILEGES;
Solution 17 - Php
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.
Solution 18 - Php
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.
Solution 19 - Php
I did something like this.
UPDATE mysql.user SET authentication_string = PASSWORD(''), password_expired = 'N' WHERE User = 'root' AND Host = 'localhost';
Solution 20 - Php
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+;
Solution 21 - Php
Just open MySQL Workbench and choose [Instance] Startup/Shutdown and click on start server. It worked for me