Renaming an User in Mysql or MariaDB

<<Back to MySQL & MariaDB How To Page

How to rename an user in Mysql or MariaDB.

There are many possible ways to rename an user in maria DB but in this post I will introduce you with rename command

Syntax :

RENAME USER 'username'@'host' TO 'new_username'@'host';

MariaDB [(none)]> select user,host from mysql.user where user like '%anand%';
+--------------+---------------------------+
| user         | host                      |
+--------------+---------------------------+
| anand_tiwari | d-test-pyx%.test.com |
| anand_tiwari | localhost                 |
+--------------+---------------------------+
2 rows in set (0.00 sec)
I want to rename my account anand_tiwari@d-test-pyx%.test.com to anand_tiwari@d-test-pyx01.test.com
MariaDB [(none)]> rename user 'anand_tiwari'@'d-test-pyx%.test.com' to 'anand_tiwari'@'d-test-pyx01.test.com'

Renaming multiple users using the RENAME USER statement:

To rename multiple statement it is obious to write an script to generate rename script instead of writing rename statement for individual user.

The same you can achieve as below

Generating rename user script

 select concat('rename user ','\'',user,'\'','@','\'',host,'\'',' to ','\'',user,'\'','@','\'','q-test-pxy01.test.com','\';') from mysql.user where host like 'd-test-pxy\%%';

here I am generating the rename script for all those users having 'd-test-pxy%' in its host and renaming them to 'q-test-pxy01.test.com'.