Script de backup Mysql

Achei, testei e aprovei

Tools of the trade:

growisofs

mkisofs

tar

cp

pg_dump

growisofs

Useful argument for growisofs are:

-Z : is used to specify the device to burn to

-dvd-compat : makes sure the written disk will have the maximum compatability with other dvd drives (useful in an emergency)

-speed : sets the speed of the disk / burn

Since growisofs is a pretty wrapper for mkisofs you can use essentially all the options of mkisofs as well.

mkisofs

We won't actually use mkisofs directly but we can use its options in growisofs. Useful options include:

-A : allows you to give the disk a title

-D : if you have very deep directories you might want to use this, it can break the iso9660 standard but apparently works on most systems

-J : use Joliet extansions. Primarly useful for making the disk readable on Windows boxes

-R : generate Rock Ridge extensions allowing long filenames

-r : a clever version of -R. If you are going to use Rock Ridge you probably want to use this version

-V : specifies the volume name

tar

Since I want to be able to make sure that I capture the directory structure and files names as well as the data it is not a good idea to just burn the individual files since even with Rock Ridge extensions in use you can't have more than 8 levels of directories and various other strange restrictions. Hence I am going to tar up the files I want to burn and then burn the tar. This also means that if I end up in the situation where I am having trouble fitting the back up on the DVD (really this might happen - one day) I can easily just gzip / bzip the tar.

-c : create an archive

-u : only update, which could make the backup much quicker

-f : create the archive as a file

-p : preserve permissions

cp

Allows us to copy portions of the file system from one place to another.

-p : preserve ownership and other file information

-r : copy directories recurvivly

-d : handle links in a nice way but don't follow them

pg_dump

We want to be able to back up the database as well as the basic files and this is best done using pg_dump which will write the database out as a simple sql file. You have got to make use that the user that is running the backup job has permission to dump the database.

-f : the file to dump to

-o : if you use foreign keys use this option

-U : connect as the given user

The Backup Script

Note: The line starting SOURCE is actually one long line that includes all the lines that follow upto the next blank line as indicated by the ↳ symbol (should be a downwards arrow with tip rightwards).

#!/bin/bash

DATE=$(date +%Y-%m-%d)

#NOTE: These two paths must end in a / in

#order to correctly build up the other paths

BACKUP_DIR="/backup/directory/"

TEMP_BACKUP_FILES_DIR="/backup/temp/"

BACKUP_FILE=$PATH"backup-"$DATE".tar"

DATABASE_FILE=$TEMP_BACKUP_FILES_DIR"foo-"$DATE".sql"

#These directories shouldn't end in a / although

#I don't think it will cause any problems if

#they do. There should be a space at the end though

#to ensure the database file gets concatenated correctly.

SOURCE="/a/location /other/locations " $DATABASE_FILE

echo Removing old backup directories

rm -rf $BACKUP_DIR

rm -rf $TEMP_BACKUP_FILES_DIR

echo Creating new backup directories

mkdir $BACKUP_DIR

mkdir $TEMP_BACKUP_FILES_DIR

echo Creating database backup

pg_dump -U username -f $DATABASE_FILE databaseName

echo Backing up $SOURCE to file $BACKUP_FILE

/bin/tar -cpf $BACKUP_FILE $SOURCE

#This is not necessary and possibly harmful for DVD+RW media

echo Quick blanking media

dvd+rw-format -blank /dev/hdc

echo Burning backup

growisofs -dvd-compat -Z /dev/hdc -r -J $BACKUP_FILE

Testing the Backup

From time to time it is a good idea to test your backup to make sure you can actually recover data from it. A good time is when you are sure it's working a bad time it when you have to get something you just deleted off it!

First mount the drive

mount -f udf /dev/hdc /mnt/dvd

then copy the tar file to where ever you have space to expand it

cp backup-file.tar /big/drive/

move to where you have copied the file and expand the tar with

cd /big/drive

tar -xf backup-file.tar

be careful you don't type a c where the x is in the tar command above or you will have to copy the file over again! Finally check that all your files are there and readable.