Mysql password expired. Can't connect

Question

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?

this is really annoying that a whole service can be shutdown because of this — tofutim, Dec 02 '16 at 20:12
Try running `/usr/local/mysql/bin/mysqladmin -u root -p password` — Connor Leech, Sep 27 '18 at 05:04
@ConnorLeech spent hours trying to reset homebrew's MariaDB expired root password, but this fixed it for me - thanks! — Alexander Holsgrove, Mar 26 '19 at 21:44

score 186 · Accepted Answer · answered Oct 28 '15 at 10:37

186

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

answered Oct 28 '15 at 10:37

CaribouCode

13,998
28
102
174

Did this work properly? Did it gave any error after following these steps in future? – Rohan Sanap Feb 20 '16 at 08:22
5

I use version 5.7.9 of MySQL and this is now deprecated as this error message from MySQL command prompt tells us: Warning (Code 1287): 'SET PASSWORD = PASSWORD('')' is deprecated and will be removed in a future release. Please use SET PASSWORD = '' instead – Fabiano Nov 21 '16 at 12:31
I am sorry this did not work for me but the alter user posted by Piotr N. worked. Perhaps operating on different version of MySQL. – MG Developer Dec 24 '18 at 02:44

score 99 · Answer 2 · edited Jan 15 '17 at 11:45

99

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.

edited Jan 15 '17 at 11:45

RiggsFolly

93,638
21
103
149

answered Jul 19 '16 at 02:51

Piotr N.

991
6
4

3

Getting Error: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'USER 'root'@'localhost' IDENTIFIED BY 'newPassword','root'@'localhost' PASSWORD ' at line 1 – Ketav Aug 31 '16 at 11:15
This worked well. ALTER USER 'username'@'%' IDENTIFIED BY 'your_password', 'user'@'%' PASSWORD EXPIRE NEVER; – TheLegendaryCopyCoder Dec 14 '16 at 08:37
1

Getting error: ```ERROR 1805 (HY000): Column count of mysql.user is wrong. Expected 45, found 46. The table is probably corrupted``` – Pulkit Pahwa Jan 19 '18 at 05:17

Kit · Answer 3 · 2017-01-12T03:25:32.280

53

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.

edited Jan 12 '17 at 03:25

answered Apr 25 '16 at 02:38

Kit

3,388
1
27
24

Worked for me too, OS X El Capital and MySQL 5.17.13! Thanks for this! – Zeke Jun 25 '16 at 06:10
5

It is worth mentioning that the word "password" is *not a placeholder*, whereas "root" is the name of the user you'd like to update: `mysqladmin -u [username] -p password` – random_user_name Jan 06 '17 at 16:06
This is the only thing that (seemingly) worked for me on Linux (Fedora 25). Thanks. – Avinash Meetoo Feb 20 '17 at 13:49

Dave Everitt · Answer 4 · 2017-06-22T15:56:12.953

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.

With an already expired password, only the global setting worked for me. — maaartinus, Mar 10 '17 at 07:01
You can add this parameter to /etc/mysql/my.cnf, under mysqld; default_password_lifetime = 0 otherwise, after mysql restart you will get same error again. — mertaydin, Jun 22 '17 at 15:25

score 11 · Answer 5 · answered Mar 10 '16 at 03:07

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.

score 9 · Answer 6 · edited Mar 05 '20 at 15:56

9

This worked for me:

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

edited Mar 05 '20 at 15:56

RiggsFolly

93,638
21
103
149

answered Oct 30 '17 at 11:23

Robert Saylor

1,279
9
11

score 7 · Answer 7 · answered May 23 '16 at 20:46

7

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

answered May 23 '16 at 20:46

Grassright

248
3
8

Laurent Zminka · Answer 8 · 2018-07-17T07:49:40.300

5

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.

edited Jul 17 '18 at 07:49

answered Jul 14 '18 at 08:33

Laurent Zminka

51
1
4

Its possible than the default_password_lifetime take again 360 days, so in my.ini add : – Laurent Zminka Jul 17 '18 at 07:48

score 4 · Answer 9 · answered Mar 26 '17 at 05:14

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

score 4 · Answer 10 · answered Jun 10 '17 at 15:30

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;

score 4 · Answer 11 · answered Feb 12 '18 at 20:50

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

score 2 · Answer 12 · answered Apr 17 '17 at 09:14

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

score 1 · Answer 13 · answered Oct 28 '15 at 10:04

1

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.

answered Oct 28 '15 at 10:04

Adarsh Gangadharan

653
4
10

When I enter the first command `mysql -u root` I get this error: `ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)` – CaribouCode Oct 28 '15 at 10:05
For the record i managed to get in by changing the first command to `mysql -u root -p` then it asked me for the password which I put in. Your next command didn't work for me because it said `You must reset your password using ALTER USER statement before executing this statement.` – CaribouCode Oct 28 '15 at 10:23
Do you know how to reset the password using ALTER USER? I'm not that good with Mysql (I'm more of a NodeJS/MongoDB person). – CaribouCode Oct 28 '15 at 10:32

score 1 · Answer 14 · answered May 16 '17 at 08:42

1

best easy solution:

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

and then works fine.

answered May 16 '17 at 08:42

ASama

31
2

score 1 · Answer 15 · edited Jan 21 '19 at 08:21

1

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;

edited Jan 21 '19 at 08:21

shizhen

12,251
9
52
88

answered Jan 21 '19 at 07:32

LiveDev

11
2

score 1 · Answer 16 · answered Apr 20 '21 at 17:07

1

On DBeaver, Edit Connection -> Driver Properties -> disable disconnectOnExpiredPasswords:

Reconnect and:

And execute:

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

answered Apr 20 '21 at 17:07

Kamil Tomasz Jarmusik

140
8

score 0 · Answer 17 · answered Oct 28 '15 at 09:59

0

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.

answered Oct 28 '15 at 09:59

Daniel W.

31,164
13
93
151

2

Thanks for your response. I've downloaded Sequel Pro and tried to connect via socket with that. I get the same error about the password being expired... – CaribouCode Oct 28 '15 at 10:03

score 0 · Answer 18 · answered May 24 '17 at 15:09

0

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.

answered May 24 '17 at 15:09

Prasant Kumar

1,008
12
13

score 0 · Answer 19 · edited Mar 05 '20 at 15:56

0

I did something like this.

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

edited Mar 05 '20 at 15:56

RiggsFolly

93,638
21
103
149

answered Mar 15 '19 at 14:27

Marcel Zebrowski

301
2
5

1

`You must reset your password using ALTER USER statement before executing this statement.` – Weijing Jay Lin Oct 26 '19 at 00:07

score 0 · Answer 20 · edited Mar 10 '22 at 00:09

0

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+;

edited Mar 10 '22 at 00:09

Elikill58

4,050
24
23
45

answered Mar 09 '22 at 21:21

ShiYueDeTianXieZuo

1

score -1 · Answer 21 · answered Oct 29 '20 at 10:18

-1

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

answered Oct 29 '20 at 10:18

Arjun

1
1

Mysql password expired. Can't connect

21 Answers21

MySQL password expiry

Linked

Related