How to Create a Username and Password in MySQL

Creating a new MySQL user with a password

To create a username and password in MySQL, you log into the MySQL command line as the root user, then run a single command that sets both at once. The command is CREATE USER 'username'@'localhost' IDENTIFIED BY 'password'; — replace username and password with your own choices, keep the single quotes, and end with a semicolon.

Before you run this command, you need to be inside MySQL itself. Open your terminal or command prompt, type mysql -u root -p, press Enter, then type the root password when prompted. You will see a mysql> prompt, which means you are ready to create the new user.

After you create the user, you must grant them permission to do anything — a new user has no rights by default. The command GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost'; gives them full access to a specific database. Then type FLUSH PRIVILEGES; to make the change take effect when ready.

Key Takeaways

You must be logged into MySQL as root before you can create a new user, which requires knowing the root password.
The CREATE USER command sets the username and password in one step, but the user has no permissions until you run GRANT.
The @'localhost' part means the user can only connect from the same machine — use @'%' if they need to connect from another computer.
FLUSH PRIVILEGES forces MySQL to reload the permission tables so your new user can log in when ready.

Understanding the parts of the CREATE USER command

The command breaks into three pieces. 'username'@'localhost' is the account identifier — the username is what the person types to log in, and @'localhost' is where they can log in from. If you use @'localhost', they can only connect from the same server. If you use @'192.168.1.5', they can only connect from that specific IP address. If you use @'%', they can connect from anywhere.

The IDENTIFIED BY 'password' part sets the password. MySQL stores it encrypted, so you type the plain password here and MySQL handles the rest. The password is case-sensitive and can include spaces, numbers, and special characters — just keep it inside the single quotes.

The semicolon at the end tells MySQL the command is complete. If you forget it, MySQL will wait on the next line for you to finish. Just type the semicolon and press Enter.

Granting permissions to the new user

A user created with CREATE USER cannot read, write, or delete anything until you give them permission. The GRANT command does this. GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost'; gives them all rights to all tables in one database. Replace database_name with the actual name of the database they need to use.

If you want to be more restrictive, you can grant only specific actions. GRANT SELECT, INSERT, UPDATE ON database_name.* TO 'username'@'localhost'; lets them read, add, and change data, but not delete. GRANT SELECT ON database_name.* TO 'username'@'localhost'; lets them only read. After any GRANT command, run FLUSH PRIVILEGES to set up the change.

If you want the user to have rights across all databases, use GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost'; — the asterisks mean all databases and all tables. This is usually only done for administrative users.

Testing the new user login

After you create the user and grant permissions, test that they can actually log in. Open a new terminal window (do not log out of root yet), and type mysql -u username -p. When prompted, type the password you set. If you see the mysql> prompt, the login worked.

If you get an "Access denied" message, the most common cause is a typo in the username or password. Go back to the root session and check that the username and password match exactly what you typed in the CREATE USER command. Remember that both are case-sensitive.

If the login works but you cannot see the database you granted access to, type SHOW DATABASES; to list what this user can see. If the database is missing, you may have granted permissions to the wrong database name, or the database does not exist yet. Create the database first with CREATE DATABASE database_name;, then grant permissions to it.

Changing a password for an existing user

If you need to change the password for a user who already exists, use ALTER USER 'username'@'localhost' IDENTIFIED BY 'new_password'; then run FLUSH PRIVILEGES. This works the same way as CREATE USER but modifies an existing account instead.

If you forget the root password itself, the recovery process depends on your operating system and MySQL version. On most systems, you can restart MySQL in safe mode without password checks, then set a new root password. This is a separate process — search for "reset MySQL root password" plus your operating system name for exact steps.

Common mistakes and how to fix them

Forgetting the quotes around the username or password causes a syntax error. MySQL needs single quotes around both the account identifier and the password. If you see "Syntax error", check that every string is wrapped in single quotes.

Forgetting FLUSH PRIVILEGES means the new user cannot log in even though the account exists. MySQL keeps permissions in memory, and FLUSH PRIVILEGES reloads them from disk. If a user says they cannot log in right after you create them, run FLUSH PRIVILEGES in the root session.

Creating a user with @'localhost' and then trying to connect from another machine fails silently — the user exists but cannot connect from that location. If you need remote access, recreate the user with @'%' or @'specific.ip.address'. You can also grant the same username different permissions for different locations: one account for @'localhost' and another for @'192.168.1.5'.

Frequently Asked Questions

Can I create a user without a password?

Yes, but it is a security risk. Use CREATE USER 'username'@'localhost'; without the IDENTIFIED BY clause. Anyone who knows the username can log in. This is only safe for testing on a machine no one else can access.

What is the difference between localhost and %?

@'localhost' means the user can only connect from the same machine where MySQL is running. @'%' means they can connect from any IP address. Use @'localhost' for security unless the user needs to connect remotely. If they do, use @'specific.ip.address' instead of @'%' if you know where they will connect from.

Do I need to restart MySQL after creating a user?

No. FLUSH PRIVILEGES reloads the permission tables without restarting. The user can log in when ready after you run FLUSH PRIVILEGES. You only need to restart MySQL if you edit the configuration file itself, not when you create users or change permissions.

What happens if I grant ALL PRIVILEGES to a regular user?

They can read, write, delete, and modify data in the databases you specified, but they cannot create new databases or change MySQL settings. Only the root user can do those things. ALL PRIVILEGES means all data permissions, not all system permissions.

Can I change a username after I create it?

Yes, use RENAME USER 'old_username'@'localhost' TO 'new_username'@'localhost'; then run FLUSH PRIVILEGES. All their permissions transfer to the new username automatically.

This guide is for general information only and is not medical, financial, legal, or other professional advice. For decisions specific to your situation, consult a qualified professional. See our Editorial Policy.