Lost All Privileges in MYSQL

Question

I have seen this same question in many forms on this site and in many other forums. However, I have tried all the solutions and still have the same outcome. I'm pretty sure I once had root access on this MySQL server in the past and I was even able to use the phpMyAdmin tool to create databases amongst others queries. Now when I go to phpMyAdmin and click on databases I am greeted by:

If I use the command tool I can login using:

mysql -u root -p

I get prompted to enter a password and I do so. When I try to do anything else, say:

mysql> SELECT user, host FROM mysql.user;

I get the following:
ERROR 1142 (42000): SELECT command denied to user 'root'@'localhost' for table 'user'

I have also followed this article ( http://benrobb.com/2007/01/15/howto-remote-root-access-to-mysql/ ) since yes I am ssh'ing to this MySQL server from a remote machine. Again, when I attempt:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;

In various forms, I am greeted by a nasty:

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

Even though I am putting the same password I used to login! I also want to mention that I updated my pear resources yesterday and I wonder if this was the cause of this mess...

If anyone can guide me, I'd truly appreciate it. Thank you for all your time and consideration.

Elshae

****Update**** In trying to troubleshoot and running mysqld I now cannot start the server back up! Every time I run:

/etc/init.d/mysql start
Starting MySQL database server: mysqld . . . . . . . . . . . . . . failed!

What should I do?

Solution 1

I appreciate everyone's feedback, I finally got it for the most part. To combat the issue of

/etc/init.d/mysql start
Starting MySQL database server: mysqld . . . . . . . . . . . . . . failed!

I did:

ps aux | grep mysql

Which gave me:

root     15265  0.0  0.1   3896  1284 pts/10   S    12:41   0:00 /bin/sh /usr/bin/mysqld_safe
mysql    15302  1.6  2.8 132532 29600 pts/10   Sl   12:41   0:00 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --port=3306 --socket=/var/run/mysqld/mysqld.sock
root     15303  0.0  0.0   3008   608 pts/10   S    12:41   0:00 logger -p daemon.err -t mysqld_safe -i -t mysqld
root     16160  0.0  0.0   3120   708 pts/10   R+   12:42   0:00 grep mysql

then I did:

kill -9 15265
kill -9 15302 

After that I was able to start up MySQL with:

/etc/init.d/mysql start

From there I found this article: https://help.ubuntu.com/community/MysqlPasswordReset and the magic for me there was:

/usr/sbin/mysqld --skip-grant-tables --skip-networking &

I was able to follow the thread and also did:

GRANT ALL PRIVILEGES ON *.* TO root@localhost IDENTIFIED BY 'yournewrootpassword' WITH GRANT OPTION;

The only thing that's a bit strange is that the root still does not have the right to create databases from phpMyAdmin amongst other privileges that the root needs. However, I am able to create databases from the command line and for now I can live with that!

Thanks again everyone for your time and all the help!

Solution 2

Stop your MySQL service (UNIX style):

/etc/init.d/mysqld stop 

Start your MySQL with privileges:

/usr/bin/mysqld_safe  --skip-grant-tables

Connect to mysql DB with your root user

/usr/bin/mysql -uroot -p mysql

Update root privileges

update mysql.user
set 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' 
where User='root';

After that, you just need to stop the MySQL service and start again.

Solution 3

Command GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION; will ONLY work if you are logged in as a user who have all rights...

As you don't have rights to GRANT, in your case above statement won't work... Please contact administrator and get ACCESS to connect...

Comments

[elshae]

Thank you for your answer, but I get: mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: NO)'