Restoring Accidently Deleted Root User in MySQL

If you, like me, have accidentally deleted your root user in MySQL, you will need to add the user manually.

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>

You will probably notice that GRANTS do not work when MySQL is started with skip-grant-tables. The way to get around this is to manually insert a record into the user table.

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’;

mysql> SELECT * FROM mysql.USER;
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.