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 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:
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
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.
- 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!
- 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.