MySQL User Management Commands

To create a user in MySQL:
CREATE USER 'user_name'@'hostname' IDENTIFIED BY 'password';

Note:All the users of MySQL are stored in the MySQL database inside the user table.

Example 1:
Create a user kaushal on localhost with password test:
CREATE USER 'kaushal'@'localhost' IDENTIFIED BY 'test'

Create a user kishore without password:
CREATE USER 'kishore'@'localhost';

To delete the user:
DROP USER 'username'@'localhost';

Example: DROP USER 'kishore'@'localhost';

To Rename the User:
RENAME USER 'username'@'hostname' TO 'username'@'hostname';

Example: RENAME USER 'kaushal'@'localhost' TO 'iics'@'localhost';

To show all the users of MySQL:
SELECT user FROM mysql.user;

To show User,Password,host of MySQL:
SELECT user,host,password FROM mysql.user;

Note:All the user information such as Hostname, username, password and its privileges for all databases are stored in “mysql” database inside the “user” table.

To reset the password for specific user:
SET PASSWORD FOR 'username'@'hostname' = PASSWORD('newpassword');

Example: SET PASSWORD FOR 'iics'@'localhost' = PASSWORD('hello');

Syntax 2:  UPDATE mysql.user SET password = PASSWORD('test') WHERE user='iics' AND host='localhost';