Commands

Command syntax will vary between MySQL Versions

====================================================================================

User Management

Show all users and hosts

SELECT user, host FROM mysql. user;

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

Add User

CREATE USER 'name'@'localhost' IDENTIFIED BY 'password.';

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

Granting Privileges

Available privileges:

GRANT

CREATE,ALTER,DROP,CREATE INSERT,Allows UPDATE,the DELETE,user SELECT,to REFERENCES,create new databases and tables. ALTER Allows the user to alter (modify) existing tables. DROP Allows the user to drop (delete) databases and tables. INSERT Allows the user to insert new rows into tables. UPDATE Allows the user to update existing rows in tables. DELETE Allows the user to delete rows from tables. SELECT Allows the user to read (select) data from tables. REFERENCES Allows the user to create foreign key constraints when defining tables. RELOAD Allows the user to execute the FLUSH statement, which reloads various server configurations and clears or refreshes caches.

Grant specific privilege ( or multiple comma separated):

GRANT PRIVILEGEPRIVILEGETYPE ON database.table TO 'username'@'host';
FLUSH PRIVILEGES;

or

Grant all:

GRANT ALL ON database.table TO 'username'@'host';
FLUSH PRIVILEGES;

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

Delete a user

DROP USER 'name'@'host';

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

Set user password

Change user password

ALTER USER 'user'@'host' IDENTIFIED BY 'NewPassword';
FLUSH PRIVILEGES;

====================================================================================

Database Management

Create a database

CREATE DATABASE name;

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

Delete a database

DROP DATABASE name;

====================================================================================