MySQL password cannot be changed by resolution

MySQL password cannot be changed by resolution

ERROR 1820 (HY000): Must reset your password using ALTER USER statement before executing this statement.

Today in MySql5.7 operation times wrong: You must SET PASSWORD before executing this statement solution, need friends can refer to the next

ERROR 1820 (HY000): Must SET PASSWORD before executing this statement

Very strange Ah, clearly signed in with a password, how also prompted to require a password.
Refer to the official document, see http://dev.mysql.com/doc/refman/5.6/en/alter-user.html. OK after doing the following:

mysql> CREATE DATABASE Yan1;
ERROR 1820 (HY000): Must SET PASSWORD before executing this statement
mysql> SET PASSWORD = PASSWORD (' 123456 ');
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE DATABASE Yan1;
Query OK, 1 row Affected (0.00 sec)


That is, with mysql> set PASSWORD = PASSWORD (' 123456 '), this sentence reset the password again! Big uncle's, really laborious ah.
This man also encountered the same problem.


Recently installed MySQL in the MySQL tool, there is always an error message:

# mysql-u Root-p
Enter Password:
ERROR 1045 (28000): Access denied for user ' root ' @ ' localhost ' (using Password:no)
Or
# mysql-u root-p password ' newpassword '
Enter Password:
Mysqladmin:connect to server at ' localhost ' failed
Error: ' Access denied for user ' root ' @ ' localhost ' (using Password:yes) '

Now we have finally found a solution. I was going to reload it.
The method operation is simple, as follows:
#/etc/init.d/mysql Stop
# Mysqld_safe--user=mysql--skip-grant-tables--skip-networking &
# mysql-u Root MySQL
mysql> UPDATE user SET Password=password (' NewPassword ') where user= ' root ' and host= ' root ' or host= ' localhost ';// The blank user password is changed to a non-empty password on the line.
mysql> FLUSH privileges;
Mysql> Quit #/etc/init.d/mysqld restart
# mysql-uroot-p
Enter Password: < Enter the newly set password newpassword>

MySql5.6 Operation times Error: must SET PASSWORD before executing this statement resolution
mysql> SET PASSWORD = PASSWORD (' 123456 ');
Query OK, 0 rows affected (0.03 sec)
mysql> CREATE DATABASE Roger;
Query OK, 1 row Affected (0.00 sec)
That is, with mysql> set PASSWORD = PASSWORD (' 123456 '), this sentence reset the password again!

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 &

Using MySQL to connect to server

Change Password: Update mysql.user set Authentication_string=password (' 123qwe ') where user= ' root ' and Host = ' localhost ';

* 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

mysql> flush Privileges;

Mysql> quit;

The modification is complete. Restart

Killall-term mysqld.

Mysqld_safe &

And then MySQL can connect.

But at this point the operation seems to be incomplete, but also alter user ...

Alter user ' root ' @ ' localhost ' identified by ' 123 ';

The web says Jiangzi can also: set password for ' root ' @ ' localhost ' =password (' 123 ');

Self-booting

CP Mysql.server/etc/init.d/mysql

chmod +x/etc/init.d/mysql

Chkconfig--add MySQL

Reboot try it.

===========================

13.7.1.1 ALTER USER Syntax

ALTER USER user_specification [, user_specification] ... user_specification:user PASSWORD EXPIRE

The ALTER USER statement modifies MySQL accounts. An error occurs if you try to modify a nonexistent account.

To use ALTER user, you must has the global CREATE USER privilege or the Updateprivilege for the MySQL database. When the READ_ONLY system variable is enabled,alter USER additionally requires the SUPER privilege.

Warning

ALTER USER is added in MySQL 5.6.6. However, in 5.6.6, ALTER USER also sets Thepassword column to the empty string, so does not use this statement until 5.6.7.

Each account name uses the format described in section 6.2.3, "Specifying account Names". If you specify only the user name part of the account name, a host name part of the '% ' is used. It is also possible to specify Current_User Orcurrent_user () to refer to the account associated with the current session.

For the account, ALTER the USER expires its password. For example:

ALTER USER ' Jeffrey ' @ ' localhost ' PASSWORD EXPIRE;

Password expiration for a account affects the corresponding row of Themysql.user table:the server sets the PASSWORD_EXPI Red column to ' Y '.

A Client session operates in restricted mode if the account password have been expired. In restricted mode, operations performed within the session result in an error until the user establishes a new account PA ssWOrd:

mysql> SELECT 1; ERROR 1820 (HY000): Must SET PASSWORD before executing this statement

As of MySQL 5.6.8, this restricted mode of operation permits SET statements, which are useful if the account password have a Hashing format that requires Old_passwordsto is set to a value different from its default before using Set PASSWORD.

It is possible for a administrative user to reset the account password, but any existing sessions for the account remain Restricted. A client using the account must disconnect and reconnect before statements can be executed successfully.

Note

It is possible to ' reset ' a password by setting it to its current value. As a matter of good policy, it is preferable to choose a different password.

This article is from the "Hand of the Paladin Control" blog, please make sure to keep this source http://wutengfei.blog.51cto.com/10942117/1931488

MySQL password cannot be changed by resolution