Create User in MySQL

We can create user in MySQL Server by using CREATE USER statement. For each account, CREATE USER creates a new row in the mysql.user system table. To create user in MySQL Server, user must have CREATE USER privileges.

Syntax to Create User in MySQL:

CREATE USER [IF NOT EXISTS]
    user [auth_option] [, user [auth_option]] ...
    DEFAULT ROLE role [, role ] ...
    [REQUIRE {NONE | tls_option [[AND] tls_option] ...}]
    [WITH resource_option [resource_option] ...]
    [password_option | lock_option] ...

user:
    (see Section 6.2.4, “Specifying Account Names”)

auth_option: {
    IDENTIFIED BY 'auth_string'
  | IDENTIFIED WITH auth_plugin
  | IDENTIFIED WITH auth_plugin BY 'auth_string'
  | IDENTIFIED WITH auth_plugin AS 'auth_string'
}

Examples Create User in MySQL:

1. Create a user with password.

CREATE USER kalpesh IDENTIFIED BY 'Kalpesh@321';

2. Create a user with hostname.

CREATE USER 'miller@localhost' IDENTIFIED BY 'Millerh@321';

3. Create User with password expire to 180 day interval.

CREATE USER 'mike'@'localhost'
  IDENTIFIED  BY 'Mike@321'
  PASSWORD EXPIRE INTERVAL 180 DAY;

4. To verify the list of users we have to execute command against mysql.user table.

mysql> select user from mysql.user ;
+------------------+
| user             |
+------------------+
| david            |
| george           |
| johnd            |
| kalpesh          |
| michael          |
| miller@localhost |
| george           |
| mike             |
| mysql.infoschema |
| mysql.session    |
| mysql.sys        |
| root             |
+------------------+