MySQL Database basic administrative commands

Hi folks, I am going to write some basic MySQL administrative commands in this post. So let's get started-

Create New User in MySQL Database
Login to MySQL with super user preferably root or user with full rights
Execute below command
CREATE USER 'username'@'hostname' IDENTIFIED BY 'YourPassword';
Example: 
CREATE USER 'fmsuser'@'192.168.0.121' IDENTIFIED BY 'Pass1@210617';

Drop User in MySQL Database
Login to MySQL with super user preferably root or user with full rights.
Execute below command
DROP USER 'username'@'hostname'
Example: 
DROP USER 'fmsuser'@'192.168.0.121'

List All Users in MySQL Database 
Login to MySQL with super user preferably root or user with full rights.
Execute below command
SELECT * FROM mysql.user

Create New MySQL Database
Login to MySQL with super user preferably root or user with full rights.
Execute below command
CREATE DATABASE newDBName DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
Note: Charset and collate can be set as per the requirement.

Permission Grant to User
This includes create, alter, drop for database, tables, routine, event, view etc.

GRANT ALL PRIVILEGES ON *.* TO 'user'@'host'
Example- 
GRANT ALL PRIVILEGES ON *.* TO 'user123'@'%'

Check User Grants
SHOW GRANTS FOR CURRENT_USER();

Grant Privilege to MySQL User on Particular Database
Login to MySQL with super user preferably root or user with full rights.
Execute below command
GRANT ALL PRIVILEGES ON newDBName. * TO 'username'@'hostname'
FLUSH PRIVILEGES;

Example: 
GRANT ALL PRIVILEGES ON usersDB. * TO 'fmsuser'@'192.168.0.121'
FLUSH PRIVILEGES;

FLUSH PRIVILEGES will help to refresh/reload the user privileges.

Generic Syntax:  
GRANT [type of permission] ON [database name].[table name] TO ‘[username]’@'localhost’;

ALL PRIVILEGES- as we saw previously, this would allow a MySQL user all access to a designated database (or if no database is selected, across the system)
CREATE- allows them to create new tables or databases
DROP- allows them to them to delete tables or databases
DELETE- allows them to delete rows from tables
INSERT- allows them to insert rows into tables
SELECT- allows them to use the Select command to read through databases
UPDATE- allow them to update table rows
GRANT OPTION- allows them to grant or remove other users' privileges

Revoke Privilege
Login to MySQL with super user preferably root or user with full rights.
Execute below command

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

Note: Now if you'd like to connect your database from anywhere/any machine the use '%' in place of hostname.

Update MySQL DB Table via MySQL-Workbench
Updating mysql database table rows is not allowed directly via mysql workbench. It throws MySQL-1175 error. In order to overcome this issue, kindly run below magic sql first prior to running the update query in a session-
SET SQL_SAFE_UPDATES = 0;