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 

非常诡异啊，明明用密码登陆进去了，怎么还提示需要密码。 
参考官方的一个文档，见http://dev.mysql.com/doc/refman/5.6/en/alter-user.html。如下操作后就ok了： 

mysql> create database yan1; 
ERROR 1820 (HY000): You 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) 


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


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

# mysql -u root -p 
Enter password: 
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) 
或者 
# 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)' 

现在终于找到解决方法了。本来准备重装的，现在不必了。 
方法操作很简单，如下： 
# /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> 

MySql5.6操作时报错：You must SET PASSWORD before executing this statement解决 
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');

自启动

 

cp mysql.server /etc/init.d/mysql

chmod +x /etc/init.d/mysql

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.

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.