Thursday, February 11, 2010

MySQL Backups to AWS Cloud

A client asked me to write a script to perform nightly MySQL database backups to the AWS cloud. They also viewed this as an opportunity to test the Amazon Web Services (AWS) waters.

They had the following requirements:
  1. Full backup every night. No need to worry about differentials or incrementals.
  2. Backups must be encrypted prior to transmission.
  3. Transmission must take place over secure channel.
  4. Following a backup run, an email notification must be sent to the DBA's.
  5. Documentation.
Rather than re-invent the wheel, and write the tool from scratch, I decided to do some research. I came across two Open Source resources, which combined together with some modifications, addressed my clients needs.

  1. MySQL Backup script with encryption
  2. s3cp - Amazon S3 command line cp program
  1. Instead of 3DES, I used AES192 CBC mode, mostly due to performance reasons. Also, due to Cipher Block Chaining mode, any file corruption during file transfer would be detected during decryption.
  2. Integrated s3cp for uploading the resulting encrypted dump file to AWS S3.
  3. Added lines to calculate backup size and md5 checksum to be included in the emails sent to DBAs.
  4. Added email notification feature.
You can download the script and support files HERE. A redacted version of documentation will follow.

Update: Redacted documentation "AWS_MySQL_backup.pdf"


No comments:

Post a Comment