Restoring Accidently Deleted Root User in MySQL

If you’ve ever been in the situation where you’ve accidentally deleted your root user in MySQL, you’ve probably noticed that GRANTS does not work when MySQL is started with skip-grant-tables. The way to get around this is to manually insert a record into the mysql.user’s table.

First off, start MySQL with –skip-grant-tables by editing /etc/my.cnf

[mysqld]
skip_grant_tables

From there on, reboot MySQL with /etc/init.d/mysqld restart.

Then get into the MySQL CLI:

[root@test ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.0.77 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Then enter the following query (replace NEW_PASSWORD_HERE):

INSERT INTO `mysql`.`user`(`Host`, `User`, `Password`, `Select_priv`, `Insert_priv`, `Update_priv`, `Delete_priv`, `Create_priv`, `Drop_priv`, `Reload_priv`, `Shutdown_priv`, `Process_priv`, `File_priv`, `Grant_priv`, `References_priv`, `Index_priv`, `Alter_priv`, `Show_db_priv`, `Super_priv`, `Create_tmp_table_priv`, `Lock_tables_priv`, `Execute_priv`, `Repl_slave_priv`, `Repl_client_priv`, `Create_view_priv`, `Show_view_priv`, `Create_routine_priv`, `Alter_routine_priv`, `Create_user_priv`, `ssl_type`, `ssl_cipher`, `x509_issuer`, `x509_subject`, `max_questions`, `max_updates`, `max_connections`, `max_user_connections`) VALUES ('%', 'root', PASSWORD('NEW_PASSWORD_HERE'), 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', '', '', '', '', '0', '0', '0', '0');

If all went well, you should see the following after you run SELECT * FROM mysql.user WHERE User = ‘root’\G;

mysql> SELECT * from mysql.user\G;
 Host: %
 User: root
 Password: 5d95c890861e8dm65
 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
 ssl_type:
 ssl_cipher:
 x509_issuer:
 x509_subject:
 max_questions: 0
 max_updates: 0
 max_connections: 0
 max_user_connections: 0

Now you can go ahead and edit /etc/my.cnf again to remove skip_grant_tables, reboot MySQL and you should be able to login to MySQL with your newly created root account.

Post a Comment

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Blog is powered by WordPress and designed by Chico Web Design