How can I change root username in MySQL?

MySQLMySQLi Database

To change the root username in MySQL, you need to use UPDATE and SET command. The syntax is as follows −

UPDATE user set user = ’yourNewUserName’ WHERE user = ’root’;

To understand the above syntax, let us switch the database to MySQL using USE command.

The query is as follows to switch the database.

mysql> use mysql;
Database changed

Now list all the users from MySQL.user table. The query is as follows −

mysql> select user from MySQL.user;

The following is the output −

+------------------+
| user             |
+------------------+
| Manish           |
| User2            |
| mysql.infoschema |
| mysql.session    |
| mysql.sys        |
| root             |
| Adam Smith       |
| User1            |
| am               |
+------------------+
9 rows in set (0.04 sec)

Look at the sample output, we have username ‘root’. Change the username root to some other name using UPDATE command.

Let us change the username ‘root’ to ‘myRoot’. The query is as follows −

mysql> update user set user = 'myRoot' where user = 'root';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0

List all users from MySQL.user table to see the username ‘root’ have been changed to ‘myRoot’. The query is as follows to list all users from MySQL.user table.

mysql> select user from MySQL.user;

The following is the output −

+------------------+
| user             |
+------------------+
| Manish           |
| User2            |
| myRoot           |
| mysql.infoschema |
| mysql.session    |
| mysql.sys        |
| Adam Smith       |
| User1            |
| am               |
+------------------+
9 rows in set (0.00 sec)

Look at the above table, ‘root’ username has been changed to ‘myRoot’.

raja
Published on 01-Feb-2019 13:11:35
Advertisements