Backing up your RDS instance to Dropbox using Sequel Pro

After searching around for a third-party backup solution to RDS, and realizing that many companies just want to charge way too much for something so simple, I decided it would be much easier to simply back up our SQL databases ourselves. While this process is manual, you could easily use a script to automate backing up (something like Mysqldump, or just write a simple python script to create the dump file then upload to dropbox).

For our purposes, we wanted to take snapshots of the databases after a certain task was completed, so it's easiest to just take manual snapshots. Fortunately, SequelPro makes backing up databases so simple anyone can do it.

Here's how.

Step 1: Download Sequel Pro:

It's free, and available here: http://www.sequelpro.com

Yes, it's Mac only.

Step 2: Authorize your IP

Make sure your IP is authorized to access your RDS server.  You can do this pretty easily from the AWS Management console. Click on Security Groups:

Then choose your security group, most likely you just have the default one. Click the icon next to the checkbox to dive deeper into the group

Lastly, enter your IP followed by /32 if it doesn't already appear in your list, and hit add.

Note that it can take a few minutes for your IP to be authorized.

Step 3: Connect to your server

It's pretty simple to connect to your server, just fill in the Host, Username, and password. Make sure to select "Connect using SSL". You can save this connection for later by pushing the Plus button in the bottom left and naming the connection.

Step 4: Select the database you want to export

Make sure you select the database you want to export from the menu located at the top of the SequelPro dialog:

Step 5: Choose "Export" from the File menu

 This will bring up a dialog box that will let you choose what to export. Make sure to choose "SQL" and you probably will want to select a compression method. You can access this option by clicking on the arrow at the bottom of the dialog box.

Make sure you choose the path you want to save to. Since we've got a large dropbox account, this is the best place to go for us. It's easy to simply export the compressed BZ2 (which takes longer, but is much more compact) right to dropbox, and have it automatically backed up to an external system.

Recovery

Recovering from this backup is also just as easy. All you have to do instead of choosing "export" on step 5 is choose "import". This will let you choose the location of the file to import, and your database will be replaced with the content of that latest backup.