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
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
sudorights. - Names for your database and user. The examples use
blogdbandbloguser. 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 24Copy 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 mysqlOn 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 calledutf8mb3) 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.
MySQL 8 already defaults to utf8mb4 with the utf8mb4_0900_ai_ci collation. It is a good choice if you will only ever use MySQL 8 or later. Pick utf8mb4_unicode_ci if the data might ever move to MariaDB.
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.
If the app runs in a Docker container or connects to 127.0.0.1 over TCP, MySQL may see it as a different host. See the host notes in create a MySQL user and grant privileges.
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;Do not give the app the root account or rights on *.*. If the app is ever compromised, the attacker would then control every database on the server.
Test the login
Log in as the new user, exactly as the app will:
mysql -u bloguser -p blogdbEnter 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.phpAdjust the path and file to match your app.
Troubleshooting
- `Error establishing a database connection` in WordPress: a value in
wp-config.phpdoes not match. Run themysql -u bloguser -p blogdbtest 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 EXISTSif 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_ciinstead.
Next steps
- Back up the new database before you go live: Back up and restore MySQL with mysqldump.
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.
Related guides
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
Databases
Where is my.cnf? MySQL and MariaDB config files explained
Find the MySQL or MariaDB config files on Ubuntu, Debian, AlmaLinux and Rocky Linux, learn the order they load in, and confirm which settings are really active.
- Beginner
- Linux VPS
- 5 min read