Database backup

gistfile1.md

Setup Database Backup

Instructions to setup MySQL backup on a server and upload to Amazon S3 including email notification on backup failures. Refer to backup gem for more details

Install rbenv and ruby (skip this if your database server already has Ruby installed)

Install rbenv:

$ sudo apt-get update
$ sudo apt-get -y install curl git-core
$ curl https://raw.githubusercontent.com/fesplugas/rbenv-installer/master/bin/rbenv-installer | bash

Add the following to your ~/.bash_profile:

export RBENV_ROOT="${HOME}/.rbenv"

if [ -d "${RBENV_ROOT}" ]; then
  export PATH="${RBENV_ROOT}/bin:${PATH}"
  eval "$(rbenv init -)"
fi

Install Ruby:

$ source ~/.bash_profile
$ rbenv bootstrap-ubuntu-12-04
$ rbenv install 2.1.5
$ rbenv rehash
$ rbenv global 2.1.5

Install backup gem:

$ gem install backup

Install Percona XtraBackup

Add Percona key:

$ apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A

Add this to /etc/apt/sources.list, replacing VERSION with the name of your distribution:

deb http://repo.percona.com/apt VERSION main
deb-src http://repo.percona.com/apt VERSION main

Install xtrabackup:

$ sudo apt-get update
$ sudo apt-get -y install percona-xtrabackup

Configure backup

$ mkdir -p ~/Backup/models
$ cd ~/Backup/models
$ wget https://gist.githubusercontent.com/yetanothernguyen/139a0d4ee70706b2394d/raw/8d6cc8a610272b83e1e67ca595a3fb88439f7bfa/backup.rb
$ backup generate:config

• Update ~/Backup/models/backup.rb with a S3 bucket to store backups and a pair of access_key_id and secret_access_key
• Update STMP credentials for sending email

You can run backup with the following command:

$ backup perform -t backup

Schedule backup

Add the following to crontab (backup runs at 4:30am everyday):

30 4 * * * /bin/bash -l -c 'backup perform -t backup'