In this tutorial, you will learn how to change user’s password, rename user account, lock/unlock user account and drop a user account in MySQL Server 8.

Change Users Password

Using an admin account to change other MySQL users password, can use following statements:

1. SET PASSWORD
2. ALTER USER.

Suppose you are connecting to MySQL server with root account, and want to change password for codercms account.

Method One: Use SET PASSWORD statement to change user codercms password to new123_:

mysql> set password for 'codercms'@'%'='new123_';

Method Two: Use ALTER USER statement to change user codercms password to abc123_:

mysql> alter user 'codercms'@'%' identified by 'abc123_';

Change Current Connecting User Password

If you are connecting MySQL server with codercms account, and you want to change it’s password, use following statement:

mysql> set password='abc123';

Rename User

Following statement will rename user name from codercms to codercms2:

rename user 'codercms'@'%' to 'codercms2'@'%';

Output:

Lock User Account

Following statement will lock the user account codercms:

mysql> alter user 'codercms2'@'%' account lock;

Then use following statement to check users status:

select user,host,account_locked from mysql.user;

Output:

If you try to connect to MySQL server with locked codercms account, you will be prompted that Account is locked.

Drop or Delete User Account

Use following statement to drop account codercms if it is useless:

mysql> drop user 'codercms2'@'%';

Summary

In this tutorial, you have learned that how to change user’s password, rename user account, lock/unlock user account and drop a user account in MySQL Server 8.