Changing a MySQL password is remarkably complicated! In a typical scenario where we would run the database on one box, and the apps accessing it on other boxes, naively changing a user’s the password may create a situation where it appears that we locked ourselves out, depending on where we try to log in from. Some apps may work and some others won’t with the new password. In a live system this can be a serious issue.
Because MySQL password changes are domain specific, the password is not necessarily changed for all domains where that user is defined. Depending on which command we use, and what domain parameters we pass in, we may accidentally change the passwords only partially for a subset of the boxes. This will make it appear that we locked ourselves out but this is not the case. The confusion can be easily fixed by forcing a password change for all domains. We will show here how to do this for the root user everywhere.
Many ways to change a password
MySQL has multiple unnecessarily complicated ways to change its passwords. All of them, under the hood change the password in the mysql.user table. Within MySQL, a user is defined per domain. So when changing the password, we don’t necessarily change it for that user at any domain.
Depending on which command we use, we either explicitly have to specify the domain we change the password for, or the more “user friendly” ones will try to hide that complexity from us, and just change it for localhost.
The command that got me into trouble: SET PASSWORD
I picked the following command because it looked the cleanest. It appeared to work. After changing the password I logged back into mysql from the command line with mysql -u root -p <your_new_password>, and it let me get in.
SET PASSWORD = PASSWORD('your_new_password');
Don’t be fooled, this is a dangerous command! It will only change our password locally, so when our apps from other boxes try to log in, they won’t be able to. It is not at all obvious this is what happens, because it won’t tell us.
The MySQL documentation shows a version of this command that will allow specifying the domain but it will still not tell us how to specify all domains
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('your_new_password');
See what happened here? Now we are saying the domain, but explicitly specifying localhost. The result will be exactly the same: partial password change. We can prove this by querying the system tables like this:
mysql> SELECT user,host,password FROM mysql.user;
+------+-----------+-------------------------------------------+
| user | host | password |
+------+-----------+-------------------------------------------+
| root | localhost | <Some Hash String 1> |
| root | % | <Some Hash Sttring 2> |
+------+-----------+-------------------------------------------+
Seeing the above output was what made me realize what is going on: Why do I have two root users, not one? But at least this way we can specify the domain.
Use ALTER USER instead
The MySQL documentation advises against using SET PASSWORD for other reasons as well. Some executions of this command apparently get logged, and so they may reveal the password in the logs. They recommend that you use the following syntax:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'your_new_password';
What eventually worked for me was this. Note how I use ‘%’ for the domain. This is how I’m telling it that I want the change apply to any login as root from any box.
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'your_new_password';
Why is this important?
Because setting the password the wrong way is not a complete failure but a partial one, it is not clear that there is an issue, when it doesn’t work. If we have many applications accessing the database and we only missed a few domain names, we may not find out that we are off line until the phone is ringing with customers complaining that our sites are inaccessible. Therefore after changing a password that your apps use throughout your system to connect to the database, its a good idea to test every single one of them to make sure they still work.
A word about logging in as “root” from anywhere
Any experienced technologist reading this may have issues with the notion that applications are allowed to log in as root and that we enable root access from any box. I use Docker, where most things are run as root in the container, as standard practice. In addition I’m sealed off from the Internet by a firewall. On a traditional box, we would probably not log into mysql as root, certainly not on a publicly addressable box. However, the technique above works for any other account as well, that we might use on a “normal” host.
Conclusion & useful links
This will allow the root account to log in from everywhere with TCP/IP access to the box, with the new password. All your apps will work again, but now using the new password. Also, you will be able to log in locally as well.
See the relevant portion of the MySQL documentation at the MySQL site.