A super simple backup solution in Rails

I recently migrated a newsletter that I run(The PHX Brief) over to a new hosting platform and used Kamal for the new deployment flow. For part of the process, I wanted to be able to easily import the database into its new home by just utilizing the database URL instead of crafting a mysqldump and mysql import command.

This lead me down the path of creating a simple Backup model that you can utilize to backup, restore, and import via URL, all with Rails-related tooling and a gem. Your Rails application also already knows your current database connection details, so why not leverage that for automatic backups instead of running another process/container?

To start the migration process, I started with a simple import_from method that took a database URL, dumped the DB and piped the output into the new database. This meant that once I was ready to migrate the data over to the new platform I could just simply import via the database URL and flip DNS over to the new hosting platform. With a simpler application like this you don’t really need a maintenance window since the data is only getting updated a few times a week.

We’ll start by creating our Backup model which will just store a reference essentially to our backup file.

rails generate model Backup

The model won’t need any additional columns, just our timestamps for created_at and updated_at, the dump details will get stored in your ActiveStorage related tables. This also assumes you've already configured ActiveStorage.

Here’s what I ended up with for the import_from method within our new Backup model:

class Backup < ActiveRecord::Base
  def self.import_from(url)
    from_config = URI.parse(url)
    config = ActiveRecord::Base.connection_db_config.configuration_hash

    %x(mysqldump --set-gtid-purged=OFF -u #{from_config.user} -p#{from_config.password} -h #{from_config.host} #{from_config.path[1..-1]} | mysql -u #{config[:username]} -p#{config[:password]} -h #{config[:host]} -P #{config[:port]} #{config[:database]})
  end
end

It’s pretty simple and leverages the connection information we already know within our Rails app on the new platform. To migrate our data from the old to the new platform then just becomes Backup.import_from(“mysql2://user:pass@host:3306/database_production”), neat.

With that part of the migration complete, I started thinking about automatic backups. Sure, there’s various containers/scripts that you can run to do backups for you or you can pay for backups at your hosting provider. But with the ability to configure recurring jobs in solid_queue, why not just add this to our new Backup model? We can write a simple mysqldump command similar to import_from, we’re just missing the encryption part. Although Rails supports encrypted attributes via encrypts there’s no way to currently encrypt ActiveStorage data with vanilla Rails, but there’s a great gem for it called lockbox. You can still use encrypts to encrypt the ActiveStorage data but those are just the pointers to the file and not the actual file data.

Great, so here’s the idea:

1. Create a new backup from our Backup model
2. Encrypt the backup file
3. Upload the backup to Digital Ocean Spaces(S3)
4. Have solid queue create a daily backup

To get started with this, I just wanted to verify that the backup was happening and contained the correct data. I’m thinking we can automatically create and store the backup whenever we create a new Backup record, then Solid Queue just needs to call Backup.create!.

With Active Storage configured locally, we can run mysqldump to the tmp/ folder and then attach our file via has_one_attached :dump. I’ve added a few comments inline as well.

class Backup < ActiveRecord::Base
  has_one_attached :dump # Our SQL dump will be stored in Active Storage as :dump

  after_create_commit :store # Automatically store a new backup whenever we create a new Backup record

  def store
    file_location = "tmp/dump-#{id}.sql"
    config = ActiveRecord::Base.connection_db_config.configuration_hash

    %x(mysqldump -u #{config[:username]} -p#{config[:password]} -h #{config[:host]} -P #{config[:port]} #{config[:database]} > #{file_location})

    # We can attach our dump file via ActiveStorage and the attach method.
    dump.attach(
      io: File.open(file_location),
      filename: "dump-#{id}.sql",
      identify: false
    )

    # I added a Honeybagder check-in to ensure it's running on a regular basis and that the dump is working correctly with a simple file size check.
    Honeybadger.check_in("check-in-id") if dump.byte_size > 10_000
  ensure
    # Ensuring that we don't leave backups laying around or taking up space.
    File.delete(file_location) if File.exist?(file_location)
  end
end

With our new store method created, we can test this in the console by running Backup.create! since we call store on after_create_commit. Once we run this in the console we can easily check that the backup ran and stored our data by downloading the last backup record’s data with Backup.last.dump.download. It should start with something like MySQL dump....

Great, now we can start encrypting it and then shipping it off to S3 or wherever you connected ActiveStorage to.

First, we’ll get the lockbox gem configured by installing and setting a lockbox key.

bundle add lockbox
bin/rails r "puts Lockbox.generate_key"

Grab the generated key and add it to your Rails credentials or ENV settings. Then add a config/initializes/lockbox.rb file setting the key.

Lockbox.master_key = Rails.application.credentials.lockbox_master_key

With lockbox configured, we can update our Backup model to encrypt our dump so that when ActiveStorage stores it, the file is encrypted. Since lockbox already supports ActiveStorage out of the box, it’s just a matter of adding encrypts_attached :dump within our Backup model.

class Backup < ActiveRecord::Base
  has_one_attached :dump
  encrypts_attached :dump

  after_create_commit :store

  def store
    file_location = "tmp/dump-#{id}.sql"

    %x(mysqldump -u #{config[:username]} -p#{config[:password]} -h #{config[:host]} -P #{config[:port]} #{config[:database]} > #{file_location})

    dump.attach(
      io: File.open(file_location),
      filename: "dump-#{id}.sql",
      identify: false
    )

    Honeybadger.check_in("4dIyQl") if dump.byte_size > 10_000
  ensure
    File.delete(file_location) if File.exist?(file_location)
  end

  def self.import_from(url)
    from_config = URI.parse(url)

    %x(mysqldump --set-gtid-purged=OFF -u #{from_config.user} -p#{from_config.password} -h #{from_config.host} #{from_config.path[1..-1]} | mysql -u #{config[:username]} -p#{config[:password]} -h #{config[:host]} -P #{config[:port]} #{config[:database]})
  end

  private

  def self.config
    ActiveRecord::Base.connection_db_config.configuration_hash
  end

  def config
    ActiveRecord::Base.connection_db_config.configuration_hash
  end
end

Go ahead and create another backup with Backup.create! in your console. Lockbox automatically encrypts and decrypts the attachment so if you look at the attachment by downloading it again you won’t be able to see the encrypted content, you’ll just see the unencrypted backup again. Instead, go find the file wherever you have ActiveStorage pointed, and you’ll see that the file is encrypted at rest because of lockbox.

Now that we can dump, encrypt, and upload our database backup, we can add a recurring job to create a daily backup with solid_queue. This assumes you're already using solid queue and have it configured.

Within config/recurring.yml add a new job to create the backup.

production:
  backup:
    command: Backup.create!
    schedule: at 2am every day

Nice. But we probably just need to store the last 30 days' worth of backups; let's create a cleanup method, too. Here’s a simple cleanup method where we just look for all backup records that were created over a month ago, and we destroy them all. This will remove the database record for the backup as well as remove the file from the ActiveStorage backend.

  def self.purge_old_backups!
    where(created_at: ..1.month.ago).destroy_all
  end

And now, we can add this to our recurring jobs:

production:
  backup:
    command: Backup.create!
    schedule: at 2am every day
  purge_old_backups:
    command: Backup.purge_old_backups!
    schedule: at 5am every day

So now we’re backing up our database at 2 am daily and then removing backups older than 30 days daily at 5 am. No additional cron scheduling or sidecar container config to deal with running backups.

We can even take this a step further and add a restore method so we can just restore our database from a recent snapshot.

def self.restore(id)
  backup = find(id)

  backup.dump.open do |file|
    %x(mysql -u #{config[:username]} -p#{config[:password]} -h #{config[:host]} -P #{config[:port]} #{config[:database]} < #{file.path})
  end
end

Now, if we wanted to restore the last snapshot, it’s as simple as Backup.restore(Backup.last). For larger applications, this probably isn’t an ideal way to restore your data, but for a small and simple app, it’s pretty straightforward.

Our entire Backup model is a whopping 50 lines of code and something we can easily use in other applications.

class Backup < ActiveRecord::Base
  has_one_attached :dump
  encrypts_attached :dump

  after_create_commit :store

  def store
    file_location = "tmp/dump-#{id}.sql"

    %x(mysqldump -u #{config[:username]} -p#{config[:password]} -h #{config[:host]} -P #{config[:port]} #{config[:database]} > #{file_location})

    dump.attach(
      io: File.open(file_location),
      filename: "dump-#{id}.sql",
      identify: false
    )

    Honeybadger.check_in("check-in-hash") if dump.byte_size > 10_000
  ensure
    File.delete(file_location) if File.exist?(file_location)
  end

  def self.restore(id)
    backup = find(id)

    backup.dump.open do |file|
      %x(mysql -u #{config[:username]} -p#{config[:password]} -h #{config[:host]} -P #{config[:port]} #{config[:database]} < #{file.path})
    end
  end

  def self.purge_old_backups!
    where(created_at: ..1.month.ago).destroy_all
  end

  def self.import_from(url)
    from_config = URI.parse(url)

    %x(mysqldump --set-gtid-purged=OFF -u #{from_config.user} -p#{from_config.password} -h #{from_config.host} #{from_config.path[1..-1]} | mysql -u #{config[:username]} -p#{config[:password]} -h #{config[:host]} -P #{config[:port]} #{config[:database]})
  end

  private

  def self.config
    ActiveRecord::Base.connection_db_config.configuration_hash
  end

  def config
    ActiveRecord::Base.connection_db_config.configuration_hash
  end
end

ActiveStorage and Solid Queue already make a great combination for this, and maybe ActiveStorage will support file encryption in the future. For now though, the lockbox gem makes it pretty straightforward by utilizing AES-GCM encryption out of the box, and you can even take it a step further by adding per-record encryption as well.