If you have forgotten the root password for MySQL on Windows, it’s fairly easy to reset it back to a preferred password.
There are several ways of doing so. I tried the different options stated in the documentation, but ended up with the following warning
[Warning] TIMESTAMP with implicit DEFAULT value is deprecated.
Please use –explicit_default_timestamp server option
(see documentation for more details)
The solution to reset the root password for MySQL on Windows I came up with is as follows
1. Create “mysql-init.sql” on C:\
Depending on your MySQL version, paste one of the following sql statements in “mysql-init.sql” and save the file.
MySQL 5.7.6 and later
[sql]ALTER USER ‘root’@’localhost’ IDENTIFIED BY ‘MyNewPass’;[/sql]
MySQL 5.7.5 and earlier
[sql]SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD(‘MyNewPass’);[/sql]
2. Open up my.ini, typically located in C:\ProgramData\MySQL\MySQL Server x.x\
Locate the [mysqld] section and add the following line
[shell]init-file=C:\\mysql-init.sql[/shell]
3. Restart MySQL using the console
net stop mysql
net start mysql
4. Check that your password has changed by running
mysql -u root -p
If your password has changed successfully, delete the file C:\mysql-init.sql so you don’t expose your password. Furthermore, remove the “init-file” line in my.ini
Same method works on Unix/Linux systems, but if you are looking for a more OS specific guide, take a look at the documentation.