Recently, many new colleagues have asked this question, especially through homebrew automatic installation of mysql, the default version is already 8.0, due to the addition of some security policies and other restrictions, so it is a little more difficult to change the user password, so I will post this summary.

  1. Mysql > select * from user where user = ‘root’;
select host, user, authentication_string, plugin from user;
Copy the code

Host: indicates the IP address where users are allowed to log in. User: indicates the user name of the current database. Authentication_string: indicates the user password. The password field and the password() function were deprecated after mysql 5.7.9; Plugin: password encryption mode;

If you find anything in the authenticATION_string field of the root user, set it to null:

use mysql;
update user set authentication_string=' ' where user='root';
Copy the code
  1. Mysql > restart mysql > restart mysql
mysql.server stop
Copy the code

Or kill the mysql process. Mysql > start mysql

mysql.server start
Copy the code
  1. Log in as root and since authentication_string is set to empty, you can log in password-free:
mysql -u root -p
passwrod:
Copy the code

You don’t need to enter the password, just press Enter

  1. Run the following command to change the password of user root:
ALTER user 'root' IDENTIFIED BY '123456' ;
Copy the code

Mysql 8 default password policy may not allow you to change the password to 123456. Mysql 8 default password policy may not allow you to change the password to 123456 If you must change the password policy, you can change the password policy first:

set global validate_password.length = 6 ;

set global validate_password.policy = 'LOW';

FLUSH PRIVILEGES;
Copy the code

The password length is changed from the default 8 digits to 6 digits, and the password policy level is changed from MEDIUM to LOW. If you want to view password verification Settings, you can directly query system variables:

SHOW VARIABLES LIKE 'validate_password.%';
Copy the code