backup snippets

Capistrano 2 task for backing up your MySQL production database before each deployment

Tagged ruby, rails, mysql, backup, capistrano  Languages ruby

This Capistrano task connects to your production database and dumps the contents to a file. The file is compressed and put in a directory specified with set :backup_dir, "#{deploy_to}/backups". This is a slight modification of All credit to court3nay.

task :backup, :roles => :db, :only => { :primary => true } do
  filename = "#{backup_dir}/#{application}.dump.#{}.sql.bz2"
  text = capture "cat #{deploy_to}/current/config/database.yml"
  yaml = YAML::load(text)

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

To automatically backup your data before you deploy a new version add this to config/deploy.rb:

task :before_deploy do

To restore the backup run the following command:

mysql database_name -uroot < filename.sql

Transfer partition from one server to another

Tagged lvm, backup, transfer  Languages bash

The following is an example of how to transfer a partition from one server to another.

In the example, I first create a file containing the partition, and then transfer the partition to the other server.

dd if=/dev/xen-1/ of=/tmp/file
scp /tmp/file username@host:/file

I then SSH to the other server and create the partition from the file.

ssh host
lvcreate -L 4G /dev/VG00/
cat > /dev/aktagon-1/

Remember to create a backup before running these commands.

Using rsync to backup a remote machine to a local directory via SSH

Tagged rsync, ssh, sync, backup  Languages bash

This command copies the files on the remote machine to the local directory /backups/

rsync --delete -r -ve "ssh -p 666" /backups/

Some useful parameters:

-r = recursive
-n = simulate
-v = verbose
-a = archive mode
-L = follow symbolic links
--size-only = 
--progress = show progress indicator
--numeric-ids = don't map user and group IDs to local user and group names
-e = use SSH and port 666
--bwlimit = limit bandwidth to 100 kilobytes/second
--exclude-from = exclude the files and directories listed in the configuration file
--delete = remove local files that have been removed on the remote server

How to export and import Oracle schemas, or backup Oracle databases

Tagged dump, export, oracle, backup, import  Languages bash

You can use the exp and imp executables to perform data backup and restoration.

-- Export
exp <username>/<password>@<server> file=data.dmp consistent=y grants=no statistics=none

-- Import
imp <username>/<password>@<server> file=data.dmp fromuser=<source_username> touser=<username>

How to backup ActiveRecord model data to YAML with ar_fixtures

Tagged activerecord, fixtures, backup, rails, yaml  Languages ruby

First install the plugin:

script/plugin install

Then dump data for all models with:

rake db:data:dump:all

There's a task for loading the data into the database, see rake -T for more information.

You can also do this without a plugin by using ActiveSupport::JSON and File.write to seed your database.

How to back up all MySQL database instances with Ruby

Tagged mysql, expect, backup, mysqldump, pty  Languages ruby
require 'rubygems'
require 'sequel'
require 'expect'
require 'pty'
require 'pp'

#$expect_verbose = true 

def exec_pty(cmd)
  PTY.spawn(cmd) do |reader, writer, pid|
    reader.expect(/Enter password/) do |line|
      writer.puts ''

    while line=reader.gets
#      print line

user      = 'fuuuuuck'
password  = ''
host      = 'localhost'
encoding  = 'utf8'
dir       = '/tmp'
timestamp ='%d-%m-%y')

connection = Sequel.mysql nil, :user => user, :password => password, :host => host, :encoding => encoding

databases = []
connection['show databases;'].each do |db|
  databases << db[:Database]
databases = databases - ['mysql', 'test', 'information_schema']

databases.each do |db|

  file = File.join(dir, "#{db}_#{timestamp}.sql")
  p file
  cmd = "mysqldump -u#{user} -p#{password} -h#{host} -Q -c -C --add-drop-table --add-locks --quick --lock-tables #{db} > #{file}"

  result = exec_pty(cmd)

How to use Rake for backing up MySQL databases

Tagged mysql, rake, backup  Languages ruby

This is a customized version of this script:

require 'find'

namespace :db do

  desc "Backup the database to a file. Options: DIR=base_dir RAILS_ENV=production MAX=20"

  task :backup => [:environment] do
    datestamp ="%Y-%m-%d_%H-%M-%S")
    base_path = ENV["DIR"] || "db"
    backup_base = File.join(base_path, 'backup')
    backup_folder = File.join(backup_base, datestamp)
    backup_file = File.join(backup_folder, "#{RAILS_ENV}_dump.sql.gz")
    db_config = ActiveRecord::Base.configurations[RAILS_ENV]
    pass = ''
    pass = '-p' + db_config['password'] if db_config['password']
    cmd = "mysqldump -P #{db_config['port']} -h #{db_config['host']} -u #{db_config['username']} #{pass} #{db_config['da
tabase']} -Q --add-drop-table -O add-locks=FALSE -O lock-tables=FALSE | gzip -c > #{backup_file}"
    sh cmd
    dir =
    all_backups = dir.entries[2..-1].sort.reverse
    puts "Created backup: #{backup_file}"
    max_backups = (ENV["MAX"] || 20).to_i
    puts max_backups
    unwanted_backups = all_backups[max_backups..-1] || []
    for unwanted_backup in unwanted_backups
      FileUtils.rm_rf(File.join(backup_base, unwanted_backup))
      puts "deleted #{unwanted_backup}"
    puts "Deleted #{unwanted_backups.length} backups, #{all_backups.length - unwanted_backups.length} backups available"



Running (and debugging) cronjobs with rbenv and bundler

Tagged rbenv, backup, cron, bundler  Languages bash

This snippet shows how to run a backup script (Backup gem) and how to debug cron in case it doesn't work.

This example uses bundler and rbenv to complicate things:

# Set the path to include rbenv

# Debug cron environment...
*/1 * * * * /bin/bash -l -c '/bin/env > /tmp/cron-env.log'

# Run backup every 12 hours
* */12 * * * /bin/bash -l -c 'eval "$(rbenv init -)" && cd /var/www/xxx/current && bundle exec backup perform --trigger xxx -c config/backup.rb >> /tmp/backup.log 2>&1'

If backup fails, check /tmp/backup.log and/or /tmp/cron-env.log.

How to take a backup of a Postgres database

Tagged backup, postgres  Languages bash

This is one way of doing it:

pg_dump -s yamaha_development > structure.sql
pg_dump --column-inserts --data-only yamaha_development  > data.sql

You can then restore it to a different database if needed:

psql -c 'drop database yamaha_test;'
psql -c 'create database yamaha_test;'

psql yamaha_test < structure.sql
psql yamaha_test < data.sql

All without foreign key constraint, etc, errors.