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.


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.



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 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/
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/ | 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:


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

#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 ]]
        declare -a arr=("" "")
        for i in "${arr[@}}"
                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
        echo "Not Saturday. Weekly backup not performed." >> /mnt/backup/$DATE/log/backup.log

#Backups daily
declare -a arr=("" "")
for i in "${arr[@]}"
        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

if [[ $(date +%u) -eq 6 ]]
        sudo mysqldump --host $HOST -u -p > /mnt/backup/$DATE/databases/
        echo "Not Saturday. Weekly database backup not performed"

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



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/


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:
# Uncomment this to allow local users to log in.
# Uncomment this to enable any form of FTP write command.
# 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)
# Activate directory messages - messages given to remote users when they
# go into a certain directory.
# Activate logging of uploads/downloads.
# Make sure PORT transfer connections originate from port 20 (ftp-data).
# If you want, you can have your log file in standard ftpd xferlog format
local_root= /mnt/backup/
#enable for standalone mode

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:

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! 

1 comment:

zodiak aka Rui said...

Just a quick thing that I had to do and took me a while to get. If you would like vsftpd to show hidden files, you need to add the following configuration option to the conf file:


then restart the server:

sudo service vsftpd restart

reconnect with the FTP client (you may need to refresh the directory listing).

You should now be able to see all hidden files that were backed up with rsync.