Databases: MariaDB/MySQL and PostgreSQL

This guide shows how to install MariaDB (compatible with MySQL) or PostgreSQL on your server, create a database with its own user, connect to it from your computer through an SSH tunnel, and make backups. Run the commands as root in an SSH session (Connecting to your server over SSH (Windows, macOS, Linux)). You'll find the server's IP address in the app.blackbox.md panel, under “Infrastructure” → “My Servers” (app.blackbox.md/my-servers).

Available to: the owner of a VPS server running Ubuntu, Debian, AlmaLinux or CentOS Stream. If the server has a control panel (“VPS hosting” plans with a control panel), you usually create databases from that control panel.

MariaDB or PostgreSQL

Install the system your application requires.

For most applications, MariaDB replaces MySQL with no changes. If the application explicitly requires MySQL, install mysql-server on Ubuntu, AlmaLinux or CentOS Stream (on Debian, it isn't in the standard repositories). The commands are then called mysql and mysql_secure_installation, and the service name and the configuration file paths differ from the ones in this guide. Don't install MySQL and MariaDB on the same server.

MariaDB: installing and securing

Ubuntu and Debian

apt update
apt install -y mariadb-server

The service starts automatically and listens only on 127.0.0.1.

AlmaLinux and CentOS Stream

dnf install -y mariadb-server
systemctl enable --now mariadb

Here, MariaDB listens on all network interfaces by default. Bind it to 127.0.0.1 with the 99-local.cnf file described below.

Your settings file

Put your own settings in a separate file, 99-local.cnf, which you create with nano or vi: on Ubuntu and Debian in /etc/mysql/mariadb.conf.d/, on AlmaLinux and CentOS Stream in /etc/my.cnf.d/. On AlmaLinux and CentOS Stream, start the file like this (on Ubuntu and Debian, bind-address = 127.0.0.1 is already set):

[mysqld]
bind-address = 127.0.0.1

After every change, restart the service and check that the output shows 127.0.0.1:3306, not 0.0.0.0:3306 or *:3306:

systemctl restart mariadb
ss -tlnp | grep 3306

mariadb-secure-installation

mariadb-secure-installation

The MariaDB root user uses socket authentication (unix_socket): only the system's root user can log in, with the mariadb command, without a password. On Ubuntu 20.04 (MariaDB 10.3), the commands are called mysql_secure_installation and mysql, and you answer n to “Set root password?”.

The database and its user

Give each application its own database and a user with rights on that database only. Generate a strong password, then open the MariaDB console:

openssl rand -base64 24
mariadb

Run the following, replacing DB_NAME, USERNAME and PASSWORD:

CREATE DATABASE DB_NAME CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'USERNAME'@'localhost' IDENTIFIED BY 'PASSWORD';
GRANT ALL PRIVILEGES ON DB_NAME.* TO 'USERNAME'@'localhost';
EXIT;

DB_NAME.* limits the rights to this database, and 'localhost' allows connections only from the server itself and through an SSH tunnel. Don't use ON *.* or '%'. Test the connection with the new user:

mariadb -u USERNAME -p DB_NAME

In the application's configuration, enter the host localhost, the port 3306, the database name, the user and the password.

PostgreSQL: installation and users

Ubuntu and Debian

apt update
apt install -y postgresql

The service starts automatically and listens only on localhost.

AlmaLinux and CentOS Stream

dnf install -y postgresql-server
postgresql-setup --initdb
systemctl enable --now postgresql

Open /var/lib/pgsql/data/pg_hba.conf. If the host lines for 127.0.0.1/32 and ::1/128 say ident, replace it with md5 (it also accepts passwords stored as SCRAM). Otherwise, applications get the error Ident authentication failed. Apply the change:

systemctl reload postgresql

The user and the database

sudo -u postgres createuser --pwprompt USERNAME
sudo -u postgres createdb --owner=USERNAME DB_NAME

At “Enter password for new role:” and “Enter it again:”, type the password. The user is not a superuser: it has full rights only in the database it owns. The warning could not change directory to "/root": Permission denied is harmless; it doesn't appear if you run the commands from /tmp. Test the connection (exit with \q):

psql -h 127.0.0.1 -U USERNAME DB_NAME

Remote access through an SSH tunnel (recommended)

To work with the database from DBeaver, HeidiSQL, MySQL Workbench, pgAdmin or another client on your computer, don't open the database port. The SSH tunnel goes through port 22, which is already open, and encrypts the traffic, while the database stays bound to 127.0.0.1.

1. On your computer, in Terminal (macOS, Linux) or PowerShell (Windows), start the tunnel. For MariaDB:

   ssh -N -L 3306:127.0.0.1:3306 root@SERVER_IP

   For PostgreSQL:

   ssh -N -L 5432:127.0.0.1:5432 root@SERVER_IP

2. Leave the window open: the tunnel works as long as the command runs. Close it with Ctrl + C.

3. In the client, connect to host 127.0.0.1, port 3306 or 5432, with the database user and password.

The tunnel uses the same authentication as a regular SSH connection: on a “Key-only” server, if your key isn't in the default location, point to it with -i. If the port on your computer is already taken by a local database, use another local port, for example -L 13306:127.0.0.1:3306, and connect to 127.0.0.1:13306. Type 127.0.0.1, not localhost: the mariadb/mysql client treats localhost as a local socket and bypasses the tunnel. Many graphical clients have an SSH tunnel built into the connection settings: SSH host SERVER_IP, port 22, user root and your key or password, then database host 127.0.0.1.

Opening the database port (not recommended)

A database exposed to the internet is scanned all the time, and attackers keep trying to guess its passwords. If an external service still has to connect directly, the port must be open at both levels:

1. The network filtering, managed by BLACKBOX, lets through only SSH (22), HTTP (80), HTTPS (443) and ping by default, so 3306 and 5432 are closed. The panel has no firewall settings: write to support with the server's IP address and the port (How to contact BLACKBOX support).

2. The operating system's firewall must be active (see the note below) and must allow the port only from your IP address, which you can find with The “Adresa ta IP” (Your IP address) tool. For PostgreSQL, replace 3306 with 5432. With ufw (Ubuntu, Debian):

   ufw allow from YOUR_IP to any port 3306 proto tcp

   With firewalld (AlmaLinux, CentOS Stream):

   firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="YOUR_IP/32" port port="3306" protocol="tcp" accept'
   firewall-cmd --reload

3. MariaDB: in 99-local.cnf, under [mysqld], set bind-address = 0.0.0.0, restart the service and create the user 'USERNAME'@'YOUR_IP' with the same CREATE USER and GRANT commands.

4. PostgreSQL: in pg_hba.conf (Ubuntu and Debian: /etc/postgresql/VERSION/main/pg_hba.conf; AlmaLinux and CentOS Stream: /var/lib/pgsql/data/pg_hba.conf), add the line host DB_NAME USERNAME YOUR_IP/32 METHOD, with the method used on the lines for 127.0.0.1/32 (scram-sha-256 or md5), then run:

   sudo -u postgres psql -c "ALTER SYSTEM SET listen_addresses = '*'"
   systemctl restart postgresql

If your IP address changes, you have to update the rule. Without TLS configured in the database, the data travels unencrypted: one more reason to use the tunnel.

Warning On a new server, ufw and firewalld are not enabled. Before you enable them, allow SSH and keep a second SSH session open: there is no web or VNC console for customers. The steps: Firewall: network filtering and ufw/firewalld on the server. If you have locked yourself out, write to support.

A database on another BLACKBOX server

BLACKBOX servers are not connected to each other by a private network: the application reaches the database on another server through that server's public IP address, like any computer on the internet. Don't assume that the database port is reachable from the other server. The recommended solution is a permanent SSH tunnel, started on the application server as a systemd service: the database stays bound to 127.0.0.1, and the traffic travels encrypted through port 22.

Permanent SSH tunnel (recommended)

The commands are for MariaDB; for PostgreSQL, replace 3306 with 5432 everywhere.

1. On the application server, create an SSH key used only for the tunnel, without a passphrase, so that the service can use it:

   ssh-keygen -t ed25519 -f /root/.ssh/db_tunnel_ed25519 -N "" -C db-tunnel
   cat /root/.ssh/db_tunnel_ed25519.pub

2. On the database server, create a separate user with no shell, and open its authorized_keys file:

   useradd -m -s /usr/sbin/nologin dbtunnel
   mkdir -p /home/dbtunnel/.ssh
   nano /home/dbtunnel/.ssh/authorized_keys

   Write a single line: the restrictions, a space, then the key shown in step 1.

   restrict,port-forwarding,permitopen="127.0.0.1:3306" ssh-ed25519 AAAA... db-tunnel

   Set the permissions:

   chown -R dbtunnel:dbtunnel /home/dbtunnel/.ssh
   chmod 700 /home/dbtunnel/.ssh
   chmod 600 /home/dbtunnel/.ssh/authorized_keys

   On AlmaLinux and CentOS Stream, also restore the SELinux labels:

   restorecon -R /home/dbtunnel/.ssh

   With these restrictions, the key opens only the tunnel to the database port: it gets no shell and can't reach other ports. Don't add it with “Add SSH key” in the panel: the panel adds it to root, without restrictions.

3. On the application server, start the tunnel once by hand. On the first connection, confirm the database server's key with yes. While the tunnel is open, the command prints nothing; stop it with Ctrl + C:

   ssh -N -i /root/.ssh/db_tunnel_ed25519 -L 3306:127.0.0.1:3306 dbtunnel@DB_SERVER_IP

4. Create the service:

   nano /etc/systemd/system/db-tunnel.service

   [Unit]
   Description=SSH tunnel to the database
   Wants=network-online.target
   After=network-online.target
   
   [Service]
   ExecStart=/usr/bin/ssh -N -i /root/.ssh/db_tunnel_ed25519 -o BatchMode=yes -o ExitOnForwardFailure=yes -o ServerAliveInterval=30 -o ServerAliveCountMax=3 -L 3306:127.0.0.1:3306 dbtunnel@DB_SERVER_IP
   Restart=always
   RestartSec=10
   
   [Install]
   WantedBy=multi-user.target

5. Enable the service and check that the tunnel listens on 127.0.0.1:3306:

   systemctl daemon-reload
   systemctl enable --now db-tunnel
   systemctl status db-tunnel
   ss -tlnp | grep 3306

6. In the application's configuration, enter the host 127.0.0.1 (not localhost), the port 3306, the database name, the user and the password. On the database server, the user created for 'localhost' is enough.

ExitOnForwardFailure closes the connection if the tunnel can't be opened, and ServerAliveInterval detects a dropped connection; in both cases, systemd restarts the service after 10 seconds. BatchMode=yes keeps the service from waiting for a confirmation or a password. If port 3306 on the application server is taken by a local database, use another local port, for example -L 13306:127.0.0.1:3306, and enter it in the application's configuration. If the service doesn't start, read its log:

journalctl -u db-tunnel -n 50

Port open only to the application server

If the tunnel doesn't suit you, ask support to open port 3306 or 5432 on the database server, and follow the steps in the “Opening the database port (not recommended)” section, with the application server's IP address instead of YOUR_IP: in the ufw or firewalld rule, in the MariaDB user ('USERNAME'@'APP_SERVER_IP') and in the pg_hba.conf line. Without TLS, the data travels unencrypted between the servers. Before you enable the firewall, keep a second SSH session open: there is no web console (see the note above).

Backups

Don't copy the files of a running database: the copy may be inconsistent. Make a dump (an export) with mysqldump or pg_dump, compressed with gzip.

1. Create the script /usr/local/bin/backup-db.sh and keep only the line for your database system:

   #!/bin/bash
   set -euo pipefail
   cd /tmp
   DIR=/var/backups/db
   DATE=$(date +%F)
   mkdir -p "$DIR"
   chmod 700 "$DIR"
   # MariaDB / MySQL
   mysqldump --single-transaction --routines DB_NAME | gzip > "$DIR/mariadb-DB_NAME-$DATE.sql.gz"
   # PostgreSQL
   sudo -u postgres pg_dump DB_NAME | gzip > "$DIR/pg-DB_NAME-$DATE.sql.gz"
   # delete dumps older than 7 days
   find "$DIR" -name '*.sql.gz' -mtime +7 -delete

2. Make it executable, run it once and check the files:

   chmod 700 /usr/local/bin/backup-db.sh
   /usr/local/bin/backup-db.sh
   ls -lh /var/backups/db

3. Open the list of cron jobs with the command below, and add the line that runs the script every night at 02:30 server time (you can see the server time with timedatectl):

   crontab -e

   30 2 * * * /usr/local/bin/backup-db.sh

--single-transaction exports InnoDB tables in a consistent state, without locking them. The script runs as root, which logs in to MariaDB through the socket. If you set a password for the MariaDB root user, put it in /root/.my.cnf, in the [client] section, with permissions 600. If crontab is missing, install the cron package (Ubuntu, Debian) or cronie (AlmaLinux, CentOS Stream) and start the service. Dumps take up space: keep an eye on the disk with df -h (The server's disk is full).

Important The dumps in /var/backups/db are on the same disk as the database: if the disk fails or the server is deleted, you lose them too. BLACKBOX does not make automatic backups of your server, and the “Backups” button on the server card is not active. Copy the dumps to another location regularly: Backup strategy for your server

Test the restore

A backup you have never tested may not work. At least once a month, restore a dump into a test database, check the tables, and delete the test database. MariaDB:

mariadb -e "CREATE DATABASE test_restore"
gunzip -c /var/backups/db/mariadb-DB_NAME-2026-10-09.sql.gz | mariadb test_restore
mariadb -e "SHOW TABLES" test_restore
mariadb -e "DROP DATABASE test_restore"

PostgreSQL:

cd /tmp
sudo -u postgres createdb test_restore
gunzip -c /var/backups/db/pg-DB_NAME-2026-10-09.sql.gz | sudo -u postgres psql -q test_restore
sudo -u postgres psql -d test_restore -c '\dt'
sudo -u postgres dropdb test_restore

For a real restore, stop the application first. For PostgreSQL, restore into an empty database created with createdb --owner=USERNAME.

Memory on small plans

The default values, 128 MB for innodb_buffer_pool_size (MariaDB) and for shared_buffers (PostgreSQL), suit 1 GB of RAM. On larger servers you can raise them carefully, especially when the web server or the application runs on the same server. You can see the memory with free -h.

On a server used only for the database, you can give up to half of the RAM to innodb_buffer_pool_size and up to a quarter to shared_buffers. Each connection also uses memory: don't raise max_connections without a reason.

MariaDB: write the value in 99-local.cnf (if the file already exists, add only the second line under [mysqld]), then restart and check:

[mysqld]
innodb_buffer_pool_size = 256M

systemctl restart mariadb
mariadb -e "SELECT @@innodb_buffer_pool_size/1024/1024 AS MB"

PostgreSQL (shared_buffers takes effect only after a restart):

sudo -u postgres psql -c "ALTER SYSTEM SET shared_buffers = '256MB'"
systemctl restart postgresql
sudo -u postgres psql -c "SHOW shared_buffers"

On plans with 1–2 GB of RAM, also add swap: Adding swap space. If the database stops on its own, check whether the system shut it down because it ran out of memory:

journalctl -k | grep -i "out of memory"

If this keeps happening, the server needs more RAM: Server upgrade: moving to a larger plan.

Frequently asked questions

The application gets “Access denied for user”. What should I check? Check the database name, the user, the password and the host: a user created for 'localhost' can connect only from the server itself or through the tunnel. If the message shows 'USERNAME'@'127.0.0.1', create the user for '127.0.0.1' as well, with the same CREATE USER and GRANT commands.

I forgot the database user's password. How do I change it? Change it as root on the server. In the mariadb console:

ALTER USER 'USERNAME'@'localhost' IDENTIFIED BY 'NEW_PASSWORD';

In PostgreSQL, the command below asks you for the new password twice:

sudo -u postgres psql -c "\password USERNAME"

Then update the password in the application's configuration.

How do I download a dump to my computer? Run the command below on your computer, with the name of the file you want. Other methods: Transferring files to the server (scp, rsync, SFTP).

scp root@SERVER_IP:/var/backups/db/FILE.sql.gz .

Can I run MariaDB and PostgreSQL on the same server? Yes, they use different ports. They share the memory, though, so for both of them choose values lower than the ones in the table.