Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Installing and using Percona XtraBackup on Ubuntu 14.04 for MySQL backups.

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.
#!/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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment