Reset MySQL root password on Windows

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.

Create MySQL user and database

Users can be created in several ways in MySQL. Eg. via phpMyAdmin or Command Line Client, which I prefer and will demonstrate.

Open up Command Line Client and enter your root password to login.

You should now get the welcome screen and be in mysql>_

We are now ready to create our database and associated user.

CREATE DATABASE mydatabase; [Enter]
GRANT ALL PRIVILEGES on mydatabase.* to myuser@localhost identified by ‘userpassword’; [Enter]
FLUSH PRIVILEGES; [Enter]

That’s it, you are now ready to use your database.
Usually I use domain_com as database and username for a better overview.