[MySQL] Note: Create Admin User

I used to be MongoDB addicted, and now I am quitting from it to MySQL for how many apps we want entangled in that way is a business decision but there are tradeoffs on both sides. MongoDB is easy to build sharding, replica and scaling, but designing an complex E-Commerce platform on it is another story.

First thing first, any MySQL operation requires an existed and verified user account. This Note is about a fast configuration of creating usable users for hello-world purpose.

Remark

— “The percent sign”, “%” means all ip’s so localhost is superfluous — When an new user is created, who has no permission to anything with the databases. In fact, if newuser even tries to login (with the password, password), they will not be able to reach the MySQL shell. GRANT it before we use it.

Login mysql from MySQL client

$ mysql -u root -p

Create User with Password

mysql> CREATE USER ‘peter’@’%’ IDENTIFIED BY ‘1234’;

Delete User

mysql> DROP USER ‘peter’@‘%’;

Grant PRIVILEGES

This command allows to the user to read, edit, execute and perform all tasks across all the databases and tables.

REVOKE [type of permission] ON [database name].[table name] FROM ‘[username]’@‘%’;

mysql> GRANT ALL PRIVILEGES ON *.* TO ‘peter’@’%’;

Revoke PRIVILEGES

mysql> REVOKE ALL PRIVILEGES ON *.* FROM ‘peter’@‘%’;

Reload all the privileges

mysql> FLUSH PRIVILEGES;

You may also like

• MySQL Handbook
• [MySQL] Note: Fast Setup and running in Node.js
• [MySQL] Note: Create Admin User
• [MySQL] Note: Database CURD
• [MySQL] Note: Row CURD
• [MySQL] Note: Relation: 1–1, 1-n, n-n, nest

Reference:

https://www.phpini.com/mysql/mysql-add-new-users-databases-privileges

http://shaocian.blogspot.tw/2012/11/mysql.html

https://www.digitalocean.com/community/tutorials/how-to-create-a-new-user-and-grant-permissions-in-mysql