Reset MySQL Password

Question

Ok, so I changed my password through phpMyAdmin and it's completely locked out my access to the root mysql user. There are various articles about this online, but none of them seem to work. Here's what i've tried so far:

Stop the mysql service: sudo /etc/init.d/mysql stop

Start with this option: sudo mysqld --skip-grant-tables &

Start mysql prompt: mysql

Enter following at mysql prompt:

UPDATE mysql.user SET Password=PASSWORD('newpass') WHERE User='root';
FLUSH PRIVILEGES;
exit;

This is all working fine, but I cannot connect using the new password, i'm not sure exactly what the problem is.

Any help is much appreciated.

Tom

I am curious. Please run this query and post it in your question or as a comment : `SHOW VARIABLES LIKE 'old_passwords';` — RolandoMySQLDBA, Oct 19 '11 at 20:38

score 2 · Answer 1 · answered Oct 19 '11 at 18:29

2

Could be because your account on MYSQL could be more than one.

For example root, root@localhost and root@xxx.xxx.xxx.xxx.

So First check the user accounts on the server:

select User from mysql.user where mysql.user like '%root%';

Then Update the password accordingly.

Alternatively, you can also use mysqladmin to change password, which I have found to be a better solution.

answered Oct 19 '11 at 18:29

Sridhar Jayakumar

181
5

When I try this it shows 2 users, both just root. – user491704 Oct 19 '11 at 18:57

score 1 · Answer 2 · answered Oct 19 '11 at 18:35

1

You should be using the SET PASSWORD syntax detailed here.

SET PASSWORD FOR 'user'@'host' = PASSWORD('newpassword');

answered Oct 19 '11 at 18:35

sinping

2,055
14
12

When I try to do this i get the error: MYSQL server is running with the --skip-grant-tables option and cannot execute this statement – user491704 Oct 19 '11 at 18:53
Ah, I see what you are trying to do now. Try a password without any special characters to verify. Make it something like 'abc123' just to see if the password you were using is the problem. – sinping Oct 19 '11 at 19:33
Also be sure you don't have a my.ini pointing you to some other server. I don't know your environment, but basically I'm saying verify you are actually hitting the server you mean to. – sinping Oct 19 '11 at 19:34
And by my.ini I mean my.cnf. – sinping Oct 20 '11 at 16:40

score 0 · Answer 3 · answered Oct 19 '11 at 18:28

0

If you're still trying to connect with phpMyAdmin, delete your browser cache and cookies.OR, after failed login using the right credentials, press ctrl+f5, in order to reload and override the cache

answered Oct 19 '11 at 18:28

DimitrisD

231
2
11

I also get the error when I try and log into mysql from the command line (using mysql -uuser -p) – user491704 Oct 19 '11 at 18:58

Reset MySQL Password

3 Answers3