How to recreate root account in MySQL

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 [email protected]’ 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:

mysql -u root
mysql> grant all privileges on *.* to 'root'@'localhost' with grant option;
Query OK, 1 rows affected (0.03 sec)</pre>

And thats it, your basic root acount is working again. Don’t forget to change the password or add the hostname to it, but that depends on your needs.