JasonDaly.name

Amazon S3 + Nightly Database Backups

Amazon S3 is a cost-effective, redundant, and straight-forward solution for off-site data backups. Using the highly recommended aws command line tool by Tim Kay and a simple shell script running nightly, creating an automatic remote database backup is easy.

After installing aws, I created a shell script as shown below.

#!/bin/bash
export AWS_ACCESS_KEY_ID= # your aws access key id
export AWS_SECRET_ACCESS_KEY= # your secret access key

DBUSER= # the database username
DBPASS= # the database password
DBNAME= # the name of the database
BUCKET= # the root bucket to store the backup

FILE=$DBNAME.bkp.$(date +_%b_%d_%y.%s).sql.gz
mysqldump -u $DBUSER -p$DBPASS $DBNAME | gzip -9 > /tmp/$FILE
aws put $BUCKET/bkp/sql/$FILE /tmp/$FILE --private
rm -f /tmp/$FILE # cleanup

A nightly (at 1AM) cron is then setup for the user running the above script

0 1 * * * /home/app/s3dbbkp 2>&1

This is clearly a very simple implementation, but for my current needs (and likely others) it is perfect.

Transferring a Live Site to a New Server Using cPanel/WHM and SSH

Using cPanel/WHM it is very easy to transfer an existing cPanel account between servers. Coordinating this with a client who manages DNS on their own can make things a bit tricky, especially when their MX records point to the same server for webmail service.

In order to guarantee no downtime on the site, typically I copy a cPanel account over to the new server including all site files and email in advance of notifying a client it is okay to change the DNS. I also tell the site at the newly located cPanel account to continue to read and write to the original database on the old server using the server’s IP address as the host. This gives the client the flexibility to change the DNS at a time convenient for them.

After the DNS Has Propagated to the New Server

Once the DNS has propagated to the new server, a bit of cleanup is required. Following the example above,

1. The database from the original server (which now contains newer information than the copy existing on the new server’s copy of the database) must be copied, and the new cPanel account’s database references must once again be set to localhost.
2. We must ensure that all deleted/added email on the original server which occured between the time the cPanel copy was performed and when the client changed their MX records must be reflected on the new server to ensure no loss of email or reappearance of previously deleted email.

To accomplish #1 above is simple. Via SSH on the new server run the following commands

mysqladmin drop your_database_name
# Now update your site to point to localhost instead of the original server's IP

Then, from the original server run the following commands

mysqldump -u your_cpanel_user -p your_database_name | ssh your_cpanel_user@the_new_server_ip mysql your_database_name

This will minimize downtime for your users¹ while updating the site to read/write from the local copy of the database now containing the newest information.

Item #2 above is equally simple via rsync from the old server

rsync -av --progress /home/your_cpanel_user/mail/ root@the_new_server_ip:/home/your_cpanel_user/mail

This command properly updates the new mail server’s mail folder for all users with the latest changes to their accounts prior to the MX record change.