Creating MySQL Users & Databases

When you install MySQL (a popular open-source relational database that stores data in tables), it starts almost empty. Before any real application can use it, you need to create a database to hold your tables and a user account your app logs in as. The part most tutorials get wrong is the MySQL user@host model — a user is not just a name, it is a name plus the address it is allowed to connect from. This page shows you exactly how to create databases and users, grant the right privileges, and avoid the classic “Access denied” trap, all on Ubuntu.

The user@host model — the most important concept

In MySQL a user account is two parts joined together: the username and the host (the network address the connection comes from). So 'app'@'localhost' and 'app'@'%' are two completely different accounts, even though both are called app. The % is a wildcard meaning “any host”.

Account	Meaning	When to use
'app'@'localhost'	App runs on the same machine as MySQL, connecting over a local socket	The app and database live on one server
'app'@'%'	App may connect from any IP address	App server is separate from the database server
'app'@'10.0.0.5'	App may connect only from that one IP	Tightest control when you know the app’s fixed address

The number-one cause of “Access denied for user ‘app’@‘localhost’” is creating the user as 'app'@'%' but connecting locally — MySQL matches localhost first and finds no such password. Always create the account for the host your app actually connects from, and prefer a specific IP over %.

When to use %: only when the app server’s IP can change or you have several app servers. When NOT to: never use % for an admin account, and never expose it to the public internet without a firewall.

Connecting as the admin user first

To create anything, you first log in as the MySQL root user (the all-powerful admin account). On a default Ubuntu install, root uses auth_socket authentication, meaning you authenticate by already being the Linux root user — no password needed.

sudo mysql

Output:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.39-0ubuntu0.24.04.2 (Ubuntu)

mysql>

You are now at the mysql> prompt where you type SQL commands. Every command ends with a semicolon.

Creating a database

A database is a named container for your tables. Create one with CREATE DATABASE. Always set the character set so your app can store any text (emoji, accented letters) correctly.

CREATE DATABASE shop_app CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Output:

Query OK, 1 row affected (0.01 sec)

You can confirm it exists:

SHOW DATABASES;

Output:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| shop_app           |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

utf8mb4 is the full Unicode character set — use it, not the older utf8, which cannot store 4-byte characters like emoji.

Creating a user

Now create the login account. Pick the host carefully (see the table above). Use a long, random password.

CREATE USER 'app'@'localhost' IDENTIFIED BY 'S0me-Str0ng-P@ssw0rd';

Output:

Query OK, 0 rows affected (0.02 sec)

If your app runs on a different server, create the account for its IP instead:

CREATE USER 'app'@'10.0.0.5' IDENTIFIED BY 'S0me-Str0ng-P@ssw0rd';

At this point the user exists but cannot touch any data — it has no privileges yet. That is exactly what we want before granting least privilege.

Granting privileges (least privilege)

A privilege is a permission to do one kind of thing (read rows, insert rows, create tables, and so on). The GRANT command hands privileges to a user, scoped to a specific database. The golden rule is least privilege: give the app only what it needs and nothing more.

Most web apps need to read and write rows and occasionally change the schema. They almost never need GRANT OPTION (the power to hand out privileges to others) or ALL PRIVILEGES. This GRANT covers a typical app:

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, INDEX, DROP, REFERENCES
  ON shop_app.* TO 'app'@'localhost';

Output:

Query OK, 0 rows affected (0.01 sec)

The shop_app.* means “every table inside the shop_app database, but no other database”. Compare your options:

Grant	What it allows	When to use
SELECT only	Read rows	A read-only reporting or analytics user
SELECT, INSERT, UPDATE, DELETE	Read and change data, but not the schema	An app where migrations run separately
The list above (+ CREATE, ALTER, DROP)	Change data and schema	An app that runs its own migrations
ALL PRIVILEGES ON *.*	Everything on every database	Avoid — this is an admin account, not an app

Never grant ALL PRIVILEGES ON *.* to an application user. If that password leaks, the attacker owns every database on the server, not just one app’s data.

Applying changes with FLUSH PRIVILEGES

After CREATE USER and GRANT, MySQL usually reloads its permission tables automatically. But if you ever edit the mysql.user grant tables directly with INSERT or UPDATE, the changes only take effect after you run:

FLUSH PRIVILEGES;

Output:

Query OK, 0 rows affected (0.00 sec)

When to use it: after manual edits to the grant tables, or just as a harmless final step out of habit. When it is not needed: after ordinary CREATE USER / GRANT / REVOKE, which already reload automatically.

Verifying the new account

Check what the user can do, then exit and test the login.

SHOW GRANTS FOR 'app'@'localhost';

Output:

+----------------------------------------------------------------------------+
| Grants for app@localhost                                                   |
+----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `app`@`localhost`                                    |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX,     |
|   ALTER ON `shop_app`.* TO `app`@`localhost`                               |
+----------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Now leave the root session and log in as the new user to prove it works:

mysql -u app -p shop_app

It prompts for the password you set; type it and you should land at the mysql> prompt connected to the shop_app database.

Best Practices

• Always create the user for the exact host your app connects from (localhost, a specific IP, or % only when truly needed).
• Apply least privilege: grant only the verb-level permissions and the single database the app needs.
• Use a long, random, unique password per app, and store it in your app’s secret manager or environment variables, never in code.
• Prefer utf8mb4 for new databases so you can store any character including emoji.
• Run SHOW GRANTS FOR 'user'@'host'; to audit accounts periodically and REVOKE anything unused.
• Never give an application account ALL PRIVILEGES or GRANT OPTION — keep admin power on the root account only.

Related Topics

• Installing MySQL
• Securing your MySQL installation
• Backing up and restoring MySQL
• Connecting to a database from your app
• PostgreSQL users & databases