mySQL ERROR 1045 (28000): Access denied for user 'root'@'localhost

[BlackWhiteAndRed]

I can't get mySQL to run even though I have the correct password.


I was having problems directing a localhost and I tried fixing things in myphpadmin. I deleted my root directory/privileges in phpmyadmin and then things took a turn for the bad. mySQL could no longer run and I have no idea how to fix it.


I've tried reinstalling phpmyadmin and everything related to mySQL and still do not have any luck getting it to run. I know mySQL is running because when I type in:
ps -ef | grep mysql
I get
mysql 1149 1 0 20:23 ? 00:00:00 /usr/sbin/mysqld
jeremy 2314 2283 0 20:34 pts/0 00:00:00 grep mysql
When I type in: "mysql -u root -p" and enter the password I get reset I get the message:

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
I've tried using an init file and loading it from there, resetting passwords and using dkpg to reset things but still have no luck.


If I type the command:
mysqld --skip-grant-tables


I get
110831 20:57:24 [Warning] Can't create test file /var/lib/mysql/jeremy-laptop.lower-test
110831 20:57:24 [Warning] Can't create test file /var/lib/mysql/jeremy-laptop.lower-test
110831 20:57:24 [Warning] One can only use the --user switch if running as root



Does anyone ever had this happen to them or know how to fix it?

[Wim Sturkenboom]

Run your last command using sudo after stopping the mysql service

sudo service mysql stop
sudo mysqld --skip-grant-tables

The latter wiil, in my experience, not return to a command prompt

In another terminal, start the mysql client and select the mysql database. If the root user entries are still there, just set a password using update user set password=password('yourpassword') where user='root'.

If there are no root entries, use grant all privileges on *.* to 'root'@'localhost' identified by 'yourpassword' with grant option.

Next exit the client, find the pid of the running mysql daemon and kill it.

Start the mysql daemon using sudo service mysql start and try again to get in.

PS you can ignore warnings about lower-test and higher-test.

[BlackWhiteAndRed]

I tried what you suggested and get the following error:

ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

I assume the "--skip-grant-table" creates a read-only of the database and when I try to write something it screams at me.

Any other suggestions?

[Wim Sturkenboom]

Sorry, I should have checked

OK, I hope you have not done more damage by also deleting the debian-sys-maint user in mysql

Have your mysql daemon running the normal way. Start your mysql client as shown below

mysql -u debian-sys-maint -p

In another terminal, 'cat' the file /etc/mysql/debian.cnf. That file contains a password; paste that password when prompted for it.

The debian-sys-maint user has full privileges so you should be able to run the earlier mentioned grant statement. Don't forget to flush the privileges before quiting the client.

Good luck.

PS
All honours go to speedman who posted this in http://ubuntuforums.org/showthread.php?t=27156&page=2 (post #12)

[BlackWhiteAndRed]

Worked! Thanks so much for helping me out and linking me to the right thread.

[Wim Sturkenboom]

Great. Please mark your thread as solved using the thread tools just above the first post on this page.

[ian.d.rossi]

Thank you so much Wim and speedman. I experienced this same problem while installing mysql-server and related packages as well as phpmyadmin. I think this is a bug.

I was able to log into mysql through the CLI and phpmyadmin and then I started getting 'root'@'localhost' access denied errors. A full purge/removal of mysql didn't work.

Thankfully, this post did the trick for me and saved the rest of my hair.

Ian Rossi

[samyakd]

After I do all this and i type "SET PASSWORD FOR root=PASSWORD('')" i get this:

ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement


Please help. Thanks.

Sorry, I should have checked

OK, I hope you have not done more damage by also deleting the debian-sys-maint user in mysql

Have your mysql daemon running the normal way. Start your mysql client as shown below

mysql -u debian-sys-maint -p

In another terminal, 'cat' the file /etc/mysql/debian.cnf. That file contains a password; paste that password when prompted for it.

The debian-sys-maint user has full privileges so you should be able to run the earlier mentioned grant statement. Don't forget to flush the privileges before quiting the client.

Good luck.

PS
All honours go to speedman who posted this in http://ubuntuforums.org/showthread.php?t=27156&page=2 (post #12)

[Wim Sturkenboom]

After I do all this and i type "SET PASSWORD FOR root=PASSWORD('')" i get this:

ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement


Please help. Thanks.

Run the mysql daemon the normal way (so not using skip-grant-tables).

Use the debian-sys-maint user as described to login, change to the mysql database and issue the correct command to set the root password.

[jpvasconcelos]

Thx Win... you save us ofc! ;D

I was some days searching for a solution and found it here! very thanks!