MySQL 8.0.17 on Windows – I forgot the root password, dont need to keep any previous DBs

installationMySQLpassword

I have previously used MySQL for a class about a year ago and not since. I've forgotten the password and I need to use it again. I don't need to be able to access any previous databases, so I tried stopping the service in command prompt and uninstalling then re-installing MySQL but this still resulted in me getting stuck at the "Accounts and Roles" section of the MySQL Installer where it asks for my root password.

Is there a way I can reset it or start a new root account all together? I've seen tutorials for Ubuntu but I'm not knowledgeable enough yet to translate this to windows.

Best Answer

It's possible re-initialize the data directory to produce an empty MySQL instance. Here is the documentation describing the process: https://dev.mysql.com/doc/refman/8.0/en/data-directory-initialization.html .

It tells the command to perform is:

bin\mysqld --initialize --console

or the following if you want to connect as root without password:

bin\mysqld --initialize-insecure --console

The data currently in the data dir would be overwritten. If there still is a chance it might be needed in future, you may find datadir param in my.cnf and backup the directory before proceeding to re-initialization.

Another possible option is to uninstall MySQL, remove or rename datadir as found in my.cnf and install again. It shouldn't ask for the old root password then.

Related Solutions

MYSQL – Unable to change ‘old_passwords’ variable

I have 3 additional suggestions (choose one):

Downgrade the PHP drivers (which understand the 16 character encrypted password)
Upgrade to the latest MySQL (where you can set old_password=1 from inception)
Change all the passwords in mysql.user to 41 character encrypted password format

Whatever you do, do not use the MD5 function to make new passwords. Use the PASSWORD function. It is very different from MD5:

The password function PASSWORD function is the equivalent of

SET @my_new_password = 'WhateverIWantAsThePassword';
SELECT CONCAT('*',UPPER(SHA1(UNHEX(SHA1(@my_new_password))))) MySQLPWDHASH;

I learned that like 2 years ago from this PalominoDB Blog Post.

You can QA the PASSWORD function with OLD_PASSWORD function. Compare the output of SELECT PASSWORD(@my_new_password); WITH SELECT OLD_PASSWORD(@my_new_password);.

Mysql – Forgot thesql owner/root username and password

You have a variety of options to reset the password http://code.openark.org/blog/mysql/recovering-a-mysql-root-password-the-fourth-solution

UPD: Following the Max's advice let me elaborate. The easiest way to reset any password is to restart MySQL with skip-grant-tables in my.cnf. Then MySQL won't ask the password. To set new password execute this:

 mysql -e "UPDATE mysql.user SET password = PASSWORD('newpassword') WHERE user = 'root' and host = '<host>'"

where <host> is host where user connects form.

Then restart MySQL again, but without skip-grant-tables option.

Best Answer

Related Solutions

MYSQL – Unable to change ‘old_passwords’ variable

Mysql – Forgot thesql owner/root username and password

Related Question