Three Ways To Change User’s MySQL/MariaDB Password in Linux

MySQL/MariaDB is an opensource database management software, which is very famous, user friendly and widely used for data storage.

This will allow the user to create a database, database user, grant different permission based on their requirements and you can access it anywhere.

If you have forgotten your MySQL user password, if you would like to change a password for a user, you can reset it by following the steps in this article.

The process is quite simple and works on these operating systems.

We already had written many of articles about MySQL/MariaDB. These can be instantly accessible by navigating to the below url.

MySQL supports these password-management capabilities. If you want you can implement this.

  • Password expiration, to require passwords to be changed periodically.
  • Password reuse restrictions, to prevent old passwords from being chosen again.
  • Password verification, to require that password changes also specify the current password to be replaced.
  • Dual passwords, to enable clients to connect using either a primary or secondary password.
  • Password strength assessment, to require strong passwords.

To do so, we need to check some prerequisites before performing this. These are listed below.

Check whether your root login detail is working or not. If not, refer the following article to reset it.

# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.3.15-MariaDB Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

Once you get into it, check list of available database in your system. This output would tell you the user’s database is currently available in system or not.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| demodb             |
| information_schema |
| mysql              |
| performance_schema |
| testdb             |
| testdb1            |
+--------------------+
6 rows in set (0.001 sec)

Finally check the user name which you are going to change the password.

MariaDB [(none)]> select user,host from mysql.user;
+----------+-----------+
| user     | host      |
+----------+-----------+
| root     | 127.0.0.1 |
| root     | ::1       |
| demouser | localhost |
| root     | localhost |
+----------+-----------+
4 rows in set (0.000 sec)

Note: In these examples, we are going to change demouser password.

Method-1: How To Change User’s MySQL/MariaDB Password in Linux Using UPDATE Syntax?

UPDATE is a DML statement that modifies rows in a table.
This also, assigns a password to a MySQL user account.

Syntax:

UPDATE mysql.user SET Password=PASSWORD('New-Password') WHERE User='DB-UserName' AND Host='localhost';

Example:

MariaDB [(none)]> UPDATE mysql.user SET Password=PASSWORD('pass123') WHERE User='demouser' AND Host='localhost';
Query OK, 1 row affected (0.003 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Flush the privileges, which makes the new changes to take effect.

MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.001 sec)

Method-2: How To Change User’s MySQL/MariaDB Password in Linux Using SET PASSWORD Syntax?

The SET PASSWORD statement assigns a password to a MySQL user account.

It may also include a password-verification clause that specifies the account current password to be replaced, and a clause that manages whether an account has a secondary password. ‘auth_string’ and ‘current_auth_string’ each represent a cleartext (unencrypted) password.

Syntax:

SET PASSWORD FOR 'DB-UserName'@'localhost' = PASSWORD('New-Password');

Example:

MariaDB [(none)]> SET PASSWORD FOR 'demouser'@'localhost' = PASSWORD('dbpass123');
Query OK, 0 rows affected (0.000 sec)

Flush the privileges, which makes the new changes to take effect.

MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.001 sec)

Method-3: How To Change User’s MySQL/MariaDB Password in Linux Using ALTER USER Syntax?

The ALTER USER statement modifies MySQL accounts. It enables authentication, role, SSL/TLS, resource-limit, and password-management properties to be modified for existing accounts. It can also be used to lock and unlock accounts.

Syntax:

ALTER USER 'DB-UserName'@'localhost' IDENTIFIED BY 'New-Password';

Example:

MariaDB [(none)]> ALTER USER 'demouser'@'localhost' IDENTIFIED BY 'mypass123';
Query OK, 0 rows affected (0.001 sec)

Flush the privileges, which makes the new changes to take effect.

MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.001 sec)

About Magesh Maruthamuthu

Love to play with all Linux distribution

View all posts by Magesh Maruthamuthu

Leave a Reply

Your email address will not be published. Required fields are marked *