forgot mysql password

byran cheung · 12-03-2014, 12:45 AM

In my server , the mysql ID is created by the command "mysql" , I forogt some mysql ID ( not root user ) password , would advise how to get back the password ( not reset it ) ? thanks

astrogeek · 12-03-2014, 01:11 AM

I am not certain of your meaning, but it is probably one of these:

1. The system user "mysql" login password - normally the mysql user will not have a login shell or password, it exists only to run the mysql server and to own the database directory tree. If you had set a password for this user you cannot recover it, but the root user can reset it.

2. The system mysql user mysql server password - again, the mysql user normally does not have a mysql server password. If you had set one with a GRANT statement, another user with GRANT privs may be able to reset it, but not recover it.

3. If you mean a normal user's mysql server password - Again, not recoverable, but can be reset by another user with GRANT privs.

In all cases the passwords are stored only as non-reversible hashes, so a user with adequate permission can change the passwords but not recover them.

For shell passwords, the root user may do this with the passwd command (see man page).

For mysql accounts, you may use a REVOKE/GRANT statement to set a new GRANT, including new password, or you can directly update the mysql.user table similar to this:

Code:

mysql> UPDATE mysql.user SET Password=PASSWORD('NewPassWord') WHERE User='username';

UPDATE: As you have updated the question while I was typing, to exclude resetting but only recover it, I will update my answer here as well...

As far as I know, you cannot recover them (i.e. reverse the hash). They are stored as one-way hashes so you can only reset, not recover.

byran cheung · 12-03-2014, 03:45 AM

Quote:

Originally Posted by astrogeek

I am not certain of your meaning, but it is probably one of these:

1. The system user "mysql" login password - normally the mysql user will not have a login shell or password, it exists only to run the mysql server and to own the database directory tree. If you had set a password for this user you cannot recover it, but the root user can reset it.

2. The system mysql user mysql server password - again, the mysql user normally does not have a mysql server password. If you had set one with a GRANT statement, another user with GRANT privs may be able to reset it, but not recover it.

3. If you mean a normal user's mysql server password - Again, not recoverable, but can be reset by another user with GRANT privs.

In all cases the passwords are stored only as non-reversible hashes, so a user with adequate permission can change the passwords but not recover them.

For shell passwords, the root user may do this with the passwd command (see man page).

For mysql accounts, you may use a REVOKE/GRANT statement to set a new GRANT, including new password, or you can directly update the mysql.user table similar to this:

Code:

mysql> UPDATE mysql.user SET Password=PASSWORD('NewPassWord') WHERE User='username';

UPDATE: As you have updated the question while I was typing, to exclude resetting but only recover it, I will update my answer here as well...

As far as I know, you cannot recover them (i.e. reverse the hash). They are stored as one-way hashes so you can only reset, not recover.

thanks reply ,

What I mean is I use the command "mysql" to create 3 mysql ID , but I forgot the password of these 3 mysql ID ( not system ID , just general mysql ID ) , except reset the password , how can I get back the password ? thanks

astrogeek · 12-03-2014, 08:55 AM

Quote:

Originally Posted by byran cheung

thanks reply ,

What I mean is I use the command "mysql" to create 3 mysql ID , but I forgot the password of these 3 mysql ID ( not system ID , just general mysql ID ) , except reset the password , how can I get back the password ? thanks

That is case 3. above...

As I said, unless you wrote them down somewhere, you cannot get them back from the system.

jlinkels · 12-03-2014, 11:04 AM

Passwords are stored as a hash and cannot be recovered.

However, mysql uses a command line history (like Bash). Maybe it is not too long ago you created those users and the commands are still in the command line history.

jlinkels