Deleted [email protected] account in MySQL

Written on July 2, 2011 by Kev

Like the fool I am, whilst messing about with user privileges in phpMyAdmin I managed to delete the root account. Suddenly I found myself with no access to any of my databases :(

It took a while to figure out how to re-create the [email protected] user, so here’s how I did it.

  1. Shut down mysql server
  2. Start the msql server up with skip-grant-tables option
  3. Log in to mysql
  4. Change to the mysql database
  5. Create the root user
  6. Re-start mysql server

For those that like a cut n paste approach, these are the commands:

service mysqld stop
mysqld_safe --skip-grant-tables &
mysql
use mysql
create user [email protected];
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' with grant option;
commit;
FLUSH PRIVILEGES;
exit
service mysqld restart

You can confirm that the root account has been created (or is indeed missing!) by listing entries on the user table:

use mysql;
select Host,User from user;

Good luck!
Kev.