Installing and using Percona XtraBackup on Ubuntu 14.04 for MySQL backups.

percona-xtrabackup.md

Installing and using Percona XtraBackup

For MySQL/MariaDB database servers on Ubuntu.

Set-up and Install

Add Percona to your repository.

Backup your sources.

sudo cp /etc/apt/sources.list /etc/apt/sources.list.backup

Edit your sources.

sudo nano /etc/apt/sources.list

Append Percona's sources to the end of file.

If needed, change trusty (Ubuntu 14.04 LTS) to the repo name of your Ubuntu install. Ubuntu 12.04 would be precise.

# Percona
deb http://repo.percona.com/apt trusty main
deb-src http://repo.percona.com/apt trusty main
```
###### Add the repository's key to your collection.
`sudo apt-key adv --keyserver keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A`
> ```
gpg: Total number processed: 1
gpg:               imported: 1
```

###### Update your packages list.
`sudo apt-get update`

###### Install XtraBackup.
`sudo apt-get install percona-xtrabackup`

###### Check that everything installed okay.
`xtrabackup --version`
> `xtrabackup version 2.2.9 based on MySQL server 5.6.22 Linux (x86_64) (revision id: )`

`innobackupex --version`
> `InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy`

###### Create a backup staging directory.
`sudo mkdir /var/backups/mysql/`

### Backup database
###### Create a daily, weekly, monthly and quarterly set of backups.
* `innobackupex` is the backup shell script.
* `--compact` saves space but takes longer to compress.
* `--no-timestamp` removes the default behavior of appending the current time to the backup directory.
* `--user` database user account to grant XtraBackup access.
* `--passsword` database user account password.

```
sudo innobackupex --no-timestamp --user=(user) --password=(password) /var/backups/mysql/daily
```
> ```
innobackupex: Backup created in directory '/var/backups/mysql/daily'
innobackupex: Connection to database server closed
innobackupex: completed OK!
```

```
sudo innobackupex --compact --no-timestamp --user=(user) --password=(password) /var/backups/mysql/weekly
sudo innobackupex --compact --no-timestamp --user=(user) --password=(password) /var/backups/mysql/monthly
sudo innobackupex --compact --no-timestamp --user=(user) --password=(password) /var/backups/mysql/quarterly
```

### Create backups CRON jobs aka time-based job schedulers.
`sudo crontab -e`

Append at the bottom of the file after the following.
> ```
# For more information see the manual pages of crontab(5) and cron(8)
#
# m h  dom mon dow   command
```

```
# Percona XtraBackup for database backup
@daily mv -f /var/backups/mysql/daily /var/backups/mysql/daily~ && innobackupex --no-timestamp --user=(user) --password=(password) /var/backups/mysql/daily
@weekly mv -f /var/backups/mysql/weekly /var/backups/mysql/weekly~ && innobackupex --compact --no-timestamp --user=(user) --password=(password) /var/backups/mysql/weekly
@monthly mv -f /var/backups/mysql/monthly /var/backups/mysql/monthly~ && innobackupex --compact --no-timestamp --user=(user) --password=(password) /var/backups/mysql/monthly
0 0 1 1,4,7,10 * mv -f /var/backups/mysql/quarterly /var/backups/mysql/quarterly~ && innobackupex --compact --no-timestamp --user=(user) --password=(password) /var/backups/mysql/quarterly
```
The job listed last runs on the first day of the month at midnight on Jan, Apr, Jul and Oct. The other jobs that use @special strings also run at midnight. If you wanted to, you could change the daily to run at 2:30am.
```
30 2 * * * mv -f /var/backups/mysql/daily /var/backups/mysql/daily~ && innobackupex --no-timestamp --user=(user) --password=(password) /var/backups/mysql/daily
```
Or the weekly job to run every Monday evening at 11pm.
```
0 23 * * 1 mv -f /var/backups/mysql/weekly /var/backups/mysql/weekly~ && innobackupex --no-timestamp --user=(user) --password=(password) /var/backups/mysql/weekly
```
['Further reading, Cron Howto'](https://help.ubuntu.com/community/CronHowto)

### Restore a backup to the database
First we must prepare the backup for restoration. This completes any uncommitted transactions and pending operations.
`innobackupex --apply-log /var/backups/mysql/daily`
The last line of the `--apply-log` returned output should say OK!
> ` innobackupex: completed OK!`

Then we restore the backup but to do this the database's *datadir* `/var/lib/mysql/` needs to be empty for the restoration to be successful. So we shut down MySQL and move its current data directory into a backup location.

###### Stop MySQL
`sudo service mysql stop`
> `mysql stop/waiting`

###### Duplicate then delete existing data
`sudo cp -aR /var/lib/mysql/ /var/lib/mysql~/`

###### Double check that everything copied okay (no output is good)
`sudo diff -qr /var/lib/mysql/ /var/lib/mysql~/`
> ` `

###### Delete the MySQL data and check that its directory is empty
```
sudo rm -R /var/lib/mysql/*
ll /var/lib/mysql/
```
###### Restore the backed up data to database data
`innobackupex --copy-back /var/backups/mysql/daily`
> `innobackupex: completed OK!`

###### Grant MySQL ownership permission to the data.
`sudo chown -R mysql:mysql /var/lib/mysql`

###### Restart MySQL
`sudo service mysql start`
> `mysql start/running, process`

Congratulations! If there are any issues and MySQL does not start you can can check its log file.
```
tail /var/log/mysql/error.log
```

And if worse comes to worse, restore the pre-deleted data.
```
sudo rm -R /var/lib/mysql
sudo mv /var/lib/mysql~ /var/lib/mysql
sudo service mysql start
```

### Learn more
Percona XtraBackup offers much more functionality than what is listed here, including encryption, parallel and threading and selective database backups. You can download its [User's Manual](http://form.percona.com/Percona-XtraBackup-22-Series-Manual.html) (requires supplying an e-mail address to access) to learn more about this excellent tool.

sql-backup.sh

#!/usr/bin/env bash
# /usr/local/bin/sql-backup.sh
# version 2.1.0
#
# A simple script that backs up an MySQL compatible database.
# https://gist.github.com/bengarrett/9cfac5e4d736fa92bf66
#
# Designed for Percona XtraBackup (innobackupex).
# https://www.percona.com/software/mysql-database/percona-xtrabackup
#
# Intended to be used as a cron-job.
# sudo crontab -e
#
# 12:00am - daily
# 00 00 * * * sql-backup -d
# 12:30am - weekly
# 30 00 * * 1 sql-backup -w
# 12:01 - monthly
# 00 01 * 1 * sql-backup -m
# 1:30am - quarterly
# 30 01 1 1,4,7,10 * sql-backup -q

FILE="/var/passwords/sql"
BACKUPS="/var/backups/mysql"

PARSED_OPTIONS=$(getopt -n "$0"  -o hdwmq --long "help,daily,weekly,monthly,quarterly"  -- "$@")
if [ $? -ne 0 ];
then
  echo "$0: unrecognised option '$1'"
  echo "Try '$0 --help' for more information."
  exit 1
fi

if [ "$EUID" -ne 0 ]
  then echo "Please run as root"
  exit 126
fi

if [ -f "$FILE" ]
then
  data=$(<$FILE)
else
  echo "Required $FILE not found."
  exit 1
fi

while true;
do
  case "$1" in
     -h|--help)
      echo "Usage: $0 [OPTION]"
      echo "Backup the database using a specific method."
      echo ""
      echo "One option is mandatory."
      echo "  -h, --help    display this help and exit"
      echo "  -d, --daily"
      echo "  -w, --weekly"
      echo "  -m, --monthly"
      echo "  -q, --quarterly"
      exit 0
     break;;

    -d|--daily)
      METHOD="daily"
      break;;

    -w|--weekly)
      METHOD="weekly"
      break;;

    -m|--monthly)
      METHOD="monthly"
      break;;

    -q|--quarterly)
      METHOD="quarterly"
      break;;

  esac
done

echo "Running the $METHOD backup."

# if exists move secondary backup to a temporary location that will be deleted afterwards.
if [ -d "$BACKUPS/$METHOD~" ]
then
  mv -f $BACKUPS/$METHOD~ $BACKUPS/$METHOD~~
fi

# move primary backup to the secondary backup.
if [ -d "$BACKUPS/$METHOD" ]
then
  mv -f $BACKUPS/$METHOD $BACKUPS/$METHOD~
fi

# create a new primary backup
innobackupex --no-timestamp --user=root --password=$data $BACKUPS/$METHOD
# change group so database can be remotely backed up
chown -R :ben $BACKUPS/*

# delete the former secondary backup
if [ -d "$BACKUPS/$METHOD~~" ]
then
  rm -rdf $BACKUPS/$METHOD~~
fi

exit 0