MySQL stores user’s password in a crypto format. It wasn’t too long ago that the default length of the crypto password was stored in a 16 character length. More recently, MySQL has changed their password length capability scheme to up to 41 characters.
Geez, How Do I Tell What my Password Length Is?
Remember, it isn’t your passwords actual length, it is the length of the MySQL crypto key of your password. Whenever you set a password for a user, MySQL converts it into a crypto key and stores it in the mysql.user table.
Here is how you can tell your MySQL Password Length
First, find out if your MySQL is using the new or old password format.
| mysql> SHOW VARIABLES LIKE ‘old_passwords’; +—————+——-+ | Variable_name | Value | +—————+——-+ | old_passwords | ON | +—————+——-+ |
If the variable “old_passwords” is set to “ON”, then, obviously, it is using the old password scheme.
Another 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 | … |
In the example above, all of the passwords are set to 16 characters.
Change MySQL Password Length
To change the user to having the exact same password but stored in the newer longer format, do this:
| mysql> SET SESSION old_passwords=FALSE; mysql> SET PASSWORD FOR ‘visitlogger’@’64.55.129.%’=PASSWORD(‘<put password here>’); mysql> flush privileges; |
Then verify:
| 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 | +—————-+—————–+——————–+ |
That’s it. We have converted our password crypto storage length in our MySQL database.
You Can Now Increase the Length of your User’s Passwords in MySQL
Latest posts by Jeff Staten (see all)
- Configure Your HP Procurve Switch with SNTP - May 5, 2015
- Configuring HP Procurve 2920 Switches - May 1, 2015
- Troubleshooting Sendmail - November 28, 2014
Great post Jeff. My question is can the 16 bit hash be converted to the 41 bit hash without each user logging in and resetting their password? It will be a pain to change all of them manually.
Gio,
If you want to convert to 41 bits passwords, the passwords have to be reset somehow. If you don’t want the users to have to do it themselves, you could setup a script to reset them yourself and then tell them what their password will be. Migrating to 41 bits isn’t actually required by MySQL itself, you could always start it up with –old-passwords.
To change the user to having the exact same password but stored in the newer longer format ….
Does this work, or do the users now have a 41 character hash of the space in password(‘ ‘)?
I need to make it more clear that they need to type their password into that spot between the quotes. Thanks for the proof reading!