How To Create User in MySQL

Last updated on September 21st, 2021 at 11:10 am

Many times you may need to create new user in MySQL to manage user access for your database. In this article we will look at how to create user in MySQL using MySQL CREATE USER command.

 

How To Create User in MySQL

Here are the steps to create user in MySQL. We will use MySQL CREATE USER statement to create user account.

Here’s the syntax of CREATE USER query

CREATE USER [IF NOT EXISTS] username@hostname
IDENTIFIED BY 'password';

In the above query, you need to specify the username and hostname as username@hostname after CREATE USER keywords.

Username is the name of the user (e.g test_user) and hostname is the host (e.g 127.0.0.1) from which the user can connect to your database.

If your users access database from same host IP as that of database server, then you can use 127.0.0.1 or localhost as hostname

password is the password (e.g pass123) for this new user.

You can optionally use keyword IF NOT EXISTS to create new user only if it doesn’t exist.

 

Bonus Read : How to Create Database in MySQL

 

MySQL CREATE USER Examples

Here’s an example of MySQL CREATE USER query to create new user.

CREATE USER 'test_user'@127.0.0.1
IDENTIFIED BY 'pass123';

The above user can connect only from 127.0.0.1 (also known as localhost), that is, from same host where your database is located. This new user won’t be able to connect to your database from any other IP (other laptops, workstations, etc).

If you want your user to be able to connect from remote IP address (e.g 54.245.23.21), specify it as your hostname

CREATE USER 'test_user'@54.245.23.21
IDENTIFIED BY 'pass123';

 

You can also use domain names and endpoints in place of hostname. Here’s an example of database username for Amazon RDS. We will use RDS endpoint (mysql–instance1.123456789012.us-east-1.rds.amazonaws.com) for hostname.

 

CREATE USER 'test_user'@mysql–instance1.123456789012.us-east-1.rds.amazonaws.com 
IDENTIFIED BY 'pass123';

 

Bonus Read : How to Create Table in MySQL

 

If you omit hostname from the above query, it will allow new user to connect from any host.

Here’s an example

CREATE USER 'test_user'@
IDENTIFIED BY 'pass123';

In the above query, you can also use username@%  (e.g test_user@%)

 

If username and hostname contain special characters then you must specify username & hostname in single quotes(‘), double quotes(“) or backticks (`).  Here’s an example.

CREATE USER 'test_user#1'@127.0.0.1
IDENTIFIED BY 'pass123';

 

Bonus Read : How to Add Foreign Key in MySQL

 

Please note, the new user created will not have any privileges, that is, they won’t be able to query any database. For that, you need to grant user privileges using GRANT statement.

Here’s an example of granting all privileges to new user, for database sample_db

mysql> grant all privileges on sample_db.* to 'test_user#1'@127.0.0.1;

 

Hopefully, now you can create user in MySQL.

 

Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!