[MySQL] Access denied for user ''@localhost

I had a MySQL database on CentOS 5 with user root and no password. I still don't know why, but he no longer had ALL rights on ANY schema, effectively rendering him powerless; plus after logging in:

mysql -h localhost -u root;

a simple

SELECT current_user(); 

would show '@localhost' and

show databases;

would show only show the information_schema and test schema.

There was no way I could get back my rights, even starting mysql with the --skip-grant-tables option held no positive results and the FAQ on MySQL's site did not cover my case. I kept getting the error: ERROR 1044 (42000): Access denied for user ''@'localhost' to database XXX

Additionally I could not select any database:

use XYZ;

Since I was told I did not have the rights on it. I couldn't even set or change the password neither from shell with:

mysqladmin -u root password 'NEW_PASSWORD'

to set or

mysqladmin -u root -p'OLD_PASSWORD' password 'NEW_PASSWORD'

to change, nor from the mysql prompt:

mysql -u root -p mysql



then:

UPDATE user SET password=PASSWORD('NEW_PASSWORD') where User='root';
FLUSH PRIVILEGES;

Luckily, one of our sysadmins had this magical script which saved my day:

 update mysql.user set Super_priv='y' where user='root';  
 update mysql.user set Select_priv='y' where user='root';  
 update mysql.user set Insert_priv='y' where user='root';  
 update mysql.user set Update_priv='y' where user='root';  
 update mysql.user set Delete_priv='y' where user='root';  
 update mysql.user set Create_priv='y' where user='root';  
 update mysql.user set Drop_priv='y' where user='root';  
 update mysql.user set Reload_priv='y' where user='root';  
 update mysql.user set Shutdown_priv='y' where user='root';  
 update mysql.user set Process_priv='y' where user='root';  
 update mysql.user set File_priv='y' where user='root';  
 update mysql.user set Grant_priv='y' where user='root';  
 update mysql.user set References_priv='y' where user='root';  
 update mysql.user set Index_priv='y' where user='root';  
 update mysql.user set Alter_priv='y' where user='root';  
 update mysql.user set Show_db_priv='y' where user='root';  
 update mysql.user set Super_priv='y' where user='root';  
 update mysql.user set Create_tmp_table_priv='y' where user='root';  
 update mysql.user set Lock_tables_priv='y' where user='root';  
 update mysql.user set Execute_priv='y' where user='root';  
 update mysql.user set Repl_slave_priv='y' where user='root';  
 update mysql.user set Repl_client_priv='y' where user='root';  
 update mysql.user set Create_view_priv='y' where user='root';  
 update mysql.user set Show_view_priv='y' where user='root';  
 update mysql.user set Create_routine_priv='y' where user='root';  
 update mysql.user set Alter_routine_priv='y' where user='root';  
 update mysql.user set Create_user_priv='y' where user='root';  

It restores ALL privileges to the root user - yay!

Now, to execute it, launch the following commands (you may need to be root):

sudo su

or whatever you have on you machine.

Paste the script it in some file we'll call mysql-restore.sql. Then stop your mysql:

/etc/init.d/mysqld stop

or the equivalent command for your setup, and start mysql in safe mode, ignoring the grant table:

mysqld_safe --skip-grant-tables &

Then run (I prefer to do it from another console):

mysql -vv < ./mysql-restore.sql

Obviously assuming your working directory contains the file, else either navigate to it before running the command or write the full path instead of simply ./

If everything goes right, you should see some output telling you that X rows have been successfully updated.

Now, I also made sure to delete from the mysql.user table all the lines which had Host=' ' and User='root' or Host='SOMETHING' and User=' ' but you may not need to do that - you can break root access from remote locations to the DB!

Finally, shutdown mysql (safe) with:

mysqladmin shutdown

And restart it:

/etc/init.d/mysqld start

Try and login again and you should be good.