Operations & migration

Backups

Physical: `mariabackup` (recommended)

Hot, non-blocking. Fork of Percona XtraBackup, ships with MariaDB.

mariabackup --backup --target-dir=/data/backup/$(date +%F) -uroot -p
mariabackup --prepare --target-dir=/data/backup/2026-05-18

# restore (stop server first)
systemctl stop mariadb
mariabackup --copy-back --target-dir=/data/backup/2026-05-18
chown -R mysql:mysql /var/lib/mysql
systemctl start mariadb

Logical: `mariadb-dump`

Small DBs, cross-major-version migrations:

mariadb-dump --single-transaction --routines --triggers --events \
  --all-databases -uroot -p > full.sql

The "three ones" rule

1 local copy
1 offsite copy
1 restore you have actually performed

Monitoring

Minimum metrics to watch:

Metric	Source	Alert
Connections	`SHOW STATUS LIKE 'Threads_connected'`	> 80% of `max_connections`
Buffer pool hit	`Innodb_buffer_pool_read_requests / _reads`	< 99%
Slow queries	slow log	several/min
Replication lag	`SHOW SLAVE STATUS`	`Seconds_Behind_Master > 10`
Disk	OS `df`	> 80%
Lock waits	`INFORMATION_SCHEMA.INNODB_LOCK_WAITS`	any

Tools: Percona PMM, Prometheus + mysqld_exporter, cloud-native dashboards.

Tuning starter pack

[mariadb]
innodb_buffer_pool_size        = 60% of RAM
innodb_redo_log_capacity       = 2G     # 10.11+; otherwise innodb_log_file_size
innodb_flush_log_at_trx_commit = 1
sync_binlog                    = 1
max_connections                = 500
slow_query_log                 = 1
long_query_time                = 1

innodb_flush_log_at_trx_commit = 0 can lose 1 second of committed transactions on crash. Leave it at 1 unless you accept that risk.

Query tuning triad:

EXPLAIN FORMAT=JSON
ANALYZE FORMAT=JSON SELECT ... — MariaDB-specific, runs the query and shows real numbers
pt-query-digest on the slow log

MySQL → MariaDB

Adjacent versions (MySQL 5.7 → MariaDB 10.5+)

mysqldump --all-databases --routines --triggers --events --single-transaction > dump.sql
mariadb -uroot -p < dump.sql
mariadb-upgrade -uroot -p

MySQL 8.0 → MariaDB 11.x (cross-branch)

Watch for:

utf8mb4_0900_ai_ci vs utf8mb4_uca1400_ai_ci — sort order, hash, FK matching differ
JSON function variants
Auth plugins (caching_sha2_password not in MariaDB)
optimizer_switch flags renamed
SELECT … INTO OUTFILE path rules

Process: stage the dump → run full app tests → pt-upgrade to compare query results → cut over.

PostgreSQL → MariaDB

Harder. Use pgloader. Mapping cheat sheet:

PostgreSQL	MariaDB
`SERIAL`/`BIGSERIAL`	`AUTO_INCREMENT`
`UUID`	`BINARY(16)` or native `UUID` (10.7+)
arrays	JSON arrays
`JSONB`	JSON (11.x perf much improved)
`tsvector` full-text	InnoDB FTS or external ES
`LISTEN`/`NOTIFY`	external queue
`RETURNING`	MariaDB 10.5+ supports `INSERT … RETURNING`

Operations & migration

On this page