Summary of modifying MySQL password method

Summary of modifying MySQL password method

How to change password before MySQL5.7 version:

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 been set password, use the following method mysqladmin-u root password Oldpass "Newpass"

Method 3: Edit the user table directly with update

[[Email protected] ~] #mysql-uroot-pmysql> use mysql;mysql> UPDATE user SET Password = Password (' newpass ') WHERE us er = ' root ';mysql> FLUSH privileges;

This can be done when the root password is lost

Mysqld_safe--skip-grant-tables&mysql-u root mysqlmysql> UPDATE user SET Password=password ("New password") WHERE user= ' root ';mysql> FLUSH privileges;

MySQL 5.7.22 changes the password in the following way:

1, version update, The original password field in user has been changed to authentication_string Update for the sake of a lot of online tutorials are not applicable, and even the official website of the document is not able to operate smoothly. If MySQL is running, first kill it: 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 & change password using MySQL connection server

mysql> Update Mysql.user Set Authentication_string=password (' hwg123 ') where user= ' root ' and Host = ' localhost '; mysql& Gt Exit[[email protected]_3 ~]# systemctl Restart Mysqld

* Note that there is no password field in the user table in the new MySQL database.

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

2, upgrade MySQL error as follows: ERROR 3009 (HY000): Column count of Mysql.user is wrong. expected, found 42. Created with MySQL 50556, now running 50722. Please use the Mysql_upgrade to fix this error. The error is due to the fact that you upgraded the database and did not use the Mysql_upgrade upgrade data structure after the upgrade.

Workaround: Use the Mysql_upgrade command

[Email protected] ~]# mysql_upgrade-u root-phwg123

3,mysql5.7.22 the database after the installation of the password change;

[Email protected] ~]# Cat/var/log/mysqld.log | grep password[[email protected] ~]# mysql-uroot-prir.*sjux6m*

You need to change the global variables after entering MySQL. Otherwise you set the password to match the password complexity.

mysql> set global validate_password_policy=0; [[email protected] ~]# systemctl restart Mysqld[[email protected] ~]# mysql-uroot-prir.*sjux6m*mysql> ALTER User User () identified by ' 12345678 ';

Or something like this:

mysql> ALTER User User () identified by ' pass123! ';

Summary of modifying MySQL password method