Raspberry Pi as backup sever of remote files and remote mySQL databases

Rare picture of a pi archiving data.

UPDATE 21st Aug, 2019

It seems impossible that it has already been 4 years since I wrote the following post. I would   just like to update it to say that the device that I programmed with the concepts I explain below is still running to this day and I think there has not been may times where it was rebooted. I had a little issue one time when the power went off at my place and the pi refused to run again - I reinstalled everything and it has been running well since.

I can now safely confirm that these concepts are truly stable and I highly recommend this implementation in case your are looking for a similar solution.

Project Goals

To use a raspberry pi with an external USB hard disk as a backup media for my data (files and mySQL databases) stored on a remote server - not on my LAN.

Why?

Because I had a raspberry pi around and an available and unused external USD drive that was begging for some action!

Before you start

0. You have an OS on your raspberry pi

1. You have ssh access to it.

2. The pi can access the internet.

3. You have ssh access from your pi to the remote server.

4. You would like to schedule the backup to run daily.

5. You would like to always perform full data backups.

ok, we have our list of requirements, lets jump into the implementation of the project.

Procedure

 

SSH connections

 

1. From a computer on your network, ssh to your Pi (password is raspberry)

ssh pi@ip_address_of_pi

2. From your pi, ssh to your remote server (mine was not on default port 22, so I had to change it to 2222)

sudo ssh -p 2222 username@remote_server

So you have confirmed your ssh connection to your pi and from your pi to the remote server that contains the files you would like to backup.

 

External HDD

 

1. Find out what is your drive device name:

sudo blkid

You will get a list that looks like this:

/dev/mmcblk0p6: LABEL="root" UUID="2b177520-9ff1-4ad5-95f5-6ec7cf61ccaf" TYPE="ext4"
/dev/sda1: UUID="cb980263-9238-469d-87ea-5b1ce2f5f421" TYPE="ext2"

You can see my drive is at /dev/sda1 and is formatted as ext2.

2. Mount the device:

sudo mount /dev/sda1 /mnt

3. Create your backup folder on the drive:

sudo mkdir /mnt/backup

 

Prepare for database backups

 

1. Run the following commands to install some tools you will need to reach and dump (save) your remote databases:

sudo apt-get update
sudo apt-get install mysql-server

2. Do a manual test to see if you can backup mysql databases from your remote host.

sudo mkdir /test
sudo mkdir /test/databases

sudo mysqldump --host ip_address_of_host -u username -pyourpassword /mnt/backup/test/databases/.sql
sudo mysqldump --host ip_address_of_host -u username -pyourpassword database_name > /mnt/backup/test/databases/database_name.sql

On my setup, I had to specifically allow remote mysql connections from my ISP on cpanel, otherwise I cannot reach the database from outside. Note there's no space between the -p command and the password.

 

Prepare ssh connection from pi to remote host

 

We will automate the backup procedure and we want it to be fully automatic, so we do not wish to have to enter the ssh connection password all the time we make a connection request. Proceed as follows to implement a no-password ssh connection:

---> The following is from this page http://www.linuxproblem.org/art_9.html I add the text here with full credit as this seems like a very very old page and I would like to not loose this information for future applications

SSH login without password

Your aim

You want to use Linux and OpenSSH to automate your tasks. Therefore you need an automatic login from host A / user a to Host B / user b. You don't want to enter any passwords, because you want to call ssh from a within a shell script.

How to do it

First log in on A as user a and generate a pair of authentication keys. Do not enter a passphrase:

a@A:~> ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/home/a/.ssh/id_rsa): 
Created directory '/home/a/.ssh'.
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /home/a/.ssh/id_rsa.
Your public key has been saved in /home/a/.ssh/id_rsa.pub.
The key fingerprint is:
3e:4f:05:79:3a:9f:96:7c:3b:ad:e9:58:37:bc:37:e4 a@A

Now use ssh to create a directory ~/.ssh as user b on B. (The directory may already exist, which is fine):

a@A:~> ssh b@B mkdir -p .ssh
b@B's password: 

Finally append a's new public key to b@B:.ssh/authorized_keys and enter b's password one last time:

a@A:~> cat .ssh/id_rsa.pub | ssh b@B 'cat >> .ssh/authorized_keys'
b@B's password: 

From now on you can log into B as b from A as a without password:

a@A:~> ssh b@B

 

The bash file

 

Use any variation of the following:

#!/bin/bash

#Create folder under /backup with a name of the format 20151020_23:59:00
DATE=`date +%m.%d.%Y_%H.%M.%S`
HOST=""

#Backup files
sudo mkdir -p /mnt/backup/$DATE
sudo chown pi /mnt/backup/$DATE
mkdir -p /mnt/backup/$DATE/log
mkdir -p /mnt/backup/$DATE/databases
touch /mnt/backup/$DATE/log/backup.log

date >> /mnt/backup/$DATE/log/backup.log
echo "******************************Starting..." >> /mnt/backup/$DATE/log/backup.log

#############################################START BACKUP PROCEDURES####################################
#Backups on Saturday:
if [[ $(date +%u) -eq 6 ]]
then
        #DECLARE ALL FOLDERS TO BACKUP ON SATURDAYS WITHOUT TRAILING SLASH:
        declare -a arr=("" "")
        for i in "${arr[@}}"
        do
                echo "BACKUP OF" >> /mnt/backup/$DATE/log/backup.log
                echo $i >> /mnt/backup/$DATE/log/backup.log
                IFS=/ read -a path <<< "$i"
                rsync -arv -e 'ssh -p 2222' @:/$i/ /mnt/backup/$DATE/${path[0]}/ >> /mnt/backup/$DATE/log/backup.log
        done
else
        echo "Not Saturday. Weekly backup not performed." >> /mnt/backup/$DATE/log/backup.log
fi

#Backups daily
#DECLARE ALL FOLDERS TO BACKUP DAILY WITHOUT TRAILING SLASH:
declare -a arr=("" "")
for i in "${arr[@]}"
do
        echo "BACK UP OF " >> /mnt/backup/$DATE/log/backup.log
        echo $i >> /mnt/backup/$DATE/log/backup.log
        IFS=/ read -a path <<< "$i"
        rsync -arv -e "ssh -p 2222" @:/$i/ /mnt/backup/$DATE/${path[0]} >> /mnt/backup/$DATE/log/backup.log
done

#DB BACKUP ON SATURDAY:
if [[ $(date +%u) -eq 6 ]]
then
        sudo mysqldump --host $HOST -u -p > /mnt/backup/$DATE/databases/
else
        echo "Not Saturday. Weekly database backup not performed"
fi

sudo mysqldump --host $HOST -u -p > /mnt/backup/$DATE/databases/

#Delete folder of name backup_XXXXX older than XXXX months
#coming later

#Send report email
#coming later

echo "******************************Finished..." >> /mnt/backup/$DATE/log/backup.log
date >> /mnt/backup/$DATE/log/backup.log

This file will do a full backup of certain files on Saturday and certains files daily. The same for the databases. It will create a folder with the date and time at which it runs and will create a log file to store debug information.

Ensure that the file runs manually (for example, if it's called backup.sh):

./backup.sh

 

Make this an automatically scheduled cron job

 

Run the following command to edit your crontab:

crontab -e

And enter the following command (this will run the file everyday at 2:00 am. Of course you can set it to match your requirements):

0 2 * * * /home/pi/backup.sh

 

Setup FTP access to your pi

 

I need to be able to access the files from computers on my LAN. One way that works for me is to setup an FTP server on the pi with the connection folder being /mnt/backup. Everytime I FTP to the pi, I'm sent to the backup folder where I can see all the folders per day and get what I'm interested in.

To install the FTP server:

sudo apt-get install vsftpd

Configure the FTP server to not allow anonymous logins and to redirect connecting users to the /mnt/backup directory:

nano /etc/vsftpd.conf

Edit the following configuration file to match your case:
anonymous_enable=NO
#
# Uncomment this to allow local users to log in.
local_enable=YES
#
# Uncomment this to enable any form of FTP write command.
write_enable=YES
#
# Default umask for local users is 077. You may wish to change this to 022,
# if your users expect that (022 is used by most other ftpd's)
local_umask=022
# Activate directory messages - messages given to remote users when they
# go into a certain directory.
dirmessage_enable=YES
#
# Activate logging of uploads/downloads.
xferlog_enable=YES
#
# Make sure PORT transfer connections originate from port 20 (ftp-data).
connect_from_port_20=YES
#
# If you want, you can have your log file in standard ftpd xferlog format
xferlog_std_format=YES
#
local_root= /mnt/backup/
chroot_local_user=YES
pam_service_name=vsftpd
userlist_enable=YES
#enable for standalone mode
listen=YES
tcp_wrappers=YES
use_localtime=YES

I ran into a few issues with FTP the server. Namely I had to create the file vsftpd.user_list file:

sudo touch vsftpd.user_list

Then I had to make my backup folder accessible to the FTP server:

sudo chmod a-w /mnt/backup

 

Things to do

 

I'm happily retrieving my data everyday but there's a couple of things I need to improve. These may be the subject of another post if there's any interest:

1. Receive report email

I have not taken too much time reading it, but this page seems to show the way to go to have email sent from the pi: http://www.sbprojects.com/projects/raspberrypi/exim4.php

2. Spin-down my external drive

My drive seems to be always operating even when it's not doing anything (which is most of the time actually) so I would like to save power and working hours by having it go into sleep mode or something like that. Not sure if that can be achieved with an external USB drive but the following pages are good places to start:

http://superuser.com/questions/651880/force-spin-down-of-external-hard-drive-on-linux-raspberry-pi

http://raspberrypi.stackexchange.com/questions/5635/how-do-i-set-up-a-storage-device-that-is-only-on-when-used

Good Luck!