开发者

Root account wont work in MySQL

I am logged in as root in my MySQL database. I try to run the following commands:



mysql> GRANT ALL ON *.* TO 'root'@'%'
    ->   IDENTIFIED BY '[password]'
    ->   WITH GRANT OPTION;
ERROR 1045 (28000): Access denied for user 'root'@'%' (using password: YES)
mysql>


I get the following error:

ERROR 1045 (28000): Access denied for user 'root'@'%' (using password: YES)

Im assuming my root user isnt set up correctly. Any ideas?

Edit


mysql> show grants for root
    -> ;
+----开发者_如何学C-------------------------------------------------------------------------------------------------+
| Grants for root@%                                                                                   |
+-----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD '*738393F115A47ACE272D98F4719B8491A2B8296F' |
| GRANT ALL PRIVILEGES ON `sometable`.* TO 'root'@'%' WITH GRANT OPTION                               |
| GRANT ALL PRIVILEGES ON `Test`.* TO 'root'@'%' WITH GRANT OPTION                                    |
+-----------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)



You might be logged into the database with an account named root, but the account you are in as does not have the correct privileges.

mysql> select user();

And follow that up with a:

mysql> select * from mysql.user\G

Look for the user and host columns to match exactly as the output. Look for grant_priv and it needs to say Y. More than likely there is additional root accounts without the grant_priv.

mysql> select * from mysql.user\G
*************************** 1. row ***************************
                 Host: localhost
                 User: root
             Password: xxxxxxxxxxxxxxx
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
          Reload_priv: Y
        Shutdown_priv: Y
         Process_priv: Y
            File_priv: Y
           Grant_priv: Y
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
         Show_db_priv: Y
           Super_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
         Execute_priv: Y
      Repl_slave_priv: Y
     Repl_client_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: Y
     Create_user_priv: Y
           Event_priv: Y
         Trigger_priv: Y
             ssl_type: 
           ssl_cipher: 
          x509_issuer: 
         x509_subject: 
        max_questions: 0
          max_updates: 0
      max_connections: 0
 max_user_connections: 0


When I enter the select * from mysql.user\G command, I get : ERROR 1142 (42000): SELECT command denied to user 'root'@'localhost' for table 'user' ERROR: No query specified

This means you're not logged in as 'root'@'%'. You're logged in as 'root'@'localhost', which are two different users. If you run SELECT USER(); as mentioned above, it will likely show you 'root'@'localhost'.

If you are logged in as 'root'@'localhost', you are logging in via local socket. Logging in through a TCP connection will get you access as 'root'@'%', which will then give you access to change grants based on the output you listed initially. To do so, login with:

mysql -u $user -p $pass -h 127.0.0.1

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜