Rename a User with the RENAME USER Statement in MySQL

Rename an account when its user name or host identity needs to change, for example:

• Replace an account name that no longer matches its owner or purpose.
• Change the host component after moving the client.

Renaming is not a substitute for responding to a leaked password. Rotate the credential with ALTER USER, lock the account if needed, and update the applications that use it.

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 transfers the account’s privileges, but it does not rewrite stored-object DEFINER attributes. MySQL normally rejects the rename if the old account is the DEFINER of a routine, view, trigger, or event. Before renaming, have an administrator check the object’s DEFINER fields in information_schema.routines, information_schema.views, information_schema.triggers, and information_schema.events, then migrate those objects deliberately. See MySQL’s RENAME USER restrictions.

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'@'localhost' IDENTIFIED BY RANDOM PASSWORD;
   CREATE USER 'test_user2'@'localhost' IDENTIFIED BY RANDOM PASSWORD;
   CREATE USER 'test_user3'@'localhost' IDENTIFIED BY RANDOM PASSWORD;
   

   MySQL returns a separate generated password for each account. Save each one securely. This syntax requires MySQL 8.0.18 or later.

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       | localhost |
   | test_user2       | localhost |
   | test_user3       | localhost |
   | testuser         | %         |
   | mysql.infoschema | localhost |
   | mysql.session    | localhost |
   | mysql.sys        | localhost |
   | root             | localhost |
   +------------------+-----------+

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

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

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   | localhost |
   | test_user2       | localhost |
   | test_user3       | localhost |
   | testuser         | %         |
   | mysql.infoschema | localhost |
   | mysql.session    | localhost |
   | mysql.sys        | localhost |
   | root             | localhost |
   +------------------+-----------+

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

7. Rename 'test_user2'@'localhost' to 'test_user2_new'@'localhost' and 'test_user3'@'localhost' to 'test_user3_new'@'localhost':

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

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   | localhost |
   | test_user2_new   | localhost |
   | test_user3_new   | localhost |
   | testuser         | %         |
   | mysql.infoschema | localhost |
   | mysql.session    | localhost |
   | mysql.sys        | localhost |
   | root             | localhost |
   +------------------+-----------+

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

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.