1

I have a mysql server which i'm able to log into using root password (no password) but for some reason I'm unable to grant other users access to mysql. When I run:

mysql> grant all on *.* to 'lel'@'%' identified by '123456';

I get this error:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

I've tried doing the next steps in order to fix root user:

/etc/init.d/mysqld stop
mysqld_safe --skip-grant-tables &
mysql -u root
mysql> use mysql;
mysql> update user set password=PASSWORD("newrootpassword") where User='root';
mysql> flush privileges;
mysql> quit
/etc/init.d/mysqld stop
/etc/init.d/mysqld start

When I check root's user in mysql, I can see it has all the relevant GRANT privileges:

mysql> select * from mysql.user where User like 'root'\G;
*************************** 1. row ***************************
                 Host: localhost
                 User: root
             Password: 
          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
*************************** 2. row ***************************
                 Host: centolel
                 User: root
             Password: 
          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
*************************** 3. row ***************************
                 Host: 127.0.0.1
                 User: root
             Password: 
          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
3 rows in set (0.00 sec)

show grants for root; gives me:

mysql> show grants for root;
ERROR 1141 (42000): There is no such grant defined for user 'root' on host '%'

Edit #1:

[root@home mysql]# mysql -h localhost -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.5.36 MySQL Community Server (GPL) by Remi

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> grant all privileges on *.* to 'itaig'@'localhost' with grant option;

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

Thanks in advance

  • You haven't told which host to update: localhost or centotel... so it doesn't know which "root" user to log in as – Kinnectus Jul 1 '14 at 13:05
  • How should I have done it please? – Itai Ganot Jul 1 '14 at 13:17
  • Try logging in to MySQL using the -h argument and supplying the host where centotel is hosted (an IP or hostname) e.g. mysql -h 192.168.0.1 -u root -p – Kinnectus Jul 1 '14 at 13:33
  • @BigChris: Please check Edit #1, thanks. – Itai Ganot Jul 1 '14 at 13:42
1

I've done everything I know to fix this issue without re-installing mysql but to no avail... I believe a file got corrupted or something, otherwise I can't imagine why I bumped into these errors. I've saved mysql data on some external disk, re-installed mysql, copied back the data and now it's working like a charm. Thanks for your help.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.