MySQL - Change Password

MySQL provides an account to each user which is authenticated with a username and a password. The default account in MySQL is a root with no password (One can however set a password to the root using a statement). Similarly, other user-defined accounts can have passwords set explicitly using an SQL statement or can have it system generated by MySQL.

MySQL Change User Password

Just like any other authenticated accounts, MySQL has a provision to change the user password. But one must make sure that there is currently no application being used by the user. If the password is reset without disconnecting the application, the application cannot connect to the server through this user again.

We can change the password for a MySQL user account using the following three SQL statements −

• UPDATE statement

• SET PASSWORD statement

• ALTER USER statement

The UPDATE Statement

The most basic way to change a user's password in MySQL is by using the UPDATE statement. This statement is used to update account details, including the account password, from the 'root' account. But, once the modifications are done using this statement, you must use the FLUSH PRIVILEGES statement to reload privileges from the grant table of the MySQL database.

Syntax

Following is the syntax to change password using the UPDATE statement −

UPDATE mysql.user 
SET authentication_string = PASSWORD(password_string)
WHERE User = user_name AND 
      Host = host_name
FLUSH PRIVILEGES;

Example

Following example demonstrates how to change the password of a user account using the UPDATE statement. Firstly, we are creating a user account "sample" with a password '123456' −

CREATE USER 'sample'@'localhost' IDENTIFIED BY '123456';

Following is the output obtained −

Query OK, 0 rows affected (0.02 sec)

Now, you can verify the list of users using the following query −

SELECT User FROM mysql.user;

The table will be displayed as shown below −

If you have the MySQL version 5.7.6 and later, you can directly modify the mysql.user table with the following query −

UPDATE user
SET authentication_string = PASSWORD('xxxxxx')
WHERE User = 'sample' AND Host = 'localhost';

After executing the above code, we get the following output −

Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

After making changes to user accounts, you need to use the FLUSH PRIVILEGES statement to apply these changes immediately −

FLUSH PRIVILEGES;

The output obtained is as shown below −

Query OK, 0 rows affected (0.01 sec)

The SET PASSWORD statement

The SET PASSWORD statement is used to set a password for a MySQL account. It contains a "password-verification" clause which lets the system know that the current user password needs to be replaced by another.

Syntax

Following is the syntax for the SET PASSWORD statement −

SET PASSWORD FOR username@localhost = password_string;

You can also change the password using SET PASSWORD without using the FOR clause. To use this syntax however, you must already be logged in on the user account you wish to change the password of −

SET PASSWORD = password_string;

Example

Now, using the SET PASSWORD statement, we are changing the password to 'hello' −

SET PASSWORD = 'hello';

Output

Following is the output of the above code −

Query OK, 0 rows affected (0.01 sec)

The ALTER USER Statement

To alter anything regarding a user account in MySQL, including changing passwords, ALTER USER statement is more preferable than SET PASSWORD statement. This statement is not used alone, instead is followed by the IDENTIFIED BY clause to authenticate the new password.

Note that the user must be connected to the MySQL server for this statement to work.

Syntax

Following is the syntax to change the password using the ALTER USER statement −

ALTER USER username IDENTIFIED BY 'password';

Example

Here, we are changing the password of the sample@localhost account to '000000' using the ALTER USER query given below −

ALTER USER sample@localhost IDENTIFIED BY '000000';

Output

Output of the above code is shown below −

Query OK, 0 rows affected (0.01 sec)

The password is now changed. To verify, log in to the sample account again using the new password −

C:\Windows\System32> mysql -u sample -p
Enter password: ******

mysql> 

Changing User password Using a Client Program

Besides using MySQL queries to change the user password in MySQL, we can also use client programs like Node.js, PHP, Java, and Python to achieve the same result.

Syntax

Following are the syntaxes −

$sql = "ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password'";
$mysqli->query($sql);

sql= "ALTER USER username IDENTIFIED BY 'new_password'";
con.query(sql, function (err, result) {
   if (err) throw err;
      console.log(result);
});

String sql = "ALTER USER 'USER_NAME'@'LOCALHOST' IDENTIFIED BY 'NEW_PASSWORD'";
statement.execute(sql);

sql = f"ALTER USER '{username_to_change}'@'localhost' IDENTIFIED BY '{new_password}'";
cursorObj.execute(sql);

Example

Following are the client programs to change the user password in MySQL −

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass);
   if($mysqli->connect_errno ) {
     printf("Connect failed: %s
", $mysqli->connect_error);
     exit();
}
//printf('Connected successfully.
');
$sql = "ALTER USER 'root'@'localhost' IDENTIFIED BY 'password1'";
if($mysqli->query($sql)){
   printf("User password has been changed successfully...!");
}
if($mysqli->error){
   printf("Failed..!" , $mysqli->error);
}
$mysqli->close();

Your password has been changed successfully...!

var mysql = require('mysql2');
var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "Nr5a0204@123"});

  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("--------------------------"); 

  //Listing the users
  sql = "SELECT USER FROM mysql.user;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log("**List of Users:**")
    console.log(result)
    console.log("--------------------------");
  }); 

  sql = "ALTER USER 'sample'@'localhost' IDENTIFIED BY 'tutorials';";
  con.query(sql, function(err){
    if (err) throw err;
    console.log("Password changed Successfully...");
  });
  sql = "FLUSH PRIVILEGES;"
  con.query(sql);
});

Connected!
--------------------------
**List of Users:**
[
  { USER: 'mysql.infoschema' },
  { USER: 'mysql.session' },
  { USER: 'mysql.sys' },
  { USER: 'root' },
  { USER: 'sample' }
]
--------------------------
Password changed Successfully...

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class ChangePassword {
	public static void main(String[] args) {
		String url = "jdbc:mysql://localhost:3306/TUTORIALS";
		String user = "root";
		String password = "password";
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "ALTER USER 'root'@'localhost' IDENTIFIED BY 'password1'";
            st.execute(sql);
            System.out.println("User 'root' password changed successfully...!");    
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}

User 'root' password changed successfully...!

import mysql.connector
# creating the connection object
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password'
)
username_to_change = 'newUser'
new_password = 'passwordSet!'
# Create a cursor object for the connection
cursorObj = connection.cursor()
cursorObj.execute(f"ALTER USER '{username_to_change}'@'localhost' IDENTIFIED BY '{new_password}'")
print(f"Password for user '{username_to_change}' changed successfully.")
cursorObj.close()
connection.close()

Password for user 'newUser' changed successfully.