Automatic file and MySQL backup with SFTP transfer

In this post I’m going to explain to you the backup mechanism behind all my domains. The backups are created automatically and securely downloaded onto another storage.

Generating the backup

First, I setup a cronjob that creates a package of all relevant content.

This job gets executed every morning at 5 o’clock.

The script backup.sh looks as follows:

Explanation

TODAY is a placeholder variable that, when generated looks like ‘140617’ (YYMMDD).
I first save all the iptables rules to a folder in /root.
Then I zip all the important folders into my backup folder.
I dump all mysql databases to root and also push this into a zip archieve moved to the backup folder.
At the end, I send an email to me.

MySQL dump without issueing username and password

To dump the MySQL databases without having to type your username and password, you need a special file parsed by mysqldump/root/.my.cnf with the following contents:

When you don’t have that file, type: touch /root/.my.cnf && chmod 600 /root/.my.cnf

Notes on the folder and file permissions (security)

Some of the files in the backup and used to create the backup contain sensitive information like root passwords and user credentials or SSH keys.

It’s important to know all of those files and give them the correct permissions (chmod 600 for files, chmod 700 for folders).
You should also give your backup directory the permission to only let root read from, because otherwise other users could extract the contents of your backup files.

Sidenote: You should never give chmod 666 or 777 to anything modify_all_the_things just to solve permission issues. It’s undoubtful the quickest, but also the dirtiest way; granting every user to read and write to a file or folder.

 

Automatically pulling those backups from a different server

Deprecated! I discourage the use of the following script and technique.

Please instead try rsync, the usage is very simple.

On a different server, I pull all my backups to a local drive and an external USB harddrive.
This starts with a cronjob, too. You can let the job get fired every 30 minutes or right when you know the backup on the other server is finished (like above 5 o’clock, you start this pulling job at 5.30 or something).

The pull-backups.sh looks as follows:

This downloads all the backup files it does not already have and stores them to /local/backups.
Make sure whom to allow to read from this directory (chmod 700 /local/backups).

The script was originally taken from http://www.overclockers.com/forums/showthread.php?t=671900.

Using SFTP without password and username

To use SFTP without having to enter the username or password, you have to generate a private/public keypair and add that public key to the target server’s /home/backup/.ssh/authorized_keys file (the private key goes to the local ‘pulling’ server /home/backup/.ssh/id_rsa).

One thought on “Automatic file and MySQL backup with SFTP transfer

  1. Pingback: easy remote to local file backup using rsync - FLOWL blog | php - security - linux - analytics

Leave a Reply

Your email address will not be published. Required fields are marked *