Create User in MySQL

The CREATE USER statement allows to create a new user in the MySQL server. Users are stored in the mysql.user system table. A newly created user has no privileges. The password is specified by IDENTIFIED BY clause.

CREATE USER myuser@localhost IDENTIFIED BY 'pwd123';

MySQL account name contains username and hostname seperated by the @ sign.

username@hostname

The hostname is optional. The user can connect from any host if hostname was omitted. Omitting the hostname from the account name is the same as using % sign for the hostname.

CREATE USER myuser IDENTIFIED BY 'pwd123';
-- equivalent to:
CREATE USER myuser@'%' IDENTIFIED BY 'pwd123';

We need to quote the username and hostname if they contains special characters. We can use backticks (`), single quotation marks ('), or double quotation marks (").

CREATE USER 'my-user'@'test-host' IDENTIFIED BY 'pwd123';

The IF NOT EXISTS clause defines that the new user should be created only if it does not exist.

CREATE USER IF NOT EXISTS myuser@localhost IDENTIFIED BY 'pwd123';

Leave a Comment

Your email address will not be published. Required fields are marked *