Saturday, July 11, 2015

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

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@

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 -u   -p > /mnt/backup/test/databases/.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.

 

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:

on localhost (where you want to backup to, i.e. your pi)

ssh-keygen
ssh-copy-id -i ~/.ssh/id_rsa.pub "@remote-host -p 2222"

You may or may not have to specify the connection port depending on your remote host ssh implementation. As you can see above I had to force the user of another port.

Now try to connect from your pi. You should not be asked for a password at this point:

ssh -port 2222 username@remote_host

 

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:


Good Luck!