How to Change MySQL or MariaDB User Password on Ubuntu Linux

There may be a time when you want to change a database user password. Some reasons for changing MySQL or MariaDB database user passwords might be to ensure a strong password, to ensure the account is compromised, or to do some house cleaning.

This brief tutorial shows students and new users how to change MySQL or MariaDB database user passwords to something substantial and unique, which might help improve your system security.

This post applies to MySQL and MariaDB database servers since both use the same underlying code.

MariaDB is a drop-in replacement for MySQL. So, if you decide to switch from MySQL to MariaDB, you should just be able to do it without impacting applications.

When you’re ready to learn how to change database users’ passwords, follow the steps below:

Before changing the MySQL user password, you may want to consider the following:

• The account you want to update
• Applications, if any, the account is used for connecting to the database
• and how strong the password should be

When you’re ready, continue.

MySQL provides various statements you can use to change a user’s password, including the UPDATE, SET PASSWORD, and ALTER statements.

These statements are different but perform the same functions.

As shown above, there are many ways to change MySQL user passwords. One way to change a user’s password is to use the UPDATE statement to update the user table of the mysql database.

Use the UPDATE statement to change the password.

For example, if you want to change the admin user password to something unique like password_1234 on the local database server, you run the SQL statement below:

USE mysql;
UPDATE user SET password = PASSWORD('password_1234') WHERE user = 'admin' AND host = 'localhost';
FLUSH PRIVILEGES;

The FLUSH PRIVILEGES  statement to reload privileges from the grant table in the mysql database.

The SQL commands above will change the admin user password to password_1234

If you use MySQL 5.7.6+, you must use the authentication_string column in the UPDATE statement. So, run the statement below to change/update the MySQL user password:

USE mysql;
UPDATE user SET authentication_string = PASSWORD('password_1234') WHERE user = 'admin' AND host = 'localhost';
FLUSH PRIVILEGES;

Again, the commands above only work with MySQL 5.7.6 and up.

Use the SET PASSWORD statement to change the password

You can also use the SET PASSWORD  statement to change the MySQL user password.

Run the SQL statement below to change the admin password:

USE mysql;
SET PASSWORD FOR 'admin'@'localhost' = PASSWORD('password_1234');

Or use the statement below for MySQL 5.7.6 up

USE mysql;
SET PASSWORD FOR 'admin'@'localhost' = 'password_1234';

Use the ALTER USER statement to change the password

Finally, you can use the ALTER USER statement to change the MySQL user password. To do that, run the commands below:

USE mysql;
ALTER USER 'admin'@'localhost' IDENTIFIED BY = 'password_1234';

Always flush privileges when changing the password so the table can be reloaded.

That’s it!

These are some of the ways to change MySQL users’ passwords. This might come in handy if you update MySQL user passwords on your server.

Enjoy!