MySQL - RENAME USER Statement

---

---

MySQL RENAME USER Statement

You can change the name of the existing user account in MySQL using the RENAME USER Statement. To create a user account, the current account needs to have CREATE USER privilege, or the UPDATE privilege for the MySQL system schema.

Syntax

Following is the syntax of the MySQL RENAME USER Statement −

RENAME USER old_name TO new_name

Where, old_name is the name of the you need to change and new_name is the desired name. While renaming a user you need to make sure that the given old_name exists and there is no user with the desired name.

Example

Assume we have created a user named sample with password 123456. First of all, make sure that you have logged in with a user with admin privileges (root).

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

You can verify the list of users using the following query −

select user from MySQl.user;

Output

Following is the output of the above query −

user
mysql.infoschema
mysql.session
mysql.sys
myuser
openkm
root
sample

Following query changes the name of the above created user.

RENAME USER 'sample'@'localhost' TO 'newUser'@'localhost';

Verification

If you verify the list of users in the database again you can observe the new name of the old user is changed −

select user from MySQl.user;

Output

The above query will produce the following output −

user
mysql.infoschema
mysql.session
mysql.sys
myuser
openkm
root
newUser

Logging with the new user’s name

Open command prompt, browse through the bin folder of MySQL installation folder and execute the command mysql -u new_user_name -p as −

MySQL_Installation_Directorybin>mysql -u sample -p
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.22 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All 
rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective owners.

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