Today I was trying to log in to mysql database administration on one of my hosting machines and as I was getting ‘permission denied for user rott@localhost’ I tried to fix the root account and reset its password. But I wasn’t successful and later on I realized, that the root account was deleted from the mysql database by one of my friends accidentally. I started to search the internet what to do, but the only thing I have found regarding this issue was question on MySQL forum with exactly the same problem but no answer. So as usual, I tried to solve it by myself and here is it is.

The pre requisition is a shell access on your MySQL machine. Then you have to stop the standard mysql daemon and start the database in safe mode.

/etc/init.d/mysql stop
mysqld_safe –skip-grant-tables &

Then log in as a root and switch to mysql system database.

mysql -u root
mysql> use mysql;

Try to check that the root user is not present in user table:

mysql> select * from user where User=’root’;

If the database return empty record, lets manually insert the root user with empty password and then set all the permissions which he normally needs:

mysql> insert into user (Host, User, Password) values (‘localhost’,’root’, »);
Query OK, 1 rows affected (0.04 sec)
mysql> update 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’ where user=’root’;
Query OK, 1 rows affected (0.03 sec)

Then quit the database console, kill the mysqld_safe daemon and start the standard mysql daemon again:

mysql> quit
killall mysqld_safe
/etc/init.d/mysql start

Try to log in into mysql console again with an empty password and for double check, try to run ‘grant’ command to see that the account is fully working: