Upload Daily Backup to Another Linux Machine

Introduction

The objective of this project will be to dump daily the database using mysqldump command and compress it using gzip. The file will be uploaded to another linux machine using the scp command. To secure the operation the key-pair will be restricted to only scp command. Finally, crontab will be used to schedule the uploads and deletion of backups older than 30 days.
After following all the steps the server with the database will upload daily at 11:00pm a compressed sql dump to a storage server that will store the files. This storage server will delete the backups after 30 days.

Create a key-pair on the machine that will upload the backups

Login on the machine that will upload the backups.

Create a keypair that will be limited to scp. Make sure to don't overwrite any key you already have.

ssh-keygen -t ed25519 -b 4096

ssh-copy-id -p 25519 -i /home/toni/.ssh/id_ed25519_limited_scp.pub -o PubkeyAuthentication=no -o PreferredAuthentications=password prodbkp@192.168.4.8

Securing the key only allowing scp command

On the machine that will store the backups allow the key transferred before to be used for login, but don't allow the incoming connection to have full terminal access.

To force the key to only use a command, command="command" option on ~/.ssh/authorized_keys file will be used.

command="command"
    Specifies that the command is executed whenever this key is used
    for authentication.  The command supplied by the user (if any) is
    ignored.  The command is run on a pty if the client requests a
    pty; otherwise it is run without a tty.  If an 8-bit clean chan‐
    nel is required, one must not request a pty or should specify
    no-pty.  A quote may be included in the command by quoting it
    with a backslash.

    This option might be useful to restrict certain public keys to
    perform just a specific operation.  An example might be a key
    that permits remote backups but nothing else.  Note that the
    client may specify TCP and/or X11 forwarding unless they are ex‐
    plicitly prohibited, e.g. using the restrict key option.

    The command originally supplied by the client is available in the
    SSH_ORIGINAL_COMMAND environment variable.  Note that this option
    applies to shell, command or subsystem execution.  Also note that
    this command may be superseded by a sshd_config(5) ForceCommand
    directive.

    If a command is specified and a forced-command is embedded in a
    certificate used for authentication, then the certificate will be
    accepted only if the two commands are identical.

Now edit authorized_keys file to add the command between other security options

emacs ~/.ssh/authorized_keys

At the beginning of the key add the following configuration:

Don't copy the full line, check when the key starts with ssh-ed25519 AAAAC...

command="if [[ \"$SSH_ORIGINAL_COMMAND\" =~ ^/usr/lib/openssh/sftp-server.? ]]; then $SSH_ORIGINAL_COMMAND ; else echo Access Denied; fi",no-pty,no-port-forwarding,no-agent-forwarding,no-X11-forwarding ssh-ed25519 AAAAC....

ssh -i /home/toni/.ssh/id_ed25519_limited_scp -p 25519 prodbkp@192.168.4.8
PTY allocation request failed on channel 0
Access Denied
Connection to 192.168.4.8 closed.

echo 'Hello World' >> /tmp/test.txt && scp -P 25519 -i /home/toni/.ssh/id_ed25519_limited_scp /tmp/test.txt  prodbkp@192.168.4.8:/tmp
test.txt                                                   100%   24    29.4KB/s   00:00

Script to dump and transfer backup

On the machine that will upload the backups create a script, It will perform the uploading operation.

Script to transfer all the databases

emacs Documents/daily_backup.sh

#! /bin/bash

DATE=$(date +\%Y\%m\%d\%H\%M\%S)

# dump each DB and upload it to staging
for DB in $(mysql -e 'show databases' -s --skip-column-names); do
    FILE="${DB}_backup_${DATE}.sql.gz"
    mysqldump --system=users -u root $DB | gzip > "/tmp/${FILE}"

    scp -P 25519 -i /home/toni/.ssh/id_ed25519_limited_scp "/tmp/${FILE}" prodbkp@192.168.4.8:/home/prodbkp/backups
    rm "/tmp/${FILE}"
done

Script to transfer a unique database

#! /bin/bash

DATE=$(date +\%Y\%m\%d\%H\%M\%S)
DB="appointments"
FILE="${DB}_backup_${DATE}.sql.gz"
mysqldump --system=users -u root $DB | gzip > "/tmp/${FILE}"

scp -P 25519 -i /home/toni/.ssh/id_ed25519_limited_scp "/tmp/${FILE}" prodbkp@192.168.4.8:/home/prodbkp/backups

Change the paths and hostnames according to your setup.

Now make sure the script works

sudo bash Documents/daily_backup.sh

Secure the command making root the owner and only allowing superuser access:

sudo chown root:root Documents/daily_backup.sh

sudo chmod 600 Documents/daily_backup.sh

Create cron command to create the backup and upload it

On the machine that will upload the backups create a cron.

To edit the cron use crontab -e. This command will open the cron config file with our preferred text editor. Root will be used to log into MariDB without the password.

sudo crontab -e

Now add the line to do a daily backup at 11 pm.

# Backup DB every day at 11.00pm
00 22 * * * /bin/bash /home/toni/Documents/daily_backup.sh

Make sure the backups are being transferred:

Create cron command to delete backups older than 30 days

On the machine that will store the backups create a cron to delete the SQLs older than 30 days.

To edit the cron use crontab -e. Root is not required this time.

crontab -e

Now add the line to delete files older than 30 days at 00:00

# Delete 30 days old files
0 0 * * * /usr/bin/find /home/prodbkp/backups -type f -mtime +30 -delete