> ## Documentation Index
> Fetch the complete documentation index at: https://help.freakhosting.com/llms.txt
> Use this file to discover all available pages before exploring further.

# How to Create a MySQL Database and User and Grant Privileges

> Create a MySQL or MariaDB user and grant privileges: CREATE DATABASE, CREATE USER 'app'@'localhost' IDENTIFIED BY, then GRANT ALL PRIVILEGES ON app.* to it.

To create a MySQL or MariaDB user and grant privileges, log in with `sudo mariadb` (or `sudo mysql`) and run `CREATE DATABASE shop;`, then `CREATE USER 'shopuser'@'localhost' IDENTIFIED BY 'a-strong-password';` and `GRANT ALL PRIVILEGES ON shop.* TO 'shopuser'@'localhost';`. The user can now use the `shop` database and nothing else.

| Difficulty | Time | Works on | Needs |
| - | - | - | - |
| Beginner | 5 minutes | MariaDB 10.5+ and MySQL 8.0+ on any supported Linux | sudo access or a MySQL admin account |

## Before you start

* MySQL or MariaDB is installed. See [install MariaDB or MySQL](https://help.freakhosting.com/portals/other/how-to-install-mysql-or-mariadb.md).
* Choose a strong password of at least 16 characters. Avoid the `'` character, which ends the password string in SQL.
* If you use a FREAKHOSTING Web Panel or Game Panel instead of a VPS, create databases in the panel: see [Web Panel databases](https://help.freakhosting.com/portals/web-panel/how-to-manage-mysql-databases.md) or [Game Panel databases](https://help.freakhosting.com/portals/game-panel/how-to-manage-databases.md).

## How to create a database and user

**1. Open the database shell**

```bash theme={null}
sudo mariadb
```

On MySQL use `sudo mysql`. If you are logged in as `root`, leave out `sudo`.

**2. Create the database**

```sql theme={null}
CREATE DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
```

`utf8mb4` stores every character, including emoji. Most current apps expect it.

**3. Create the user**

```sql theme={null}
CREATE USER 'shopuser'@'localhost' IDENTIFIED BY 'Str0ng-Passw0rd!';
```

`'localhost'` means the user can only connect from this server, which is what a website on the same VPS needs.

**4. Grant privileges on that database**

```sql theme={null}
GRANT ALL PRIVILEGES ON shop.* TO 'shopuser'@'localhost';
```

`shop.*` means every table in `shop`. The user gets no rights on other databases.

**5. Leave the shell**

```sql theme={null}
EXIT;
```

`GRANT` and `CREATE USER` take effect straight away. You do not need `FLUSH PRIVILEGES`.

## How to check it worked

Log in as the new user and list what it can see:

```bash theme={null}
mariadb -u shopuser -p -e "SHOW DATABASES; SELECT CURRENT_USER();"
```

Type the password when asked. You should see `shop` (plus `information_schema`) and `shopuser@localhost`.

To see a user's rights, run this as admin:

```sql theme={null}
SHOW GRANTS FOR 'shopuser'@'localhost';
```

```text theme={null}
GRANT USAGE ON *.* TO `shopuser`@`localhost` IDENTIFIED BY PASSWORD '*6746...'
GRANT ALL PRIVILEGES ON `shop`.* TO `shopuser`@`localhost`
```

`USAGE` means "can log in, no global rights". That is correct.

## What does the host part ('localhost', '%', an IP) mean?

A MySQL account is a user name **plus** the host it connects from. `'shopuser'@'localhost'` and `'shopuser'@'198.51.100.25'` are two separate accounts, each with its own password and privileges.

| Host value | Who can connect | Use it for |
| - | - | - |
| `'localhost'` | Only programs on this server | Websites and bots on the same VPS (the usual choice) |
| `'198.51.100.25'` | Only that one remote IP | An app server or your office IP |
| `'%'` | Any IP address | Avoid. Only with a strict firewall rule |

For remote users, also change `bind-address` and your firewall. See [allow remote MySQL connections](https://help.freakhosting.com/portals/other/how-to-allow-remote-mysql-connections.md).

## Which privileges should I grant?

| Need | Grant |
| - | - |
| Website or CMS (WordPress, forums) | `ALL PRIVILEGES ON shop.*` |
| App that only reads and writes rows | `SELECT, INSERT, UPDATE, DELETE ON shop.*` |
| Reporting or dashboards | `SELECT ON shop.*` |

Example of a limited user:

```sql theme={null}
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'shopuser'@'localhost';
```

> [!WARNING]
>
> Never use `GRANT ALL PRIVILEGES ON *.*` for an app. That gives it rights on every database, including the `mysql` system database that holds all accounts.

## How to change, revoke or delete a user

Change a password:

```sql theme={null}
ALTER USER 'shopuser'@'localhost' IDENTIFIED BY 'N3w-Str0ng-Pass!';
```

Remove a privilege:

```sql theme={null}
REVOKE DELETE ON shop.* FROM 'shopuser'@'localhost';
```

Delete the user and, if you no longer need it, the database:

```sql theme={null}
DROP USER 'shopuser'@'localhost';
DROP DATABASE shop;
```

> [!WARNING]
>
> `DROP DATABASE` deletes every table in it with no undo. [Back up the database](https://help.freakhosting.com/portals/other/how-to-back-up-and-restore-mysql-databases.md) first.

## Frequently asked questions

### Why do I get "Access denied for user 'shopuser'@'localhost'"?

The password is wrong, or the account exists for a different host. Run `SELECT user, host FROM mysql.user;` as admin and check that a row exists for `shopuser` with `localhost`.

### Can I create the user with GRANT in one step?

Not on MySQL 8.0 and later: `GRANT` no longer creates users. Run `CREATE USER` first, then `GRANT`. That order works on MariaDB too.

### Do I need FLUSH PRIVILEGES?

No. It is only needed after editing the grant tables directly with `INSERT` or `UPDATE`, which you should avoid.

### How do I list all databases?

Run `SHOW DATABASES;` as admin.

## Related guides

> [card] [VPS and dedicated server guides](https://help.freakhosting.com/portals/other/vps-and-dedicated-server-guides.md)
>
> Every guide in this section.

> [card] [Allow remote connections](https://help.freakhosting.com/portals/other/how-to-allow-remote-mysql-connections.md)
>
> Let another server reach MySQL safely.

> [card] [Back up and restore](https://help.freakhosting.com/portals/other/how-to-back-up-and-restore-mysql-databases.md)
>
> Protect the data before changes.

> [card] [Install WordPress on a VPS](https://help.freakhosting.com/portals/other/how-to-install-wordpress-on-a-vps.md)
>
> Uses a database and user like this.

> [card] [Install phpMyAdmin](https://help.freakhosting.com/portals/other/how-to-install-phpmyadmin.md)
>
> Manage users in the browser.

***

> [!NOTE]
>
> **Checked against the official [MySQL 8.0 Reference Manual](https://dev.mysql.com/doc/refman/8.0/en/create-user.html) and [MariaDB documentation](https://mariadb.com/docs/server/reference/sql-statements/account-management-sql-statements/create-user)** on 4 November 2026; SQL commands tested on Ubuntu 24.04 LTS with MariaDB 10.11. Commands can differ on other distributions or releases.

> [!NOTE]
>
> ### Need Extra Help?
>
> If you encounter any issues, our support team is ready to assist:
>
> * **Live Chat:** Quick assistance via our website.
> * **Support Ticket:** [Open a Ticket](https://freakhosting.com/clientarea/submitticket.php)
> * **Discord:** [Join our Community](https://discord.gg/freakhosting)
> * **Email:** [support@freakhosting.com](mailto:support@freakhosting.com)

> [!TIP]
>
> ### Save on Your Hosting
>
> Ready to upgrade your hosting? Use code **KB20** at checkout for **20% off** your first order!

***

**Last Updated:** November 2026


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.