What to do when you get access denied for user 'root'@'localhost'?
I used to access the root user in MySQL just fine. But recently, I am no longer able to.
I am able to login fine :
mysql -u root -p
Here is the mysql status after login :
mysql> status
--------------
mysql Ver 14.14 Distrib 5.5.28, for debian-linux-gnu (i686) using readline 6.2
Connection id: 37
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.5.28-0ubuntu0.12.04.3 (Ubuntu)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/run/mysqld/mysqld.sock
Uptime: 4 min 16 sec
Threads: 1 Questions: 112 Slow queries: 0 Opens: 191
Flush tables: 1 Open tables: 6 Queries per second avg: 0.437
--------------
But when I want to do any action, such as :
mysql> CREATE DATABASE moyennegenerale;
ERROR 1044 (42000): Access denied for user 'root'@'%' to database 'moyennegenerale'
I understand % is used to signify any host , but my status clearly states localhost. Does somebody have an idea of what might be going on?
In case of access denied for user 'root'@'localhost' -
Try running this:
SELECT user,host,password FROM mysql.user WHERE user='';
This will show what anonymous users exist. Most likely, you will see a line with a blank user, host %, and a blank password as shown below:
mysql> select user,host,password from mysql.user;
+-----------+-------------+-------------------------------------------+
| user | host | password |
+-----------+-------------+-------------------------------------------+
| lwdba | 127.0.0.1 | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| lwdba | localhost | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| lwdba | % | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| root | localhost | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| root | 127.0.0.1 | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| vanilla | localhost | |
| mywife | % | |
| | % | | <<<--- LOOK !!!
| replicant | 10.64.113.% | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| kumar | % | |
+-----------+-------------+-------------------------------------------+
So, how did you login? Run this query:
SELECT USER(),CURRENT_USER();
What does this tell you?
USER() reports how you attempted to authenticate in MySQL
CURRENT_USER() reports how you were allowed to authenticate in MySQL
The second function CURRENT_USER() reveals which anonymous user was used to log in.
What privileges did you have when you logged in?
Please run
SHOW GRANTS;
This will unveil what privileges you had at the time you logged in. The fact that you were blocked from creating a database shows you were not root but some lower-privileged user.
Please clean up your user grants.
Feb 17, 2012 : MySQL : Why are there "test" entries in mysql.db?
Feb 17, 2012 : What is the mysql.db table used for?
Jan 18, 2012 : MySQL error: Access denied for user 'a'@'localhost' (using password: YES)
As for resetting the root password, please do the following:
echo "SET PASSWORD FOR root@localhost=PASSWORD('password');" > /var/lib/mysql/rootpwd.sql
service mysql restart
rm -f /var/lib/mysql/rootpwd.sql