BORG Backup | local and remote backup | part 2/2

The second part now deals with the backup of a remote SERVERS, PC or notebooks. We use SSH/SSHFS to backup the remote WINDOWS 11 with its data, Docker data inc. SQL. For this we use a script that does all the typing in the terminal / command prompt for us.

The creation of the users and SSH keys is described in part 1. As well as the basics and the basic script that we use for backup.

Requirements

First initialize the repository. In this case I use the directory “/backup-server” as repository. If you use another directory, make sure you always adapt it to the example here. The user for me is “borgback” as it is described in the part. If you use a different user name, you have to adjust the command lines and the script accordingly.

sudo borg init --encryption=repokey /backup-server

Mount drives with SSHfs

To mount the drive of the remote machine we use “SSHfs”. You can easily install this if you don’t already have it.

sudo apt install sshfs

Next you need the directory where we will mount the remote drive. Create it in the directory “/media” and give it the appropriate access rights.

sudo mkdir /media/backup-server
sudo chown borgback:borgback /media/backup-server

As a last preparation you edit the file “fuse.conf” to give SSHfs the right user rights.

sudo nano /etc/fuse.conf

remove the “#” in front of “user_allow_other

user_allow_other

Save and exit the editor. Please restart your PC !

now you can mount your remote drive

sshfs -o allow_root [email protected]:C:/ /media/backup-server

Remember to change the username@PIaddress in the command line if necessary.

In the video I explain in detail how you can view the mounted drive and how the directory structure is built. It is important to know how the directory structure is built and which paths you have to work with. Because just one typo, or one wrong path, and the whole thing just brings error messages.

Execute test backup

For testing we backup all “Documente” folders of all users on the remote machine

sudo borg create -s -p /backup-server::'test backup' /media/backup-server/Users/*/Documents

Unmount mounted drive

umount /media/beckup-server

Save Repository Key

Save your repository password and key ! If you ever lose access you will need the password and key to access the backup. You can export the key, just use

sudo borg key export /backup-server >desktop/repro-key-server.txt

Backup SQL with mySQLdump

MySQL, the database, is probably one of the most valuable pieces in any used network. Well you and I agree that we want to backup the SQL database as well. For this we use our repro “/backup-server” and our own backup set “sql-server”.

If you don’t have “mysqldump” installed, please use the command

sudo apt install mysql-client-core-8.0

under Ubuntu, Mint etc. with other distributions simply adapt the command briefly.

Now to the script, els first the main script. The entries you have to change, I have marked in bold. Please also pay attention to the explanations in the video.

Script file: backup-server.sh
(Please pay attention to the authorization as executable program)

#!/bin/sh

# Einfaches BORG Backup script.sh
# by Michael Klissner, www.klissner.uk

# --------------------------------------------------------------------------------------------
# --- Parameter und Einstellungen
# --------------------------------------------------------------------------------------------
# Parameter
REPOSITORY='/Dein-Repro'

# Wenn Sie dies einstellen, muss das Projektarchiv nicht auf der 
# Kommandozeile angegeben werden:
export BORG_REPO=$REPOSITORY

# Weitere Informationen finden Sie im Abschnitt Hinweise zur Passphrase
# Wenn Du as einstellst wird der Passphrase nicht in der 
# Kommandozeile eingegeben werden
export BORG_PASSPHRASE='DeinPasswortDerSicherung'

# mout laufwerke
# shfs -o allow_root [email protected]:C:/ /media/backup-server

echo mySQLdump : erstellen
echo "-------------------------------------------------------------"
echo "#### beginne SQL dump : $(date) #### SQL"  
# SQL Dump erstellen, Kopie der Datenbank anlegen
# und unter VerzeichnisFürDeineSicheung/backup.sql ablegen
mysqldump -h DeinServerIP -u SQLrootBenutzer -pSQLrootPasswort --all-databases > VerzeichnisFürDeineSicheung/backup.sql

# --------------------------------------------------------------------------------------------
# --- Backup der Server Daten
# --------------------------------------------------------------------------------------------
echo Repro : $REPOSITORY
echo Date  : $(date)
echo "-------------------------------------------------------------"

echo "#### beginne backup   : $(date) #### SERVER"                                            
borg create                                                  \
      --verbose                                              \
      --stats                                                \
      --info                                                 \
      --filter AME                                           \
      --show-rc                                              \
      --compression lz4                                      \
      --progress                                             \
      --exclude-caches                                       \
      --exclude '/media/backup-server/Users/Default'         \
      --exclude '/media/backup-server/Users/*/NTUSER.*'      \
      --exclude '/media/backup-server/Users/*/ntuser.*'      \
      $REPOSITORY::'{hostname}_{now:%Y-%m-%d_%H-%M}'         \
      /media/backup-server/Users/*/Documents                 \
      /media/backup-server/Users/*/Downloads                 \
      /media/backup-server/Users/*/OneDrive                  \
      /media/backup-server/Users/*/Videos                    \
      /media/backup-server/Users/*/.ssh                      \
      /media/backup-server/Users/*/Music

echo "#### Backup beendet   : $(date) #### SERVER"

## Mit den keep Optionen werden die sieben letzten täglichen Backups,
## eins aus den jeweiligen letzten vier Wochen und eins der letzten sechs
## Monate behalten

echo "#### beginne pruning  : $(date) #### SERVER"

borg prune -v $REPOSITORY             \
      --list                          \
      --glob-archives '{hostname}_*'  \
      --show-rc                       \
      --keep-daily=7                  \
      --keep-weekly=4                 \
      --keep-monthly=6

echo "#### pruning beendet  : $(date) #### SERVER" 
         
echo "#### beginne compact  : $(date) #### SERVER" 
borg compact
echo "#### compact beendet  : $(date) #### SERVER"

# --------------------------------------------------------------------------------------------
# --- Backup der SQL Daten
# --------------------------------------------------------------------------------------------
echo Repro : $REPOSITORY
echo Date  : $(date)
echo "-------------------------------------------------------------"

echo "#### beginne backup   : $(date) #### SQL"                                            
borg create                                                  \
      --verbose                                              \
      --stats                                                \
      --info                                                 \
      --filter AME                                           \
      --show-rc                                              \
      --compression lz4                                      \
      --progress                                             \
      --exclude-caches                                       \
      $REPOSITORY::'sql-server_{now:%Y-%m-%d_%H-%M}'         \
      VerzeichnisFürDeineSicheung/backup.sql

echo "#### Backup beendet   : $(date) #### SQL"

## Mit den keep Optionen werden die sieben letzten täglichen Backups,
## eins aus den jeweiligen letzten vier Wochen und eins der letzten sechs
## Monate behalten

echo "#### beginne pruning  : $(date) #### SQL"

borg prune -v $REPOSITORY               \
      --list                            \
      --glob-archives 'sql-server_*'    \
      --show-rc                         \
      --keep-daily=7                    \
      --keep-weekly=4                   \
      --keep-monthly=6

echo "#### pruning beendet  : $(date) #### SQL" 
         
echo "#### beginne compact  : $(date) #### SQL" 
borg compact
echo "#### compact beendet  : $(date) #### SQL"

# --------------------------------------------------------------------------------------------
# --- Backup Vorgänge abgeschlossen
# --------------------------------------------------------------------------------------------

echo "#### SQL Daten löschen: $(date) #### Clean up"
sudo rm VerzeichnisFürDeineSicheung/backup.sql

echo "#### Laufwerke trennen  $(date) #### Clean up"
umount /media/backup-server
echo "#### Trennung vom Kolegtiv beendet"

echo "-------------------------------------------------------------"
echo "#### backup beendet  :  $(date) #### Info"

For startup we need a start script file, because we cannot execute the mount in the script file “backup-server.sh” executed under sudo.

Script file: backup-server-start.sh
(Please pay attention to the permission as program executable)

#!/bin/sh

# Mounten des SERVER Laufwerks C:\ mit SSHfs
sshfs -o allow_root borgback@192.168.1.106:C:/ /media/backup-server

# das Script zum sichern mit SUDO Rechten starten
sudo ./Schreibtisch/backup-server.sh

With this short script file you start the backup.

./desk/backup-server.start.sh

You may be asked for the “passphrase” of the key, please enter it.

Second you will probably be asked for the sudo password, of course you enter that too.

Automation

You can put the script in the root crontab -e, so you don’t have to enter the sudo password.

The “passphrase” of the key however cannot be bypassed at the moment. The only solution here is a SSH key without “passphrase”.

Also the mashed drive is then permanently mounted, but can also be kept as a retrieval. On call but again requires many tricks 17 (live hacks).

Link to support / donation for the channel
PayPal Link
Bank transfer, Bitcoin and Lightning

#BORG #Backup #BorgBackup #Linux #WSL #WINDOWSsubsystemForLinux #PullBackup #RemoteBackup #BORGbackupScript #BORGpullBackup #BORGremoteBackup #SSHfs #SSHfsBackup #SSHfsBORG #SSHfsBORGBackup