Recently a client got locked out of a
MySQL deployment managed by a
XAMPP. They requested us to reset the password for the
root account. To do so, we followed the following procedure:
Using a text editor, we opened the following file, which is the
MySQL configuration file:
We found the section for the
MySQL daemon, which starts after the line that contains the following tag:
Right below the tag, we added a new line and added the following directive:
Example of a configuration file:
[mysqld] skip-grant-tables port= 3306 socket = "C:/xampp/mysql/mysql.sock" basedir = "C:/xampp/mysql" tmpdir = "C:/xampp/tmp" ...
skip-grant-tables directive causes the server not to read the grant tables in the
MySQL system schema and, thus, start without using the privilege system. This option gives anyone unrestricted server access to all databases.
After saving the file, we restarted the
MySQL server from the
XAMPP interface. This action forced the new settings to be loaded and take effect.
Next, using the command line, we connected to the
MySQL server using the
CLI client for
In the above command, we issued the
mysql.exe application with only one parameter, the
--user=root, which instructs the client to ignore the default login username and use root instead.
Once we activated the
MySQL command line, we switched the usage to the
mysql database using the following command:
Using the following command, you can set the password for the
UPDATE user SET password = 'newPassword' WHERE user = 'root';
Note: In case you want to leave the
root password blank, then you can use the following:
UPDATE user SET password = '' WHERE user = 'root';
Please note that there might be more than one entry for
root in the system, as it could contain different IPs etc. To see all available options, execute the following:
SELECT * FROM user \G;
After we finished messing with the
MySQL internals, we executed the following command to exit the
Then, we edited the MySQL configuration file again:
We found the section for the MySQL daemon again, which starts after the line that contains the following tag:
We removed our
skip-grant-tables directive and saved the file.
We restarted the
MySQL server via the
XAMPP interface after saving the file. The restart forced the updated settings to load and take effect due to this action.
Success!! The root user had the password we set for it and we could access the database as expected.
The same guide can be used for other installations of MySQL so long you know where the MySQL configuration file is.