//
you're reading...
Featured, MySQL

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.

Discussion

14 thoughts on “How to recreate root account in MySQL

  1. Thank you for writing everything plain and clear.
    Cheers!

    Posted by Alex | February 12, 2010, 4:48 pm
  2. thanks dude amazing post

    Posted by sunil | September 5, 2012, 7:42 pm
  3. Thanks!!!!!!!!

    Posted by Betty B Thomas | November 25, 2012, 9:24 pm
  4. It works, thank you very much!!

    Posted by Peter | January 21, 2013, 3:55 am
  5. I am getting

    “The MySQL server is running with the –skip-grant-tables option so it cannot execute this statement”
    Exception

    what to do?

    If try enabling ‘grant-table’ login not permitting.

    Please Reply

    Posted by raju | September 2, 2013, 12:32 pm
  6. It’s simply awesome…

    Posted by sunil | September 19, 2013, 12:26 pm
  7. Works perfect, you saved my life

    Posted by Vladimir | November 20, 2013, 10:37 pm
  8. Thanks =)

    Posted by njalk | June 17, 2014, 3:19 pm
  9. I had to run:

    FLUSH PRIVILEGES;

    after: update user…

    Posted by Georgi Yanchev | July 21, 2014, 2:54 pm
  10. thanks a lot.. you rock!

    Posted by Mehmet | August 11, 2014, 5:39 am
  11. This post is pretty good to read. Great!!
    Thank you very much for sharing, can I post it on my blog to share with my friends?

    Posted by Jack Lee | August 17, 2014, 12:49 pm

Trackbacks/Pingbacks

  1. Pingback: mysql fresh install asking for root password - November 23, 2010

  2. Pingback: Connexeon Blog » Recreating deleted mySQL root user - March 19, 2013

  3. Pingback: mysql fresh install asking for root password - Just just easy answers - September 7, 2013

Leave a comment

Your email address will not be published. Required fields are marked *