How to reset the root password in mysql8.0.12

After installing the database, if you accidentally close the installation window or have not used the root user to log in to the system for a long time, you may forget the root password. In this case, you need to reset the MySQL root password. Of course, the easiest way is to delete the database data directory and then reinstall the database. But most of the time we need to keep the data in the data directory, so we need to find out how to reset the root password.

We know that when we know the root password, we can use the "ALTER USER 'root'@'localhost' IDENTIFIED BY 'new password';" command to modify the root user's password, as shown below:

Only this one line of command is needed to reset the root password. No other operations are required. It is very simple.
But how do you reset your root password if you forget it? According to the official documentation, there are two ways to change the password under Windows. For the official documentation, please refer to the link

Solution 1: Execute the initialization file

Step 1: Log in to the system using an account with administrative privileges;

Step 2: Stop the MySQL service. No matter what method you use, you must stop it, as shown below:

Step 3: Create a file mysql-init.txt, which contains the password change instruction "ALTER USER 'root'@'localhost' IDENTIFIED BY 'new password';", and then save the file, as shown in the figure:

Step 4: In the terminal interface, enter the bin directory where MySQL is installed:

Step 5: In the bin directory, execute mysqld and specify the mysql-ini.txt just created as the initialization file. The command used is "mysqld –defaults-file="D:\Programs\MySQL\MySQL-8.0.12-winx64\my.ini" –init-file="D:\Programs\MySQL\mysql-ini.txt"", where –defaults-file specifies the configuration file and –init-file specifies the file to be executed for initialization, as shown in the following figure:

After pressing the Enter key, do not close the terminal. You need to open another terminal to confirm whether the password change is successful:

Obviously, the login was successful and the password modification method was correct!

Of course, if you really close the terminal, you can also use the "net start MySQL" command to start MySQL and then verify it:

Step 6: Delete the created mysql-ini.txt file. Of course, you can also keep it!

Solution 2: Reset the root password by skipping the verification during login

Step 1: First shut down the MySQL service, then use the "--skip-grant-tables" configuration item to skip the permission verification method and restart the MySQL service:

The command used here is "mysqld –shared-memory –skip-grant-tables". If it is just "mysqld –skip-grant-tables", mysqld startup will fail and prompt "TCP/IP, –shared-memory, or –named-pipe should be configured on NT OS" error. After testing, it is found that only by adding “--shared-memory” can the database be started and accessed.

Step 2: Open a terminal, log in to the database without a password, and run mysql directly:

/p>

Step 3: First, refresh the execution instruction "FLUSH PRIVILEGES;" to refresh the permissions:

Then execute the command "ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_psd_123';" to update the password. "new_psd_123" is the new password set:

After the settings are completed, start the MySQL service normally and use the username and password to verify whether the settings are successful!

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.