Mysql – Changing the administrative user on thesql from `root` to something else

MySQLmysqladmin

Just installed mysql on Ubuntu –

sudo apt-get install mysql-server
sudo apt-get install mysql-client

As part of the installation process, I provided a password for the root user.

I can now log in with 

mysql -u root -p

But I'd love some other user besides root to own the mysql administration. I'm coming over from the world of Postgres and have never used MySQL before, so I'm wondering how I could change this user.

Thanks!

EDIT

This is based off a recommendation from the WordPress installation guide:

It is a safer practice to choose a so-called "mortal" account as your mysql admin, so that you are not entering the command "mysql" as the root user on your system. (Any time you can avoid doing work as root you decrease your chance of being exploited.)

Best Answer

Connect to MySQL as -u root, then

First, see what root has:

SHOW GRANTS;

Then add a new user, similar to the root:

GRANT ALL PRIVILEGES ON *.* TO 'foobar'@'localhost' IDENTIFIED BY ('long secure pwd') WITH GRANT OPTION;

where foobar is the "mortal" mentioned in your quote.

Then re-login as foobar to verify it. Then get rid of root (or at least emasculate it). Perhaps this

REVOKE ALL *.* FROM root@localhost;

Then verify with

SHOW GRANTS FOR root@localhost;
SELECT user, host FROM mysql.user WHERE user = 'root';

The first should show only GRANT USAGE, which lets root in, but without any privileges. The second checks to see of root can get in from any other "host". If there are any ways in, we can discuss zapping them.

Related Question