Filed under Databases

Replication & backups with Ruby

If there’s one thing certain in life, it is the uncertainty. As you go higher up in the ladder of life, the fall grows steeper, risk becomes greater. Same rules apply in the digital world.

In the process of building and maintaining software, there are plenty of accidents, ways to screw things, foolish mistakes and enemies to sabotage work. So most would agree it’s sensible to have some solid backup strategy as your insurance policy in case a disaster strikes your budding app. But when you say backups & redundancy, it sounds really expensive and time consuming, isn’t it ? Well, Not anymore; with all the cloud services floating around it’s possible to have a good data backup plan with few additional bucks. So in this post I’m sharing a general backup approach easily implementable using wonderful Ruby Backup Gem that you could use or adopt according to your application needs and risk.

When it comes to data replication and recovery, there are various aspects you might need to look into depending on the nature/scale/risk of your data. The method suggested here assumes there are 2 servers for the master and slave where you can setup data replication with MySQL. Also this approach was largely inspired by the strategy adopted by Marco Arment for Instapaper.

Here is an overview of the strategy suggested in this post.

Backup Overview

Backup Overview

1) To achieve point-in-time recovery I’m using a simple master-slave database setup with MySQL. This is very straightforward and Here, here are some examples on how to set it up.

2) Install Backup Gem

gem install backup in both master and slave servers.

3) Setup MySQL binary log syncing from master to slave

– Setup Backup config in master (This will create a ~/Backup/config.rb file)

  sudo backup generate --databases='mysql' --storages='s3' --compressors='gzip'

– Additionally create a default config file defaults.rb (Put mail alert, twitter alert configs here)

– To sync binlogs every 5 mins put this code in ~/Backup/config.rb. Do usual SSH key copy procedure to avoid password prompting when rsyncing.

– Update crontab to sync MySQL logs every 5 minutes

0,5,10,15,20,25,30,35,40,45,50,55 * * * * /bin/bash -l -c 'cd /home/username/Backup /usr/bin/backup perform --trigger sync_logs --config-file
/home/username/Backup/config.rb'

4) In the slave, follow the same process as in the master and setup the backup directory + config.rb. Additionally create a default.rb to store common configurations.

– Create config.rb

  backup generate --databases='mysql' --storages='s3' --compressors='gzip'

– Add email, twitter notification settings in defaults.rb

5) MySQL binlogs will be synced with S3 every half an hour. For this add half_an_hour.rb to your ‘Backup’ directory (All this can be put in config.rb as well. But for the sake of clarity I’m separating them based on the frequency).

6) Daily backup a full copy the database to local disk. For this use the daily.rb script.

7) Weekly store a full copy of database in S3. Use the weekly.rb script for this purpose.

8 ) Use Dropbox to store a copy every month. Plus if one of your workplace machine is synced with Dropbox account it will get synced to the local machine automatically and you can burn them to disks.
Use monthly.rb for this task.

9) Finally update the crontab in your slave to run your backup scripts according to frequencies you intend.

#sync every 30 minutes
0,30 * * * * cd /home/username/Backup  /usr/bin/backup perform --trigger sync_backup --config-file /home/username/Backup/half_an_hour.rb
/var/log/cron/cron.log  /var/log/cron/error.log
 
#backup daily
0 6 * * * cd /home/username/Backup  /usr/bin/backup perform --trigger daily_backup --config-file /home/username/Backup/daily.rb  /var/log/cron/cron.log
/var/log/cron/error.log
 
#backup every week
0 6 1,8,15,22 * * cd /home/username/Backup  /usr/bin/backup perform --trigger weekly_backup --config-file /home/username/Backup/weekly.rb  /var/log/cron/cron.log
/var/log/cron/error.log
 
#backup monthly
0 6 26 * * cd /home/username/Backup  /usr/bin/backup perform --trigger monthly_backup --config-file /home/username/Backup/monthly.rb  /var/log/cron/cron.log
/var/log/cron/error.log

So at the end of this process you will have several redundant copies of your database as well as MySQL transactions up to last 5 minutes. In case of an emergency (ie: disastrous SQL query) you could pick up the nearest full backup and apply MySQL binary logs one up till the disaster occurred and you are good to go.

As a last note (not the least) kudos to the team behind Backup gem. You guys made my life lot easier!