Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from Dec 19 '12 at 18:43

This question came from our site for professional and enthusiast programmers.

Try connecting with mysql -u root -p -h localhost and see what happens (totally guessing...) –  Carlos Campderrós Dec 19 '12 at 9:27
Belongs on –  jishi Dec 19 '12 at 13:55

4 Answers 4

I think you have anonymous users

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     |   | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| lwdba     | localhost   | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| lwdba     | %           | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| root      | localhost   | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| root      |   | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| vanilla   | localhost   |                                           |
| mywife    | %           |                                           |
|           | %           |                                           | <<<--- LOOK !!!
| replicant | 10.64.113.% | *xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| kumar     | %           |                                           |

So, how did you login? Run this query:


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 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.

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

I learned this efficient method from @ShlomiNoach.

Give it a Try !!!

share|improve this answer

After you enter as the root user check your privileges:

 mysql> show grants for 'root'@'localhost';

After checking your privileges you can try to give another user all the privileges, or you can try to give the root user all privileges again:

 mysql> grant all privileges on *.* to 'root'@'localhost';

If your root user doesn't have privileges you can try to restore them, so:

Stop the mysqld server

Restart the server this way mysqld_safe --skip-grant-table

Restore root privileges with:

 mysql> flush privileges;
 mysql> grant all privileges on *.* to 'root'@'localhost' with grant option;
share|improve this answer
This is what i get after the show grants for 'root'@'localhost' command : ERROR 1141 (42000): There is no such grant defined for user 'root' on host 'localhost' –  jules testard Dec 19 '12 at 13:32
What's the output of the second command? –  Atropo Dec 19 '12 at 13:43

Your privileges may be be reduced?

Also, you can try to create another account with root privileges.

And delete root user, and re-create with specified privileges.

This may be be helpful:

C. - LINUX (Make file with query who create, a new user with full root privileges)

C. - Windows (Make file with query who create, a new user with full root privilegles)

Query: (Create root user)

  CREATE USER 'new_root'@'%' IDENTIFIED BY '***';

MySQL Manual Create User:

share|improve this answer
Yes but if I don't have accesss to root, how do I grant privileges? –  jules testard Dec 19 '12 at 9:27
@jules - Look -> Link in my anserw –  Jan Czarny Dec 19 '12 at 9:50

I know what you did.

Do this:

SELECT `User`, `Grant_priv` FROM `mysql`.`user` WHERE `User` = 'root';

You will probably notice it returns a 'N' for Grant_priv. So do this:

UPDATE `mysql`.`user` SET `Grant_priv` = 'Y' WHERE `User` = 'root';
SELECT `User`, `Grant_priv` FROM `mysql`.`user`;

And walla! Hope that helps.

share|improve this answer
also, for security reasons, you should always delete the root user after creating a new dba.. just make sure they are GRANT ALL PRIVILEGES then at the end WITH GRANT OPTION.. that bit is important. –  Kevin Florida Mar 29 '14 at 6:51

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.