参考： https://tableplus.com/blog/2018/10/how-to-create-a-superuser-in-mysql.html

------------------

How to create a superuser in MySQL?

October 25, 2018

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.


Download TablePlus for Mac.

Not on Mac? Download TablePlus for Windows.

On Linux? Download TablePlus for Linux

Need a quick edit on the go? Download TablePlus for iOS.