Adding a New MySQL User Made Easy: A Complete Guide

Creating a dedicated MySQL user is one of the simplest ways to manage database access safely and cleanly. Instead of sharing the root account, you can give each application, developer, or service its own login with only the permissions it needs.

This guide walks through the practical steps for adding a new MySQL user from the command line, granting privileges, checking access, updating permissions, and handling common permission issues. It also highlights security practices that help protect your databases while keeping user management straightforward.

#	Preview	Product	Price
1		MySQL Crash Course: A Hands-on Introduction to Database Development	$38.18	Buy on Amazon
2		Learning MySQL: Get a Handle on Your Data	$36.38	Buy on Amazon
3		MySQL Cookbook: Solutions for Database Developers and Administrators	$56.73	Buy on Amazon
4		Database Programming Using Python and MySQL	$8.00	Buy on Amazon
5		PHP & MySQL: Server-side Web Development	$27.19	Buy on Amazon

As an Amazon Associate I earn from qualifying purchases.

Prerequisites for Creating a MySQL User

Before you create a new MySQL user, make sure you have access to a MySQL or MariaDB server and an account with permission to manage users. In most setups, this means signing in as root or as another administrative account that has privileges such as CREATE USER, GRANT OPTION, and the ability to modify privileges. If you are working on a shared hosting account, a managed database service, or a production system, you may need to use the provider’s control panel or request access from an administrator instead of connecting directly as root.

You should also know where the MySQL server is running. For a local development machine, the host is often localhost or 127.0.0.1. For a remote database server, you need the server hostname or IP address, the MySQL port, and any network access requirements such as VPN connectivity, firewall allowlisting, or SSH tunneling. MySQL commonly listens on port 3306, but cloud platforms and hardened environments may use a different port or restrict direct access entirely.

#1 Best Overall

Sale

MySQL Crash Course: A Hands-on Introduction to Database Development

$38.18

Buy on Amazon

Information to gather before running commands

• Admin username: An account that can create users and grant privileges.
• Admin password or authentication method: This may be a password, Unix socket authentication, or cloud-managed identity flow.
• New username: The MySQL account name you want to create, such as app_user or reporting_user.
• Allowed host: The location the user can connect from, such as localhost, a specific application server IP, or a controlled subnet.
• Target database: The database or schema the user should access.
• Required permissions: The minimum privileges needed, such as SELECT, INSERT, UPDATE, or EXECUTE.

The host portion of a MySQL account is especially because MySQL identifies users as a combination of ‘username’@’host’. For example, ‘app_user’@’localhost’ is different from ‘app_user’@’%’. The percent sign allows connections from any host, which can be convenient during testing but is usually too broad for production. A safer approach is to restrict the account to the exact application server IP address or to localhost when the application and database run on the same machine.

Finally, confirm which MySQL version you are using, because authentication plugins and password policies can differ between MySQL 5.7, MySQL 8.0, MariaDB, and cloud-managed variants. MySQL 8.0 commonly uses caching_sha2_password, while older clients may expect mysql_native_password. If your application uses an older database driver, checking compatibility before creating the user can prevent login errors later. It is also helpful to decide on a strong password format in advance and review any active password validation rules so the user creation command succeeds on the first attempt.

Connecting to the MySQL Server

Before you can create a new MySQL user, you need to open a session on the MySQL server with an account that already has sufficient administrative privileges. In most environments, this means using the root MySQL account or another account that has the CREATE USER, GRANT OPTION, and related privilege-management permissions. The connection method depends on where MySQL is running: on your local machine, on a remote database server, inside a container, or as part of a managed hosting platform.

On a Linux server where MySQL is installed locally, the most common method is to connect from the terminal using the MySQL client. If your MySQL root account uses socket authentication, you may be able to connect with elevated system privileges instead of typing a database password:

sudo mysql

If the MySQL root account or administrator account requires a password, use the -u option to specify the username and -p to prompt for the password:

mysql -u root -p

After running the command, enter the password when prompted. For security, MySQL does not display password characters as you type. If the login succeeds, you will see the MySQL prompt, usually shown as mysql>. From there, you can run SQL statements to create users, grant privileges, inspect existing accounts, and update authentication settings.

Connecting to a remote MySQL server

If the database is hosted on another server, include the host address with the -h option. You may also need to specify the port with -P if MySQL is not listening on the default port, 3306:

mysql -u admin_user -p -h 203.0.113.10 -P 3306

For managed database services, the host value is often a DNS endpoint rather than an IP address, such as mysql-prod.example.rds.amazonaws.com. Make sure your client machine is allowed through the server firewall, cloud security group, VPN, or private network route. If the server is configured to require encrypted connections, you may also need SSL options supplied by your provider, such as a CA certificate path.

Useful connection checks

Once connected, confirm that you are using the intended account and server before making changes. These quick checks help prevent accidentally creating users on a development database when you meant to update production, or vice versa:

• Check the connected user: SELECT USER(), CURRENT_USER();
• Check the server version: SELECT VERSION();
• Check the active host and port externally: confirm the values used with -h and -P.
• List available databases: SHOW DATABASES;

The difference between USER() and CURRENT_USER() can be useful when troubleshooting permissions. USER() shows the login identity you attempted to use, while CURRENT_USER() shows the MySQL account that authenticated and whose privileges are being applied. This matters because MySQL accounts are defined by both username and host, such as ‘app_user’@’localhost’ or ‘app_user’@’%’.

Common connection problems

If you receive Access denied for user, check that the username, password, and host portion of the MySQL account match how you are connecting. For example, an account created as ‘admin’@’localhost’ cannot be used from a remote workstation. If you see Can’t connect to MySQL server, verify that the MySQL service is running, the hostname is correct, the port is open, and the server is listening for TCP connections. On Linux systems, service status can often be checked with systemctl status mysql or systemctl status mysqld, depending on the distribution.

After you have a working administrative session and have confirmed you are connected to the correct server, you are ready to create the new MySQL user account with the appropriate username, host restriction, authentication method, and password.

Creating a New MySQL User

Once you are connected to the MySQL server with an account that has user-management privileges, you can create a dedicated account for an application, developer, reporting tool, or administrator. In MySQL, a user is identified by both a username and a host value. This means 'app_user'@'localhost' and 'app_user'@'%' are treated as separate accounts, even though the username is the same.

Rank #2

Sale

Learning MySQL: Get a Handle on Your Data

$36.38

Buy on Amazon

The standard way to create a user is with the CREATE USER statement. The following example creates a user named app_user who can connect only from the same server where MySQL is running:

CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'StrongPassword123!';

If the user needs to connect from another server, replace localhost with the appropriate host or IP address. For example, this creates a user that can connect from a specific application server:

CREATE USER 'app_user'@'192.0.2.15' IDENTIFIED BY 'StrongPassword123!';

You may also see examples using '%' as the host, which allows the user to connect from any host:

CREATE USER 'app_user'@'%' IDENTIFIED BY 'StrongPassword123!';

Use % carefully. It is convenient for testing or dynamic environments, but it increases exposure if network access to MySQL is not tightly controlled. In production, prefer a specific hostname or IP address whenever possible.

Choosing the Right Host Value

After running CREATE USER, MySQL creates the account but does not automatically grant it access to databases or tables. A newly created user can authenticate, but without privileges it will not be able to read, write, or manage data. Privileges are assigned separately with GRANT, which is covered in the next section.

Handling Existing Users

If you try to create an account that already exists, MySQL returns an error similar to ERROR 1396 (HY000): Operation CREATE USER failed. To avoid this, use CREATE USER IF NOT EXISTS when you want the command to succeed even if the account is already present:

CREATE USER IF NOT EXISTS 'app_user'@'localhost' IDENTIFIED BY 'StrongPassword123!';

Be careful with this form when managing passwords. If the user already exists, the password may not be changed depending on the MySQL version and authentication settings. To update an existing user’s password, use ALTER USER instead of relying on CREATE USER.

Authentication Plugin Considerations

Modern MySQL versions commonly use caching_sha2_password as the default authentication plugin. Older clients may require mysql_native_password to connect successfully. If a legacy application cannot authenticate, you can create the user with a specific plugin:

CREATE USER 'legacy_user'@'localhost'
IDENTIFIED WITH mysql_native_password BY 'StrongPassword123!';

Use the default authentication method unless you have a compatibility requirement. After the account is created, you can list matching users with:

SELECT user, host, plugin FROM mysql.user WHERE user = 'app_user';

Granting Privileges to the User

After creating a MySQL user, the account can authenticate but usually cannot do much until privileges are assigned. Privileges define what the user can access and what actions they can perform, such as reading tables, inserting rows, creating databases, or managing other users. In most cases, you should grant only the permissions needed for the application or person to do their job, rather than giving broad administrative access.

The basic privilege syntax uses the GRANT statement. For example, to allow a user named app_user from localhost to read, insert, update, and delete data in all tables of a database named app_db, run:

GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'app_user'@'localhost';

The part before ON lists the allowed actions. The part after ON defines the scope. Using app_db.* applies the privileges to every table in that database, while app_db.orders would apply them only to the orders table. The user portion, 'app_user'@'localhost', must match the user and host exactly as they were created.

Rank #3

Sale

MySQL Cookbook: Solutions for Database Developers and Administrators

$56.73

Buy on Amazon

Common privilege examples

• Read-only access: GRANT SELECT ON app_db.* TO 'report_user'@'localhost';
• Application read/write access: GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'app_user'@'localhost';
• Allow table changes: GRANT CREATE, ALTER, DROP ON app_db.* TO 'dev_user'@'localhost';
• Grant access to a single table: GRANT SELECT ON app_db.customers TO 'analyst'@'localhost';

For a development environment, you may sometimes grant all privileges on a specific database:

GRANT ALL PRIVILEGES ON app_db.* TO 'dev_user'@'localhost';

This gives the user full control over objects inside app_db, but not over the entire MySQL server. Avoid using GRANT ALL PRIVILEGES ON *.* unless you are intentionally creating a highly trusted administrative account. Server-wide privileges can affect every database and may include sensitive operations depending on the permissions granted.

Privilege scope examples

In modern MySQL versions, privilege changes made with GRANT usually take effect immediately. If you manually modify grant tables, which is uncommon for normal user management, run FLUSH PRIVILEGES; afterward. Once privileges are granted, the next step is to verify that the user can connect and perform only the intended actions.

Verifying User Access and Permissions

After creating a MySQL user and granting privileges, confirm that the account can connect from the expected host and perform only the actions you intended. This step helps catch common mistakes such as granting access to the wrong host, forgetting privileges on a specific database, or creating an account that exists but cannot authenticate successfully.

Check the User Entry in MySQL

From an administrative MySQL session, you can inspect the account in the mysql.user table. This confirms that the username and host combination exists. MySQL treats 'app_user'@'localhost' and 'app_user'@'%' as different accounts, so always verify both parts.

SELECT user, host
FROM mysql.user
WHERE user = 'app_user';

If no rows are returned, the user was not created under that name. If the host value is not what you expected, the user may fail to connect from your application server or workstation. For example, an account created as 'app_user'@'localhost' can connect only from the same machine running MySQL, while 'app_user'@'192.168.1.%' allows connections from matching network addresses.

Review Granted Privileges

Use SHOW GRANTS to view the exact permissions assigned to the account. This is the most direct way to confirm database-level, table-level, or global privileges.

SHOW GRANTS FOR 'app_user'@'localhost';

A typical result might show access limited to one database:

GRANT SELECT, INSERT, UPDATE
ON `app_db`.*
TO `app_user`@`localhost`

Check that the privileges match the role of the user. An application account might need SELECT, INSERT, UPDATE, and DELETE on its own database, but it usually should not have CREATE USER, GRANT OPTION, SUPER, or broad access to *.*.

Test Logging In as the New User

The best verification is to connect as the new user and run a few real queries. From the command line, log in with the account you created:

mysql -u app_user -p

If the user must connect to a remote server, include the host:

mysql -u app_user -p -h mysql.example.com

Once connected, confirm the active account and available databases:

Rank #4

Database Programming Using Python and MySQL

$8.00

Buy on Amazon

SELECT USER(), CURRENT_USER();
SHOW DATABASES;

• USER() shows the username and host used to initiate the connection.
• CURRENT_USER() shows the MySQL account whose privileges are being applied.
• SHOW DATABASES should list only the databases the user is allowed to see.

Test Allowed and Blocked Actions

Run a small set of queries that matches the privileges you granted. For a user with read and write access to app_db, you might test selecting from a table and inserting a harmless test row. Also try an action the user should not be allowed to perform, such as reading from another database or creating a new user.

If permissions do not appear to work, recheck the exact username and host, then run SHOW GRANTS again. In modern MySQL versions, privilege changes usually take effect immediately after GRANT or REVOKE. If you manually changed grant tables, run FLUSH PRIVILEGES; from an administrative account before testing again.

Changing Passwords or Updating User Privileges

After a MySQL account is created, you may need to rotate its password, expand access for a new application feature, or remove permissions that are no longer required. These changes are handled with standard SQL statements from an administrative MySQL session, typically using an account such as root or another user that has the required administrative privileges.

Changing a MySQL User Password

For MySQL 5.7.6 and later, including MySQL 8.x, the preferred way to change a password is ALTER USER. Always specify the user name and host together, because MySQL treats 'appuser'@'localhost' and 'appuser'@'%' as separate accounts.

ALTER USER 'appuser'@'localhost' IDENTIFIED BY 'NewStrongPassword123!';
FLUSH PRIVILEGES;

In most modern MySQL versions, FLUSH PRIVILEGES is not required after ALTER USER, but running it does not hurt in typical administrative workflows. If you are maintaining an older MySQL installation, you may see examples using SET PASSWORD instead:

SET PASSWORD FOR 'appuser'@'localhost' = 'NewStrongPassword123!';

If password validation is enabled, MySQL may reject weak passwords with an error. In that case, choose a longer password with mixed case letters, numbers, and symbols, or check the active password policy using:

SHOW VARIABLES LIKE 'validate_password%';

Updating User Privileges

To add permissions, use GRANT just as you did when setting up the account. For example, if an application user needs permission to modify data in one database but should not manage schema changes, grant only the required data-level privileges:

GRANT SELECT, INSERT, UPDATE, DELETE
ON app_database.*
TO 'appuser'@'localhost';

To remove privileges, use REVOKE. The following example removes permission to delete rows while leaving the user able to read, insert, and update data:

REVOKE DELETE
ON app_database.*
FROM 'appuser'@'localhost';

You can also replace broad privileges with narrower ones. For instance, if a user currently has access to every table in a database but only needs one table, revoke the database-level permissions and grant table-specific permissions:

REVOKE ALL PRIVILEGES
ON app_database.*
FROM 'reportuser'@'localhost';

GRANT SELECT
ON app_database.monthly_reports
TO 'reportuser'@'localhost';

Checking the Updated Permissions

After changing privileges, confirm the account’s current access with SHOW GRANTS. This is the fastest way to verify that MySQL applied the permission set you intended:

SHOW GRANTS FOR 'appuser'@'localhost';

If the user is already connected, some privilege changes may not fully affect the existing session until the user reconnects. Have the application or user disconnect and sign in again, then test the required action directly, such as reading from a table, inserting a row, or connecting from the expected host.

• Use ALTER USER for password changes on current MySQL versions.
• Use GRANT to add permissions for a database, table, or specific operation.
• Use REVOKE to remove permissions that are too broad or no longer needed.
• Use SHOW GRANTS to confirm the final privilege state.

Security Best Practices for MySQL Users

After creating MySQL users and assigning privileges, the next step is keeping those accounts secure over time. MySQL permissions should be treated as part of your application’s security boundary: a compromised database user can expose data, alter records, or disrupt services. The safest approach is to create narrowly scoped accounts, use strong authentication, and review access regularly instead of relying on broad privileges that are never revisited.

Best Value

Sale

PHP & MySQL: Server-side Web Development

$27.19

Buy on Amazon

Use the least privilege model

Grant each user only the permissions required for its task. For example, an application that only reads product data should not have INSERT, UPDATE, DELETE, DROP, or GRANT OPTION. A reporting user may only need SELECT on specific tables, while a migration user may temporarily need schema-changing privileges such as ALTER or CREATE. Avoid granting privileges globally with *.* unless the account truly administers the entire server.

• Prefer database-level or table-level grants over global grants.
• Do not use ALL PRIVILEGES for application accounts unless there is a clear operational need.
• Reserve GRANT OPTION for trusted administrator accounts only.
• Remove temporary privileges immediately after maintenance or deployment work is complete.

Use strong passwords and secure authentication

Every MySQL account should have a unique, high-entropy password. Do not reuse operating system passwords, developer passwords, or passwords shared between environments. If your MySQL version supports password validation, enable the validate_password component or plugin and configure a policy that rejects weak passwords. For service accounts, store credentials in a secure secrets manager or protected configuration file rather than hard-coding them in application source code.

Where possible, restrict each user to the host it actually connects from. The host portion of a MySQL account is part of the identity, so 'app_user'@'localhost', 'app_user'@'10.0.1.%', and 'app_user'@'%' are different accounts. Avoid 'user'@'%' for production systems unless broad network access is unavoidable and protected by firewalls, private networking, and strong credentials. For remote connections, require encrypted transport with TLS so passwords and query data are not sent in clear text over the network.

Separate accounts by role and environment

Do not share one MySQL user across every application, developer, job, and script. Separate accounts make auditing easier and limit the damage if one credential is leaked. A web application, background worker, backup process, analytics tool, and human administrator should typically use different accounts with different grants. Development, staging, and production should also have separate credentials so access to a lower environment does not automatically provide access to production data.

Audit and rotate access regularly

Review MySQL accounts and grants on a recurring schedule. Remove unused users, rotate passwords after staff changes or suspected exposure, and check for wildcard hosts or excessive privileges. Commands such as SHOW GRANTS FOR 'user'@'host'; help confirm what an account can do, while querying the mysql.user table can help identify old or overly broad accounts. Consistent auditing keeps your permission model aligned with the applications and people that currently need database access.

Frequently Asked Questions

Do I need to run FLUSH PRIVILEGES after creating a MySQL user?

Usually, no. If you use standard statements such as CREATE USER, GRANT, ALTER USER, or REVOKE, MySQL updates the privilege tables automatically. You typically only need FLUSH PRIVILEGES if you manually modify the grant tables directly, which is not recommended for normal user management.

What is the difference between ‘user’@’localhost’ and ‘user’@’%’ in MySQL?

'user'@'localhost' can connect only from the same server where MySQL is running. 'user'@'%' can connect from any host, assuming firewalls, bind address settings, and network access allow it. For better security, use the most specific host possible, such as 'appuser'@'10.0.1.%' instead of allowing access from everywhere.

How can I check what permissions a MySQL user has?

Use SHOW GRANTS FOR 'username'@'host'; to view the privileges assigned to a specific account. Make sure the host part matches the account you created, because 'appuser'@'localhost' and 'appuser'@'%' are separate MySQL accounts. You can also test access by logging in as that user and trying the expected database operations.

What should I do if the new MySQL user gets “Access denied” when logging in?

First, confirm the username, password, and host match the account you created. Then check whether the user is connecting from localhost, an IP address, or another hostname, because MySQL treats these as different account sources. If the login works but database actions fail, run SHOW GRANTS as an administrator and verify the user has privileges on the correct database or table.

Should I grant ALL PRIVILEGES to a new MySQL user?

Only grant ALL PRIVILEGES when the account truly needs full control over a specific database, such as an application owner account in a controlled environment. For most users, grant only the permissions they need, such as SELECT, INSERT, UPDATE, and DELETE. Avoid granting global privileges or GRANT OPTION unless the user is meant to administer other accounts.

Bottom Line

Creating a new MySQL user is straightforward once you understand the core workflow: create the account, grant only the privileges it needs, apply the changes, and test the login. Whether you are using SQL from the command line or adjusting permissions for an application, careful privilege management helps keep your database secure and reliable.

As a next step, review your existing MySQL users and remove or tighten any unnecessary access. If something does not work as expected, check the username, host, authentication method, and granted privileges before expanding permissions.

Quick Recap

SaleBestseller No. 1

MySQL Crash Course: A Hands-on Introduction to Database Development

$38.18

SaleBestseller No. 2

Learning MySQL: Get a Handle on Your Data

$36.38

SaleBestseller No. 3

MySQL Cookbook: Solutions for Database Developers and Administrators

$56.73

Bestseller No. 4

Database Programming Using Python and MySQL

$8.00

SaleBestseller No. 5

PHP & MySQL: Server-side Web Development

$27.19

Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.