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 strong password, the account is compromised or just doing some house cleaning.
This brief tutorial shows students and new users how to change MySQL or MariaDB database user password to something strong and unique which might help improve your system security.
This post applies to both MySQL and MariaDB database servers. Since both use the same underlying code.
In fact, MariaDB is a drop-in replacement for MySQL. So in the future 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 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 that you can use to change the password of a user including the UPDATE , SET PASSWORD, and ALTER statements.
These statements are different but perform the same functions.
As shown above, there many ways to change MySQL user password. One way to change a user’s password is to use the UPDATE statement to update the user table of the mysql database.
Use UPDATE statement to change 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 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 SET PASSWORD statement to change password
You can also use the SET PASSWORD statement to change 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 password
Finally, you can use the ALTER USER statement to change MySQL user password. To do that, run the commands below:
USE mysql; ALTER USER 'admin'@'localhost' IDENTIFIED BY = 'password_1234';
Alway flush privileges when done making changes to password so the table can be reloaded.
These are some of the ways to change MySQL user’s passwords. This might come in handy if you need to update MySQL user passwords on your server.
You may also like the post below: