Consistent MariaDB backups with an LVM snapshot

Most steps run on rocky01 as student with sudo; step 6 and part of step 7 run on ubu01. Take snapshots of both VMs first (VBoxManage snapshot rocky01 take before-l63 and the same for ubu01). Step 2 needs two SSH sessions to rocky01 side by side.

1Look at the database volume and the space for a snapshot

Run this on rocky01:

sudo vgs vg_data
sudo lvs vg_data
df -h /var/lib/mysql
sudo du -sh /var/lib/mysql
sudo mariadb -e "SELECT VERSION(); SELECT COUNT(*) AS orders FROM saatshop.orders;"

You should see something like this:

  VG      #PV #LV #SN Attr   VSize VFree
  vg_data   2   3   0 wz--n- 9.99g 2.24g
  LV      VG      Attr       LSize Pool Origin Data%  Meta%  Move Log Cpy%Sync Convert
  lv_app  vg_data -wi-ao---- 4.25g
  lv_db   vg_data -wi-ao---- 2.00g
  lv_logs vg_data -wi-ao---- 1.50g
Filesystem                 Size  Used Avail Use% Mounted on
/dev/mapper/vg_data-lv_db  2.0G  197M  1.8G  10% /var/lib/mysql
125M	/var/lib/mysql
VERSION()
10.11.18-MariaDB
orders
5

vg_data has 2.24 GiB unallocated after lv_app (4.25 GiB), lv_logs (1.50 GiB) and lv_db (2.00 GiB), so a 512 MiB snapshot fits with room to spare. The database itself is only about 125 MiB, most of it the 96 MiB redo log, ib_logfile0. If your VFree is below 512 MiB, look for a leftover snapshot from Lesson 46 with sudo lvs -a vg_data before you go on.

✅ Checkpoint: VFree is at least 512 MiB, lv_db is mounted on /var/lib/mysql, and SaatShop has its five orders.

2Watch the read lock block a real write

In the first session, open the MariaDB client as root and take the lock:

sudo mariadb
FLUSH TABLES WITH READ LOCK;

Query OK, 0 rows affected (0.003 sec)

In the second session, place an order as the app user (password Lab-Only-Pass-56). It will hang:

mariadb -u saatshop -p saatshop -e "INSERT INTO orders (customer, item, qty, amount) VALUES ('Kavya Nair', 'Brass diya set', 2, 898.00);"

Back in the first session, look at what the server is doing, then let it go:

SELECT ID, USER, TIME, STATE, LEFT(INFO,40) AS INFO FROM information_schema.PROCESSLIST WHERE COMMAND = 'Query' ORDER BY ID DESC;
UNLOCK TABLES;
exit

You should see something like this:

+----+----------+------+-------------------------+------------------------------------------+
| ID | USER     | TIME | STATE                   | INFO                                     |
+----+----------+------+-------------------------+------------------------------------------+
|  9 | saatshop |    8 | Waiting for backup lock | INSERT INTO orders (customer, item, qty, |
|  8 | root     |    0 | Filling schema table    | SELECT ID, USER, TIME, STATE, LEFT(INFO, |
+----+----------+------+-------------------------+------------------------------------------+
2 rows in set (0.001 sec)
Query OK, 0 rows affected (0.000 sec)

The app’s INSERT waited 8 seconds in state “Waiting for backup lock”, the name MariaDB 10.5 and later use for the global read lock. The moment you ran UNLOCK TABLES, the second session finished. Reads were never blocked.

Remove the test order so SaatShop stays at five, the number later lessons expect:

sudo mariadb -e "DELETE FROM saatshop.orders WHERE customer = 'Kavya Nair'; SELECT COUNT(*) AS orders FROM saatshop.orders;"

orders
5

✅ Checkpoint: You saw the INSERT in state “Waiting for backup lock” and it finished right after UNLOCK TABLES.

3Lock, snapshot and release in one session

Paste this whole block. Everything between the two EOF lines goes to one client session:

sudo mariadb <<'EOF'
SET SESSION lock_wait_timeout = 10;
FLUSH TABLES WITH READ LOCK;
system date +%T.%N
system lvcreate --snapshot --size 512M --name lv_db_snap vg_data/lv_db
system date +%T.%N
UNLOCK TABLES;
EOF
sudo lvs vg_data

You should see something like this:

15:52:10.418203877
File descriptor 3 (socket:[51874]) leaked on lvcreate invocation. Parent PID 6402: mariadb
  Logical volume "lv_db_snap" created.
15:52:10.802617306
  LV         VG      Attr       LSize   Pool Origin Data%  Meta%  Move Log Cpy%Sync Convert
  lv_app     vg_data -wi-ao----   4.25g
  lv_db      vg_data owi-aos---   2.00g
  lv_db_snap vg_data swi-a-s--- 512.00m      lv_db  0.00
  lv_logs    vg_data -wi-ao----   1.50g

The two timestamps show the lock was held for under 0.4 seconds, the time lvcreate needed. The “leaked” line is a harmless warning: lvcreate inherited the client’s connection to MariaDB (file descriptor 3) and LVM closes such descriptors and says so. The script version sets LVM_SUPPRESS_FD_WARNINGS=1 in front of lvcreate to keep the log clean. lv_db is now an origin (owi) and lv_db_snap is its snapshot at 0.00 percent.

✅ Checkpoint: lvs shows lv_db_snap with Attr swi-a-s---, Origin lv_db and a 512 MiB COW area.

4Mount the snapshot read-only and look inside

Run this:

sudo mkdir -p /mnt/dbsnap
sudo mount -o ro,nouuid /dev/vg_data/lv_db_snap /mnt/dbsnap
findmnt /mnt/dbsnap
sudo ls /mnt/dbsnap/saatshop
sudo umount /mnt/dbsnap
sudo lvremove -y vg_data/lv_db_snap

You should see something like this:

TARGET      SOURCE                         FSTYPE OPTIONS
/mnt/dbsnap /dev/mapper/vg_data-lv_db_snap xfs    ro,relatime,seclabel,nouuid,attr2,inode64,logbufs=8,logbsize=32k,noquota
db.opt  orders.frm  orders.ibd
  Logical volume "lv_db_snap" successfully removed.

The saatshop folder holds the table definition (orders.frm) and the InnoDB data file (orders.ibd), frozen at 15:52:10 while the live ones keep changing. Without nouuid the mount fails with the duplicate UUID error from Lesson 46. You removed the snapshot by hand this time; from now on the post-script does it.

✅ Checkpoint: sudo lvs vg_data lists only lv_app, lv_db and lv_logs again, and /mnt/dbsnap exists and is empty.

5Put the freeze into the pre and post scripts

Replace the Lesson 62 pre-script on rocky01 (paste the whole block):

sudo tee /usr/local/sbin/backup-pre.sh >/dev/null <<'EOF'
#!/bin/bash
# /usr/local/sbin/backup-pre.sh  (SaatVedha Linux course, Lessons 62 and 63)
# Runs as root on rocky01 before every backup. Exit 0 = go ahead, anything else stops the job.
set -euo pipefail
META=/var/local/backup-meta
VG=vg_data
LV=lv_db
SNAP=lv_db_snap
SNAP_SIZE_MB=512            # room for the changes made while the copy runs
SNAP_MNT=/mnt/dbsnap
say() { logger -t backup-pre "$*"; echo "backup-pre: $*"; }

# 1. Only back up a healthy app, or you save a broken state
for svc in mariadb php-fpm nginx; do
    if ! systemctl is-active --quiet "$svc"; then
        say "FAIL: $svc is not running"
        exit 2
    fi
done

# 2. Save what you need to rebuild the server, not only its files
install -d -m 0700 "$META"
rpm -qa --qf '%{NAME}-%{VERSION}-%{RELEASE}.%{ARCH}\n' | sort > "$META/packages.txt"
lsblk -o NAME,SIZE,TYPE,FSTYPE,MOUNTPOINTS > "$META/lsblk.txt"
{ vgs; lvs; } > "$META/lvm.txt" 2>&1
cp /etc/fstab "$META/fstab"

# 3. A logical dump of the SaatShop database
mariadb-dump --single-transaction --routines --triggers --databases saatshop > "$META/saatshop.sql"

# 4. A consistent LVM snapshot of the whole database volume (Lesson 63)
if lvs "$VG/$SNAP" >/dev/null 2>&1; then
    say "FAIL: $VG/$SNAP already exists, the last run did not clean up"
    exit 3
fi
free_mb=$(vgs --noheadings --units m --nosuffix -o vg_free "$VG" | awk '{printf "%d", $1}')
if (( free_mb < SNAP_SIZE_MB )); then
    say "FAIL: $VG has ${free_mb} MiB free, the snapshot needs ${SNAP_SIZE_MB} MiB"
    exit 3
fi
# The read lock lives only as long as this one client session, so the snapshot
# is taken from inside the session with the client's "system" command.
if ! mariadb <<SQL
SET SESSION lock_wait_timeout = 10;
FLUSH TABLES WITH READ LOCK;
system LVM_SUPPRESS_FD_WARNINGS=1 lvcreate --snapshot --size ${SNAP_SIZE_MB}M --name $SNAP $VG/$LV
UNLOCK TABLES;
SQL
then
    say "FAIL: could not lock MariaDB within 10 seconds"
    exit 4
fi
# The client ignores the exit code of "system", so check the result yourself
if ! lvs "$VG/$SNAP" >/dev/null 2>&1; then
    say "FAIL: snapshot $VG/$SNAP was not created"
    exit 4
fi
mkdir -p "$SNAP_MNT"
mount -o ro,nouuid "/dev/$VG/$SNAP" "$SNAP_MNT"
say "OK: metadata, saatshop.sql and $SNAP (mounted on $SNAP_MNT) ready"
EOF

Replace the post-script, check both, and run them by hand:

sudo tee /usr/local/sbin/backup-post.sh >/dev/null <<'EOF'
#!/bin/bash
# /usr/local/sbin/backup-post.sh  (SaatVedha Linux course, Lessons 62 and 63)
# Runs as root on rocky01 after every backup attempt. $1 is "ok" or "failed".
# It must clean up even after a failed run, so it does not stop at the first error.
set -uo pipefail
META=/var/local/backup-meta
VG=vg_data
SNAP=lv_db_snap
SNAP_MNT=/mnt/dbsnap
result=${1:-unknown}
rc=0
say() { logger -t backup-post "$*"; echo "backup-post: $*"; }

# Release the database snapshot: unmount it, then remove it
if mountpoint -q "$SNAP_MNT"; then
    umount "$SNAP_MNT" || { say "WARN: could not unmount $SNAP_MNT"; rc=1; }
fi
if lvs "$VG/$SNAP" >/dev/null 2>&1; then
    used=$(lvs --noheadings -o data_percent "$VG/$SNAP" | tr -d ' ')
    say "snapshot $SNAP was ${used}% full"
    lvremove -y "$VG/$SNAP" >/dev/null || { say "WARN: could not remove $VG/$SNAP"; rc=1; }
fi

# The dump holds customer data: once it is on the backup server, don't keep a spare copy here
rm -f "$META/saatshop.sql"
date '+%F %T' > "$META/last-$result"
say "backup result recorded: $result"
exit "$rc"
EOF
sudo bash -n /usr/local/sbin/backup-pre.sh && sudo bash -n /usr/local/sbin/backup-post.sh && echo "syntax OK"
sudo /usr/local/sbin/backup-pre.sh; echo "pre exit code: $?"
findmnt -n /mnt/dbsnap
sudo /usr/local/sbin/backup-post.sh ok

You should see something like this:

syntax OK
  Logical volume "lv_db_snap" created.
backup-pre: OK: metadata, saatshop.sql and lv_db_snap (mounted on /mnt/dbsnap) ready
pre exit code: 0
/mnt/dbsnap /dev/mapper/vg_data-lv_db_snap xfs    ro,relatime,seclabel,nouuid,attr2,inode64,logbufs=8,logbsize=32k,noquota
backup-post: snapshot lv_db_snap was 0.02% full
backup-post: backup result recorded: ok

The pre-script keeps everything from Lesson 62 and adds section 4: the leftover check, the free-space check, the lock and snapshot in one here-document, a check that the snapshot exists (the client ignores the exit code of system), and the read-only mount. If FLUSH TABLES WITH READ LOCK can’t get the lock within 10 seconds, the client stops with error 1205 before lvcreate ever runs, and the script exits 4.

The post-script deliberately has no set -e: it must keep going and clean up even when one step fails. It unmounts, records how full the snapshot got, removes it, and exits 1 only if the cleanup itself failed. The file names, arguments and sudo rule are the same as in Lesson 62, so bkpsvc needs no new rights.

✅ Checkpoint: pre exits 0 with the snapshot mounted on /mnt/dbsnap, and post reports the snapshot’s Data% and records “ok”.

6Add the snapshot to the nightly job on ubu01

On ubu01, add /mnt/dbsnap to the job’s sources and run the job through systemd:

sudo sed -i 's#^SOURCES=.*#SOURCES=(/etc /srv/app /var/local/backup-meta /mnt/dbsnap)#' /usr/local/bin/backup-rocky01.sh
grep '^SOURCES' /usr/local/bin/backup-rocky01.sh
sudo systemctl start backup-rocky01.service
journalctl -u backup-rocky01.service -n 8 -o cat --no-pager
ls /srv/backup/rocky01/$(date +%F)/mnt/dbsnap/saatshop
cd /srv/backup/rocky01/$(date +%F)/mnt/dbsnap && getfattr -n user.rsync.%stat ibdata1; cd

You should see something like this:

SOURCES=(/etc /srv/app /var/local/backup-meta /mnt/dbsnap)
Starting backup-rocky01.service - Pull a backup of rocky01 into /srv/backup (SaatVedha Lesson 62)...
  Logical volume "lv_db_snap" created.
backup-pre: OK: metadata, saatshop.sql and lv_db_snap (mounted on /mnt/dbsnap) ready
backup-post: snapshot lv_db_snap was 0.04% full
backup-post: backup result recorded: ok
backup-rocky01: OK: /srv/backup/rocky01/2026-10-27 (156M)
backup-rocky01.service: Deactivated successfully.
Finished backup-rocky01.service - Pull a backup of rocky01 into /srv/backup (SaatVedha Lesson 62).
db.opt  orders.frm  orders.ibd
# file: ibdata1
user.rsync.%stat="100660 0,0 27:27"

The job is the same as in Lesson 62; only the source list grew. The snapshot lived for the length of the copy and only 0.04 percent of its 512 MiB was used, because SaatShop barely writes at night. ibdata1 arrived with its real owner saved: UID and GID 27, the mysql account on Rocky, and mode 0660. On rocky01, sudo lvs vg_data shows no snapshot, because the post-script removed it.

✅ Checkpoint: The journal shows the snapshot created and removed with “result recorded: ok”, and the saatshop folder is in tonight’s backup on ubu01.

7Restore the copy into a scratch MariaDB and prove it starts

On ubu01, send tonight’s database copy back to your home folder on rocky01 (this uses your Lesson 33 key):

rsync -a /srv/backup/rocky01/$(date +%F)/mnt/dbsnap/ rocky01:restore-test/

On rocky01, put it in a folder SELinux treats like /var/lib/mysql, and start a second MariaDB on it with no network and its own socket:

sudo semanage fcontext -a -e /var/lib/mysql /var/lib/mysql-restore
sudo mkdir /var/lib/mysql-restore
sudo rsync -a --chown=mysql:mysql ~/restore-test/ /var/lib/mysql-restore/
sudo rm -f /var/lib/mysql-restore/mysql.sock
sudo restorecon -R /var/lib/mysql-restore
ls -ldZ /var/lib/mysql-restore
sudo systemd-run --unit=mariadb-restore-test --uid=mysql --gid=mysql /usr/libexec/mariadbd --no-defaults --datadir=/var/lib/mysql-restore --socket=/var/lib/mysql-restore/restore.sock --pid-file=/var/lib/mysql-restore/restore.pid --skip-networking --innodb-buffer-pool-size=64M
sleep 5; sudo journalctl -u mariadb-restore-test -o cat --no-pager | grep -E 'crash recovery|To recover|ready for'
sudo mariadb --socket=/var/lib/mysql-restore/restore.sock -t -e "SELECT COUNT(*) AS orders, MAX(id) AS last_id FROM saatshop.orders; CHECK TABLE saatshop.orders;"

You should see something like this:

drwxr-xr-x. 6 mysql mysql unconfined_u:object_r:mysqld_db_t:s0 4096 Oct 27 16:21 /var/lib/mysql-restore
Running as unit: mariadb-restore-test.service; invocation ID: 3e0b7c5a91d24f6c8a1b2d9e7f406c15
2026-10-27 16:21:44 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=58103
2026-10-27 16:21:44 0 [Note] InnoDB: To recover: 12 pages
2026-10-27 16:21:44 0 [Note] /usr/libexec/mariadbd: ready for connections.
+--------+---------+
| orders | last_id |
+--------+---------+
|      5 |       5 |
+--------+---------+
+-----------------+-------+----------+----------+
| Table           | Op    | Msg_type | Msg_text |
+-----------------+-------+----------+----------+
| saatshop.orders | check | status   | OK       |
+-----------------+-------+----------+----------+

The equivalence rule tells SELinux to label /var/lib/mysql-restore the same way as /var/lib/mysql, so MariaDB, confined as mysqld_t, may use it. systemd-run starts mariadbd as a temporary service, which runs it in the right SELinux domain; --no-defaults keeps it away from /etc/my.cnf.d, so it can’t collide with the real server’s port or socket. InnoDB applied a few pages of redo log, the normal “crash recovery” after any snapshot, and the table checks clean with all five orders.

Stop the scratch server and remove the copies:

sudo systemctl stop mariadb-restore-test
sudo rm -rf /var/lib/mysql-restore ~/restore-test

A restore test is only finished when it’s written down. Note the date, the backup you used, how long the copy and the start took, and the row count, so next month you can compare. At work, do this on a separate server, never next to production.

✅ Checkpoint: The scratch MariaDB started, showed 5 orders with last id 5 and CHECK TABLE said OK, and the restore folders are gone again.