How to create a superuser in MySQL?

This guide will walk you through the steps to create a new user in MySQL and make it a super user 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' 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.

New to TablePlus? It’s a modern, native tool with an elegant GUI that allows you to simultaneously manage multiple databases such as MySQL, PostgreSQL, SQLite, Microsoft SQL Server and more. 