In this tutorial, you will learn how to create a new user in MySQL server using command line.

Syntax

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

A user account in MySQL consists of account name and host name parts, for example: account_name@host_name.

1. account_name is the name of the user, and will be used in MySQL server connection.
2. host_name is the name of the host from which the user connects to MySQL server.
3. The host_name of the user account is optional, an account name without a host name is equivalent to account_name@% .
4. You need to quote the account_name and host_name if they contains special characters like - .
5. The IF NOT EXISTS option will create a new user only when the user does not exist.

Please note that, the user created has no any privileges, you can use grant statement to grant privileges to the user.

You need to connect to the MySQL Server with the account which has all the privileges before trying MySQL statements.

Show Users in MySQL Server

Run following MySQL Statement to show users in MySQL Server:

mysql> select user, host from mysql.user;

Output:

+------------------+-----------+
| user             | host      |
+------------------+-----------+
| small_cms        | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
| small_cms        | localhost |
+------------------+-----------+
6 rows in set (0.00 sec)

Create a new MySQL User

Use following statement to create codercms account, and can only connect to MySQL server from localhost.

mysql> create user codercms@localhost identified by 'abc123_';

If you want the new user account can connect to MySQL server from any host, use following statement.

mysql> create user 'codercms'@'%' identified by 'abc123_';

Output:

Query OK, 0 rows affected (0.00 sec)

Check users list of MySQL, and you will see the new created user account codercms included in the list.

mysql> select user, host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| codercms         | %         |
| small_cms        | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
| small_cms        | localhost |
+------------------+-----------+
7 rows in set (0.00 sec)

Create Multiple User Account in One Statement

Use following statement to create multiple users account in one statement:

mysql> CREATE USER user1@localhost, user2 IDENTIFIED BY 'Init_Password';

Connect to MySQL Server with New Created User Account

Exit mysql command line,

mysql> exit
Bye

Connect to MySQL server with new created account:

$ mysql -u codercms -p

Type password and press ENTER, will get following output if everything goes well:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 8.0.21 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

In this tutorial, you have created a new MySQL user account codercms .