Complete steps to reset the root user password in mysql8

Preface

Recently, many new colleagues have asked this question, especially for MySQL automatically installed through homebrew, whose default version is 8.0. Due to the addition of some security policies and other restrictions, it is a little troublesome to change the user password. I might as well post this summary.

Let’s take a look at the detailed introduction.

Here are the steps:

1. First, check the root user's related information in the user table of the mysql database:

select host, user, authentication_string, plugin from 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; the password field and password() function are deprecated after MySQL 5.7.9;

plugin: password encryption method;

If you find that there is content under the authentication_string field of the root user, set it to empty first:

use mysql;
update user set authentication_string='' where user='root';

2. Restart the mysql service and shut down the service directly by command in mac:

mysql.server stop

Or kill the mysql process. Then start the mysql service:

mysql.server start

3. Log in as root user. Since authentication_string has been set to empty, you can log in without a password:

mysql -u root -p
Password:

No need to enter a password, just press Enter

4. Enter the mysql database and use ALTER to modify the root user password:

ALTER user 'root' IDENTIFIED BY '123456' ;

The root in the statement depends on whether the root user in your actual user table is root or root@localhost. Since I changed the password to a simpler format like 123456, the default password policy of MySQL 8 may not allow it. If you must change it, you can modify the password policy first:

set global validate_password.length = 6;

set global validate_password.policy = 'LOW';

FLUSH PRIVILEGES;

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

SHOW VARIABLES LIKE 'validate_password.%';

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.