How to create a capistrano task to download latest database_backup.tgz and import locally?

Question

I'm trying to do something with a Capistrano task that is similar to the heroku db:pull functionality if you are familiar with it.

I have a remote server. On that server I have a bunch of backups in the /path/db_backups/ folder. And in that folder there's a backup of the database everyday.

All I want to do is

Download the latest backup on the client machine.
Untar it.
Import it into local mysql db.

Anyone know of a good way to handle this? Is there a gem I am unaware of? Is there a script you have handy?

score 6 · Answer 1 · answered Dec 05 '12 at 10:47

I'm not sure if there is a gem for that. I usually copy/pastle this task on capistrano (config/deploy.rb) to pull a compressed database from the server and store it on my development environment

namespace :utils do
  desc 'pull the DB from the server'
  task :pull_db, :roles => :db, :only => { :primary => true } do

    website = "http://www.my_website.com"
    filename = "#{application}.dump.#{Time.now.to_f}.sql"
    filename_bz2 = "#{filename}.bz2"
    remote_file = "#{current_path}/public/#{filename_bz2}"


    text = capture "cat #{deploy_to}/current/config/database.yml"
    yaml = YAML::load(text)

    on_rollback { run "rm #{remote_file}" }
    run "mysqldump -h#{yaml[rails_env]['host']} -u #{yaml[rails_env]['username']} -p #{yaml[rails_env]['database']} | bzip2 -c > #{remote_file}" do |ch, stream, out|
      ch.send_data "#{yaml[rails_env]['password']}\n" if out =~ /^Enter password:/
    end

    local_text = run_locally("cat config/database.yml")
    local_yaml = YAML::load(local_text)

    run_locally("wget #{website}/#{filename_bz2}")
    run_locally("bzip2 -d #{filename_bz2}")

    run_locally("bundle exec rake db:drop")
    run_locally("bundle exec rake db:create")

    if local_yaml['development']['password'] && !local_yaml['development']['password'].blank?
      run_locally("mysql -h#{local_yaml['development']['host']} -u#{local_yaml['development']['username']} -p#{local_yaml['development']['password']} #{local_yaml['development']['database']} < #{filename}")
    else
      run_locally("mysql -h#{local_yaml['development']['host']} -u#{local_yaml['development']['username']} #{local_yaml['development']['database']} < #{filename}")
    end

    run_locally("rm #{filename}")
    run "rm #{remote_file}"
  end
end

BTW, I did the code myself, I found the way to compress and zip the db on the server and then I created the rest of the code. Did it worked for your? I'm currently using this script for some real world app and it works fine. — rorra, Dec 06 '12 at 08:28

severin · Accepted Answer · 2012-12-07T11:46:08.240

3

The following script should achieve that:

# Find out which file to copy and save its name in a local text file:
# ssh allows you to specify a command that should be executed on the remote
# machine instead of opening a terminal session on it. I use this to get
# a sorted (ls -t sorts by modification date) list of all backups. I then
# truncate this list to one entry using head -1 and save the file name in a
# local file (filename.txt).
# (12.34.56.78 is a placeholder for the ip/hostname of your server)
ssh 12.34.56.78 ls -t /path/to/backups/ | head -1 > filename.txt

# Copy the backup specified in filename.txt to the tmp dir on your local machine.
scp 12.34.56.78:/path/to/backups/`cat filename.txt` /tmp/db_backup.sql.tar

# Untar the backup archive.
cd /tmp && tar -xf db_backup.sql.tar

# Import into database of choice.
mysql -u your_username -p database_to_import_to < /tmp/db_backup.sql

(This assumes that you are on a UNIX system and have scp and tar installed...)

edited Dec 07 '12 at 11:46

answered Dec 03 '12 at 08:57

severin

10,148
1
39
40

What I'm looking for is a script that can figure out the file name based on the last modification date. There's a pattern to the backups, they're created every night. However it sucks to enter this when you want to import. I'm looking for a way to handle it automatically so it just pulls latest db. – Daniel Fischer Dec 04 '12 at 00:29
1

@DanielFischer Then provide some example file names. So somebody can figure out the pattern for you and give you a solution. Do you think that we are seers, or what? And why did you not ask for it in the fist place, so Severin could answer upfront to what you wanted? – Szymon Jeż Dec 04 '12 at 10:13
No need to get aggressive ;) @DanielFischer please provide as much information as possible (example filenames etc.) so that someone can answer your question properly. It takes some time to come up with a script/solution, so I guess noone blindly commits his time to providing a solution if the problem is not completely understood... – severin Dec 04 '12 at 10:19
Yes, sorry, thank you severin. I appreciate your willingness to help. Here's an example of `ls -la` output: https://gist.github.com/0b6f955c6231c56a0850 This is why I thought it would make sense to just grab latest file by unix timestamp then pipe that through somehow but I honestly don't know anything besides that technical name. – Daniel Fischer Dec 05 '12 at 01:08
@DanielFischer I updated my answer with a new first step that determines the correct backup file to download. I hope this works for you. If you have any problems with it, please ask again. And if it works, please accept my answer ;) – severin Dec 07 '12 at 11:47

How to create a capistrano task to download latest database_backup.tgz and import locally?

2 Answers2