How to install and secure MariaDB on Ubuntu

Many websites, bots and games keep their data in a MySQL-compatible database, and MariaDB is the free, widely used version of it. Installing it takes a minute. Securing it takes a few more, and it is worth doing every time, because an exposed or badly configured database is among the most common ways servers are compromised.

Install

sudo apt update
sudo apt install mariadb-server
sudo systemctl status mariadb

It starts automatically and listens only on the local machine by default. That default is a good thing, and you should be careful before changing it.

Run the security script

sudo mysql_secure_installation

It asks a series of questions. Sensible answers for a new server:

  • Switch to unix_socket authentication: on Ubuntu this is already the default for root, so you can say no or yes safely. It means root logs in through the operating system account, not a password.
  • Set or change the root password: optional with unix_socket, since root can only log in from the server itself.
  • Remove anonymous users: yes.
  • Disallow root login remotely: yes.
  • Remove the test database: yes.
  • Reload privilege tables: yes.

Afterwards, you log in as the database administrator with sudo mysql.

Create a database and a user for each application

Do not let applications use the administrator account. If one app is hacked, the attacker would own every database. Create a separate database and user for each:

sudo mysql

Then, at the prompt:

CREATE DATABASE appdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'a-long-random-password';
GRANT ALL PRIVILEGES ON appdb.* TO 'appuser'@'localhost';
FLUSH PRIVILEGES;
EXIT;
  • utf8mb4 supports all characters, including emoji.
  • 'appuser'@'localhost' means the user can only connect from the server itself.
  • appdb.* limits the permissions to that one database.

Test the login:

mysql -u appuser -p appdb

Store the password in your application's settings or environment variables, never in code that you share. See Environment variables and keeping your bot token safe.

Keep it private

The line that matters is the bind address, found in /etc/mysql/mariadb.conf.d/50-server.cnf:

bind-address = 127.0.0.1

It means the database accepts connections only from the same machine. For nearly every setup, this is exactly what you want, because the application runs on the same server.

If you need to reach the database from your own computer, for example to inspect data, do not open its port to the internet. Use an SSH tunnel instead:

ssh -L 3307:127.0.0.1:3306 alex@YOUR_SERVER_IP

Then connect your database tool to 127.0.0.1 port 3307 on your computer. The traffic travels inside the encrypted SSH connection. See Connect to your VPS over SSH.

If you absolutely must allow remote connections, change the bind address, create users restricted to specific source addresses, and allow only those addresses in your firewall, as in Set up a firewall on your VPS with UFW. Port 3306 on the open internet is attacked constantly.

Back it up

Copying the data folder while the server runs can produce a broken copy. Use a dump:

sudo mysqldump --single-transaction --databases appdb > appdb-backup.sql

Schedule it with cron, as in How to schedule tasks with cron, keep several days of copies, and store some of them away from the server. A backup you have never restored is only a hope, so try restoring it into a test database:

sudo mysql < appdb-backup.sql

See The 3-2-1 backup rule for the principle.

Basic tuning

The defaults work for small sites. If a database is slow, look first at your queries and indexes, which matter far more than settings. A few values in the configuration can help on larger servers, such as the InnoDB buffer pool size, which should be a sensible share of your memory. Change one thing at a time. Keep an eye on memory, as in What the CPU, memory and disk numbers on your server mean, because a database that grows past available memory slows dramatically.

Troubleshooting

  • "Access denied for user". Wrong password, or the user is defined for a different host. Check with SELECT user, host FROM mysql.user;.
  • Cannot connect. The service is not running. Check systemctl status mariadb, and the log.
  • "Too many connections". The application is not closing connections. Use a pool.
  • Disk full. Binary logs or large tables. See Fixing "No space left on device".

Keep the system updated with security patches. See Turn on automatic security updates.

Still stuck? Open a ticket on Discord - run /ticket in any channel, pick a department, and include your Server ID or Invoice ID so we can jump straight in.