MHOSTMHOST

MySQL / MariaDB: install and configure

In this article you'll install MariaDB — the modern standard "MySQL" for Linux — on your mHost VPS, run mysql_secure_installation to lock it down, create a database with a dedicated user and privileges, configure bind-address for local-only or remote access, and connect to the server with the mysql client.

What you'll need

  • An mHost VPS running Ubuntu 22.04/24.04, Debian 11/12, or AlmaLinux/Rocky Linux 8/9.
  • SSH access as root or a user with sudo.
Tip: all commands below assume you're root. If you're using a regular sudo account, prefix each command with sudo.
Tip: if you need a full web server and PHP alongside the database, it's easier to use the ready-made LEMP/LAMP recipe in VMmanager 6 (see "Ready-made VMmanager 6 recipes") — it deploys the whole stack, MariaDB included, in one run. Below is how to install and configure MariaDB by itself, manually.

Step 1. Install MariaDB

Ubuntu / Debian

bash
apt update
apt install mariadb-server

AlmaLinux / Rocky Linux

bash
dnf install mariadb-server

The mariadb-server package is in the standard repository for both distro families (AppStream on AlmaLinux/Rocky) — no third-party repo needed. The client — the mysql command — is installed alongside the server automatically.

Check that it installed:

bash
mysql --version
Tip: apt/dnf install the MariaDB version from your distro's own repository (typically somewhere between 10.5 and 10.11+, depending on the release) — that's fine for a regular project. If you need a specific newer version, MariaDB maintains its own repository with a setup-command generator for any distro — see mariadb.org/download.
MySQL or MariaDB? The mysql-server package in the standard repositories of Ubuntu, Debian, AlmaLinux, and Rocky Linux has long been replaced by MariaDB — a MySQL fork compatible with it on protocol, SQL, tooling, and port 3306 (it uses the same mysql client). Everything below installs MariaDB — that's the standard modern "MySQL" for Linux servers. If you specifically need "genuine" MySQL from Oracle, add its official repository (see dev.mysql.com/downloads/repo) — the rest of this article's steps (database, user, bind-address) carry over almost word for word.

Step 2. Enable and start MariaDB

bash
systemctl enable --now mariadb
systemctl status mariadb

enable --now both adds MariaDB to startup and starts it immediately. The status should show active (running).

Step 3. Secure the installation: mysql_secure_installation

A fresh MariaDB install isn't locked down yet — it has anonymous users, a test database, and a root account with no restriction on remote login. The mysql_secure_installation script fixes all of that in one pass:

bash
mysql_secure_installation
Tip: starting with MariaDB 10.5, the command has a "native" name, mariadb-secure-installation; mysql_secure_installation still works as a symlink kept for backward compatibility — either name works, including on MySQL.

The script asks a series of questions:

  • Enter current password for root — a fresh install has no password yet, just press Enter.
  • Switch to unix_socket authentication (the wording varies between versions, and on Ubuntu/Debian the question may not appear at all — that login method is already enabled by default there) — say yes (Y): it lets root log in without a password, but only from the server itself as the system root user, and it's more secure than a password.
  • Change the root password — if you're keeping unix_socket auth, a separate root password isn't required, so you can answer n; if you want a password in addition to the socket, answer Y and set a strong one.
  • Remove anonymous users — Y.
  • Disallow root login remotely — Y: root shouldn't be able to log in over the network at all.
  • Remove test database and access to it — Y.
  • Reload privilege tables now — Y.
Verify: the exact wording and order of the questions vary slightly between MariaDB/MySQL versions and distros (Oracle MySQL may add a separate question about the validate_password plugin and password strength level before this list) — answer by intent rather than looking for an exact text match.
Done: you can now log into the MariaDB console as root from the server itself — no password, via unix_socket: the mysql command (or sudo mysql if you're on a regular account). We'll use this console in the next step.

Step 4. Create a database, a user, and privileges

Connect to the MariaDB console as root:

bash
mysql

Inside the console (the MariaDB [(none)]> prompt), run three statements in a row — replace app_db, app_user, and especially STRONG_PASSWORD_HERE with your own values:

sql
CREATE DATABASE app_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'STRONG_PASSWORD_HERE';
GRANT ALL PRIVILEGES ON app_db.* TO 'app_user'@'localhost';
  • CREATE DATABASE — the utf8mb4 charset correctly stores any character, including emoji and most languages; plain utf8 (without mb4) in MySQL/MariaDB is a legacy 3-byte variant not used for new projects.
  • CREATE USER — the @'localhost' part restricts the user to connections from this same server (over a unix socket or 127.0.0.1). Remote access needs a separate entry — see step 5.
  • GRANT — privileges are granted only on the app_db database, not on every database on the server (*.*). That's enough for an ordinary application; don't use root for application connections.
Tip: in current MySQL/MariaDB versions, FLUSH PRIVILEGES; isn't needed after GRANT/CREATE USER — the server applies the changes immediately. The command is only needed after directly editing the mysql.* system tables with INSERT/UPDATE. Many older tutorials still include it out of habit — it's not wrong, just an extra step.

Check that the privileges were granted correctly:

sql
SHOW GRANTS FOR 'app_user'@'localhost';

Exit the console:

sql
EXIT;
Important: don't grant applications GRANT ALL PRIVILEGES ON *.*, and don't use root as an application user. Privileges should be scoped to a specific database (as in the example above) — that way a leaked password from one application doesn't expose the other databases on the server.

Step 5. bind-address: local-only or remote access

bind-address determines which network interface MariaDB accepts TCP connections on. Check the current value straight from the client:

bash
mysql -e "SHOW VARIABLES LIKE 'bind_address';"

or at the OS level:

bash
ss -tlnp | grep 3306

Local access only (default, recommended)

If the database is only needed by an application on this same VPS (PHP, Django, FastAPI, Node.js — doesn't matter, they all connect over localhost), there's no need to open port 3306 to the outside at all — this is the safest option.

On Ubuntu/Debian this is already the default in the package's config:

ini
# /etc/mysql/mariadb.conf.d/50-server.cnf — [mysqld]
bind-address = 127.0.0.1
Verify: MariaDB itself listens on all interfaces out of the box — it's specifically the Ubuntu/Debian package that restricts it to 127.0.0.1 via this file. The AlmaLinux/Rocky package may not set that restriction by default — be sure to check with the command at the start of this step, and if you need strictly local access, set the line explicitly:
ini
# /etc/my.cnf.d/mariadb-server.cnf — [mysqld]
bind-address = 127.0.0.1

After editing the file, restart MariaDB — restart, not reload: bind-address is only applied at startup:

bash
systemctl restart mariadb

Remote access

If you need to connect to the database from outside — from another server or your own computer — change bind-address to the VPS's public IP address, or to 0.0.0.0 (listen on all interfaces):

ini
bind-address = 0.0.0.0

and restart MariaDB the same way as above.

Then add a separate user for connecting from that external address specifically — the @'localhost' entry from step 4 won't match remote connections:

sql
CREATE USER 'app_user'@'203.0.113.10' IDENTIFIED BY 'STRONG_PASSWORD_HERE';
GRANT ALL PRIVILEGES ON app_db.* TO 'app_user'@'203.0.113.10';

Replace 203.0.113.10 with the actual IP address you'll be connecting from. Instead of '%' (allow from any IP), try to specify a concrete address or subnet — that way the password isn't your only line of defense.

Important: port 3306 exposed to the internet is a frequent target for password brute-forcing. If ufw isn't set up on the server yet, go through the "Setting up the UFW firewall" article first — it installs ufw, allows SSH, and enables the firewall. Then open 3306 not to "anywhere," but only to a specific address:

Ubuntu / Debian (ufw)

bash
ufw allow from 203.0.113.10 to any port 3306 proto tcp

AlmaLinux / Rocky (firewalld)

bash
firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="203.0.113.10" port protocol="tcp" port="3306" accept'
firewall-cmd --reload
Tip: if the source of the connections isn't known in advance, or there are many of them, consider an SSH tunnel (ssh -L 3306:localhost:3306 user@SERVER_IP) or a VPN instead of opening 3306 to the public internet — that way you can leave bind-address at 127.0.0.1 altogether.

Step 6. Connect with the client

Locally, on the VPS itself

As root, login is passwordless, via the unix socket (see step 3):

bash
mysql

As the application user — with a password:

bash
mysql -u app_user -p app_db

The client will prompt for the password (STRONG_PASSWORD_HERE from step 4) and open the MariaDB [app_db]> prompt.

Tip: if you explicitly pass -h 127.0.0.1 instead of leaving the host empty, the client connects over TCP instead of the unix socket — and the server matches that connection against the host 127.0.0.1, not localhost. That won't work for the user from step 4 (@'localhost'). In practice it's simpler to leave the host out: the mysql client defaults to the socket for localhost.

Remotely, from another computer

Install a client on the machine you'll be connecting from:

  • Ubuntu/Debian: apt install mariadb-client
  • AlmaLinux/Rocky/Fedora: dnf install mariadb
  • macOS (Homebrew): brew install mariadb

Connect, specifying the server's IP:

bash
mysql -h SERVER_IP -u app_user -p app_db

GUI clients understand the same protocol too — DBeaver, TablePlus, HeidiSQL, MySQL Workbench: point them at the same host, port 3306, username, and password.

Done: after entering the password you should see the MariaDB [app_db]> prompt. Check that the database is there:
sql
SHOW DATABASES;

app_db should be in the list.

What's next

Sooner or later you'll need to restore the data in this database — don't put off setting up regular backups, see "Database backups". If you haven't restricted access to the server with a firewall yet, go back to the "Setting up the UFW firewall" article — without it, an open port 3306 (or really any service) stays exposed to scanning and brute-forcing.

Related articles

PreviousPostgreSQL: install and configureNextRedis: install and configure