MySQL root password restore Worked Example



Introduction...

This is an illustation of the steps to change a lost root password on MySQL running on Windows 10.
MySQL version 5.6.33

Step 1 - Shutdown MySQL

In my case this is via the Windows services screen.

windows button (left of Alt button) + R
Type into the open box: services.msc
and click ok

Step 2 - Make a backup copy of my.ini file

The location of the my.ini file will be shown in the properties for MySQL in the services screen.

on my PC the my.ini was in:
C:\ProgramData\MySQL\MySQL Server 5.6

Step 3 - Add skip-grant-tables to my.ini

[mysqld]
skip-grant-tables
The section [mysqld] will already be in the file

Step 4 - Restart MySQL

This will bring MySQL up in a state that a password will not be required.

Step 5 - mysql

It will be possible to get into mysql without username and password:-

mysql

Step 6 - Change to mysql database

use mysql

Step 7 - change root password

update user set password=PASSWORD("rootpass") where user='root';

Step 8 - Flush privileges

flush privileges;

then quit

Step 9 - Test password works (and that incorrect password fails)

mysql -uroot -prootpass

Also test with an incorrect password to check it doesn't work

Step 10 - Remove entry from my.ini

Remove the previously added line:

skip-grant-tables

Step 11 - Stop and start MySQL

Use the services screen to restart.

MySQL Version for this illustration

MySQL:
version 5.6.33
version_comment MySQL Community Server (GPL)
version_compile_machine x86_64
version_compile_os Win64

Note: on linux systems the my.ini is called my.cnf.

Disclaimer

This page was created due to few step by step illustrations being available of how to restore the root password on a MySQL database.
It is intended for assistance in learning, test or development environments only.

No liability will be accepted as a result of adverse effects of using information on this webpage in commercial environments.

Page created on 22nd Oct 2016
Push Symbol. Push Symbol. Door logos