Recover MySQL Root Password in XAMPP

MySQL

I posted this on StackOverflow and was told to come here.

I have an XAMPP MySQL installation and want to recover the root password. I am aware that questions have asked this already, however they are Linux methods of doing it (I'm on Windows) and I can't seem to figure out what exactly the Linux commands translate to in XAMPP – I tried looking for a place to configure the arguments to not use network etc but couldn't. This is a localhost installation.

Best Answer

This resetroot.bat method did not work for me, even after following the instructions above.

My XAMPP uses this DB version:

Server: MariaDB
Server version: 10.0.17-MariaDB mariadb.org binary distribution

However, using this answer, I was able to do it manually.

Go to your **xampp\mysql\bin** folder
Edit my.ini and insert skip-grant-tables below [mysqld]
Restart MySQL
Set new password for your root user by running UPDATE mysql.user SET Password=PASSWORD('new_password') WHERE User='root' in phpMyAdmin in the mysql database (or just leave it like this if MySQL cannot be accessed from remote hosts)
Remove the skip-grant-tables in the my.ini file

Related Solutions

MySQL – How to Reset Root Password in LAMPP Server on Ubuntu

The skip-grant-tables solution is not a recommended one, for a couple of reasons:

It makes the database vulnerable (even with skip-networking)
It requires taking your database down twice.

A solution which requires taking the database down just once is as follows:

Create a temporary SQL text file, say /tmp/init.sql
Within this file, write:

SET PASSWORD FOR root@localhost = PASSWORD('the_new_password');
Add the following to your MySQL config file (on Ubuntu this is on /etc/mysql/my.cnf), under the [mysqld] section:

init-file=/tmp/init.sql
Restart MySQL once. The init file is read and executed upon startup. The password is reset.
Proceed to remove the init-file=/tmp/init.sql entry from my.cnf (do not forget this). Even as the server is up and running.
Remove the /tmp/init.sql file.

There are even more solutions! Please refer to a past blog post of mine. Make sure to check out comment #4 by strcmp

Mysql – reset thesql root password on ubuntu

Here is a more secure way to reset password without skip-grant-tables

Suppose you want root to have myn3wp@ssw0rd as the password

Step 01 : Create a script to execute when mysqld first start up

Create a file called /var/lib/mysql/init-file.sql with these two lines

GRANT ALL PRIVILEGES ON *.* to root@localhost
IDENTIFIED BY 'myn3wp@ssw0rd' WITH GRANT OPTION;

Step 02 : Run these three(3) lines in the OS

chown mysql:mysql /var/lib/mysql/init-file.sql
service mysql restart --init-file=/var/lib/mysql/init-file.sql
rm -f /var/lib/mysql/init-file.sql

Step 03 : THERE IS NO STEP 03. YOU ARE DONE !!!

Give it a Try !!!

CAVEAT

I used to recommend two restarts of mysql that required editing my.cnf
I learned a better approach from @ShlomiNoach
This answer improves on it more by not editing my.cnf at all

UPDATE 2013-06-17 07:05 EDT

OK Since mysqld for Ubuntu does not like init-file on the command, you must edit the my.cnf. Please do these next two steps

STEP 01) Add these lines to my.cnf under the [mysqld] group header

[mysqld]
init-file=/var/lib/mysql/init-file.sql

STEP 02) Allow mysql to read files from /etc/mysql

sudo vim /etc/apparmor.d/usr.sbin.mysqld

Add the line:

/etc/mysql/*.sql r,

STEP 03) Restart MySQL

This should work for you. Give it a Try !!!