Create User In MySQL 

Syntax of the Create User statement:

CREATE USER  [ IF NOT EXISTS ] account name IDENTIFIED BY 'password' ;

In the syntax first we mention account after the key word CREATE USER. The account name consists of two parts First is username and second is hostname separated by @ sign.


Hostname part in account name is optional if we removed it then the user can connect from any host. Account without hostname is


If username and hostname contain special characters like space or - etc then you need to quote both separately.


IF NOT EXISTS option is create USER only if users not exist.

Note: CREATE USER statement create a new user without any privileges. By using GRANT statement you grant the privilege to user.

Create User In MySQL 

1). By using MySQL client tool connect to MySQL server.

mysql  -u  root  -p

2). Enter the password of root account and hit enter button.

Enter password: ********

3). Now show users from current MySQL server by using

mysql>  select   user  from  mysql.user;

4). Now create new user called as alex.

mysql> create user alex@localhost identified by 'secure1pass !' ;

5). Show all users again by using

mysql>  select   user  from  mysql.user;

Now you see the user alex in user list. The user alex is successfully created.

6). Open new session and login MySQL again as alex.

mysql  -u  alex  -p

Now enter the password for user alex and hit enter button.

7). Show the databases that alex has access:

mysql> show databases;

Here is the list of databases that alex can access:

| Database                       |
|  information_schema | 
 1 row in set (0.01 sec)

8). Go to the session of the user root and create a new database called bobdb:

mysql> create database alexdb;

9). Select the database alexdb:

mysql> use alexdb;

10). Create a new table called lists:

mysql> create table lists( 
-> id int auto_increment primary key, 
-> todo varchar(100) not null, 
-> completed bool default false);

Note that when you press Enter, instead of showing the mysql> command, the mysql tool shows the -> that accepts new clause of the statement.

11). Grant all privileges on the alexdb to alex:

mysql> grant all privileges on alexdb.* to alex@localhost;

Note that you will learn how to grant privileges to a user in the GRANT tutorial.

 12).Go to the alex’s session and show databases:

mysql> show databases;

Now, alex can see the alexdb:

| Database                      | 
| alexdb                           |
| information_schema | 
2 rows in set (0.00 sec)

13). Select the database alexdb:

mysql> use alexdb;

show the tables from the alexdb database:

mysql> show tables;

The user alex can see the lists table:

| Tables_in_alexdb | 
| lists                         | 
1 row in set (0.00 sec)

14). Insert a row into the lists table:

mysql> insert into lists(todo) values('Learn MySQL');

15). Query data from the lists table:

mysql> select * from lists;

This is the output:

| id | todo                     | completed | 
| 1  | Learn MySQL     | 0                  | 
1 row in set (0.00 sec)

So the user alex can do everything in the alexdb database.

16). Disconnect from the MySQL Server from both sessions:

mysql> exit

In this tutorial, we learned how to use the MySQL CREATE USER to create a new user in the database server.