Skip to main content
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.

​
Before you start

  • MySQL or MariaDB is installed. See install MariaDB or MySQL.
  • 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 or Game Panel databases.

​
How to create a database and user

1

Open the database shell

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

Create the database

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

Create the user

'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

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

Leave the shell

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:
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:
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. For remote users, also change bind-address and your firewall. See allow remote MySQL connections.

​
Which privileges should I grant?

Example of a limited user:
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:
Remove a privilege:
Delete the user and, if you no longer need it, the database:
DROP DATABASE deletes every table in it with no undo. Back up the database 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.

VPS and dedicated server guides

Every guide in this section.

Allow remote connections

Let another server reach MySQL safely.

Back up and restore

Protect the data before changes.

Install WordPress on a VPS

Uses a database and user like this.

Install phpMyAdmin

Manage users in the browser.
Checked against the official MySQL 8.0 Reference Manual and MariaDB documentation on 4 November 2026; SQL commands tested on Ubuntu 24.04 LTS with MariaDB 10.11. Commands can differ on other distributions or releases.

​
Need Extra Help?

If you encounter any issues, our support team is ready to assist:

​
Save on Your Hosting

Ready to upgrade your hosting? Use code KB20 at checkout for 20% off your first order!
Last Updated: November 2026