MySQL password hashing

Whenever you upgrade your MySQL installation, make sure to upgrade any client that uses it.

In some cases, clients that use a version prior to 4.1 will have a problem authenticating against the MySQL database if the latter has a post 4.1 version.

The trick is that after 4.1 (i.e. 4.11 and up), MySQL changed the way it stores the passwords in the user table inside the mysql system database.

Password hashes are now 41 bytes long instead of the old 16 bytes.

So for example, if your MySQL server is 5.0, while your php-mysql library is 4.1, your web applications will fail to connect to the database. As such, it is recommended that you upgrade the client.

In any case, MySQL offers a way to change the hash back into the old format. For the sake of argument, assume the user in question is john, and you want to be able to connect using password dummy. In this case, connect to your MySQL server from the prompt (SSH and use ‘mysql -u root -p mysql’ on linux, or go to your mysql/bin windows directory and execute the same query), then issue the following queries:

update user set Password=OLD_PASSWORD(‘dummy’) where User=’john’;
flush privileges;



the OLD_PASSWORD() function will generate the old 16 bytes hash. The first query will eventually update the user password to use this hash. The second query is necessary in order for the MySQL service to re-read the new user privileges.


PS: if your root password is not working, refer as well to the guide on resetting it.


Alternatively, if your database has many users and you didn’t keep track of them, you can use the following query and it will return usernames that are using the new hash


select distinct(User) from User where LENGTH(Password)!=41;