Why is there no \'password\' column in my MySQL 5.7 user table, and how do I change the root password?

MySQL user DB does not have password columns

In the process of attempting to change the root password for MySQL, an unknown error arose: "ERROR 1054 (42S22): Unknown column 'password' in 'field list'." This enigmatic issue seemingly stemmed from the absence of a "password" column in the user table.

Understanding MySQL Password Storage in MySQL 5.7

Delving deeper, it became apparent that in MySQL 5.7, the password storage mechanism underwent significant changes. The traditional "password" column was replaced with "authentication_string." This paradigm shift prompted a revised approach to password management.

Changing the Password

To update the root password, follow these updated steps:

1. Select the mysql database: mysql> use mysql;
2. View the tables within the database: mysql> show tables;
3. Inspect the columns of the user table: mysql> describe user;
4. Observe the presence of the "authentication_string" column.
5. Execute the following command to change the password: update user set authentication_string=password('1111') where user='root';

Conclusion

The absence of a "password" column in the user table was an unexpected but understandable consequence of the architectural changes introduced in MySQL 5.7. By embracing the new "authentication_string" column, you can effectively manage MySQL passwords in this updated version.

The above is the detailed content of Why is there no \'password\' column in my MySQL 5.7 user table, and how do I change the root password?. For more information, please follow other related articles on the PHP Chinese website!