Resetting the root password/privileges for MySQL/PHPMyAdmin (XAMPP 1.7.3, MySQL 5.1.41)

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/

16 Replies to “Resetting the root password/privileges for MySQL/PHPMyAdmin (XAMPP 1.7.3, MySQL 5.1.41)”

  1. I just started using XAMPP for windows. When I start PhpMyAdmin it asks me for a username and password that I don’t believe I ever set up.

    How do you get around this? I just got this and I’m new, so sorry for the stupid question.

    Thanks,

    Alex

    1. Hi Alex,

      I would try logging in using the username ‘root’ and no password. I believe this is the default setup. If this doesn’t work, you should be able to reset the root password (to nothing) using the steps in this post.

      If you go to http://localhost/security/, you should see the security settings and whether the root user has a password set.

      I’m no expert myself, so can’t guarantee I’m suggesting the right things, but hope it helps.

      Jon

  2. Hi Jon,
    I installed LAMPP for linux. Same problem is coming “asks me for a username and password that I didn’t set”.
    And i tried logging in using the username ‘root’ and no password still I am unable to logging in. It throws following error :-
    #2002 Cannot log in to the MySQL server
    Connection for controluser as defined in your configuration failed.
    If any one can help me !!!!!!!

    1. Hi, I’m afraid my knowledge of this sort of thing is basically limited to what’s in the post – I’m definitely no expert. My only advice would be to try various combinations of ‘root’, ‘admin’ and nothing as the username/password. If nothing works, head for Google! Sorry I can’t be more help.

  3. Hi,
    I was playing with users for a new database I created and changed the root pass. Immediately after that it stopped working. I tried everything you said. It still doesn’t work. When I change the dir it keeps aborting the resetting with this error: “too many arguments .”

    I don’t know what I’m doing wrong.

    I have the portable version but i changed my path accordingly to its location.

    Thak you in advance

    1. Hi,

      I’m afraid I’ve never come across an error like that, and I haven’t used the portable version of XAMPP, so I don’t think I can be of any help – sorry.

      I hope someone out there (or Google) can help/has helped!

      Jon

  4. hi,
    i tried doing the given procedure, but it seems nothing has worked out for me,
    when i login in phpmyadmin using user ‘root’ and no password
    it logs in but have no priveleges to create database
    under users tab its showing ‘root’ has still has password.

Leave a Reply

Your email address will not be published. Required fields are marked *


The reCAPTCHA verification period has expired. Please reload the page.