How to create a database and user for a web app in MySQL or MariaDB

Almost every web app asks for the same four things during setup: a database name, a user name, a password and a host. This guide creates all of them correctly in MySQL or MariaDB, with full Unicode support and a user that can only touch its own database. It takes about five minutes.

Before you start

• MySQL or MariaDB running on your Linux VPS. If you still need to install it, follow install MySQL 8.4 on Ubuntu and Debian or install MariaDB on AlmaLinux and Rocky Linux.
• An SSH session as a user with sudo rights.
• Names for your database and user. The examples use blogdb and bloguser. Use lowercase letters, numbers and underscores only.

Pick names and a password

Use one database and one user per app. When you later remove or move an app, you only touch its own database and user.

Generate a strong password on the server:

openssl rand -base64 24

Copy it into your password manager now. You will paste it into the app's config file later. The examples below show StrongPassword123!ReplaceMe; never use that value, replace it with your own.

Create the database with utf8mb4

Open the database client as an admin:

sudo mysql

On MariaDB you can also use sudo mariadb. Then create the database:

CREATE DATABASE blogdb
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

Why these settings:

• utf8mb4 stores every Unicode character, including emoji and many Asian scripts. The older utf8 (also called utf8mb3) cannot store emoji and causes errors or lost text.
• utf8mb4_unicode_ci sorts and compares text in a sensible, case-insensitive way, and it works on both MySQL and MariaDB. That keeps the database portable if you ever move between them.

Create a dedicated user

If the app runs on the same server, which is the usual case, create the user for localhost:

CREATE USER 'bloguser'@'localhost' IDENTIFIED BY 'StrongPassword123!ReplaceMe';

The @'localhost' part means this account can only log in from the server itself. Nobody can use it from the internet, even if they learn the password.

Grant rights on that database only

GRANT ALL PRIVILEGES ON blogdb.* TO 'bloguser'@'localhost';

blogdb.* limits the rights to this one database. The user cannot see or change any other database on the server, and cannot create users. Most apps, including WordPress and Laravel, need to create and alter their own tables, which is why the user gets full rights on its own database.

Confirm what you granted, then leave the admin session:

SHOW GRANTS FOR 'bloguser'@'localhost';
EXIT;

Test the login

Log in as the new user, exactly as the app will:

mysql -u bloguser -p blogdb

Enter your password. Then check the character set and that you can create a table:

SELECT @@character_set_database, @@collation_database;
CREATE TABLE healthcheck (id INT PRIMARY KEY);
DROP TABLE healthcheck;

The first query should return utf8mb4 and utf8mb4_unicode_ci. If the table commands run without errors, the user is ready.

Add the details to your app

Use these values in the app's installer or config file:

• Database name: blogdb
• User name: bloguser
• Password: the one you generated
• Host: localhost

For WordPress, these go in wp-config.php as DB_NAME, DB_USER, DB_PASSWORD and DB_HOST. Set DB_CHARSET to utf8mb4. For Laravel, they go in the .env file as DB_DATABASE, DB_USERNAME, DB_PASSWORD and DB_HOST.

Keep the config file readable only by the app's own user, since it now contains the password:

sudo chmod 640 /var/www/example.com/wp-config.php

Adjust the path and file to match your app.

Troubleshooting

• Error establishing a database connection in WordPress: a value in wp-config.php does not match. Run the mysql -u bloguser -p blogdb test with the same values to find which.
• ERROR 1045 Access denied: wrong password, or the app connects from a host other than localhost. Check the host part of the account.
• ERROR 1007 Can't create database; database exists: the name is taken. Pick another, or use CREATE DATABASE IF NOT EXISTS if you meant to reuse it.
• Emoji turn into ????: the app's connection uses an older character set. Set the app's charset option to utf8mb4.
• Unknown collation: 'utf8mb4_0900_ai_ci' on MariaDB: you copied a MySQL 8 command. Use utf8mb4_unicode_ci instead.

Next steps

• Back up the new database before you go live: Back up and restore MySQL with mysqldump.