This is a customized version of this script:

   1  require 'find'
   2  
   3  namespace :db do
   4  
   5    desc "Backup the database to a file. Options: DIR=base_dir RAILS_ENV=production MAX=20"
   6  
   7    task :backup => [:environment] do
   8      datestamp = Time.now.strftime("%Y-%m-%d_%H-%M-%S")
   9      base_path = ENV["DIR"] || "db"
  10      backup_base = File.join(base_path, 'backup')
  11      backup_folder = File.join(backup_base, datestamp)
  12      backup_file = File.join(backup_folder, "#{RAILS_ENV}_dump.sql.gz")
  13      FileUtils.mkdir_p(backup_folder)
  14      db_config = ActiveRecord::Base.configurations[RAILS_ENV]
  15      pass = ''
  16      pass = '-p' + db_config['password'] if db_config['password']
  17      cmd = "mysqldump -P #{db_config['port']} -h #{db_config['host']} -u #{db_config['username']} #{pass} #{db_config['da
  18  tabase']} -Q --add-drop-table -O add-locks=FALSE -O lock-tables=FALSE | gzip -c > #{backup_file}"
  19      sh cmd
  20      dir = Dir.new(backup_base)
  21      all_backups = dir.entries[2..-1].sort.reverse
  22      puts "Created backup: #{backup_file}"
  23      max_backups = (ENV["MAX"] || 20).to_i
  24      puts max_backups
  25      unwanted_backups = all_backups[max_backups..-1] || []
  26      for unwanted_backup in unwanted_backups
  27        FileUtils.rm_rf(File.join(backup_base, unwanted_backup))
  28        puts "deleted #{unwanted_backup}"
  29      end
  30      puts "Deleted #{unwanted_backups.length} backups, #{all_backups.length - unwanted_backups.length} backups available"
  31  
  32    end
  33  
  34  end

   1  require 'rubygems'
   2  require 'sequel'
   3  require 'expect'
   4  require 'pty'
   5  require 'pp'
   6  
   7  #$expect_verbose = true 
   8  
   9  def exec_pty(cmd)
  10    PTY.spawn(cmd) do |reader, writer, pid|
  11      reader.expect(/Enter password/) do |line|
  12        writer.puts ''
  13      end 
  14  
  15      while line=reader.gets
  16  #      print line
  17      end
  18    end 
  19  end
  20  
  21  user      = 'fuuuuuck'
  22  password  = ''
  23  host      = 'localhost'
  24  encoding  = 'utf8'
  25  dir       = '/tmp'
  26  timestamp = Time.now.strftime('%d-%m-%y')
  27  
  28  connection = Sequel.mysql nil, :user => user, :password => password, :host => host, :encoding => encoding
  29  
  30  databases = []
  31  connection['show databases;'].each do |db|
  32    databases << db[:Database]
  33  end
  34  databases = databases - ['mysql', 'test', 'information_schema']
  35  
  36  
  37  databases.each do |db|
  38  
  39    file = File.join(dir, "#{db}_#{timestamp}.sql")
  40    p file
  41    cmd = "mysqldump -u#{user} -p#{password} -h#{host} -Q -c -C --add-drop-table --add-locks --quick --lock-tables #{db} > #{file}"
  42  
  43    
  44    result = exec_pty(cmd)
  45  end

First install the plugin:

   1  script/plugin install http://github.com/mileszs/ar_fixtures/commits/master 

Then dump data for all models with:

   1  rake db:data:dump:all

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

http://sourceforge.net/projects/automysqlbackup/

   1  cd /etc/mysql
   2  wget http://garr.dl.sourceforge.net/sourceforge/automysqlbackup/automysqlbackup.sh.2.5
   3  
   4  ln -s automysqlbackup.sh.2.5 backup-script
   5  chmod 740 backup-script
   6  
   7  vim backup-script

Next tell cron to run it 4 in the morning:

   1  crontab -e

Add the following line:

   1  0 4 * * * /etc/mysql/backup-script

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

   1  -- Export
   2  exp <username>/<password>@<server> file=data.dmp consistent=y grants=no statistics=none
   3  
   4  -- Import
   5  imp <username>/<password>@<server> file=data.dmp fromuser=<source_username> touser=<username>