Create a database and a dedicated user

Each application should have its own database and its own database account. That way, a bug or leaked password in one app cannot read or change another app's data. This guide creates a database called blog and a user called blog_app with only the privileges that app needs.

Before you begin

• MariaDB installed and hardened (see Secure your MariaDB installation).
• SSH access as a user with sudo rights.
• A plan for names: use short, lowercase names without spaces, such as blog and blog_app.

Step 1: Generate a strong password

Do not invent a password by hand. The openssl rand command produces random bytes, and -base64 24 turns them into 24 characters of letters, numbers and symbols that are hard to guess.

openssl rand -base64 24

Vx3b9LqT2mZk0aYw8RcE1pHn4uJd7sGf==

Copy the output into a password manager or a secure note. You will need it twice: once to create the account and once in the application's configuration.

Step 2: Open the MariaDB client as root

Use sudo mariadb to open the command-line client with administrative rights. Each line below ending in a semicolon is a SQL statement, and you can type them one at a time.

sudo mariadb

Your prompt changes to MariaDB [(none)]>. Everything from here until Step 5 happens inside this prompt.

Step 3: Create the database

The CHARACTER SET utf8mb4 option lets the database store every Unicode character, including emoji. Always choose it for new databases.

CREATE DATABASE blog CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Query OK, 1 row affected (0.001 sec)

Step 4: Create the user and grant privileges

The account is named 'blog_app'@'localhost'. The @'localhost' part means it can only connect from the server itself, which is what you want when the app runs on the same VPS. Replace PASTE_YOUR_PASSWORD_HERE with the password from Step 1.

CREATE USER 'blog_app'@'localhost' IDENTIFIED BY 'PASTE_YOUR_PASSWORD_HERE';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, INDEX, DROP ON blog.* TO 'blog_app'@'localhost';
FLUSH PRIVILEGES;

The GRANT line gives the account the everyday data privileges, plus the schema privileges that migrations need. The ON blog.* part limits those privileges to the blog database, so the account cannot touch anything else. It does not get GRANT OPTION, so it cannot create other accounts. FLUSH PRIVILEGES reloads the permission tables, which is harmless here but useful after manual edits.

Leave the MariaDB prompt:

EXIT;

Step 5: Test the new account

Log in as the new user, the way your application will. The -p flag makes the client ask for the password, so it does not appear in your shell history.

mariadb -u blog_app -p blog -e "SELECT DATABASE();"

Enter password:
+------------+
| DATABASE() |
+------------+
| blog       |
+------------+

If you see blog in the output, the account works and is connected to the right database.

Step 6: Store the credentials for your application

Put the database name, user and password in your application's configuration, not in its public files. Keep the file readable only by the owner and the web server group, as shown in Deploy a PHP application. A typical configuration uses these four values:

DB_HOST=localhost
DB_NAME=blog
DB_USER=blog_app
DB_PASS=PASTE_YOUR_PASSWORD_HERE

Troubleshooting

• ERROR 1007: Can't create database 'blog'; database exists. A database with that name is already there. Choose a different name, or inspect it with SHOW DATABASES; before deciding. Do not drop a database unless you are certain it is unused.
• ERROR 1045: Access denied for user 'blog_app'@'localhost'. The password is wrong. Reset it with ALTER USER 'blog_app'@'localhost' IDENTIFIED BY 'new-password'; from the sudo mariadb prompt.
• The application reports missing privileges. Check what the account has with SHOW GRANTS FOR 'blog_app'@'localhost';, then add the missing privileges with another GRANT.
• The app connects to 127.0.0.1 but not localhost. MariaDB treats those as different hosts for accounts that use the socket. Use localhost in your configuration, or create a matching account for the host you use.

Next steps

With a database and account ready, you can install an application that uses it. See Deploy a PHP application for a complete example, or Host a PHP-based content management system for a typical CMS setup.