MySQL
SECURITY

Changing the root Password in MySQL: A Guide

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.

Tools used in the tutorial
Tool Description Link
Dbvisualizer DBVISUALIZER
TOP RATED DATABASE MANAGEMENT TOOL AND SQL CLIENT
MySQL logo MySQL
THE MYSQL DATABASE

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’;

Changing the Password of a root user in MySQL Server
Changing the Password of a root user in MySQL Server

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:

  1. Why are you changing the password?
  2. Do you have a system advising you when it’s time to change the passwords pertaining to the users in your database?
  3. How often do you change passwords? Do you rotate passwords? Why?

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:

  1. Always change passwords frequently. “A lot of time has passed” is a good answer. “A MySQL security plugin has told me to do so” is also a very good answer. With that being said, “Because I want to” is a good answer, too.
  2. For you to understand what we’re talking about here, we have in mind MySQL security plugins — specifically, the password_validation plugin. This plugin lets you set an expiration date for your passwords and has many other options that can be used to improve your user security within your MySQL database. If you don’t have it enabled, enable it immediately. For those of you saying “but I don’t want my password policies to be too [strict|mundane|insert other argument here]”, keep in mind that the password validation policy can be modified to your liking.
  3. Passwords should be changed often and they shouldn’t be rotated (i.e. you shouldn’t use a password that you’ve used before) — if that password gets compromised, say goodbye to your database. The same goes for websites that require a login — always use unique passwords for every website you have an account on. Of course, that’s easier said than done, but password managers like LastPass, KeePass, or 1Password are a very great place to start.

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:

  1. validate-password=FORCE_PLUS_PERMANENT
  2. validate_password_policy=MEDIUM
  3. validate_password_check_user_name=1

These options will:

  1. Make the password validation plugin permanent in your MySQL Server installation.
  2. Enable a MEDIUM password strength policy (you can also set it to LOW or HIGH.)
  3. Reject any password that matches your user name in a forward or reverse order for higher security.

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:

  1. Logging in to your MySQL Server: mysql -uroot -p[password]
  2. Stopping the MySQL Server from running: sudo service mysqld|mariadb stop
  3. Creating a password file and adding the ALTER USER query into the file.
  4. Running MySQL anew by running this command (the “&” makes the command run infinitely — you just need to replace the username with your username and the file name with your file name): sudo mysqld --init-file=/home/[username]/file-you-just-created &

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,

Dbvis download link img
About the author
LukasVileikisPhoto
Lukas Vileikis
Lukas Vileikis is an ethical hacker and a frequent conference speaker. He runs one of the biggest & fastest data breach search engines in the world - BreachDirectory.com, frequently speaks at conferences and blogs in multiple places including his blog over at lukasvileikis.com.
The Table Icon
Sign up to receive The Table's roundup
More from the table
Title Author Tags Length Published
title

SQL EXPLAIN: The Definitive Tool to Optimize Queries

author Antonello Zanini tags MySQL POSTGRESQL SQL 12 min 2024-10-29
title

Everything You Need to Know About SQL Constraints: The What, Why, and How

author Lukas Vileikis tags BEGINNER MySQL SQL 8 min 2024-10-28
title

Database Replication in MySQL: Ensuring Data Consistency and Availability

author Ochuko Onojakpor tags Database replication MySQL 12 min 2024-10-21
title

A Complete Guide to the MySQL Boolean Type

author Antonello Zanini tags MySQL 8 min 2024-10-17
title

MySQL Backup and Recovery Best Practices: A Guide

author Lukas Vileikis tags BIG DATA MySQL OPTIMIZATION SECURITY SQL 7 min 2024-10-15
title

SQL Not Equal Operator: Definitive Guide with Examples

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 6 min 2024-10-14
title

MySQL DISTINCT Clause Explained: Why, How & When

author Lukas Vileikis tags BEGINNER MySQL SQL 7 min 2024-10-10
title

Counter in MySQL: Counting Rows with Ease

author Lukas Vileikis tags MySQL SQL 8 min 2024-10-03
title

SQL OFFSET: Skipping Rows in a Query

author Antonello Zanini tags MySQL ORACLE POSTGRESQL SQL SQL SERVER 7 min 2024-09-30
title

The MySQL RENAME COLUMN Statement: How, What, and Why

author Lukas Vileikis tags MySQL SQL 6 min 2024-09-26

The content provided on dbvis.com/thetable, including but not limited to code and examples, is intended for educational and informational purposes only. We do not make any warranties or representations of any kind. Read more here.

Cookie policy

We use cookies to ensure that we give you the best experience on our website. However you can change your cookie settings at any time in your browser settings. Please find our cookie policy here ↗