Take the 2-minute tour ×
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 stackoverflow.com 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 serverfault.com –  jishi Dec 19 '12 at 13:55
add comment

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

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
add comment

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> 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
add comment

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: http://dev.mysql.com/doc/refman/5.5/en/resetting-permissions.html

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

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

Query: (Create root user)

  CREATE USER 'new_root'@'%' IDENTIFIED BY '***';
  GRANT ALL PRIVILEGES ON *.* TO 'new_root'@'%' IDENTIFIED BY '***' WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;

MySQL Manual Create User: http://dev.mysql.com/doc/refman/5.5/en/adding-users.html

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
add comment

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';
FLUSH PRIVILEGES;
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 at 6:51
add comment

Your Answer

 
discard

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