Incremental file backup and db dumps with mysqldump and autorestic, restic, sql, gzip, weekly, daily

Incremental file backup and db dumps .md

Incremental file backup and MySQL backup

1. Install

Install autorestic: https://autorestic.vercel.app/installation

Let's use /root/backup to store our env file and autorestic config, make a data folder inside to store the actual backups inside, and a temporary database folder for MySQL dumps.

mkdir /root/backup
mkdir /root/backup/data
mkdir /root/backup/database
cd /root/backup

2. Environment

Create the environment file: nano .autorestic.env

AUTORESTIC_LOCAL_RESTIC_PASSWORD=

AUTORESTIC_PROD_DB_MYSQL_HOST=localhost
AUTORESTIC_PROD_DB_MYSQL_PORT=3306
AUTORESTIC_PROD_DB_MYSQL_DATABASE=
AUTORESTIC_PROD_DB_MYSQL_USER=
AUTORESTIC_PROD_DB_MYSQL_PASS=

Generate a unique key / password in AUTORESTIC_LOCAL_RESTIC_PASSWORD. This is used to encrypt and decrypt all backups. Also please save backup this key in a different location as well (password manager or something).

3. MySQL Backup

Make a bash script for MySQL backup: nano backup-db-hook.sh

#!/usr/bin/bash
BACKUP_PATH=./database
mkdir $BACKUP_PATH

export $(cat .autorestic.env | xargs)
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
GZIP="$(which gzip)"
MYSQL_PERMS=" --protocol tcp "
MYSQL_PERMS+=" -h $(echo $AUTORESTIC_PROD_DB_MYSQL_HOST) "
MYSQL_PERMS+=" -P $(echo $AUTORESTIC_PROD_DB_MYSQL_PORT) "
MYSQL_PERMS+=" -u $(echo $AUTORESTIC_PROD_DB_MYSQL_USER) "
DB="$AUTORESTIC_PROD_DB_MYSQL_DATABASE"
export MYSQL_PWD="$AUTORESTIC_PROD_DB_MYSQL_PASS"

echo "$(date +"%d-%m-%Y %H:%M:%S") Backing up $DB to $BACKUP_PATH"
TABLES="$($MYSQL $MYSQL_PERMS $DB -Bse 'SHOW TABLES FROM '$DB)"
for TABLE in $TABLES ; do
  echo "$(date +"%d-%m-%Y %H:%M:%S") Backing up $TABLE"
  FILE="$BACKUP_PATH/${TABLE}.sql.gz"
  $MYSQLDUMP $MYSQL_PERMS --no-tablespaces $DB $TABLE | $GZIP -9 > $FILE
done
echo "$(date +"%d-%m-%Y %H:%M:%S") ...done!"

(credits to jeremyharris/backup.sh for separate db tables script)

4. Config

Make a config file: nano .autorestic.yml

version: 2

backends:
  local:
    type: local
    path: /root/backup/data # where you want to store all backups

global:
  forget:
    keep-daily: 7
    keep-weekly: 4

locations:
  prod:
    from: /var/www/html # where the website files are
    to: local
    cron: '0 1 * * *' # at 01:00
    forget: "yes" # or "prune". Yes must be in quotes, otherwise it's a boolean

  prod-db:
    from: /root/backup/database/
    to: local
    cron: '0 0 * * *' # at 00:00
    forget: "yes"
    hooks:
      before:
      - bash backup-db-hook.sh

Note: The backend name must match with the env restic password name:
AUTORESTIC_EXAMPLE_RESTIC_PASSWORD=
backends:
  example:
    type: local

5. Initialize repository

autorestic check

This will check your config and initialize a new repository in the backup destination folder or remote location.

6. Cron Job

Add the cron job. autorestic recommends every 5 minutes.

crontab -e

*/5 * * * * cd /root/backup; /usr/local/bin/autorestic -c /root/backup/.autorestic.yml --ci cron --lean

---

Notes:

• Backup your key (env RESTIC_PASSWORD) somewhere else (like a password manager). If you lose it, your backups are useless.
• Use autorestic exec -av snapshots while in the /root/backup folder to see the list of backups.