elshae elshae - 1 year ago 91
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:


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.


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 Source

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!

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download