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 'email@example.com 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 22.214.171.124), specify it as your hostname
CREATE USER 'firstname.lastname@example.org 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 'email@example.com 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 'firstname.lastname@example.org;
Hopefully, now you can create user in MySQL.