Setup and configure MySQL backup using Holland and Xtrabackup

Setting up a database backup is a primary task for database administrators and we see perl and shell scripts wrapped around few of the backup-tools in practice. With right tools things can look easy and today we shall look into one of that!

MySQL Database backup can be done with mysqldump, mysqlhotbackup, xtrabackup, lvm / file-system snapshots or doing delimited-text file backups.

In this post we will learn setting up and configuring MySQL backup using Holland backup framework & Xtrabackup. We won’t be needing any scripts 🙂 (let’s have short intro first…)

Holland Backup Framework:

Holland is an Open Source backup framework written in Python at Rackspace. It offers more reliability and flexibility when backing up MySQL databases. As Holland is plugin-based framework, it can conceivably backup almost anything you want by whatever means. (It does support Postgres.)

Xtrabackup:

XtraBackup is an open-source hot backup utility for MySQL, developed and maintained by Percona.

So let’s get started with setting-up MySQL backup with Holland Framework using Xtrabackup.

Setting up Holland:

Installing dependencies:

apt-get install libmysqlclient-dev
apt-get install python-setuptools
apt-get install python-mysqldb

– OR –

yum install MySQL-python
yum install python-setuptools
yum install mysql-devel.x86_64

Holland has ready-made packages available for Red-Hat, CentOS, and Ubuntu which are available via the OpenSUSE build system.  We can get generic tarballs for others from here or we can also get it from Github.

For this exercise we will get it from Git. (Follow the steps)

1. mkdir holland
2. wget https://github.com/holland-backup/holland/archive/master.zip
3. unzip master.zip
4. cd holland-master
5. sudo mkdir -p /etc/holland  /var/log/holland /var/spool/holland
6. sudo python ./setup.py install

Holland framework is now installed. You can check the version as:

holland --version
## We should see following output
	Holland Backup v1.0.11
	Copyright (c) 2008-2010 Rackspace US, Inc.
	More info available at http://hollandbackup.org

	[[[[[[[]]]]]]] [[[[[[[]]]]]]]
	[[[[[[[]]]]]]]       [[[[[[[]]]]]]]
	[[[[[[[]]]]]]] [[[[[[[]]]]]]]
	[[[[[[[]]]]]]] [[[[[[[]]]]]]]

Well so we have Holland framework installed, let’s move on to the plugins.

(Note that the path in the setup steps are relative make sure you follow one after another.)

Installing plugins (Common, MySQL and Xtrabackup):

1. cd plugins/holland.lib.common/
2. sudo python setup.py install
3. cd ../holland.lib.mysql/
4. sudo python setup.py install
5. cd ../holland.backup.xtrabackup/
6. sudo python setup.py install

Common and MySQL as essential plugins. As we’re using Xtrabackup as backup provider for Holland, we’ll install holland.backup.xtrabackup plugin. Other plugin includes LVM, mysqldump, pgdump (for postgres).

Setup configuration files for MySQL backup:

1. cd ../../config/backupsets/examples
2. sudo mkdir -p /etc/holland/backupsets
3. sudo cp xtrabackup.conf /etc/holland/backupsets
4. cd ../../providers
5. sudo mkdir -p /etc/holland/providers/
6. sudo cp xtrabackup.conf /etc/holland/providers/
7. cd ../
8. sudo cp holland.conf /etc/holland

Backup user:
We’ll create the specific MySQL database user for holland backup as follows:

mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'bkpuser'@'localhost' identified by 'xxxx';

Holland Backup Configuration:

• holland.conf: Holland’s configuration file which resides under /etc/holland/ by default.
• provier configuration: Confiruation for provider, here we have Xtrabackup.
• backupsets configuration: Configuration for MySQL backups.

Sample configuration for holland:

root@ubuntu:/etc/holland# cat holland.conf 
## Root holland config file
[holland]

## Paths where holland plugins may be found.
## Can be comma separated
plugin_dirs = /usr/share/holland/plugins

## Top level directory where backups are held
backup_directory = /var/spool/holland

## List of enabled backup sets. Can be comma separated. 
## Read from /backupsets/.conf
# backupsets = example, traditional, parallel_backups, non_transactional
backupsets = default 

# Define a umask for file generated by holland
umask = 0007

# Define a path for holland and its spawned processes
path = /usr/local/bin:/usr/local/sbin:/bin:/sbin:/usr/bin:/usr/sbin

[logging]
## where to write the log
filename = /var/log/holland/holland.log

## debug, info, warning, error, critical (case insensitive)
level = info

Sample configuration for backup-set:

root@ubuntu:/etc/holland/backupsets# cat xtrabackup.conf 
[holland:backup]
plugin = xtrabackup
backups-to-keep = 1
auto-purge-failures = yes
purge-policy = after-backup
estimated-size-factor = 1.0

[xtrabackup]
innobackupex = innobackupex
stream = yes
slave-info = no

[compression]
method = gzip
inline = yes
level = 1

[mysql:client]
defaults-extra-file = ~/.my.cnf
# user = ""
# password = ""
# host = ""
# port = ""
# socket = ""

Sample configuration for provider:

root@ubuntu:/etc/holland/providers# cat xtrabackup.conf 
[holland:backup]
backups-to-keep = 1
estimated-size-factor = 1.0
plugin = xtrabackup

[xtrabackup]
innobackupex = innobackupex
stream = yes
slave-info = no

[compression]
method = gzip
inline = yes
level = 1

[mysql:client]
defaults-extra-file = ~/.my.cnf
user = "bkpuser"
password = "xxxx"
#host = "HOSTNAME"
#port = "3306"
#socket = "SOCKET"

Now we can do a dry run to verify our setup and configuration:

holland -d backup --dry-run xtrabackup

Check output and correct if any errors. Most likely errors could be due to permissions, fix if any.

MySQL backup command using Holland and Xtrabackup:

holland -d backup xtrabackup

(Above command is actually: holland -d backup [backup-set-name])

The above holland backup command should generate log output similar to following:

2015-06-01 01:20:01,977 [DEBUG] Adding plugin directory: u'/usr/share/holland/plugins'
2015-06-01 01:20:01,980 [INFO] Holland 1.0.11 started with pid 865
2015-06-01 01:20:01,999 [INFO] --- Starting backup run ---
2015-06-01 01:20:02,002 [DEBUG] Set advisory lock on /etc/holland/backupsets/xtrabackup.conf
2015-06-01 01:20:02,004 [INFO] Creating backup path /backup/xtrabackup/20150601_012002
2015-06-01 01:20:02,025 [INFO] * Generating mysql option file: /backup/xtrabackup/20150601_012002/my.cnf
2015-06-01 01:20:02,025 [INFO]   + Added !include /etc/my.cnf
2015-06-01 01:20:02,025 [INFO]   + Added !include /home/backup/.my.cnf
2015-06-01 01:20:02,025 [INFO]   + Added [client] section with credentials from [mysql:client] section
2015-06-01 01:20:02,025 [DEBUG] Writing out config to /backup/xtrabackup/20150601_012002/backup.conf
2015-06-01 01:20:02,066 [INFO] Estimated Backup Size: 1.29TB
2015-06-01 01:20:02,066 [INFO] Adjusting estimated size by 0.40 to 526.35GB
2015-06-01 01:20:02,066 [INFO] Starting backup[xtrabackup/20150601_012002] via plugin xtrabackup
2015-06-01 01:20:02,067 [DEBUG] * Executing: /bin/gzip -4
2015-06-01 01:20:02,070 [INFO] Executing: /usr/bin/innobackupex-1.5.1 --defaults-file=/backup/xtrabackup/20150601_012002/my.cnf --stream=tar --tmpdir=/backup/xtrabackup/20150601_012002 --slave-info --no-timestamp --defaults-group=mysqld1 /backup/xtrabackup/20150601_012002
2015-06-01 01:20:02,071 [INFO]   > /backup/xtrabackup/20150601_012002/backup.tar.gz 2 > /backup/xtrabackup/20150601_012002/xtrabackup.log
2015-06-01 08:45:58,084 [WARNING] Skipping --prepare/--apply-logs since backup is streamed
2015-06-01 08:45:58,086 [INFO] Final on-disk backup size 166.07GB
2015-06-01 08:45:58,086 [INFO] 12.62% of estimated size 1.29TB
2015-06-01 08:45:58,086 [DEBUG] Writing out config to /backup/xtrabackup/20150601_012002/backup.conf
2015-06-01 08:45:58,087 [INFO] Backup completed in 7 hours, 25 minutes, 56.06 seconds
2015-06-01 08:46:00,480 [INFO] Purged xtrabackup/20150530_012001
2015-06-01 08:46:00,481 [INFO] 1 backups purged
2015-06-01 08:46:00,485 [INFO] Released lock /etc/holland/backupsets/xtrabackup.conf
2015-06-01 08:46:00,485 [INFO] --- Ending backup run ---

Okay, so finally our MySQL backup is all set. We may cron above holland-backup command to have daily or weekly MySQL database backup as per choice.

Notes on configuration items:

Most common backup configuration options are included in the conf files, I’d still cover few of the options here.

backupsets/xtrabackup.conf

•  backups-to-keep – This option of holland backup-sets configuration is our retention period. It will keep the backups for this many days and purge the older ones.
• before-backup-command / after-backup-command : This are the commands executed before and after the backup execution. Typical usecase could be doing some cleanup jobs before starting backup, uploading backup to a different location (say S3 or shared network device!)
• failed-backup-command – This is newly introduced variable which holds the command to execute upon backup failure. A very generic case could be a send-email script to alert us!

provider/xtrabackup.conf

• pre-command – Executed before the xtrabackup command. You might want to have, say mysqldump of data-definitions!
• slave-info – This enables the –slave-info innobackupex option.
• method – gzip is the default compression method for xtrabackup. We may choose pigz or other.

Hope this helps. Do comment if you face any issues setting things up or need clarity.

Happy Backup, MySQLer!

PS: I’d thank my friend Andy for his tutorial for Holland backup ages ago.

Reference: http://docs.hollandbackup.org