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
.
account_name
is the name of the user, and will be used in MySQL server connection.host_name
is the name of the host from which the user connects to MySQL server.- The
host_name
of the user account is optional, an account name without a host name is equivalent toaccount_name@%
. - You need to quote the
account_name
andhost_name
if they contains special characters like-
. - 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
.
One Reply to “Create a new MySQL User Account”