Backup: Restic

Running `restic` every 5 seconds to back up a MySQL database on a remote Ubuntu server is technically possible but not recommended due to:

• Backup overhead (even incremental backups every 5 seconds is resource-intensive).
• Potential data inconsistency and corruption due to overlapping jobs.
• `cron` does not support per-second precision; you'd need `systemd` timers for that.

That said, here's a safe and practical version (e.g., every 1–5 minutes), and also a technical example for every 5 seconds using `systemd`.

Recommended Approach: Every 1 Minute Using systemd Timer

Step 1: Create MySQL Dump Script

/usr/local/bin/mysql_backup_restic.sh

#!/bin/bash

# Settings
MYSQL_USER="root"
MYSQL_PASS="yourpassword"
BACKUP_DIR="/var/backups/mysql"
DUMP_FILE="$BACKUP_DIR/mysql_dump.sql"
RESTIC_REPO="/path/to/restic/repo"
RESTIC_PASSWORD="yourresticpassword"

# Dump MySQL
mkdir -p "$BACKUP_DIR"
mysqldump -u "$MYSQL_USER" -p"$MYSQL_PASS" --all-databases > "$DUMP_FILE"

# Backup with restic
export RESTIC_REPO
export RESTIC_PASSWORD

restic backup "$DUMP_FILE"

Make it executable:

chmod +x /usr/local/bin/mysql_backup_restic.sh

Step 2: Create systemd Service

/etc/systemd/system/mysql-restic-backup.service

[Unit]
Description=Backup MySQL and upload to restic

[Service]
Type=oneshot
ExecStart=/usr/local/bin/mysql_backup_restic.sh

Step 3: Create Timer (Every Minute)

/etc/systemd/system/mysql-restic-backup.timer 

[Unit]
Description=Run mysql-restic-backup every minute

[Timer]
OnBootSec=1min
OnUnitActiveSec=1min
Unit=mysql-restic-backup.service

[Install]
WantedBy=timers.target

Enable and start:

sudo systemctl daemon-reexec
sudo systemctl enable --now mysql-restic-backup.timer

Every 5 Seconds (Experimental)

Use this only for testing. Real backups this frequently are unsafe.

Edit timer:

[Timer]
OnBootSec=5
OnUnitActiveSec=5
Unit=mysql-restic-backup.service

This runs the service every 5 seconds. Warning: It may trigger overlaps or heavy disk/network usage.

To avoid overlap, you can use a systemd feature to prevent concurrent executions:

Edit the service:

[Service]
Type=oneshot
ExecStart=/usr/local/bin/mysql_backup_restic.sh
ExecStartPre=/bin/sleep 1
ExecStartPost=/bin/sleep 1
TimeoutStartSec=4

Would you like a `rest-server` (remote repo) example included too?