How to change the password correctly by MySQL8.0

This article mainly explains "how to change the password correctly in MySQL8.0". The content of the explanation in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought slowly and deeply, and study and learn "MySQL8.0 how to change the password correctly".

Preface

When mysql updates the password, it always refuses to connect and login fails? MySQL8.0 cannot change the password by directly modifying the mysql.user table. How to change your password correctly Note: clear root password

MySQL8.0 cannot change the password by directly modifying the mysql.user table.

Because the authentication_string field can only be the 43-bit string password after MySQL encryption, others cause errors. The error is not reported, but you can no longer log in to mysql, and you will always be prompted that you cannot authenticate.

Reference: MySQL8.0

Mysql > USE mysql;Database changedmysql > UPDATE user SET authentication_string= "123456" WHERE user= "root"; Query OK, 1 row affected (0.39 sec) Rows matched: 1 Changed: 1 Warnings: 0mysql > FLUSH privileges; # refresh save Query OK, cancel password function after 0 rows affected (0.13 sec) mysql 5.7.9, authentication_string=password ("123456") will report an error

C:\ WINDOWS\ system32 > mysql-u root-p

Enter password: *

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

1234567891011121314

If you have changed your password in this way, and you are unable to access mysql. When I express my sympathy, I also provide a detailed solution for you. Please see the remarks for this article: clear the root password

Correct password change ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY "your_password"; mysql > USE mysql;Database changedmysql > ALTER USER' root'@'localhost' IDENTIFIEED WITH mysql_native_password BY "markjun"; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIEED WITH mysql_native_password BY "markjun"' at line 1mysql > ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY "markjun" Query OK, 0 rows affected (0.18 sec) mysql > ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY "123456"; Query OK, 0 rows affected (0.08 sec) mysql > SELECT user, authentication_string FROM user +-+-+ | user | authentication_string | | +-+-+ | mysql.infoschema | $$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | mysql.session | $$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | mysql.sys | | | $Aguil005 $THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | root | * 6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | +-+-+ 4 | Rows in set (0.00 sec) mysql > FLUSH privileges Query OK, 0 rows affected (0.38 sec) mysql > quit;ByeF:\ MySQL\ mysql-8.0.13-winx64\ bin > mysql- u root-pEnter password: *.. MySQL >

Note: clear the root password

Stop MySQL task net stop MySQL

Mysqld command mysqld-- console-- skip-grant-tables-- shared-memory

Enter mysql mysql-u root without password

Clear root password UPDATE user SET authentication_string= "" WHERE user= "root"

The other terminal enters without a password.

F:\ MySQL\ mysql-8.0.13-winx64\ bin > mysql- u root-pEnter password: * ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) F:\ MySQL\ mysql-8.0.13-winx64\ bin > mysql- u rootWelcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 7...Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.mysql > USE mysql;Database changedmysql > SELECT user, authentication_string FROM user +-+-+ | user | authentication_string | | +-+-+ | mysql.infoschema | $$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | mysql.session | $$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | mysql.sys | | | $Aguil005 $THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | root | 123456 | +-| -+ 4 rows in set (0.34 sec) mysql > UPDATE user SET authentication_string= "" WHERE user= "root" Query OK, 1 row affected (0.20 sec) Rows matched: 1 Changed: 1 Warnings: 0mysql > SELECT user, authentication_string FROM user +-+-+ | user | authentication_string | | +-+-+ | mysql.infoschema | $$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | mysql.session | $$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | mysql.sys | | | $Aguil005 $THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | | root | | +-| -+ 4 rows in set (0.00 sec) mysql > FLUSH privileges Query OK, 0 rows affected (0.10 sec) mysql > quit;Bye

Stop the mysqld task, end the Ctrl+C task, or simply close the cmd terminal running mysqld.

You need to stop running the above mysqld task first, otherwise an error will be reported.

F:\ MySQL\ mysql-8.0.13-winx64\ bin > net start mysql

The MySQL service is starting.

The MySQL service could not be started.

The service did not report any errors.

Please type NET HELPMSG 3534 for more help.

Stop the above mysqld task first

F:\ MySQL\ mysql-8.0.13-winx64\ bin > net start mysql

The MySQL service is starting.

The MySQL service has started successfully.

Now mysql root doesn't have a password.

F:\ MySQL\ mysql-8.0.13-winx64\ bin > mysql- u root-p

Enter password:

Welcome to the MySQL monitor. Commands end with; or\ g.

...

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

Mysql >

Thank you for your reading, the above is the content of "how to correctly change the password of MySQL8.0". After the study of this article, I believe you have a deeper understanding of how to correctly change the password of MySQL8.0, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!