How to Create a User in MySQL: Simple Steps

To create a user in MySQL, use the CREATE USER 'username'@'host' IDENTIFIED BY 'password'; command. This adds a new user with a password and host specification to your MySQL server.

Syntax

The CREATE USER statement adds a new user account in MySQL. Here is the basic syntax:

• 'username': The name of the new user.
• 'host': The host from which the user can connect (use '%' for any host).
• IDENTIFIED BY 'password': Sets the password for the user.

sql

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

Example

This example creates a user named alice who can connect from any host with the password MyPass123.

sql

CREATE USER 'alice'@'%' IDENTIFIED BY 'MyPass123';

Output

Query OK, 0 rows affected (0.01 sec)

Common Pitfalls

Common mistakes when creating users include:

• Forgetting to specify the host, which defaults to 'localhost'.
• Using weak or empty passwords.
• Not granting privileges after creating the user, so the user cannot do anything.
• Trying to create a user that already exists without using IF NOT EXISTS.

sql

/* Wrong: Missing host, defaults to localhost only */
CREATE USER 'bob'@'localhost' IDENTIFIED BY 'pass';

/* Right: Specify host explicitly */
CREATE USER 'bob'@'localhost' IDENTIFIED BY 'pass';

Quick Reference

Command	Description
CREATE USER 'user'@'host' IDENTIFIED BY 'password';	Create a new user with password
DROP USER 'user'@'host';	Remove a user from MySQL
GRANT ALL PRIVILEGES ON db.* TO 'user'@'host';	Give user full access to a database
FLUSH PRIVILEGES;	Reload privilege tables after changes

Key Takeaways

Use CREATE USER with 'username'@'host' and a password to add a new MySQL user.

Always specify the host to control where the user can connect from.

Set a strong password to keep your database secure.

Remember to grant privileges after creating the user to allow actions.

Use IF NOT EXISTS to avoid errors when creating users that may already exist.