1. Problem: after logging into mysql, no matter what command you run, you will always prompt this

mysql&> select user,authentication from mysql.user;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql&> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

2. Solutions

The solution is as follows.

(1) MySQL version 5.7.6 (previous) users can use the following command.
        mysql&> SET PASSWORD = PASSWORD('l!vWT#mL93');
 
(2) Users of MySQL version 5.7.6 (after the start) can use the following command.
        mysql&> ALTER USER USER() IDENTIFIED BY 'l!vWT#mL93';
     

3. Cause analysis

(1) MySQL version 5.6.6 onwards adds the password_expired feature, which allows setting a user's expiration time. This feature has been added to the mysql.user data table, but its default value is "N", which can be changed using the ALTER USER statement.
　　Enter the following command to force the account password to expire.
　　mysql&> ALTER USER 'root'@'localhost' PASSWORD EXPIRE;
　　At this point, the user can log in to the MYSQL server, but cannot run any commands until the user sets a new password for the account, and will get the above error, and can run all commands within the account permissions normally by changing the password. Since the password expiration days cannot be achieved by command in this version, the DBA can set the password expiration time for MySQL users by cron timer task.

(2) Starting from MySQL version 5.7.4, the feature of password expiration time for users has been improved, and a global variable default_password_lifetime can be used to set the password expiration policy, and this global variable can set a global automatic password expiration policy. A default value can be set in MySQL's my.cnf configuration file.
　　(a) This will cause all MySQL users to have a password expiration time of 120 days, and MySQL will start counting the time from startup.
　　　　The my.cnf configuration is as follows.
　　　　[mysqld]
　　　　default_password_lifetime=120
　　(b) If you want to set the password to never expire, my.cnf is configured as follows.
　　　　my.cnf is configured as follows.
　　　　[mysqld]
　　　　default_password_lifetime=0
(3) If you want to set individual specific values for each specific user account, you can use the following command (note: this command will override the global policy) in "days", the command is as follows.
　　ALTER USER 'root'@'localhost' PASSWORD EXPIRE INTERVAL 250 DAY;

(4) If you want the user to restore the default policy, the command is as follows.
　　ALTER USER 'root'@'localhost' PASSWORD EXPIRE DEFAULT;
(5) Individual users will disable the password expiration function for late trouble, the command is as follows.
　　ALTER USER 'testuser'@'localhost' PASSWORD EXPIRE NEVER;