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 root@localhost 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:

1. service mysqld stop
2. mysqld_safe --skip-grant-tables &
3. mysql
4. use mysql
5. create user root@localhost;
6. GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' with grant option;
7. commit;
8. FLUSH PRIVILEGES;
9. exit
10. 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.

Tags: mysql

This entry was posted on Saturday, July 2nd, 2011 at 7:54 am and is filed under Linux. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.