Summary of how to modify the root password in MySQL 5.7 and MySQL 8.0

MySQL 5.7 version:

Method 1: Use the SET PASSWORD command

Format: mysql> set password for username@localhost = password('new password');

Example:mysql> set password for root@localhost = password('123');

mysql5.7 and mysql8.0 and above can be used

Method 2: Using mysqladmin

Format: mysqladmin -u username -p password new password

Example: mysqladmin -uroot -p password 123

MySQL version 5.7

Method 3: Use UPDATE to edit the user table directly

mysql> use mysql; 
mysql> update user set password=password('123') where user='root' and host='localhost'; 
mysql> flush privileges;

MySQL 5.7 and MySQL 8.0 and above

Method 4: Use alter. There is a pitfall in using this method. Many blogs say that you can use the following method to modify it, but it has not been successful:

alter user 'root'@'localhost' IDENTIFIED BY 'liuhehe';

Error:

ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'localhost'

Later, I checked the record of user='root' in the user table and found that the value of the host column was '%'. I modified the above SQL to

mysql> alter user 'root'@'%' IDENTIFIED BY 'liuhehe';
Query OK, 0 rows affected (0.03 sec)

Execution successful! !

The following method is only used for MySQL 5.7 version

Method 4: When you forget the root password

Take Windows as an example:

1. Shut down the running MySQL service.

2. Open a DOS window and go to the mysql\bin directory.

3. Enter mysqld --skip-grant-tables and press Enter. --skip-grant-tables means skipping the permission table authentication when starting the MySQL service.

4. Open another DOS window (because the previous DOS window cannot be moved) and go to the mysql\bin directory.

5. Type mysql and press Enter. If successful, the MySQL prompt > will appear.

6. Connect to the privilege database: use mysql; .

6. Change the password: update user set password=password("123") where user="root"; (don't forget to add a semicolon at the end).

7. Refresh privileges (required step): flush privileges; .

8. Exit quit.

9. Log out of the system, log back in, and log in using the username root and the new password 123 you just set.

Note: There is no password field in the MySQL database under version 5.7. The password field has been changed to

authentication_string.

Summarize

The above is a summary of the method of changing the root password in MySQL 5.7 and MySQL 8.0. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!