Yes, Another MySql: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Question

So I'm not sure what caused this to scramble my MySql password (upgrade to Mountain Lion, reinstalling ruby/rails (other issues), or simply just bad luck) but here we are:

Logging in to mysql is fine, but I can't log into the root

Ayman$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 96
Server version: 5.5.25 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

When I try to login to root:

Ayman$ mysql -u root -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

Looked at the users:

mysql> CREATE USER root@localhost IDENTIFIED BY 'out22out';
ERROR 1227 (42000): Access denied; you need (at least one of) the CREATE USER privilege(s)     for this operation
mysql> SELECT USER(),CURRENT_USER();
+-----------------+----------------+
| USER()          | CURRENT_USER() |
+-----------------+----------------+
| Ayman@localhost | @localhost     |
+-----------------+----------------+
1 row in set (0.00 sec)

mysql> SELECT USER, HOST FROM mysql.user;
ERROR 1142 (42000): SELECT command denied to user ''@'localhost' for table 'user'
mysql> select user, host from mysql.user;
ERROR 1142 (42000): SELECT command denied to user ''@'localhost' for table 'user'
mysql> grant all on *.* to Ayman@'%';
ERROR 1045 (28000): Access denied for user ''@'localhost' (using password: NO)
mysql> 

I've tried reinstalling MySql (not sure how to uninstall it), stopping the server instance and restarting it, and every other suggestion on the myriad of similar questions to the right. I've been on this for 3 days now and it's killing me.

Any clue?

Solution 1

You can start MySQL (the server, not the command line monitor) with --skip-grant-tables, which shuts off the permissions systems temporarily. That'll let you get in, update your accounts/passwords without those screwed up permissions getting in the way. Once things are fixed, you do a flush privileges; to re-enable the permissions system.

Solution 2

Ok I figured it out using the tutorial on this website:

I had no clue how to run skip grant tables as evidenced below:

Last login: Wed Aug 22 23:10:42 on console
Ayman$ --skip-grant-tables
-bash: --skip-grant-tables: command not found
Ayman$ mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock'    (2)
Ayman$ --skip-grant-tables
-bash: --skip-grant-tables: command not found

Here is where I googled Skip Grant Tables and found the tutorial listed above:

Don't think any of the initial part of the tutorial worked:

Ayman$ # vi /etc/rc.d/init.d/mysql
Ayman$ $bindir/mysqld_safe --datadir=$datadir --pid-file=$server_pid_file $other_args     >/dev/null 2>&1 &
[1] 302
Ayman$ $bindir/mysqld_safe --skip-grant-tables --datadir=$datadir --pid-  file=$server_pid_file $other_args >/dev/null 2>&1 &
[2] 303
[1]   Exit 127                $bindir/mysqld_safe --datadir=$datadir --pid-   file=$server_pid_file $other_args > /dev/null 2>&1
Ayman$ mysql start
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock'   (2)
[2]+  Exit 127                $bindir/mysqld_safe --skip-grant-tables --datadir=$datadir -  -pid-file=$server_pid_file $other_args > /dev/null 2>&1
Ayman$ # service mysql start
Ayman$ mysql -u root mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)
Ayman$ mysql

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

HERE IS THE MAGIC!:

Ayman$ mysql -u root mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.5.27 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> UPDATE user SET password=PASSWORD('newpassword') WHERE user='root';
Query OK, 4 rows affected (0.11 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> flush privileges
-> ;
Query OK, 0 rows affected (0.00 sec)

More stuff from the link above that didn't work:

mysql> service mysql stop
-> ;
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 'service mysql stop'    at line 1
mysql> # service mysql stop
mysql> # vi /etc/rc.d/init.d/mysql
mysql> $bindir/mysqld_safe --datadir=$datadir --pid-file=$server_pid_file $other_args   >/dev/null 2>&1 &
    -> 
    -> ;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock'   (2)
ERROR: 
Can't connect to the server

mysql> $bindir/mysqld_safe --skip-grant-tables --datadir=$datadir --pid-  file=$server_pid_file $other_args >/dev/null 2>&1 &;
No connection. Trying to reconnect...
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock'   (2)
ERROR: 
Can't connect to the server

mysql> $bindir/mysqld_safe --skip-grant-tables --datadir=$datadir --pid-  file=$server_pid_file $other_args >/dev/null 2>&1 &;
No connection. Trying to reconnect...
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
ERROR: 
Can't connect to the server

mysql> $bindir/mysqld_safe --datadir=$datadir --pid-file=$server_pid_file $other_args   >/dev/null 2>&1 &;
No connection. Trying to reconnect...
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
ERROR: 
Can't connect to the server

IT's ALIVE!!!!:

mysql> quit
Bye
Ayman$ mysql -u root -p
Enter password: newpassword 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 25
Server version: 5.5.27 MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

Thank you to MARC B and the StackOverflow community for pointing me in the right direction and getting this novice back in the game!!

Comments

[aalab002]

I figured it out! I googled skip grant tables per your advice and found this tutorial thegeekstuff.com/2009/07/… Basically just ran 'Ayman$ mysql -u root mysql' 'mysql> UPDATE user SET password=PASSWORD('newpassword') WHERE user='root';' and then, 'mysql> flush privileges' Then I logged back in: 'Ayman$ mysql -u root -p' 'Enter password: newpassword' And it worked! Thanks @MarcB! Wish I could upvote you but I'm just a lowly noob!

[tan]

Thanks for the solution. This solved my problem. I kept getting the same error despite doing the troubleshooting mentioned by other stackoverflow threads.