mysql改密码后登录不了

[keke_321]

mysql-5.6.14 ndb-7.3.3

用以下命令更改root密码：
mysql> UPDATE mysql.user SET Password = PASSWORD('newpwd')
-> WHERE User = 'root';
mysql> FLUSH PRIVILEGES;

更改后用命令：select host,user,password from mysql.user;
查看，是16字符加密的密码。
（好像应该是41字符的加密密码才对，我猜的）

退出重登录报错如下：
ERROR 1275 (HY000): Server is running in --secure-auth mode, but 'root'@'localhost' has a password in the old format; please change the password to the new format

old_passwords 参数值为1
mysql> SELECT @@session.old_passwords, @@global.old_passwords;
+-------------------------+------------------------+
| @@session.old_passwords | @@global.old_passwords |
+-------------------------+------------------------+
| 1 | 1 |
+-------------------------+------------------------+
1 row in set (0.00 sec)


现在不能登陆，求高手支招，
1.如何重新登陆
2.如何更改密码

[幻想先锋]

修改my.cnf/my.ini文件中的secure_auth为0就可以用旧密码登录了，登录后修改SESSION的old_passwords值，再把所有的旧密码修改为新密码即可。 [mysqld] old_passwords = 0 secure_auth = 1 mysql> show variables like 'old_passwords'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | old_passwords | 1 | +---------------+-------+ 1 row in set (0.00 sec) mysql> select password('mypass'); +--------------------+ | password('mypass') | +--------------------+ | 6f8c114b58f2ce9e | +--------------------+ 1 row in set (0.00 sec) mysql> set @@session.old_passwords=0; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'old_passwords'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | old_passwords | 0 | +---------------+-------+ 1 row in set (0.00 sec) mysql> select password('mypass'); +-------------------------------------------+ | password('mypass') | +-------------------------------------------+ | *6C8989366EAF75BB670AD8EA7A7FC1176A95CEF4 | +-------------------------------------------+ 1 row in set (0.00 sec) mysql> use mysql Database changed mysql> update user set password=password('root123') where user = 'root'; Query OK, 4 rows affected (0.00 sec) Rows matched: 4 Changed: 4 Warnings: 0

[幻想先锋]

当secure_auth改为0后，old_passwords会强制改为1，所以要在SESSION中修改为0，PASSWORD()函数才会返回41位的加密密码。 关于新旧密码的加密，参见http://dev.mysql.com/doc/mysql-security-excerpt/5.6/en/password-hashing.html