Mysql – Deleted MySQL “root” user (using phpMyAdmin)…how to restore

MySQLphpmyadminusers

I'm test-running a Joomla installation (locally, using WAMP) with an MySQL database supporting it…this is in preparation to moving everything online once I have it configured.

I was working in phpMyAdmin to change the password of a superuser I had set up with a login-name other than "root." While doing so, I saw three additional "root" users, and on the phpMyAdmin home page, a warning about these root users not having a password. I thought that since I created my own superuser with "All Privileges," I didn't need the root users…so I deleted them. Immediately things stopped working…and now, when I click on the "Users" tab in phpMyAdmin, it simply returns a red bar that says "No Privileges"…my specific superuser is gone. However, it seems I can still move around in phpMyAdmin (maybe because I'm still logged in?).

Could someone please tell me step-by-step how (if?) I can re-add the root users using phpMyAdmin? I see instructions posted in various places, but they seem to address users who are using terminal windows or some other program. I'd hate to see months of work go down the drain!

EDIT: Using Windows, I've tried the following:

There is no file called "resetroot.bat" anywhere in the wamp/sql folders, as someone suggested…so that option is out.
I've tried to access MySQL using a terminal connection: in the MySQL root folder there is a file called mysql.exe that seems to grant this when executed (there is no "mysql_safe.exe" as some solutions have suggested). However, I have been unsuccessful in starting mysql with the –skip-grant-tables option from outside mysql.exe, and when I try to do it from within mysql.exe, I just get an odd prompt ("->") and no other commands seem to work.

Please keep in mind that I am a newbie at this! I am trying different things, but without any success…that is why I'll probably need step-by-step instructions.

Best Answer

OK, I finally found a solution. To give proper credit, I had to blend answers provided by two people: RiggsFolly's answer to this question on resetting the root password in wamp, which is what I'm using, and jm666's answer for this question on restoring a deleted root user in MySQL using MAMP on OS X.

For Wamp Users:

Click on the Wamp system tray icon to bring up the tray menu. Click MySQL > Service > Stop Service.
Edit your MySQL's my.ini file...that's your configuration file for your SQL database, and it's generally located in the root folder for your MySQL database (try looking in wamp > bin > mysql > mysql5.5 [or whatever version]). Use a text editor, and scroll down in the my.ini file to [wampmysqld]. Immediately after this entry, insert a new line containing the following command:

skip-grant-tables
This tells the MySQL database that you want to override any restrictions such as root user or password parameters. This is a major security hole, so once we are done with our repairs, we'll need to undo this! Save the my.ini file.
Resume MySQL services in Wamp (click MySQL > Service > Start/Resume Service).
Now open the MySQL console: click MySQL > MySQL Console

A "DOS-like" terminal window will open up (if it asks you for a password, simply press enter). You should see a MySQL prompt. Copy the following block of code and paste it at the prompt.

INSERT INTO mysql.user
SET user = 'root', 
host = 'localhost', 
password = '', 
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',
Event_priv = 'y',
Trigger_priv = 'y',
Create_tablespace_priv = 'y';

Press enter. You should get some sort of message saying that one line (of database code) was affected, with some warnings (probably because you are messing with the root user...I think you can safely ignore these). Close the MySQL terminal.
Remember that security hole we opened in step two? Time to fix that. Reopen your my.ini SQL configuration file, and remove the skip-grant-tables line you added. Save the my.ini file and close.
RESTART your MySQL Service (same as before, but click on MySQL > Service > Restart Services instead).

THAT should do the trick!

A note on the root user in MySQL for newbies: for some strange reason, even if you create another user as a superuser, MySQL seems to need this user to operate...however, if you don't set up a password for root, it notes this as a security hole. Whatever you do, don't delete it: you're better off just setting some wildly obscure password for it and leaving it alone if you want to use another superuser account. It would seem that there should be more protections to help prevent unwary users from doing this (a warning, or something)....that's my twelve cents on the issue.

Related Solutions

Mysql – Is this a normal set of MySQL privileges

In reality, those three user accounts are actually quite dangerous. They pose a very great threat to test databases.

Unfortunately, mysql comes with full access to test databases. How can you find them ?

Run this query:

 SELECT user,host,db from mysql.db;

Upon installation of mysql, you will see two rows that give full access to any database named 'test' or whose first 5 characters are 'test_'.

Why is this a problem ???

Try running this command:

$ mysql -u'' -Dtest

You will have connected the test database without a password.

Now, create a table and load it with a row:

CREATE TABLE mytable (a int);
INSERT INTO mytable VALUES (1);

OK, big deal. Could you double this table in size 30 times ???

INSERT INTO mytable SELECT * FROM mytable;
INSERT INTO mytable SELECT * FROM mytable;
... (30 times)
INSERT INTO mytable SELECT * FROM mytable;

What do you get ?? A table with 1,073,741,824 rows. Easily, 4GB+.

Imagine creating any table of any size. How about creating a bunch of tables in the test database and freely accessing them at will ?

The best thing you can do under these circumstances is to run this query:

DELETE FROM mysql.db;

and restart mysql. Then, those three accounts will be properly rendered inoperative.

Give it a Try !!!

UPDATE 2011-09-12 10:00 EDT

This delete:

DELETE FROM mysql.db;
FLUSH PRIVILEGES;

is just what you need for an initial installation. However, if you have users already established, You can run this instead:

DELETE FROM mysql.db WHERE db IN ('test','test_%');
FLUSH PRIVILEGES;

This will remove the specific two DB permissions.

As I mentioned in my answer, the three permissions are very dangerous for test databases. Running this DELETE will neutralize those accounts for having full rights to test databases.

Mysql – Why can’t this thesql user log in with password

Because you granted it access without password... If a user has no password, logging in with one will always fail.

Set the password for user, only then you'll be able to use -p

In addition, you might want to remove the Any user.

Best Answer

Related Solutions

Mysql – Is this a normal set of MySQL privileges

Mysql – Why can’t this thesql user log in with password

Related Question