The user password in MySQL is stored in the user table, the password reset is actually to change the value of record in this table. To change the password in case the password is forgotten, the idea is to bypass the authentication of MySQL and get into the system and update the record password value with SQL command.

In MySQL 5, one can start MySQL service with --skip-grant-tables option, this option will tell the service to skip loading the grant tables when starting, hence the root user can login with empty password.

mysqld –skip-grant-tables

 After login, can run below SQL command to change the password

UPDATE user SET authentication_string='' WHERE user='root';

This will set the password as empty.

However, it seems --skip-grant-tables cannot work properly without some tuning in MySQL 8.  What can we do then? There are two possible options.

1. Create an init file and run MySQL service with option --init-file. In init file, put the SQL command which is to update the password value.
2. Dig deep in checking how to use --skip-grant-tables in MySQL 8.

Let's take a look at how these options work.

Option 1: --init-file option

This option will specify a file containing SQL command to be executed before the service starts. Hence, we just need to put the command to update the password in this file and start MySQL service with this option. The password will be reset/updated.

Step 1: Stop MySQL service

net stop mysql

Step 2: Create a txt file and put below command in

ALTER USER 'root'@'localhost' IDENTIFIED BY '';

Step 3: Start the service on command line and with --init-file option

mysqld --init-file=/some/path/to/cmd.txt --console

It's done. Note: please run the command as privileged user when you encounter error of permission denied. If you see errors like:

2018-12-25T02:51:23.739089Z 0 [System] [MY-010116] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe (mysqld 8.0.13) starting as process 1912
2018-12-25T02:51:23.759426Z 1 [ERROR] [MY-011011] [Server] Failed to find valid data directory.
2018-12-25T02:51:23.761196Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2018-12-25T02:51:23.762550Z 0 [ERROR] [MY-010119] [Server] Aborting
2018-12-25T02:51:23.766230Z 0 [System] [MY-010910] [Server] C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqld.exe: Shutdown complete (mysqld 8.0.13)  MySQL Community Server - GPL.

Please run below command to initialize the data directory

mysqld --initialize --console

Option 2: Use --skip-grant-tables option

In contrast to MySQL 5, some more options need to be added in MySQL 8. 

mysqld --console --skip-grant-tables --shared-memory

After starting the service, login with an empty password

mysql -u root

Then execute SQL command to update password

UPDATE mysql.user SET authentication_string='' WHERE user='root' and host='localhost';

Normally option 1 is recommended. And hope these help.