Check out ExpanDrive for Teams - Centralized cloud based configuration console for Teams

Offsite MySQL backup to Strongspace

You should be doing offsite MySQL backup. Replication is nice, but a corrupted master means a corrupted replica. Thankfully, automated offsite MySQL backup to Strongspace is fairly simple. You write the contents of your database to disk using `mysqldump` and push the dumpfile off to Strongspace with [rsync](https://www.strongspace.com/help/basic-rsync-backup).

### Basic MySQL backup

On the MySQL server, create a folder to store the backup files

> $ mkdir -p ~/backup/mysql/

Dump the contents of your database to that folder and compress with gzip

> mysqldump –user=USERNAME –password=PASSWORD DATABASE_NAME | gzip > “DATABASE_NAME – `date +%F %T`.sql.gz”

[mysqldump](http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html) has a bunch of option, but this is the most basic form and should work well in most cases. Check `mysqldump –help` if you want to tweak something or are having trouble.

Now push the backup off to Strongspace

>rsync -avz ~/backup/mysql username@username.strongspace.com:/strongspace/username/home

### Automating the backup

With the basic backup process is working, let’s roll it into a small script that to run nightly with [cron](http://en.wikipedia.org/wiki/Cron). To start, you will probably want to setup [password-less login](https://www.strongspace.com/help/password-less-login-with-ssh-keys) to avoid putting your Strongspace password in the script. Put this script in `~/backup/mysql_backup.sh`, substituting your information in the configuration section.

#!/bin/sh
### Configuration
#
MYSQL_USERNAME=backup_username
MYSQL_PASSWORD=your_mysql_password
MYSQL_DATABASE=production
BACKUP_DIR="/users/home/somebody/mysql_backup"
STRONGSPACE_USERNAME=username
STRONGSPACE_PATH=/strongspace/${STRONGSPACE_USERNAME}/home/mysql_backup
#
#### Do the backup. Note: use absolute paths for mysqldump/gzip/find/rsync
/opt/local/bin/mysqldump --user=${MYSQL_USERNAME} --password=${MYSQL_PASSWORD} ${MYSQL_DATABASE} | /usr/bin/gzip > "${BACKUP_DIR}/${MYSQL_DATABASE} - `date +%F %T`.sql.gz"
# remove backups that are more than 2 weeks old
/usr/xpg4/bin/find . -mtime +14 -exec rm {} ;
# Push the backups off to Strongspace
/opt/local/bin/rsync -a ${BACKUP_DIR} ${STRONGSPACE_USERNAME}@${STRONGSPACE_USERNAME}.strongspace.com:${STRONGSPACE_PATH}
#

Next, we want to make the script executable, and visible by no-one else.

> $ chmod 700 ~/backup/mysql_backup.sh

Run the script and make sure everything works. If everything worked it won’t output any error message and a new backup will have arrived on Strongspace

> $ ~/backup/mysql_backup.sh

Schedule to run that job every night at midnight using cron. Run `crontab` and add a line like this

> 0 0 * * * /user/home/somebody/backup/mysql_backup.sh

As always, test the [restore process](http://www.google.com/search?q=mysqldump+restore). A backup that doesn’t correctly restore isn’t a backup.