elshae elshae - 2 months ago 14
MySQL Question

Lost All Privileges in MYSQL

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:

enter image description here

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?

Answer

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!

Comments