1. First, you have to log in with the root user, which has the CREATE USER privilege

Run this command to create a new user with a password:

CREATE USER 'username'@'localhost' IDENTIFIED BY 'the_password';

At this point, the new user has no permission over the databases. The next thing to do is to grant privileges to the new user.

2. Make it a superuser

To make this new user a superuser, we have to provide it with full root access to everything in the database, which means to GRANT ALL PRIVILEGES:

GRANT ALL PRIVILEGES ON *.* TO 'user_name'@'localhost' WITH GRANT OPTION;

It’s done, the new user now has the root-like permission.

3. Then create another account for the same new username

CREATE USER 'username'@'%' IDENTIFIED BY 'the_password';

And grant full root access:

GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' WITH GRANT OPTION;

Both 'username'@'localhost' and 'username'@'%' are superuser accounts with full privileges to do anything.

The 'username'@'localhost' account can be used only when connecting from the local host. The 'username'@'%' account uses the '%' wildcard for the host part, so it can be used to connect from any host.

4. To double check the privileges given to the new user, run SHOW GRANTS command:

SHOW GRANTS FOR username;

5. Finally, when everything is settled, reload all the privileges:

FLUSH PRIVILEGES;

And all the changes will take effect immediately.