backup · Linux · MySQL

Backup your MySQL/MariaDB database easily using Percona XtraBackup


Over the years I have looked around for easy to use MySQL database backup tools that I can quickly and reliably automate. I’ve tried many unsatisfactory solutions but finally I have found something I am happy with, Perscona’s XtraBackup.

Unfortunately for Windows users this is a Linux only solution.

Percona is an enterprise focused database company with the aim of delivering their clients faster and more reliable MySQL solutions. Fortunately for the rest of us they also believe in open source and offer a number of free tools suitable for all MySQL and MariaDB users in addition to their own MySQL server platform.

Below is a Markdown formatted cheat sheet that I wrote which enables Linux users to easily back-up their MySQL compatible databases. With simple instructions on how to create, restore and automate backups. While the instructions were written with Ubuntu/Debian in mind they should be useful for all other distributions with the usual adjustments.

Percona XtraDB Software page.
Percona XtraDB downloads (not needed for Ubuntu/Debian users).

Gist bengarrett / 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.

#!/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""[email protected]")
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

view raw
sql-backup.sh
hosted with ❤ by GitHub

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s