15

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.

Adrian Ber
  • 20,474
  • 12
  • 67
  • 117

2 Answers2

41

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';
Adrian Ber
  • 20,474
  • 12
  • 67
  • 117
  • 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? – Khom Nazid Apr 17 '19 at 13:10
  • It shouldn't affect normal functioning of MySQL. – Adrian Ber Apr 17 '19 at 15:10
  • 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. – Ωmega Oct 08 '19 at 12:49
  • if use mysql workbench, you need to deactivate the safe update mode: Edit -> Preferences -> Sql Editor then uncheck Safe update. https://stackoverflow.com/questions/11448068/mysql-error-code-1175-during-update-in-mysql-workbench – YuAn Shaolin Maculelê Lai Dec 18 '19 at 03:24
  • table user doesnt exists – Toma Tomov Mar 25 '20 at 19:25
1

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.

electr0sheep
  • 67
  • 8
  • 1
    This happens only after MySQL 5.7, it was not the case when I posted my answer. I updated my answer. – Adrian Ber May 27 '16 at 09:16
  • Changed again in [MySQL-8.0](https://dev.mysql.com/doc/refman/8.0/en/resetting-permissions.html) best refer back to the manual if you ever need to do this. – danblack Dec 19 '19 at 09:25