将Mysql旧密码升级到新版本[重复]

Question

This question already has an answer here:

• How to upgrade from MySQL old passwords to new Password system 1 answer

This probably has been asked before. Some passwords for the users are stored in 16 bit format. Mysql has been upgraded to 5.5, but the passwords in the table remained in a 16 bit format.

A way to tell what password scheme your MySQL is using is to run this query below, it will list each user’s password length (16 or 41):

mysql> SELECT `User`, `Host`, Length(`Password`) FROM mysql.user;
+—————-+—————–+——————–+
| User | Host | Length(`Password`) |
+—————-+—————–+——————–+
| root | localhost | 16 |
| mary | 20.20.3.2 | 16 |
| fred | localhost | 16 |
| sally | 64.56.139.194 | 16 |
| fschmidt | 64.56.139.194 | 16 |
…

To change the user to having the exact same password but stored in the newer longer format, we can do this:

mysql> SET SESSION old_passwords=FALSE;
mysql> SET PASSWORD FOR ‘visitlogger’@’64.55.129.%’=PASSWORD(‘ ‘);
mysql> flush privileges;

Now we can verify the changes:

mysql> SELECT `User`, `Host`, Length(`Password`) FROM mysql.user;
+—————-+—————–+——————–+
| User | Host | Length(`Password`) |
+—————-+—————–+——————–+
| root | localhost | 41 |
| mary | 20.20.3.2 | 41 |
| fred | localhost | 41 |
| sally | 64.56.139.194 | 41 |
| fschmidt | 64.56.139.194 | 41 |
+—————-+—————–+——————–+

Can anyone help me figuring out a way to make this changes automatically. It will be a pain to do this manually for all my users. Thank you!

</div>

Answer 1

If you want to set up password to some predefined value to all users that have their password in old format, without issuing SET PASSWORD for each one of them, you can do

SET SESSION old_passwords=FALSE;
UPDATE mysql.user 
   SET password = PASSWORD('<some predefined value>')
 WHERE LENGTH(password) = 16;
FLUSH PRIVILEGES;

Note: before doing any updates make sure you a have solid backup. Just in case.