Testing Restic Incremental Backups on an Active MySQL Server Without Downtime

Running a production MySQL instance on a small home‑lab server is a common scenario for many of us. The database is the heart of the service, but it also means that any backup strategy must preserve consistency while keeping the application online. Restic is a popular, fast, deduplicating backup tool that works well with block‑level snapshots and remote backends. In this post I’ll walk through a practical, repeatable method to test incremental Restic backups against a live MySQL server, covering the trade‑offs, commands, and pitfalls you’ll encounter.

Why Incremental Testing Matters

Incremental backups are the bread and butter of modern backup workflows. They reduce bandwidth, storage, and restore time by only copying data that has changed since the last snapshot. However, when the source is a database, a naive incremental run can capture half‑written pages, corrupting the backup. MySQL’s FLUSH TABLES WITH READ LOCK (or the newer FLUSH TABLES WITH READ LOCK FOR BACKUP) can lock the tables long enough for a consistent snapshot, but that pause can be noticeable on a busy system.

Instead of locking the entire database, we can use MySQL’s built‑in point‑in‑time recovery (PITR) support: take a full backup, then stream the binary log (binlog) to Restic. The binlog contains every change, so Restic can replay it later to bring the backup up to date. The trick is to verify that the incremental run actually consumes the binlog without stopping the server.

Prerequisites

sudo apt-get install restic

Create a Restic repository (here on a local NFS share):

export RESTIC_REPOSITORY=/mnt/backup/restic
export RESTIC_PASSWORD=SuperSecret
restic init

1. Prepare MySQL for Incremental Backup

1.1 Enable Binary Logging

The binlog must be enabled and retained long enough for the backup window. Add or verify these lines in /etc/mysql/mysql.conf.d/mysqld.cnf:

[mysqld]
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 7
max_binlog_size = 100M

Restart MySQL:

sudo systemctl restart mysql

1.2 Create a Backup User

CREATE USER 'restic'@'localhost' IDENTIFIED BY 'ResticPass';
GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'restic'@'localhost';
FLUSH PRIVILEGES;

The RELOAD and LOCK TABLES privileges let us run FLUSH TABLES WITH READ LOCK if needed, while REPLICATION CLIENT allows us to read the binlog position.

2. Full Backup – The Baseline

A full backup is the anchor for all subsequent increments. We’ll use mysqldump for the data and Restic for the binary log directory.

# Dump all databases
mysqldump -u root -p --all-databases --single-transaction --quick --lock-tables=false > /tmp/all.sql

# Store dump in Restic
restic backup /tmp/all.sql

Tip: The --single-transaction flag ensures a consistent snapshot without locking tables. It works only with transactional engines (InnoDB).

After the first run, Restic will have a snapshot named something like 2026-10-29T12:00:00Z. Note its ID:

restic snapshots
# 2026-10-29T12:00:00Z  root@myserver  all.sql

3. Incremental Backup – The Real Test

The goal is to run an incremental backup while MySQL stays online and verify that Restic correctly records the binlog changes.

3.1 Capture the Current Binlog Position

mysql -u restic -pResticPass -e "SHOW MASTER STATUS\G"
# File: mysql-bin.000123
# Position: 123456

Store the file and position in variables:

BINLOG_FILE=$(mysql -u restic -pResticPass -e "SHOW MASTER STATUS\G" | grep File | awk '{print $2}')
BINLOG_POS=$(mysql -u restic -pResticPass -e "SHOW MASTER STATUS\G" | grep Position | awk '{print $2}')

3.2 Stream the Binlog to Restic

Restic can read a directory or a file stream. We’ll use mysqlbinlog to tail the binlog from the captured position and pipe it to Restic.

mysqlbinlog --start-position=$BINLOG_POS --stop-position=0 $BINLOG_FILE | \
  restic backup - --stdin --stdin-filename mysql-bin.log

Explanation:

• --start-position tells mysqlbinlog to start at the last known position.
• --stop-position=0 means “keep reading until the end of the file”.
• restic backup - reads from stdin; --stdin-filename gives the file a name inside the repository.

This command runs quickly and does not lock the database. The binlog is still being written, so Restic will capture the new entries as they appear.

3.3 Verify the Incremental Snapshot

After the command finishes, check the snapshot list:

restic snapshots
# 2026-10-29T12:00:00Z  root@myserver  all.sql
# 2026-10-29T12:05:00Z  root@myserver  mysql-bin.log

The second snapshot should be smaller than the full dump but contain the new binlog entries. Restic’s deduplication will avoid storing duplicate data from previous snapshots.

4. Automating the Incremental Cycle

You can wrap the above logic in a systemd service and timer to run every hour.

4.1 Service Unit (/etc/systemd/system/restic-mysql.service)

[Unit]
Description=Restic incremental backup for MySQL
After=mysqld.service

[Service]
Type=oneshot
Environment=RESTIC_REPOSITORY=/mnt/backup/restic
Environment=RESTIC_PASSWORD=SuperSecret
ExecStart=/usr/local/bin/restic-mysql-backup.sh

4.2 Timer Unit (/etc/systemd/system/restic-mysql.timer)

[Unit]
Description=Hourly Restic MySQL backup

[Timer]
OnCalendar=*-*-* *:00:00
Persistent=true

[Install]
WantedBy=timers.target

4.3 Backup Script (/usr/local/bin/restic-mysql-backup.sh)

#!/usr/bin/env bash
set -euo pipefail

# Capture binlog position
read BINLOG_FILE BINLOG_POS < <(
  mysql -u restic -pResticPass -e "SHOW MASTER STATUS\G" |
  awk '/File:/ {print $2} /Position:/ {print $2}'
)

# Stream binlog to Restic
mysqlbinlog --start-position=$BINLOG_POS --stop-position=0 $BINLOG_FILE |
  restic backup - --stdin --stdin-filename mysql-bin.log

Make it executable:

sudo chmod +x /usr/local/bin/restic-mysql-backup.sh

Enable the timer:

sudo systemctl enable --now restic-mysql.timer

Now Restic will automatically capture incremental changes every hour without any manual intervention.

5. Testing the Incremental Workflow

5.1 Simulate a Data Change

mysql -u root -p -e "INSERT INTO testdb.users (name) VALUES ('Alice');"

Wait a minute for the timer to run, then list snapshots:

restic snapshots
# 2026-10-29T12:05:00Z  root@myserver  mysql-bin.log
# 2026-10-29T13:00:00Z  root@myserver  mysql-bin.log

The new snapshot should be present. Verify its size:

restic info

You’ll see that the new snapshot only adds a few kilobytes, thanks to deduplication.

5.2 Restore from Incremental

To test recovery, restore the latest binlog snapshot to a fresh directory:

mkdir /tmp/restic-restore
restic restore latest --target /tmp/restic-restore

The directory will contain mysql-bin.log. Apply it to a clean MySQL instance:

mysqlbinlog /tmp/restic-restore/mysql-bin.log | mysql -u root

Check that the inserted row appears:

mysql -u root -e "SELECT * FROM testdb.users;"

If the row is present, the incremental backup worked as intended.

6. Trade‑offs and Caveats

7. Advanced Tips

7.1 Using mysqldump with --master-data

If you prefer a single dump that includes the binlog position, use:

mysqldump -u root -p --all-databases --single-transaction --master-data=2 > /tmp/full.sql

The --master-data=2 flag writes the binlog position into the dump file as a comment. Restic can store this file, and you can parse the position later if needed.

7.2 Restic’s --exclude for Large Logs

If your MySQL instance generates huge binary logs, you might want to exclude them from the full backup to keep the snapshot lean:

restic backup /tmp/all.sql --exclude /var/log/mysql/*.log

Then rely on the incremental binlog stream for changes.

7.3 Monitoring Restic Health

Use Restic’s check command to verify repository integrity:

restic check

Schedule it nightly with a cron job or systemd timer. If check reports errors, investigate immediately.

8. Security Checklist

9. Wrap‑up

By combining MySQL’s binlog with Restic’s incremental backup engine, you can keep a live database backed up without any noticeable downtime. The approach scales from a single‑node home‑lab to a small production cluster, as long as you manage binlog retention and repository encryption properly.

The key takeaways:

1. Full dump + binlog stream gives you a consistent, recoverable state.
2. Restic’s deduplication keeps storage costs low even with frequent increments.
3. Automation via systemd eliminates manual errors and ensures repeatability.
4. Security is handled by Restic’s encryption and MySQL’s user privileges.

Give it a try on your own server, tweak the timing and retention to fit your workload, and you’ll have a solid, low‑maintenance backup strategy that keeps your MySQL data safe while staying online.

See also

• Fixing the 2‑second DNS lookup lag on my Pi by tweaking resolvectl and systemd‑resolved cache
• Diagnosing a 30‑second Nginx start‑up lag on an 8‑core ARM home‑server
• When `ip link` shows no IP but you can ping: diagnosing an absent IPv4 on a bridge interface
• When Wayland Won’t Run My Games: Tweaking a Laptop for Better Battery Life
• When a backup script keeps my CPU pegged: a quick ps, nice, ionice tweak to keep the terminal responsive.