We use XAMPP for our local development (on Windows), and I have a habit of messing around with the database and doing bad things by mistake, because it’s only for local development, so it doesn’t matter that much. It’s still annoying when I lock myself out of all my databases though!
While setting up a wordpress site locally for testing, I managed to remove the global privileges of the root user in PHPMyAdmin, thinking it was the user for the wordpress database. I did the usual thing of searching for solutions online, and after trying a few different things involving the command line and MySQL Workbench, I came across this Apache Friends forum thread: http://www.apachefriends.org/f/viewtopic.php?f=16&t=46832. The instructions boil down to the following:
- Open mysql/resetroot.bat in a text editor
- Add “–datadir=c:/xampp/mysql/data” into the “mysql\bin\mysqld.exe…” line (line 18), after “–no-defaults”. Change the datadir parameter to the path to your data directory – the one shown is the default.
- Run resetroot.bat – this should reset the MySQL root password and privileges. You can then login to MySQL/PHPMyAdmin using the username root and no password. For security, you should set a password for the MySQL admin user in the XAMPP Security panel: http://localhost/security/