Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

This question already has an answer here:

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

share|improve this question
    
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
add comment

marked as duplicate by Chris S Mar 20 '13 at 5:42

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

3 Answers

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.

share|improve this answer
    
When I try this it shows 2 users, both just root. –  user491704 Oct 19 '11 at 18:57
add comment

You should be using the SET PASSWORD syntax detailed here.

SET PASSWORD FOR 'user'@'host' = PASSWORD('newpassword');
share|improve this answer
    
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
add comment

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

share|improve this answer
    
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
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.