Accidentally deleted mysql localhost, ROOT

[runedog48]

I messed up big time and deleted that account... damn... don't have any access. I think I might of had deleted my other one but i'm not sure. It's saying access denied

Code: Select all

Error
MySQL said:

#1045 - Access denied for user 'root'@'localhost' (using password: YES)

phpMyAdmin tried to connect to the MySQL server, and the server rejected the connection. You should check the host, username and password in your configuration and make sure that they correspond to the information given by the administrator of the MySQL server.


If I did delete my other account what is the file that I need to edit to change my password?

[hackattack142]

I have never tried it myself but there is a resetroot.bat file in the xampp/mysql folder that might work.

Another possible but less desirable option is if you do not have any other users you want to save would be to overwrite the user tables. If you have the zip file corresponding to your XAMPP version, you could take the "user.frm", "user.myd", and "user.myi" files from xampp/mysql/data/mysql and replace the ones in your xampp folder in the same location.

[Sharley]

To use the C:\xampp\mysql\resetroot.bat file you would need to edit the file to be able to use it in a Windows environment - currently written to use in a *nix environment and so will not do anything except produce an error message.

These posts have some of the details required to edit the resetroot.bat file that will eliminate the need to lose any of your current data:
viewtopic.php?p=181829#p181829
viewtopic.php?p=167128#p167128
viewtopic.php?p=130759#p130759
http://bravo.newnetenterprises.com/word ... -password/

There is also a detailed alternative method to be found on the MySQL Development site here:
http://dev.mysql.com/doc/refman/5.0/en/ ... sions.html

------------------------------------------

Of course if you deleted the super user root then your problems will not be cured by simply resetting the user root password, you would need to recreate the user root once again then give root a new password.

Here are some instructions to restore your lost root super user and to give it a new password.
First download, from the link in my signature, and extract the xamppcontrol3.exe file to your xampp installation folder, usually C:\xampp and use this new much improved Control Panel instead of the one included in the release.
The XCPv3 forum topic can be read here:
viewtopic.php?f=16&t=46743

1. Stop Apache and MySQL by using the new XAMPP Control Panel v3.

2. Open a plain text editor and add the following 3 lines (nothing else) to the very top of a new page:

Code: Select all

INSERT INTO mysql.user (Host, User, Password) VALUES('localhost', 'root', PASSWORD('newpass'));

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION;

FLUSH TABLES;

Exactly as written in the code section above, you may copy and paste if you prefer but you must change newpass to the password of your choice.

3. Save the file into the C:\xampp\mysql folder as undoroot.sql.
Change the folder location to where you have your own XAMPP installed if different from the default.

4. Go to the XCPv3 and click on the xampp-Shell button.

5. At the Shell # prompt type

Code: Select all

mysqld --init-file="C:\xampp\mysql\undoroot.sql"

Click on Enter which should then start the MySQL server using the init. file and the instruction it contains.
Change the folder location to where you have your own XAMPP installed.

6. Next go to the XCPv3 where you should see that MySQL has started - click on the Stop button so that MySQL is no longer running.

7. Now you can Start Apache and MySQL, again using the XCPv3 and then click on the MySQL Admin button where, if all goes to plan, you should be asked for the username/password combination of root and the new password you set in the init file undoroot.sql above.

You should now have access to all your databases that you had previous to accidentally deleting the root user.

Good luck and again my best wishes.

[ma^1c]

Thank. This procedure helped restore my super user root account. However I did not bother with the updated control panel. As a result, the procedure wiped my databases.