MariaDB on a VPS: installation, security, users and remote access

MariaDB is a MySQL-compatible database server. Websites, game servers, Discord bots and plugins all use it. We'll show two ways to install it, then cover security, managing databases and users, safe access from your computer, and backups.

How it works#

MariaDB runs on the VPS as the mariadb service and by default only accepts connections from the VPS itself (address 127.0.0.1, port 3306, and a local socket). Apps on the same VPS can connect, nobody on the internet can reach the database. Keep it that way until you really need access from outside.

The version depends on the system: Ubuntu 24.04 installs MariaDB 10.11, Debian 13 installs MariaDB 11.8. The difference doesn't matter for anything in this guide.

On both Ubuntu and Debian, the database user root authenticates via unix socket: only someone logged in to the VPS as the system root can get into the database as root. There's no password to guess, because there is none.

Option A: the Database tab in the customer area#

The fastest way, no command line needed.

1. In the customer area, open My services, click your VPS and go to the Database tab.
2. If no database is running on the VPS, you'll see “No database server is running on this VPS yet”. Click Install MariaDB. The installation runs in the background and usually takes 1–⁠3 minutes; the page refreshes on its own.
3. Click Connect automatically. The customer area connects to the database over a secure connection into the VPS, so the database doesn't have to be reachable from the internet.
4. You now have these sub-tabs:

Important details:

• A database name may contain only letters without accents, digits and underscores, and must not start with a digit.
• Databases are created with the utf8mb4 encoding (supports all of Unicode, including emoji).
• After you create a user, the password is shown only once (“Save these credentials — you won't see the password again”). Store it in a password manager.
• The Allow remote connections option creates a user that can connect from anywhere (host %). For that to work, MariaDB has to listen on a public address (bind-address) and the port has to be allowed in the firewall. The steps are below in Direct remote access. If you don't need remote connections, leave this option unchecked.

The rest of this guide covers the same things from the command line. That's useful when you want full control over the database or need things the tab doesn't do (backups, tuning, SSH tunnel).

Option B: manual installation#

Works on both Ubuntu 24.04 and Debian 13:

apt update
apt -y install mariadb-server

• apt update – loads the current package list.
• apt -y install mariadb-server – installs the database server and the mariadb client. The service starts on its own after installation and is enabled at VPS boot.

Check that it's running:

systemctl status mariadb
mariadb --version

You'll see Active: active (running) and the version number (10.11 on Ubuntu, 11.8 on Debian). Close the systemctl output with q.

Security: mariadb-secure-installation#

The mariadb-secure-installation script removes test leftovers that have no place in production. Run it:

mariadb-secure-installation

Answer according to the table:

At the end you'll see All done!.

Logging in to the MariaDB console#

As root on the VPS:

mariadb

The MariaDB [(none)]> prompt appears. SQL statements end with a semicolon ;. Leave the console with exit.

As a different database user:

mariadb -u <USER> -p

• -u <USER> – the database user name.
• -p – asks for the password (it isn't shown while typing).

A database and user for your app#

Each app should have its own database and its own user with privileges on that database only. If someone compromises the app, they only get to its data.

Run the following in the MariaDB console (mariadb). Replace <STRONG_PASSWORD> with a password from the password generator (letters and digits only, at least 24 characters):

CREATE DATABASE aplikace CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'aplikace'@'localhost' IDENTIFIED BY '<STRONG_PASSWORD>';
GRANT ALL PRIVILEGES ON aplikace.* TO 'aplikace'@'localhost';
SHOW GRANTS FOR 'aplikace'@'localhost';

Grant privileges based on what the app needs:

Other useful statements:

SHOW DATABASES;
SELECT User, Host FROM mysql.user;
ALTER USER 'aplikace'@'localhost' IDENTIFIED BY '<NEW_PASSWORD>';
REVOKE ALL PRIVILEGES ON aplikace.* FROM 'aplikace'@'localhost';
DROP USER 'aplikace'@'localhost';

• SHOW DATABASES; – lists databases.
• SELECT User, Host FROM mysql.user; – lists users and the hosts they may connect from.
• ALTER USER … IDENTIFIED BY – changes the password.
• REVOKE … – removes privileges.
• DROP USER … – deletes the user (the database stays).

The user's host: where they may connect from#

A MariaDB user isn't just a name but a pair of name + host. 'jan'@'localhost' and 'jan'@'%' are two different users, each with their own password and privileges.

Access from your computer#

Want to look at the database with a program like HeidiSQL or DBeaver? You have two options. We recommend an SSH tunnel: the database stays unreachable from the internet and the connection is encrypted.

SSH tunnel (recommended)#

An SSH tunnel forwards a port on your computer through an encrypted SSH connection to the database port on the VPS.

1. On your computer, open a terminal (Windows Terminal, PowerShell, macOS Terminal) and run:

   ssh -L 3307:127.0.0.1:3306 -N [email protected]

   • ssh – connects to the VPS over SSH.
   • -L 3307:127.0.0.1:3306 – port 3307 on your computer is forwarded to address 127.0.0.1, port 3306 as seen from the VPS (that is, to MariaDB). Port 3307 is used so it doesn't clash with a database you might have running on your computer.
   • -N – don't run any command on the VPS, just keep the tunnel open.
   • [email protected] – the user and IP address of your VPS (from the Network tab).

   After you log in, the terminal prints nothing and seems to “hang”. That's correct: the tunnel runs until you close the window or press Ctrl+C.

2. In your database program, connect to Host 127.0.0.1, Port 3307, with a database user and password (for example aplikace).

HeidiSQL and DBeaver can also create the tunnel themselves. In HeidiSQL, choose the MariaDB/MySQL network type over SSH tunnel (… (SSH tunnel)), fill in the VPS IP address, port 22, the user and password or SSH key on the SSH tunnel tab, and set 127.0.0.1 as the database host in the main settings. In DBeaver, add SSH in the connection settings (plus button, SSH option) and check Use SSH Tunnel.

Direct remote access#

Only use this when another server or a program that can't do tunnels has to connect to the database. Always restrict access to a specific IP address.

1. Let MariaDB listen on the network. Create the file nano /etc/mysql/mariadb.conf.d/60-remote-access.cnf with this content:

   [mysqld]
   bind-address = 0.0.0.0

   • [mysqld] – the option group for the database server.
   • bind-address = 0.0.0.0 – listen on all IPv4 addresses of the VPS (this overrides the default 127.0.0.1 from 50-server.cnf, because the files are read alphabetically and the later one wins).

2. Restart MariaDB and check where it listens:

   systemctl restart mariadb
   ss -tlnp | grep 3306

   You'll see 0.0.0.0:3306.

3. Create a user with the IP address you'll connect from (for example your public IP 198.51.100.25):

   CREATE USER 'jan'@'198.51.100.25' IDENTIFIED BY '<STRONG_PASSWORD>';
   GRANT ALL PRIVILEGES ON aplikace.* TO 'jan'@'198.51.100.25';

4. Allow port 3306 only from this IP address in the customer area firewall (guide): rule Inbound · Allow · TCP · port 3306 · Source IP / range 198.51.100.25. If you use the Allow everything mode, also add a rule below it: Inbound · Drop · TCP · port 3306 without a source IP. Rules are evaluated top to bottom and the first match wins, so your IP gets through and everyone else doesn't.

5. If you use UFW, allow the port there too:

   ufw allow from 198.51.100.25 to any port 3306 proto tcp

   • allow from 198.51.100.25 – only from your IP address.
   • to any port 3306 proto tcp – to TCP port 3306.

Database backups#

The mariadb-dump program saves a database to an SQL file you can restore from at any time.

Backing up one database:

mariadb-dump --single-transaction --routines --events aplikace > /root/aplikace-$(date +%F).sql

• --single-transaction – makes a consistent backup of InnoDB tables without locking, so the app can keep running.
• --routines – includes stored procedures and functions (they aren't backed up without this flag).
• --events – includes scheduled events (also not backed up otherwise). Triggers are backed up automatically.
• aplikace – the database name.
• > /root/aplikace-$(date +%F).sql – saves the output to a file with today's date in its name.

Backing up all databases at once, compressed:

mariadb-dump --single-transaction --routines --events --all-databases | gzip > /root/vsechny-db-$(date +%F).sql.gz

• --all-databases – all databases, including the statements to create them.
• | gzip > – compresses the output; SQL usually shrinks to a fraction of its size.

Restoring:

mariadb aplikace < /root/aplikace-2026-10-01.sql
gunzip < /root/vsechny-db-2026-10-01.sql.gz | mariadb

• The first line loads the backup of one database into the existing aplikace database (if it doesn't exist, create it first with CREATE DATABASE).
• The second restores the backup of all databases; it creates the databases itself.

Automatic nightly backups and sending them off the VPS are covered in Backups on a VPS.

Performance tuning basics#

The most important performance setting is innodb_buffer_pool_size: how much RAM MariaDB may use as a cache for data and indexes. The default is 128 MB.

[mysqld]
innodb_buffer_pool_size = 1G

After the change, restart MariaDB: systemctl restart mariadb. You can find the size of your databases with this query:

SELECT table_schema AS db, ROUND(SUM(data_length + index_length) / 1024 / 1024) AS mb FROM information_schema.tables GROUP BY table_schema;

Verification#

• systemctl is-active mariadb prints active.
• mariadb -u aplikace -p aplikace -e "SELECT 1;" prints a table with the number 1 after you enter the password.
• ss -tlnp | grep 3306 shows 127.0.0.1:3306 (local only), or 0.0.0.0:3306 if you enabled direct access.

Troubleshooting#

ERROR 1698 (28000): Access denied for user 'root'@'localhost'#

You're logging in as the database root with a password (mariadb -u root -p) or as a regular system user. Root authenticates via unix socket: log in to the VPS as root and run just mariadb, or use sudo mariadb.

ERROR 1045 (28000): Access denied for user 'aplikace'@'localhost' (using password: YES)#

Wrong password, or no user exists with this host. Check SELECT User, Host FROM mysql.user;. If the app connects via 127.0.0.1 and the error says 'aplikace'@'127.0.0.1', create the user with host 127.0.0.1 as well and grant it the same privileges. You can set the password again with ALTER USER.

ERROR 1130 (HY000): Host '198.51.100.25' is not allowed to connect to this MariaDB server#

No user exists for this IP address. Create a user with a host matching your IP (see Direct remote access), or use an SSH tunnel.

ERROR 2002 (HY000): Can't connect to local … server through socket '/run/mysqld/mysqld.sock'#

MariaDB isn't running. Check the status and the log:

systemctl status mariadb
journalctl -u mariadb -n 50

• journalctl -u mariadb -n 50 – the last 50 lines of the service log. After a configuration change, the cause is often a typo in a .cnf file; the log names the offending line.

ERROR 2003 (HY000): Can't connect to … server on '203.0.113.10'#

The remote connection didn't get through. Check that MariaDB listens on 0.0.0.0 (ss -tlnp | grep 3306), that both the customer area firewall and UFW allow port 3306 for your IP, and that your IP address hasn't changed.

Importing a backup fails with Unknown command '\-'#

Newer versions of mariadb-dump add a line at the start of the file that enables “sandbox mode”, which neither the MySQL client nor older MariaDB clients understand. Import the backup with a mariadb client of the same or a newer version. If you have to import into MySQL, delete the first line containing enable the sandbox mode from a copy of the file.

Further reading#

• MariaDB: mariadb-secure-installation (opens in a new window)
• MariaDB: mariadb-dump (opens in a new window)
• MariaDB: Remote connection guide (opens in a new window)
• MariaDB: InnoDB system variables (opens in a new window)
• HeidiSQL: Help (opens in a new window)