Summary of MySQL password modification methods

Methods for changing passwords before MySQL 5.7:

Method 1: Use the SET PASSWORD command

mysql -u root
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpass');

Method 2: Using mysqladmin

mysqladmin -u root password "newpass"

If the root password has been set, use the following method

mysqladmin -u root password oldpass "newpass"

Method 3: Use UPDATE to edit the user table directly

[root@ ~]#mysql -uroot -p
mysql> use mysql;
mysql> UPDATE user SET Password = PASSWORD('newpass') WHERE user = 'root';
mysql> FLUSH PRIVILEGES;

When you lose your root password, you can

mysqld_safe --skip-grant-tables&
mysql -u root mysql
mysql> UPDATE user SET password=PASSWORD("new password") WHERE user='root';
mysql> FLUSH PRIVILEGES;

The way to change the password in MySQL 5.7.22 is as follows:

1. Version update. The password field in the original user has been changed to authentication_string. Due to the version update, many online tutorials are no longer applicable, and even the official website documents cannot be operated smoothly. If MySQL is running, kill it first: killall -TERM mysqld. Run mysqld_safe --skip-grant-tables & If you do not want to be connected remotely at this time: mysqld_safe --skip-grant-tables --skip-networking & Use mysql to connect to the server and change the password

mysql> update mysql.user set authentication_string=password('hwg123') where user='root' and Host = 'localhost';
mysql> exit
[root@Centos7_3 ~]# systemctl restart mysqld

*One thing to note is that there is no Password field in the user table under the new version of MySQL database.

Instead, the encrypted user password is stored in the authentication_string field.

2. The following error is reported when upgrading MySQL: ERROR 3009 (HY000): Column count of mysql.user is wrong. Expected 45, found 42. Created with MySQL 50556, now running 50722. Please use mysql_upgrade to fix this error. The error is caused by the fact that you have upgraded the database before and did not use mysql_upgrade to upgrade the data structure after the upgrade.

Solution: Use the mysql_upgrade command

root@localhost ~]# mysql_upgrade -u root -phwg123

3. Change the password after installing the MySQL5.7.22 database;

[root@ ~]# cat /var/log/mysqld.log | grep password
[root@ ~]# mysql -uroot -pRir.*sJUX6M*

After entering mysql, you need to change the global variables. Otherwise, the password you set must meet the password complexity requirements.

mysql> set global validate_password_policy=0;
[root@zabbixserver ~]# systemctl restart mysqld
[root@zabbixserver ~]# mysql -uroot -pRir.*sJUX6M*
mysql> ALTER USER USER() IDENTIFIED BY '12345678';

Or like this:

mysql> ALTER USER USER() IDENTIFIED BY 'Pass123!';