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

今天在MySql5.6操作时报错：You must SET PASSWORD before executing this statement解决方法，需要的朋友可以参考下

ERROR 1820 (HY000): You must SET PASSWORD before executing this statement 
。如下操作后就ok了： 
mysql> SET PASSWORD = PASSWORD('123456'); 
Query OK, 0 rows affected (0.03 sec) 

也就是用mysql> SET PASSWORD = PASSWORD('123456');这句话重新设置一次密码！大爷的，真费劲啊。 
这位仁兄也遇到了相同的问题。 

最近新装好的mysql在进入mysql工具时，总是有错误提示: 

现在终于找到解决方法了。本来准备重装的，现在不必了。 
方法操作很简单，如下： 
# /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';//把空的用户密码都修改成非空的密码就行了。 
mysql> FLUSH PRIVILEGES; 
mysql> quit # /etc/init.d/mysqld restart 
# mysql -uroot -p 
Enter password: <输入新设的密码newpassword> 
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) 
也就是用mysql> SET PASSWORD = PASSWORD('123456');这句话重新设置一次密码！

如果 MySQL 正在运行，首先杀之： killall -TERM mysqld。 

运行mysqld_safe --skip-grant-tables & 

如果此时不想被远程连接：mysqld_safe --skip-grant-tables --skip-networking &

 

使用mysql连接server

更改密码： update mysql.user set authentication_string=password('123qwe') where user='root' and Host = 'localhost';

*特别提醒注意的一点是，新版的mysql数据库下的user表中已经没有Password字段了

而是将加密后的用户密码存储于authentication_string字段

mysql> flush privileges;

mysql> quit;

 

修改完毕。重启

killall -TERM mysqld。 

mysqld_safe &

然后mysql就可以连接了

 

但是此时操作似乎功能不完全，还要alter user...

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

网文说酱紫也可以：set password for 'root'@'localhost'=password('123');

 

自启动

 

chkconfig --add mysql

reboot试试看

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





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 have the global CREATE USER privilege or the UPDATE privilege for the mysql database. When the read_only system variable is enabled, ALTER USER additionally requires the SUPER privilege.

Warning

ALTER USER was added in MySQL 5.6.6. However, in 5.6.6, ALTER USER also sets the Password column to the empty string, so do 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 '%' is used. It is also possible to specify CURRENT_USER or CURRENT_USER() to refer to the account associated with the current session.

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

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

Password expiration for an account affects the corresponding row of the mysql.user table: The server sets the password_expired column to 'Y'.

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

mysql> SELECT 1;




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



 mysql> SET PASSWORD = PASSWORD('new_password');




Query OK, 0 rows affected (0.01 sec)




mysql> SELECT 1;


+---+
| 1 |
+---+
| 1 |
+---+




1 row in set (0.00 sec)

As of MySQL 5.6.8, this restricted mode of operation permits SET statements, which is useful if the account password has a hashing format that requires old_passwords to be set to a value different from its default before using SET PASSWORD.

It is possible for an 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.