Quick database backup script for a Drupal multisite

db_dump.rb

#!/usr/bin/env ruby

require "yaml"
require "optparse"
require "ostruct"
require "date"

config_file = File.open(File.expand_path('~') + "/etc/smt_config.yml")
acct_file = File.open(File.expand_path('~') + "/etc/smt_acct.yml")

site_config = YAML.load(config_file)
acct_config = YAML.load(acct_file)

sites = site_config["sites"]

dbpass = acct_config["database"]["backup_pass"]
user = acct_config["database"]["backup_user"]
db_host = acct_config["servers"]["prod_db"]
remote_user = acct_config["servers"]["staging_user"]


date = Date.today.strftime("%Y%m%d")

options = OpenStruct.new

options.site = nil
options.operation = nil

OptionParser.new do |opts|
  opts.banner = "Usage: smtstage.rb [options]"

  opts.on('-s', '--site SITE',
  "Sites to operate on, comma delimited (smt,gov,hwc) ('all' for all sites)") do |site|

    if site == "all"
      options.site = sites
    else
      options.site = []
      site_list = site.split(",")
      site_list.each do |sl|
        unless sites.detect {|s| s["abbrev"].downcase == sl }
          puts "Unknown site: #{sl} (valid options: smt,sbf,hwc,gov,tcc,tsc,scc,mvp,sdc,tec)"
          site_list.delete(sl)
        else
          options.site << sites.detect {|s| s["abbrev"].downcase == sl }
        end
      end
    end
  end

end.parse!




# outputs files in the format "database.YYYYMMDD.sql.gz"

# Dump table structure
# 


options.site.each do |sl|
  
  db = sl["db"]


puts "Dumping table structure for #{sl['name']} ..."


tables = `mysql --skip-column-names -e 'show tables' -u #{user} -p#{dbpass} -h #{db_host} #{db}`

system "mysqldump --complete-insert --disable-keys --single-transaction --no-data -u #{user} -p#{dbpass} -h #{db_host} #{db}  > #{db}.#{date}.sql"

# Dump data - except for cache, sessions, search, watchdog

puts "Dumping data for #{sl['name']} ..."

tables =  `mysql --skip-column-names -e 'show tables' -u #{user} -p#{dbpass} -h #{db_host} #{db} | egrep -v \'(^search.*)|(^cache.*)|(^zz_search_index)|^(accesslog|cache|sessions|watchdog|zz_search_index_backup)$\'`

table_list = tables.split("\n")

table_list.each do |tl|
  
  `mysqldump --complete-insert --disable-keys --single-transaction --no-create-info -u #{user} -p#{dbpass} -h #{db_host} #{db} #{tl} >> #{db}.#{date}.sql`


end

puts "Compressing dump for #{sl['name']} ..."

system "gzip -f #{db}.#{date}.sql"

puts "Sending file for #{sl['name']} ..."

system "scp #{db}.#{date}.sql.gz #{remote_user}:db_dumps/"


# 
end

puts "Done."