intro
Changing the root password in MySQL Server is something that concerns many database administrators, no matter whether they’re novices or experienced DBAs. Here’s how to do that, what problems can arise on the way, and how to solve them.
Preface
Every DBA comes across problems during his or her career. The problems are numerous and many of them can be addressed by making sure a proper SQL client is in use — the customers of DbVisualizer can surely attest to that.
With that being said, not all problems can be solved by using a SQL client. Many problems are small and, at first glance, insignificant — but improper dealing with them can be the cause of many issues on the way.
When to Change Passwords in MySQL?
With so many MySQL updates being introduced left, right, and center, it’s easy to get lost in the database world; especially over something so trivial as changing a password. Shouldn’t you change them frequently anyway?
Well, you should — and your passwords should be strong, too — but even when that’s done, you still have so many other things to worry about!
Fortunately, you have SQL clients built precisely to solve these kinds of problems — we’ll give ours a shout a little later, we’d like to direct your attention back to the topic for now.
Answering the question when to change passwords in a DBMS is simple — every once in a while, or whenever you feel like it. That means that changing your password every month isn’t necessary, but once every 90 days might be an option.
How to Change Passwords in MySQL?
Now that you know how often you should rotate your passwords in your DBMS, let’s talk about how to do that too. And, thankfully, doing so is relatively simple: issue one query and you’re done. The SQL query you need to issue looks like so:
ALTER USER ‘user’@’host’ IDENTIFIED BY ‘password’;
Looks simple, huh? With that being said, we’d be lying if we said that issuing a simple query is all you need to worry about. Far from it — when changing your root password, answer the following questions beforehand:
Answering these questions will provide you with a good understanding of your circumstances — and guide you on how to apply tools to deal with the circumstances that may change during time, too. Starting from the top:
Password Security Plugins
Answered all of those questions? Great, move on to installing the password_validation plugin before you perform any password changes. Install it by including validate_password.so in the plugin-load-add option in your my.cnf configuration file and restart MySQL Server. Alternatively, you can also set how often passwords should be changed and what’s their security policy by adding these options:
These options will:
Got the password security plugin set up? Great, restart MySQL (if you don’t have it setup properly, MySQL won’t start and inform you what’s wrong in the error log — make sure you follow all of the aforementioned steps carefully.)
Now you’re all set to change your password.
Changing Your Password — Things to Consider
Before running the ALTER TABLE query to change your root account password, keep in mind that there are various ways you can approach this problem, too.
You can also change your password by:
Alternatively, if you don’t want the hassle of running ALTER TABLE statements all the time, consider shutting down MySQL and then providing a password for it to use within my.cnf instead — do so under the [user] section by providing a username= and a password=. Doing so that way (or via the file as shown) is more secure because by default, all terminal commands can be observed in the CLI.
Last but not least, you should also consider using powerful, community-liked SQL clients like the fully-fledged SQL client built by DbVisualizer: DbVisualizer is the most highly-rated SQL client on the market today and with powerful features, over 50 supported database management systems, and the highest customer satisfaction on the market, it will surely solve all of your database issues at the drop of a hat. Try DbVisualizer today and until next time!
Frequently Asked Questions
What SQL query should be used when changing passwords in MySQL?
Use the ALTER USER query like so:
ALTER USER ‘username’@’host’ IDENTIFIED BY ‘password’;
Is it possible to change my password without running a SQL query?
Yes — you can also do so by forcing MySQL to run a file with your query inside of it or via my.cnf (see examples above.)
Should I use a password validation plugin?
Yes — a password validation plugin will make your passwords stronger and your database harder to breach for an attacker.
Why should I use an SQL client?
SQL clients like the one built by DbVisualizer will provide you with the ability to edit your tables like a spreadsheet, ERD capabilities,
