Rename a User with the RENAME USER Statement in MySQL

In some specific cases, you may want to rename an existing user, for example:

• This user account has been leaked.
• Change the username to a more meaningful username

To rename one or more existing user accounts from the MySQL server, use the RENAME USER statement.

MySQL RENAME USERStatement Syntax

The RENAME USER statement is used to rename one or more existing accounts. The following is the basic syntax of the RENAME USER statement :

RENAME USER
    user_account TO new_user_account
    [, user_account2 TO new_user_account2]
    [, ...];

Here:

• You should specify the existing user account to be renamed before the TO keyword.
• You should specify the new user account after the TO keyword.

You cannot use an existing user account as a new user account or you will receive an error message.

RENAME USER operation will transfer all privileges of the old user to the new user. However, it does not drop or invalidate database objects that depend on the old user.

MySQL RENAME USER Examples

Let’s take some examples using MySQL RENAME USER statement.

Follow the steps below to run a MySQL RENAME USER example.

1. Connect to the MySQL server using the mysql client tool:

   mysql -u root -p
   

   Enter the password for the root account and press Enter:

   Enter password: ********
   

2. Show all users of the current MySQL server :

   SELECT user, host FROM mysql.user;
   

   Here is the current user list:

   +------------------+-----------+
   | user             | host      |
   +------------------+-----------+
   | root             | %         |
   | sqliz            | %         |
   | test_role1       | %         |
   | test_role2       | %         |
   | testuser         | %         |
   | mysql.infoschema | localhost |
   | mysql.session    | localhost |
   | mysql.sys        | localhost |
   | root             | localhost |
   +------------------+-----------+

3. Create 3 new users: test_user1, test_user2, and test_user3,

   CREATE user 'test_user1'@'%' IDENTIFIED by 'SqLiZ9879123!';
   CREATE user 'test_user2'@'%' IDENTIFIED by 'SqLiZ9879123!';
   CREATE user 'test_user3'@'%' IDENTIFIED by 'SqLiZ9879123!';
   

4. Show all users of the current MySQL server:

   SELECT user, host FROM mysql.user;
   

   Here is the current user list:

   +------------------+-----------+
   | user             | host      |
   +------------------+-----------+
   | root             | %         |
   | sqliz            | %         |
   | test_role1       | %         |
   | test_role2       | %         |
   | test_user1       | %         |
   | test_user2       | %         |
   | test_user3       | %         |
   | testuser         | %         |
   | mysql.infoschema | localhost |
   | mysql.session    | localhost |
   | mysql.sys        | localhost |
   | root             | localhost |
   +------------------+-----------+

5. Use RENAME USER to rename 'test_user1'@'%' to 'test_user1_new'@'%':

   RENAME USER 'test_user1'@'%' TO 'test_user1_new'@'%';
   

6. Display all users of the current MySQL server:

   SELECT user, host FROM mysql.user;
   

   Here is the current user list:

   +------------------+-----------+
   | user             | host      |
   +------------------+-----------+
   | root             | %         |
   | sqliz            | %         |
   | test_role1       | %         |
   | test_role2       | %         |
   | test_user1_new   | %         |
   | test_user2       | %         |
   | test_user3       | %         |
   | testuser         | %         |
   | mysql.infoschema | localhost |
   | mysql.session    | localhost |
   | mysql.sys        | localhost |
   | root             | localhost |
   +------------------+-----------+

   Here, the user 'test_user1'@'%' has been renamed to 'test_user1_new'@'%'.

7. rename 'test_user2'@'%' to 'test_user2_new'@'%' and rename 'test_user3'@'%' to 'test_user3_new'@'%':

   RENAME USER 'test_user2'@'%' TO 'test_user2_new'@'%';
   RENAME USER 'test_user3'@'%' TO 'test_user3_new'@'%';
   

8. Show all users of the current MySQL server:

   SELECT user, host FROM mysql.user;
   

   Here is the current user list:

   +------------------+-----------+
   | user             | host      |
   +------------------+-----------+
   | root             | %         |
   | sqliz            | %         |
   | test_role1       | %         |
   | test_role2       | %         |
   | test_user1_new   | %         |
   | test_user2_new   | %         |
   | test_user3_new   | %         |
   | testuser         | %         |
   | mysql.infoschema | localhost |
   | mysql.session    | localhost |
   | mysql.sys        | localhost |
   | root             | localhost |
   +------------------+-----------+

   Here, 'test_user2'@'%' has been renamed to 'test_user2_new'@'%' and 'test_user3'@'%' has been renamed to 'test_user3_new'@'%'.

Conclusion

In this article, you learned how to use MySQL RENAME USER statement to rename one or more user accounts in MySQL.

You can rename a user account in one statement, or you can rename multiple user accounts in one statement.

In addition to changing the username, you may also want to change a user’s password or drop a user.