The process of resetting the root user password under MySQL windows

Yesterday, a small partner of another project called to say that they were planning to do a data migration on MySQL after the project, the plan is to start the MySQL test on the local windows, then the whole program to discuss with the foreigner. There is a very wonderful thing, the original MySQL DBA left the office 1 months ago, The MySQL document on the Linux server was sent to the project team during the turnover, but MySQL on a local windows was probably missing the MySQL document because it was rarely used. Now the little Buddy is in a hurry, so let me help reset MySQL The root user's password. Brother this dabbler MySQL DBA also had to gig, on the network query data discovery method is similar, mostly by skip the GRANT TABLE, and then re-login to MySQL under another DOS and then change the root password in the permissions library; However, when testing on the machine found skipping permission check after the login MySQL total error under another DOS, after repeated testing found that the following processing flow is available:

1. First Use DOS command ' net stop MySQL ' to turn off the MySQL service, if DOS command is not available, first go to the environment variable to add similar ';%SystemRoot%\System32 ' parameters.

2. Under DOS switch to the MySQL installation file bin folder, enter the command ' mysqld--skip-grant-tables ', note that after entering this command, the cursor should normally stop on the next line, If the DOS interface is prompted, the message will be processed. The situation I encountered is the MySQL installation folder under the Data folder is missing, may be due to misoperation or MySQL upgrade to 5.7 caused, so use the skip command can encounter problems, Causes subsequent logons to MySQL to fail. The first time I found this problem, I first manually assigned a data folder (manual assignment causes other problems, which are described in detail later). Using the Skip command is OK at this point.

3. Then we need to open a new DOS window, because the last step entered the skip command after the cursor stopped, we opened the DOS window, and then switch to the MySQL installation folder bin directory, enter the MySQL command to access MySQL. You do not need to enter a password at this time.

4. After logging in to MySQL successfully, we enter the ' use MYSQL ' command to access the system permissions database. Note: Here is the second problem, because in the second step, I chose to manually assign the data folder in the first time, so the system does not have permissions database ' MYSQL ', Consider using the data file of the previous version of 5.7 but it is not reliable, and later found that this phenomenon is mostly due to MySQL upgrade, using the following command, the system will automatically create a data file and some missing system database:

Mysqld--initialize-insecure--user=mysql

According to the data, after the initialization of the system will actually build the default database after the default to set the root password is empty, but we have to this step, or continue to try the normal process of modifying the root password.

5. Use the regular UPDATE statement to update the root password in the MySQL user table:

update mysql.user set authentication_string=password(‘123456‘) where user=‘root‘ and Host =‘localhost‘;

Note that the password field in the user table is changed to authentication_string after MYSQL5.7, so here we use authentication_string.

6. After the success of the update is the third place to note, the MySQL command line input ' flush privileges ', notification system permissions changes.

7. Finally quit, restart, and found that the modification was successful.

The process of resetting the root user password under MySQL windows