Finding and resolving "new" passwords in a old_password-encoded MySQL database
September 30, 2009
This article is for those folks who, for some reason, had to revert to a MySQL database using the old_passwords option, but who may have had new passwords created using the version 4.1-and-above password protocols.
(For specifics on forcing MySQL to revert to the old-style password algorithms, see our article on "mysqlnd cannot connect to MySQL 4.1+ using old authentication" errors, Part Two: Fixing MySQL Password Authentication on PHP before version 5.3.)
The new password format uses a longer data string in the user tables. Find them using query
SELECT Host, User, Password FROM mysql.user WHERE LENGTH(Password) > 16;
For each user, re-assign a password using the OLD_PASSWORD() function and either SET PASSWORD or UPDATE, i.e.
SET PASSWORD FOR 'some_user'@'some_host' = OLD_PASSWORD('newpassword');
UPDATE mysql.user SET Password = OLD_PASSWORD('newpassword') WHERE Host =
'some_host' AND User = 'some_user';
Please note, again, that sticking to the old password protocols is not usually desirable. It might be easier, in that passwords employ one-way encryption, so cannot be reconstructed from the data in the user tables, and therefore can't be converted into the new format.
However, if you need maximum security, use the new password protocols.