linux mysql

Backup your MySQL/MariaDB database
easily using Percona XtraBackup

Reading time of 844 words
4 minutes
Reading time of 844 words ~ 4 minutes


Did you find this article helpful?
Please consider tipping me a coffee as a thank you.
Ko-fi Buy Me a Coffee
Did you find this article helpful? Please consider tipping me a coffee or three as a thank you.
Tip using Ko-fi or Buy Me a Coffee

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’

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 (requires supplying an e-mail address to access) to learn more about this excellent tool.

Written by Ben Garrett

Did you find this article helpful?
Please consider tipping me a coffee as a thank you.
Ko-fi Buy Me a Coffee
Did you find this article helpful? Please consider tipping me a coffee or three as a thank you.
Tip using Ko-fi or Buy Me a Coffee