Posts Tagged backup

Automated MYSQL Backups using mysqldump

This is a short walk though for building a script to automate mysqldump and copying the backups offsite. I used this script to backup the database for the RSVP system I developed for our Computer Science Awards Banquet. A future improvement for this script is using rdiff-backup but currently I just needed something to work… quickly… so here it goes:

STEP 1: Create a user with LOCK TABLES and SELECT privileges to the database you want to backup. I made mine local access only with no password but if you’re paranoid, you can pass the password to mysqldump with the –password flag.

STEP 2: If you wish to do offsite backup, use ssh-keygen to create a key pair for your sqldb server to connect to your offsite backup server. This will allow the scp to be automated (and not prompt for a password).
ssh-keygen -t rsa
Generating public/private rsa key pair.
Enter file in which to save the key (/home/bkupusr/.ssh/id_rsa):
Created directory '/home/bkupusr/.ssh'.
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in /home/bkupusr/.ssh/id_rsa.
Your public key has been saved in /home/bkupusr/.ssh/id_rsa.pub.
The key fingerprint is:
a2:b2:aw:w2:63:25:2a:62:fs:d5:ff:fd:11:f1:aa:60 bkupusr@sqlhost

Copy the /home/bkupusr/.ssh/id_rsa.pub. from the sqlhost to the bkuphost’s /home/bkupusr/.ssh/authorized_keys2 file. Make sure the contents of id_rsa.pub take up exactly one line.

STEP 3: Create a script… like the following:
#!/bin/sh
offsitehost=bkuphost
offsiteuser=bkupusr
user=bkupusr
db=dbtobackup
date=`date +%m%d%Y-%H%M`
file="/var/backup/csrsvp-bkup-$date.sql.gz"
mysqldump --user=$user --databases $db | gzip > $file
scp $file $offsiteuser@$offsitehost:~/backup

This script appends a timestamp to the backup file to differentiate between previous backups.

STEP 4: And add the script to your crontab.
crontab -e

On the hour:


0 * * * * ./bkupdb.sh >/dev/null 2>&1

, , , , , , ,

No Comments

Backup Solution

I’ve been looking around for some time now for a suitable off-site backup solution.  The big thing I’ve been looking for is something that works on Linux, Mac and Windows.  It is rare to find a solution that works well on all three, and supports headless operation on Linux.

SpiderOak (https://spideroak.com/) offers 2Gb of free storage with unlimited machines and then $10 per additional 100Gb per month.  I am currently testing out their free account on my Mac and it works well!  It features a nicely designed UI which is very intuitive and easy to use.

I have not had the chance to test the client on Windows or Linux yet but I’m very excited to see how well it is able to operate on Linux (headless).

But on a side note, they say on their website that they’ve contributed back to the open source community (See https://spideroak.com/code).   I think that’s very cool!

So the conclusion is: I think this will satisfy my needs for a normal backup solution for important data (being documents and files).  I still have over 200Gbs of photos on my server which I’m not willing to pay $20/mo to store on a web-based service.  My plan for those files is just to take an external drive offsite each month (unless anyone wants to suggest a better idea!).

, , , ,

3 Comments