Remote backup script

This script, when running, backup a specified remote directory using rsync. Additionally, it will run mysqldump when dbname is specified.

It will also keep the last 3 backups (number of copies can be set in the script)

Setup

Local/Remote

Set up key-based ssh authorization, specify location of id_rsa (or suitable for you platform) in the script.

Remote

Create a no-password local MySQL user, give it global SELECT and LOCK TABLE privilege (for mysqldump)

You can issue following SQL to do this:

CREATE USER 'backup'@'localhost';
GRANT SELECT, LOCK TABLES ON * . *
TO 'backup'@'localhost'
WITH MAX_QUERIES_PER_HOUR 0
MAX_CONNECTIONS_PER_HOUR 0
MAX_UPDATES_PER_HOUR 0
MAX_USER_CONNECTIONS 0;

Create files name dbname.txt with dbname as content (one each line) in each of the location you wish to backup latest-$DBNAME.tar.gz.

It is not recommend to put dbname.txt in DocumentRoot because you do not want the db dump can be downloaded when you restored the entire directory. I put the file in the same location as vhost.conf generated by vhost-apache2 script.

Example

Run

$ ./backup.sh backup@remote.server /home/web_vhosts ./web_backup/snapshot

will rsync all the files to ./web_backup/snapshot with additional dbname.txt voodoo. Run again to push the last backup to ./web_backup/snapshot.0 and backup changes made.

Known issue