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?
Best Answer
I think you have anonymous users
Try running this:
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:So, how did you login? Run this query:
What does this tell you?
The second function
CURRENT_USER()
reveals how which anonymous user was used to log in.What privileges did you have when you logged in?
Please run
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:
I learned this efficient method from @ShlomiNoach.
Give it a Try !!!