Backing Up MariaDB

A database is files in /var/lib/mysql — but copying them while MariaDB runs gives you a broken copy. mariadb-dump asks the server for everything and writes it out as SQL: the statements that build it all again.

sudo mariadb-dump shop > ~/shop.sql
head -20 ~/shop.sql

-- MariaDB dump 10.19-11.8.6-MariaDB, for debian-linux-gnu (x86_64)
--
-- Host: localhost    Database: shop
-- ------------------------------------------------------
-- Server version	11.8.6-MariaDB-0+deb13u1 from Debian
…
CREATE TABLE `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
…
INSERT INTO `products` VALUES
(1,'Espresso',1.80),

Plain text: you can read it, grep it, compare two of them with diff.

Smaller: gzip on the way

sudo mariadb-dump shop | gzip > ~/shop-$(date +%F).sql.gz

SQL packs very well — a tenth of the size is common.

Restoring

A dump is SQL, so restoring is feeding it back:

sudo mariadb -e "CREATE DATABASE shop_test"
sudo mariadb shop_test < ~/shop.sql
sudo mariadb shop_test -e "SELECT * FROM products"

From a compressed one: zcat ~/shop-2026-11-01.sql.gz | sudo mariadb shop_test.

💡 Restore into a new database first, look at it, then switch — an old dump restored over the live data overwrites today's orders.

Every night: cron

In root's crontab (sudo crontab -e):

30 2 * * * mariadb-dump --all-databases | gzip > /var/backups/db-$(date +\%F).sql.gz

--all-databases takes every database. And the \%: in a crontab a bare % means "new line" — it has to be escaped.

⚠️ A backup on the same disk dies with the disk. Copy it somewhere else — rsync to another server (the Archives & Backups lessons).