apt install mariadb-server gets you a running database. Everything that goes wrong afterwards is in the details, and most of it is avoidable if you know about it before you have data.

Install

# Debian and Ubuntu
sudo apt install mariadb-server

# Fedora
sudo dnf install mariadb-server
sudo systemctl enable --now mariadb

# Arch
sudo pacman -S mariadb
sudo mariadb-install-db --user=mysql --basedir=/usr --datadir=/var/lib/mysql
sudo systemctl enable --now mariadb

sudo mariadb-secure-installation      # or mysql_secure_installation

That script removes anonymous accounts, disables remote root login, and drops the test database. Run it. The defaults it clears are from a much older era of the internet.

Socket authentication

sudo mariadb
# you are in, with no password

This confuses people who expect a root password prompt. The root account is configured with the unix socket plugin, which trusts the operating system user connecting over the local socket. If you are root on the machine, you are root in the database.

SELECT user, host, plugin FROM mysql.user;
-- root  localhost  unix_socket
-- app   localhost  mysql_native_password

It is a good default. There is no password to leak, and access already requires root on the host.

-- if you want a password on root as well
ALTER USER 'root'@'localhost' IDENTIFIED VIA mysql_native_password
  USING PASSWORD('...');

Generally unnecessary, and it gives you one more credential to manage. Leaving socket auth in place and using sudo mariadb is the cleaner arrangement.

Users and databases

CREATE DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

CREATE USER 'myapp'@'localhost' IDENTIFIED BY 'a-long-random-password';
GRANT ALL PRIVILEGES ON myapp.* TO 'myapp'@'localhost';
FLUSH PRIVILEGES;

ALL PRIVILEGES ON myapp.* is scoped to one database, which is the important part. An application account should never have ON *.*, and should not have GRANT OPTION.

For an account that only reads:

CREATE USER 'reporting'@'10.0.0.%' IDENTIFIED BY '...';
GRANT SELECT ON myapp.* TO 'reporting'@'10.0.0.%';

The host part is half the identity. 'app'@'localhost' and 'app'@'%' are different accounts with different grants, and a login failing for an account you are sure exists is usually this.

-- what can this account do
SHOW GRANTS FOR 'myapp'@'localhost';

-- who am I connected as right now
SELECT USER(), CURRENT_USER();

USER() is who you claimed to be, CURRENT_USER() is which account row matched. When they differ, host matching is why.

Generate passwords rather than inventing them, and keep them out of your shell history:

openssl rand -base64 24

Our secrets management guide covers where to put it afterwards, and it is not in a world-readable config file.

utf8 is not UTF-8

This is the single most common data problem in MySQL and MariaDB deployments.

MySQL’s utf8 is a three byte encoding. It cannot represent characters outside the basic multilingual plane: emoji, some CJK characters, some mathematical symbols. Inserting one either raises an error or, on an older configuration, silently truncates the row at that point.

utf8mb4 is real UTF-8. Use it everywhere.

-- check what you have
SELECT schema_name, default_character_set_name
FROM information_schema.schemata;

SELECT table_name, table_collation
FROM information_schema.tables WHERE table_schema = 'myapp';

# /etc/mysql/mariadb.conf.d/50-server.cnf
[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

[client]
default-character-set = utf8mb4

-- fixing an existing database
ALTER DATABASE myapp CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
ALTER TABLE mytable CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

MariaDB 10.6 and later default to utf8mb4, and MySQL 8 does too. An older database that has been upgraded in place keeps whatever it was created with, so check rather than assume.

One consequence: utf8mb4 uses up to four bytes per character, so an index on a long VARCHAR can exceed the key length limit. That is why you occasionally see VARCHAR(191) in older schemas, and on modern InnoDB with DYNAMIC row format it is no longer necessary.

The configuration that matters

# where the config lives
mariadb --help | grep -A1 'Default options'
ls /etc/mysql/mariadb.conf.d/       # Debian and Ubuntu
ls /etc/my.cnf.d/                   # Fedora and Arch

Put your changes in a new file in the include directory rather than editing the packaged one, so upgrades do not overwrite them.

# /etc/mysql/mariadb.conf.d/99-local.cnf
[mysqld]
# the one that matters most
innodb_buffer_pool_size = 4G

# durability, see below
innodb_flush_log_at_trx_commit = 1

# one file per table, so dropping one returns space
innodb_file_per_table = 1

# connections
max_connections = 200

# slow query logging, invaluable and cheap
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1

# character set
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

innodb_buffer_pool_size is the setting worth understanding. It is how much table and index data the server keeps in memory. If your working set fits, reads never touch the disk.

The commonly quoted figure is 70% of system RAM on a dedicated database server. On a machine also running a web server and an application, much less: leave room for everything else plus the page cache. Our memory pressure guide covers what happens when you overcommit, and the failure mode is the OOM killer choosing your database.

-- is the pool big enough
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

If Innodb_buffer_pool_reads is a large fraction of Innodb_buffer_pool_read_requests, queries are going to disk and the pool is too small.

innodb_flush_log_at_trx_commit trades durability for speed. 1 flushes on every commit and loses nothing on a crash. 2 flushes once a second and can lose a second of transactions if the host dies. On a replica or a cache-like workload, 2 is a large speed gain; on anything holding data you cannot recreate, leave it at 1.

sudo systemctl restart mariadb

Most of these need a restart. Some are settable at runtime:

SET GLOBAL slow_query_log = 1;
SET GLOBAL long_query_time = 1;

Remote connections

# /etc/mysql/mariadb.conf.d/99-local.cnf
[mysqld]
bind-address = 10.0.0.5        # not 0.0.0.0

CREATE USER 'app'@'10.0.0.%' IDENTIFIED BY '...';
GRANT ALL PRIVILEGES ON myapp.* TO 'app'@'10.0.0.%';

# firewall it to the hosts that need it
sudo ufw allow from 10.0.0.10 to any port 3306

Never expose 3306 to the internet. It is scanned constantly, and the failure mode is total. If an application needs to reach a database across an untrusted network, use a WireGuard tunnel or an ssh tunnel rather than opening the port.

For TLS on the connection:

[mysqld]
ssl_cert = /etc/mysql/server-cert.pem
ssl_key = /etc/mysql/server-key.pem
ssl_ca = /etc/mysql/ca.pem
require_secure_transport = ON

-- and per account
ALTER USER 'app'@'10.0.0.%' REQUIRE SSL;

Our openssl guide covers generating the certificates.

Backups

# logical, single database
mysqldump --single-transaction --routines --triggers myapp > myapp.sql

# everything
mysqldump --single-transaction --all-databases --events --routines --triggers \
  | gzip > all-$(date -I).sql.gz

--single-transaction is not optional for InnoDB. Without it, mysqldump locks tables for the duration and your application stops. With it, the dump runs in a consistent snapshot and nothing blocks.

--routines and --triggers are also not defaults, and a restore missing your stored procedures is discovered at the worst possible time.

# restore
mysql myapp < myapp.sql
zcat all-2026-09-20.sql.gz | mysql

Logical dumps restore slowly, because the server replays every statement and rebuilds every index. A 100 GB dump can take hours. For large databases:

sudo apt install mariadb-backup

sudo mariabackup --backup --target-dir=/backup/full --user=root
sudo mariabackup --prepare --target-dir=/backup/full

Physical backups copy the data files, so restoring is a file copy rather than a replay.

Automate it with a systemd timer, and put credentials in a file rather than on the command line:

# /root/.my.cnf, mode 600
[client]
user = root
password = ...

Test the restore. Into a scratch database, on a schedule. A backup nobody has restored is a hypothesis. Our backups guide covers the wider practice.

Finding slow queries

# turn on the slow log, then
sudo mysqldumpslow -s t /var/log/mysql/slow.log | head -20

-- what is running right now
SHOW FULL PROCESSLIST;
SELECT * FROM information_schema.processlist WHERE command != 'Sleep';

-- explain a query before optimising it
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

-- missing indexes usually show as type: ALL

EXPLAIN showing type: ALL on a large table means a full scan, and an index is usually the answer. Adding indexes at random is not: each one costs write performance and space.

-- table sizes, which is where the space went
SELECT table_name,
       ROUND((data_length + index_length) / 1024 / 1024) AS mb
FROM information_schema.tables
WHERE table_schema = 'myapp'
ORDER BY (data_length + index_length) DESC;

Where the files live

sudo ls -la /var/lib/mysql/
sudo du -sh /var/lib/mysql/

If this is on a Btrfs filesystem, disable copy on write for the directory before the data goes in, since database files rewritten in place fragment badly:

sudo systemctl stop mariadb
sudo mv /var/lib/mysql /var/lib/mysql.old
sudo mkdir /var/lib/mysql
sudo chattr +C /var/lib/mysql
sudo cp -a /var/lib/mysql.old/. /var/lib/mysql/
sudo chown -R mysql:mysql /var/lib/mysql
sudo systemctl start mariadb

Our chattr guide covers why the flag has to be set on an empty directory.

For anything running under SELinux, moving the data directory requires relabelling, per our SELinux guide, and “the database will not start after I moved it” is nearly always a label problem.

Frequently Asked Questions

What is the difference between MariaDB and MySQL?

MariaDB began as a fork of MySQL when Oracle acquired it, and for basic use they are interchangeable. They have diverged since: different optimisers, different clustering, some different storage engines and features. MariaDB is the default in most distribution repositories and is community governed, which is why it is the common choice on Linux.

Why can root log in without a password but my user cannot?

Because root is configured to authenticate through the unix socket plugin, which trusts the operating system user rather than checking a password. That is why sudo mysql works with no credentials. Accounts created for applications use password authentication and need one.

Should I use utf8 or utf8mb4 for character sets?

utf8mb4 always. MySQL utf8 is a three byte encoding that cannot store emoji or many CJK characters, and inserting one either errors or silently truncates the row. utf8mb4 is real UTF-8. This is the most common data corruption problem in MySQL deployments.

What is the single most important setting to tune?

innodb_buffer_pool_size, which is how much data and index the server caches in memory. The default is small. On a dedicated database server, a common starting point is around 70 percent of system RAM, and on a shared machine considerably less, leaving room for everything else.

Is mysqldump good enough for backups?

For small to medium databases, yes, provided you use single-transaction so it does not lock the tables. Restores are slow because the server replays every statement, so on large databases a physical backup tool such as mariabackup is much faster to restore. Either way, a backup you have not restored is not a backup.

How do I let an application on another machine connect?

Change bind-address so the server listens beyond localhost, create the user with a host pattern rather than localhost, and firewall the port to only the hosts that need it. Never expose 3306 to the internet, and use TLS for the connection if it crosses anything you do not control.