How to create a superuser in MySQL 8?

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:

• ALL PRIVILEGES – a full root access to the databases. If no database is specified, it has global access across the system.
• CREATE – create new tables or databases
• DROP – delete tables or databases
• DELETE – delete rows from tables
• INSERT – insert rows into tables
• SELECT – use the SELECT command to read through databases
• UPDATE – update table rows
• GRANT OPTION – grant or remove other users’ privileges

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' 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:

FLUSH PRIVILEGES;

And all the changes will take effect immediately.