Install and secure MariaDB or MySQL, and allow remote access safely

Install MariaDB (or MySQL), lock it down, create a least-privilege app user, and connect remotely the safe way: an SSH tunnel or a single allowed IP.

  • Intermediate
  • Linux VPS
  • 3 min read

By RunRacks Team · Published

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:

Terminal
sudo apt install -y mariadb-server

AlmaLinux and Rocky Linux:

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

Prefer MySQL? On Ubuntu install mysql-server instead; later steps use mysql where this guide says mariadb.

2. Run the secure installation

Terminal
sudo mariadb-secure-installation

Recommended 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:

SQL
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.

The database stays on 127.0.0.1 and is never exposed. From your computer:

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

Leave 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.

  1. 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. Set bind-address = 0.0.0.0 in the [mysqld] section (for MySQL on Ubuntu: /etc/mysql/mysql.conf.d/mysqld.cnf).
  2. Restart: sudo systemctl restart mariadb.
  3. Create a user limited to that address: CREATE USER 'appuser'@'198.51.100.7' IDENTIFIED BY '...'; and grant it rights on appdb.* only.
  4. Open the firewall to that IP only, as shown in the UFW guide or the firewalld guide.

Back up a database

Terminal
sudo mariadb-dump --single-transaction --routines appdb | gzip > appdb-$(date +%F).sql.gz

mysqldump 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 3306 shows the bound address.
  • Service will not start after a config change: sudo journalctl -u mariadb -n 50 --no-pager shows the bad line.

Next steps

Was this guide helpful?

Still stuck?

Ask RunRacks AI for a quick answer, or open a ticket and our team will take a look at your server.

Linux VPS

Deploy a Node.js app with PM2 behind Nginx

Install a Node.js LTS release, keep your app running with PM2 across crashes and reboots, and serve it through Nginx with HTTPS.

  • Intermediate
  • Linux VPS
  • 3 min read

Linux VPS

Create an SFTP-only user limited to one folder

Give someone file access over SFTP without a shell, locked inside one folder. Configure internal-sftp with a Match block, set the chroot ownership right and test with WinSCP.

  • Advanced
  • Linux VPS
  • 5 min read