Access denied for user 'root'@'%'

  • 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:       [email protected]
    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?

  • 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 [email protected]=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 !!!

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

    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.

    This, combined with closing, and re-opening the connection (in my case from HeidiSQL) did the trick!

  • 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;

    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'

    What's the output of the second command?

    There are a "better" way to do this in Debian, where you don't need to restart the server. Just as `root` connect as `[email protected]` 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.

    Try this. sudo mysql --defaults-file=/etc/mysql/debian.cnf

  • 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:

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

License under CC-BY-SA with attribution

Content dated before 6/26/2020 9:53 AM