Skip to content
CitexNode
All guides

Set up MariaDB

Install, secure, create a database and user, allow remote access only where needed, back up with mariadb-dump.

5 min read

On this page

MariaDB is the database server most PHP applications such as WordPress expect. This guide installs it from your distribution's repositories.

Covers Debian 12 and 13, Ubuntu 22.04 and 24.04, AlmaLinux 9 and Rocky Linux 9. All commands run as root; to log in, see Connect via SSH.

Install

Debian and Ubuntu:

bash
apt update
apt install -y mariadb-server

AlmaLinux and Rocky Linux:

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

Is the service running?

bash
systemctl status mariadb
mariadb --version

Secure it

bash
mariadb-secure-installation

The script asks a few questions. Good answers for a new server:

QuestionAnswer
Enter current password for rootjust press Enter
Switch to unix_socket authenticationn (root already logs in via the socket)
Change the root passwordn
Remove anonymous usersy
Disallow root login remotelyy
Remove test databasey
Reload privilege tablesy

After that, root can only reach the database locally as the Linux root user, without a password:

bash
mariadb

Type exit to leave the console.

Create a database and user

Each application gets its own database and its own user. Never use root for an application. First generate a strong password:

bash
openssl rand -base64 24

Then in the MariaDB console (mariadb), with your names and the password:

sql
CREATE DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'myapp'@'localhost' IDENTIFIED BY 'YOUR-PASSWORD';
GRANT ALL PRIVILEGES ON myapp.* TO 'myapp'@'localhost';
FLUSH PRIVILEGES;

'myapp'@'localhost' means the user may only log in from the server itself. That is right for an application on the same VPS.

Check that it worked

bash
mariadb -u myapp -p myapp -e 'SELECT CURRENT_USER(), DATABASE();'

After the password you should see myapp@localhost and myapp.

Remote access, only if needed

If the application runs on the same server, skip this section. MariaDB then only listens on 127.0.0.1, which is the safest state.

If you need access from another machine, allow it from one fixed address only. In the example, 203.0.113.10 stands for the address you connect from.

  1. Let MariaDB listen on all addresses. The file depends on the system:

    • Debian and Ubuntu: /etc/mysql/mariadb.conf.d/50-server.cnf
    • AlmaLinux and Rocky Linux: /etc/my.cnf.d/mariadb-server.cnf

    In the [mysqld] section (Debian/Ubuntu) or [mariadb] or [mysqld] (Alma/Rocky):

    ini
    bind-address = 0.0.0.0

    Then systemctl restart mariadb.

  2. Create a user for that address only:

    sql
    CREATE USER 'myapp'@'203.0.113.10' IDENTIFIED BY 'YOUR-PASSWORD';
GRANT ALL PRIVILEGES ON myapp.* TO 'myapp'@'203.0.113.10';
FLUSH PRIVILEGES;

    Do not use '%' as the host. That allows login attempts from anywhere.

  3. Open port 3306 in the firewall for that address only.

    Debian and Ubuntu with ufw:

    bash
    ufw allow from 203.0.113.10 to any port 3306 proto tcp

    AlmaLinux and Rocky Linux with firewalld:

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

Back up with mariadb-dump

A dump is an SQL file you can restore the database from. Create a folder and back up one database:

bash
mkdir -p /var/backups/mariadb
chmod 700 /var/backups/mariadb
mariadb-dump --single-transaction --routines --triggers myapp | gzip > /var/backups/mariadb/myapp-$(date +%F).sql.gz

All databases at once:

bash
mariadb-dump --single-transaction --routines --triggers --all-databases | gzip > /var/backups/mariadb/all-$(date +%F).sql.gz

--single-transaction creates a consistent snapshot without locking the tables (for InnoDB, the default).

Restore into an empty database:

bash
gunzip -c /var/backups/mariadb/myapp-2026-11-05.sql.gz | mariadb myapp

A dump on the same server does not help if the server is gone. How to create it every night and copy it off the server is covered in Cron jobs and timers and Set up backups.

Common errors

  • ERROR 1045 (28000): Access denied. Wrong password, or the user only exists for a different host. SELECT User, Host FROM mysql.user; lists them.
  • ERROR 2002: Can't connect to local server through socket. The service is not running. systemctl status mariadb and journalctl -u mariadb show why, often a full disk (df -h).
  • No connection from outside. Check with ss -tlnp | grep 3306 that MariaDB listens on 0.0.0.0, then the firewall and the user's host.
  • Broken umlauts or emojis. The database was not created with utf8mb4. Use the statement above for new databases.

What next

If you get stuck, open a ticket.