MySQL password modification method summary

  • 2021-11-02 03:08:55
  • OfStack

How to change passwords before MySQL version 5.7:

Method 1: Use the SET PASSWORD command

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

Method 2: Use mysqladmin

mysqladmin -u root password "newpass"

If root has already set a password, use the following method

mysqladmin -u root password oldpass "newpass"

Method 3: user table is edited directly with UPDATE

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

When the root password is lost, you can do this

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

MySQL 5.7. 22 The password can be changed as follows:

1. Version update. Because the password field in user has been changed to authentication_string version update, many online tutorials are not applicable, and even the documents in official website are not able to operate smoothly. If MySQL is running, kill it first: killall-TERM mysqld. Run mysqld_safe--skip-grant-tables & If you do not want to be remotely connected at this time: mysqld_safe--skip-grant-tables--skip-networking & Use mysql to connect server to change 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 special note is that the Password field is no longer in the user table under the new mysql database

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

2. Upgrade mysql reported the following error: ERROR 3009 (HY000): Column count of mysql. user is wrong. Expected 45, found 42. Created with running 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 and did not use

Solution: Use the mysql_upgrade command

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

3. Change the password after the installation of 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 should conform to the password complexity.

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 something like this:

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


Related articles: