Incremental file 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.

1mkdir /root/backup
2mkdir /root/backup/data
3mkdir /root/backup/database
4cd /root/backup

2. Environment

Create the environment file nano .autorestic.env and fill the access data to the database:

1AUTORESTIC_LOCAL_RESTIC_PASSWORD=
2
3AUTORESTIC_PROD_DB_MYSQL_HOST=localhost
4AUTORESTIC_PROD_DB_MYSQL_PORT=3306
5AUTORESTIC_PROD_DB_MYSQL_DATABASE=
6AUTORESTIC_PROD_DB_MYSQL_USER=
7AUTORESTIC_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

 1#!/usr/bin/bash
 2BACKUP_PATH=./database
 3mkdir $BACKUP_PATH
 4
 5export $(cat .autorestic.env | xargs)
 6MYSQL="$(which mysql)"
 7MYSQLDUMP="$(which mysqldump)"
 8GZIP="$(which gzip)"
 9MYSQL_PERMS=" --protocol tcp "
10MYSQL_PERMS+=" -h $(echo $AUTORESTIC_PROD_DB_MYSQL_HOST) "
11MYSQL_PERMS+=" -P $(echo $AUTORESTIC_PROD_DB_MYSQL_PORT) "
12MYSQL_PERMS+=" -u $(echo $AUTORESTIC_PROD_DB_MYSQL_USER) "
13DB="$AUTORESTIC_PROD_DB_MYSQL_DATABASE"
14export MYSQL_PWD="$AUTORESTIC_PROD_DB_MYSQL_PASS"
15DUMP_OPTIONS="--single-transaction --no-tablespaces"
16
17echo "$(date +"%d-%m-%Y %H:%M:%S") Backing up $DB to $BACKUP_PATH"
18TABLES="$($MYSQL $MYSQL_PERMS $DB -Bse 'SHOW TABLES FROM '$DB)"
19for TABLE in $TABLES ; do
20  echo "$(date +"%d-%m-%Y %H:%M:%S") Backing up $TABLE"
21  FILE="$BACKUP_PATH/${TABLE}.sql.gz"
22  $MYSQLDUMP $MYSQL_PERMS $DUMP_OPTIONS $DB $TABLE | $GZIP -9 > $FILE
23done
24echo "$(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 auto.yml

 1version: 2
 2
 3backends:
 4  local:
 5    type: local
 6    path: /root/backup/data # where you want to store all backups
 7
 8global:
 9  forget:
10    keep-daily: 7
11    keep-weekly: 4
12
13locations:
14  prod:
15    from: /var/www/html # where the website files are
16    to: local
17    cron: '0 1 * * *' # at 01:00
18    forget: "yes" # or "prune". Yes must be in quotes, otherwise it's a boolean
19    options:
20      backup:
21        exclude:
22          - 'vendor'
23
24  prod-db:
25    from: /root/backup/database/
26    to: local
27    cron: '0 0 * * *' # at 00:00
28    forget: "yes"
29    hooks:
30      before:
31      - bash backup-db-hook.sh

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

5. Initialize repository

1autorestic 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

1*/5 * * * * cd /root/backup; /usr/local/bin/autorestic -c /root/backup/auto.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.

1## list of backups
2autorestic -c auto.yml exec -av snapshots
3
4## choose one snapshot id, then list all the files within it
5autorestic -c auto.yml exec -av -- ls --long 041c88da
6
7## now you can choose to restore only one file for example, in the relative folder ./restore/
8autorestic -c auto.yml exec -av -- restore --target=./restore/ --include=/root/backup/database/example.sql.gz 041c88da

Last modified on 2024-11-18