create a new user in MySQL and make it a superuser with root-like access to the databases.
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. There are several privileges a user can have:
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;
'username'@'%' are superuser accounts with full privileges to do anything.
'username'@'localhost' the 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:
And all the changes will take effect immediately.