MariaDB is the default MySQL-compatible database on Debian, Ubuntu, AlmaLinux and Rocky Linux. The commands below use MariaDB; MySQL differences are noted where they matter.
1. Install
Ubuntu and Debian:
sudo apt install -y mariadb-serverAlmaLinux and Rocky Linux:
sudo dnf install -y mariadb-server
sudo systemctl enable --now mariadbPrefer MySQL? On Ubuntu install mysql-server instead; later steps use mysql where this guide says mariadb.
2. Run the secure installation
sudo mariadb-secure-installationRecommended answers: keep root on unix_socket authentication if offered, remove anonymous users, disallow root login remotely, remove the test database and reload privilege tables. On MySQL the command is sudo mysql_secure_installation.
3. Create a database and an app user
Open the shell with sudo mariadb and run:
CREATE DATABASE appdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'use-a-long-random-password';
GRANT ALL PRIVILEGES ON appdb.* TO 'appuser'@'localhost';
FLUSH PRIVILEGES;Give each application its own user and database. Never let apps connect as root.
4. Remote access, option A: SSH tunnel (recommended)
The database stays on 127.0.0.1 and is never exposed. From your computer:
ssh -N -L 3307:127.0.0.1:3306 alex@203.0.113.25Leave it running, then point your database client (DBeaver, HeidiSQL, MySQL Workbench) at host 127.0.0.1, port 3307, user appuser. Many of these clients can open the SSH tunnel for you in their connection settings.
4. Remote access, option B: one allowed IP
Only do this if an SSH tunnel is not possible.
Warning: Never open port 3306 to the whole internet. Allow it only from the specific IP address that needs it, and use a strong password.
- Make MariaDB listen on the public interface. On Ubuntu/Debian edit
/etc/mysql/mariadb.conf.d/50-server.cnf; on AlmaLinux/Rocky edit/etc/my.cnf.d/mariadb-server.cnf. Setbind-address = 0.0.0.0in the[mysqld]section (for MySQL on Ubuntu:/etc/mysql/mysql.conf.d/mysqld.cnf). - Restart:
sudo systemctl restart mariadb. - Create a user limited to that address:
CREATE USER 'appuser'@'198.51.100.7' IDENTIFIED BY '...';and grant it rights onappdb.*only. - Open the firewall to that IP only, as shown in the UFW guide or the firewalld guide.
Back up a database
sudo mariadb-dump --single-transaction --routines appdb | gzip > appdb-$(date +%F).sql.gzmysqldump takes the same options. Restore with gunzip < appdb-2026-01-31.sql.gz | sudo mariadb appdb.
Troubleshooting
- ERROR 1045 Access denied: the user exists for a different host. Check with
SELECT user, host FROM mysql.user;because'appuser'@'localhost'and'appuser'@'198.51.100.7'are separate accounts. - Remote connection times out: the firewall blocks it or MariaDB still listens on
127.0.0.1.sudo ss -tlnp | grep 3306shows the bound address. - Service will not start after a config change:
sudo journalctl -u mariadb -n 50 --no-pagershows the bad line.