How to change root username in MySQL

Question

I'm running MySQL in Ubuntu, default installation.

How can I change the username from root to another one, let's say admin? Preferably from the command line.

Absurdity

Answer 1

After connecting to MySQL run

use mysql;
update user set user='admin' where user='root';
flush privileges;

That's it.

If you also want to change password, in MySQL < 5.7, run

update user set password=PASSWORD('new password') where user='admin';

before flush privileges;. In MySQL >= 5.7, the password field in the user table was renamed to authentication_string, so the above line becomes:

update user set authentication_string=PASSWORD('new password') where user='admin';

Absurdity

Will this affect the normal functioning of MySQL, which may require root user for something? Or is it safe to change that username for MySQL hardening? – Discretionary

It shouldn't affect normal functioning of MySQL. – Absurdity

Just wondering, if there is any option or workaround to set a user name alias, being able to use both names root and admin for the same root account. – Moleskins

if use mysql workbench, you need to deactivate the safe update mode: Edit -> Preferences -> Sql Editor then uncheck Safe update. #11448568 – Petulah

table user doesnt exists – Messene

Answer 2

I just wanted to say that for me, there was no column 'password'.

To change password, the correct field was authentication_string

So the command is

update user set authentication_string=PASSWORD('new password') where user='admin';

I'm no MySQL expert, so I'm not sure exactly why, but what I said is correct, at least in my case.

Unpaged

This happens only after MySQL 5.7, it was not the case when I posted my answer. I updated my answer. – Absurdity

Changed again in MySQL-8.0 best refer back to the manual if you ever need to do this. – Durwyn

Answer 3

For example, you can change the username root to john as shown below. *I recommend to use RENAME USER ... because directly modifying mysql.user is sometimes problematic. *You probably need to log in with the user root:

RENAME USER 'root'@'localhost' to 'john'@'localhost';

Or:

UPDATE mysql.user SET User='john' WHERE User='root';
FLUSH PRIVILEGES;

Then, you can log in to MySQL:

mysql -u john -p

Whipperin