How to modify the root user password in mysql 8.0.16 winx64 and Linux

Please handle basic operations such as connecting to the database by yourself. This article focuses on recording how to change the password.

1. Query user password:

Query user password command:

select host, user, authentication_string from mysql.user;

host: The IP address 'location' that allows users to log in % indicates that it can be remote;

user: the user name of the current database;

authentication_string: user password (this field is mentioned later);

2. Set (or modify) user password:

If the default root password is empty, you will not be able to connect using navicat (it seemed to be OK when I installed 5.7 before), so you need to change the root password here.

This is a crucial step. I was fooled for a long time because of this. Later, I found out after a lot of research that the password field and password() function were deprecated after MySQL 5.7.9.

authentication_string: field indicates the user password.

3. Steps to modify the root password:

1. If there is content under the authentication_string field of the current root user, you can set it to empty first, otherwise proceed directly to step 2.

update user set authentication_string='' where user='root';#The password is set to empty

2. Use ALTER to modify the root user password. The method is ALTER user 'root'@'localhost' IDENTIFIED BY 'new password'. as follows:

alter user 'root'@'%' identified with mysql_native_password by 'xxxx';
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'mypwd#2019';
or alter user 'root'@'localhost' identified with mysql_native_password by 'xxxx';
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mypwd#2019'

hint:

The content following root@ is the Host field of the user table. The default for a new installation is localhost. Because remote access is added here, localhost is manually changed to %.

After the change, you can execute: flush privileges; (reload the permission table)

flush privileges;

Note: The following method is no longer applicable for versions after MySQL 8.0. Remember! ! !

UPDATE user SET password=PASSWORD("new password") WHERE user='user name';

If there is anything wrong with the above text, please correct it in detail and leave a message to facilitate our common growth in the future;

I hope this blog can be helpful to you!