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

Set up a utf8mb4 database and a dedicated user with a strong password for WordPress, Laravel or any web app, then test the login before you configure the app.

  • Beginner
  • Linux VPS
  • 4 min read

By RunRacks Team · Published

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

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:

Terminal
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:

Terminal
sudo mysql

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

SQL
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:

SQL
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

SQL
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:

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

Test the login

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

Terminal
mysql -u bloguser -p blogdb

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

SQL
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:

Terminal
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

Was this guide helpful?

Still stuck?

Ask RunRacks AI for a quick answer, or open a ticket and our team will take a look at your server.

Databases

Reset a Forgotten MySQL Root Password on Linux

Recover access to MySQL or MariaDB on a Linux VPS by starting the server with skip-grant-tables, setting a new root password and restarting normally.

  • Intermediate
  • Linux VPS
  • 5 min read

Databases

MySQL Point-in-Time Recovery Using Binary Logs

Recover a MySQL or MariaDB database to an exact moment after a bad write or dropped table by restoring a full backup and replaying binary logs up to the incident.

  • Advanced
  • Linux VPS
  • 7 min read