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>

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.

   1  dd if=/dev/xen-1/aktagon.com-disk of=/tmp/file
   2  scp /tmp/file username@host:/file

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

   1  ssh host
   2  lvcreate -L 4G /dev/VG00/aktagon.com-disk
   3  cat aktagon.com-disk.bz2 > /dev/aktagon-1/aktagon.com-disk

Remember to create a backup before running these commands.

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 http://pastie.caboo.se/42574. All credit to court3nay.

   1  task :backup, :roles => :db, :only => { :primary => true } do
   2    filename = "#{backup_dir}/#{application}.dump.#{Time.now.to_f}.sql.bz2"
   3    text = capture "cat #{deploy_to}/current/config/database.yml"
   4    yaml = YAML::load(text)
   5  
   6    on_rollback { run "rm #{filename}" }
   7    run "mysqldump -u #{yaml['production']['username']} -p #{yaml['production']['database']} | bzip2 -c > #{filename}" do |ch, stream, out|
   8      ch.send_data "#{yaml['production']['password']}\n" if out =~ /^Enter password:/
   9    end
  10  end

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

   1  task :before_deploy do
   2      backup
   3    end

To restore the backup run the following command:

   1  mysql database_name -uroot < filename.sql