29

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?

0
19

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

12

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.

2
  • 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. Mar 29 '14 at 6:51
  • This, combined with closing, and re-opening the connection (in my case from HeidiSQL) did the trick!
    – xorinzor
    Aug 2 '18 at 14:12
5

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;
4
  • 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
  • 2
    There are a "better" way to do this in Debian, where you don't need to restart the server. Just as root connect as debian-sys-maint@localhost and use the password in /etc/mysql/debian.cnf. That user have ALL privileges in MySQL. The good thing is you don't need to restart your server.
    – Anders
    Feb 11 '16 at 16:49
  • Try this. sudo mysql --defaults-file=/etc/mysql/debian.cnf
    – Anders
    Feb 11 '16 at 16:59
0

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

0
0

Check if you are running the import query against information_schema db, I fell for that for a while because I was using a new MysQL client and didn't realize that the query was trying to create tables in information_schema db (since the new db to be created didn't exist when running the query, the connection fell back to the first db in the server).

I tried every single answer here and took me nowhere as root access was used and privileges seemed right.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy