{"id":2206,"date":"2015-06-02T07:10:54","date_gmt":"2015-06-02T07:10:54","guid":{"rendered":"http:\/\/kedar.nitty-witty.com\/?p=2206"},"modified":"2025-04-12T06:53:41","modified_gmt":"2025-04-12T06:53:41","slug":"setup-and-configure-mysql-backup-using-holland-and-xtrabackup","status":"publish","type":"post","link":"https:\/\/kedar.nitty-witty.com\/blog\/setup-and-configure-mysql-backup-using-holland-and-xtrabackup","title":{"rendered":"Setup and configure MySQL backup using Holland and Xtrabackup"},"content":{"rendered":"\n

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!<\/p>\n\n\n\n

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

In this post we will learn setting up and configuring MySQL backup using Holland backup framework & Xtrabackup. We won’t be needing any scripts \ud83d\ude42 (let’s have short intro first…)<\/em><\/p>\n\n\n\n\n\n\n\n

Holland Backup Framework:<\/h2>\n\n\n\n

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.)<\/p>\n\n\n\n

Xtrabackup:<\/h2>\n\n\n\n

XtraBackup is an open-source hot backup utility for MySQL, developed and maintained by Percona.<\/p>\n\n\n\n

So let’s get started with setting-up MySQL backup with Holland Framework using Xtrabackup.<\/p>\n\n\n\n

Setting up Holland:<\/h2>\n\n\n\n

Installing dependencies:<\/p>\n\n\n\n

apt-get install libmysqlclient-dev\napt-get install python-setuptools\napt-get install python-mysqldb\n<\/pre>\n\n\n\n
– OR –<\/p>\n\n\n\n
yum install MySQL-python\nyum install python-setuptools\nyum install mysql-devel.x86_64\n<\/pre>\n\n\n\n
Holland has ready-made packages available for Red-Hat, CentOS, and Ubuntu which are available via the OpenSUSE build system<\/a>.  We can get generic tarballs for others from here<\/a> or we can also get it from Github<\/a>.<\/p>\n\n\n\n
For this exercise we will get it from Git. (Follow the steps)<\/p>\n\n\n\n
1. mkdir holland\n2. wget https:\/\/github.com\/holland-backup\/holland\/archive\/master.zip\n3. unzip master.zip\n4. cd holland-master\n5. sudo mkdir -p \/etc\/holland  \/var\/log\/holland \/var\/spool\/holland\n6. sudo python .\/setup.py install\n<\/pre>\n\n\n\n
Holland framework is now installed. You can check the version as:<\/p>\n\n\n\n
holland --version\n## We should see following output\n\tHolland Backup v1.0.11\n\tCopyright (c) 2008-2010 Rackspace US, Inc.\n\tMore info available at http:\/\/hollandbackup.org\n\n\t[[[[[[[]]]]]]] [[[[[[[]]]]]]]\n\t[[[[[[[]]]]]]]       [[[[[[[]]]]]]]\n\t[[[[[[[]]]]]]] [[[[[[[]]]]]]]\n\t[[[[[[[]]]]]]] [[[[[[[]]]]]]]\n<\/pre>\n\n\n\n
Well so we have Holland framework installed, let’s move on to the plugins.<\/p>\n\n\n\n
(Note that the path in the setup steps are relative<\/span> make sure you follow one after another.)<\/em><\/p>\n\n\n\n
Installing plugins (Common, MySQL and Xtrabackup):<\/p>\n\n\n\n
1. cd plugins\/holland.lib.common\/\n2. sudo python setup.py install\n3. cd ..\/holland.lib.mysql\/\n4. sudo python setup.py install\n5. cd ..\/holland.backup.xtrabackup\/\n6. sudo python setup.py install\n<\/pre>\n\n\n\n
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).<\/p>\n\n\n\n
Setup configuration files for MySQL backup:<\/strong><\/p>\n\n\n\n
1. cd ..\/..\/config\/backupsets\/examples\n2. sudo mkdir -p \/etc\/holland\/backupsets\n3. sudo cp xtrabackup.conf \/etc\/holland\/backupsets\n4. cd ..\/..\/providers\n5. sudo mkdir -p \/etc\/holland\/providers\/\n6. sudo cp xtrabackup.conf \/etc\/holland\/providers\/\n7. cd ..\/\n8. sudo cp holland.conf \/etc\/holland\n<\/pre>\n\n\n\n
Backup user:<\/strong>
We’ll create the specific MySQL database user for holland backup as follows:<\/p>\n\n\n\n
mysql> GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'bkpuser'@'localhost' identified by 'xxxx';\n<\/pre>\n\n\n\n
Holland Backup Configuration:<\/strong><\/h3>\n\n\n\n
    \n
  • holland.conf: Holland’s configuration file which resides under \/etc\/holland\/ by default.<\/li>\n\n\n\n
  • provier configuration: Confiruation for provider, here we have Xtrabackup.<\/li>\n\n\n\n
  • backupsets configuration: Configuration for MySQL backups.<\/li>\n<\/ul>\n\n\n\n
    Sample configuration for holland:<\/p>\n\n\n\n
    root@ubuntu:\/etc\/holland# cat holland.conf \n## Root holland config file\n[holland]\n\n## Paths where holland plugins may be found.\n## Can be comma separated\nplugin_dirs = \/usr\/share\/holland\/plugins\n\n## Top level directory where backups are held\nbackup_directory = \/var\/spool\/holland\n\n## List of enabled backup sets. Can be comma separated. \n## Read from \/backupsets\/.conf\n# backupsets = example, traditional, parallel_backups, non_transactional\nbackupsets = default \n\n# Define a umask for file generated by holland\numask = 0007\n\n# Define a path for holland and its spawned processes\npath = \/usr\/local\/bin:\/usr\/local\/sbin:\/bin:\/sbin:\/usr\/bin:\/usr\/sbin\n\n[logging]\n## where to write the log\nfilename = \/var\/log\/holland\/holland.log\n\n## debug, info, warning, error, critical (case insensitive)\nlevel = info\n<\/pre>\n\n\n\n
    <\/p>\n\n\n\n
    Sample configuration for backup-set:<\/p>\n\n\n\n
    root@ubuntu:\/etc\/holland\/backupsets# cat xtrabackup.conf \n[holland:backup]\nplugin = xtrabackup\nbackups-to-keep = 1\nauto-purge-failures = yes\npurge-policy = after-backup\nestimated-size-factor = 1.0\n\n[xtrabackup]\ninnobackupex = innobackupex\nstream = yes\nslave-info = no\n\n[compression]\nmethod = gzip\ninline = yes\nlevel = 1\n\n[mysql:client]\ndefaults-extra-file = ~\/.my.cnf\n# user = \"\"\n# password = \"\"\n# host = \"\"\n# port = \"\"\n# socket = \"\"<\/pre>\n\n\n\n
    Sample configuration for provider:<\/p>\n\n\n\n
    root@ubuntu:\/etc\/holland\/providers# cat xtrabackup.conf \n[holland:backup]\nbackups-to-keep = 1\nestimated-size-factor = 1.0\nplugin = xtrabackup\n\n[xtrabackup]\ninnobackupex = innobackupex\nstream = yes\nslave-info = no\n\n[compression]\nmethod = gzip\ninline = yes\nlevel = 1\n\n[mysql:client]\ndefaults-extra-file = ~\/.my.cnf\nuser = \"bkpuser\"\npassword = \"xxxx\"\n#host = \"HOSTNAME\"\n#port = \"3306\"\n#socket = \"SOCKET\"\n<\/pre>\n\n\n\n
    <\/p>\n\n\n\n
    Now we can do a dry run to verify our setup and configuration:<\/p>\n\n\n\n
    holland -d backup --dry-run xtrabackup<\/pre>\n\n\n\n
    Check output and correct if any errors. Most likely errors could be due to permissions, fix if any.<\/p>\n\n\n\n
    MySQL backup command using Holland and Xtrabackup:<\/strong><\/p>\n\n\n\n
    holland -d backup xtrabackup\n<\/pre>\n\n\n\n
    (Above command is actually: holland -d backup [backup-set-name])<\/em><\/p>\n\n\n\n
    The above holland backup command should generate log output similar to following:<\/p>\n\n\n\n
    2015-06-01 01:20:01,977 [DEBUG] Adding plugin directory: u'\/usr\/share\/holland\/plugins'\n2015-06-01 01:20:01,980 [INFO] Holland 1.0.11 started with pid 865\n2015-06-01 01:20:01,999 [INFO] --- Starting backup run ---\n2015-06-01 01:20:02,002 [DEBUG] Set advisory lock on \/etc\/holland\/backupsets\/xtrabackup.conf\n2015-06-01 01:20:02,004 [INFO] Creating backup path \/backup\/xtrabackup\/20150601_012002\n2015-06-01 01:20:02,025 [INFO] * Generating mysql option file: \/backup\/xtrabackup\/20150601_012002\/my.cnf\n2015-06-01 01:20:02,025 [INFO]   + Added !include \/etc\/my.cnf\n2015-06-01 01:20:02,025 [INFO]   + Added !include \/home\/backup\/.my.cnf\n2015-06-01 01:20:02,025 [INFO]   + Added [client] section with credentials from [mysql:client] section\n2015-06-01 01:20:02,025 [DEBUG] Writing out config to \/backup\/xtrabackup\/20150601_012002\/backup.conf\n2015-06-01 01:20:02,066 [INFO] Estimated Backup Size: 1.29TB\n2015-06-01 01:20:02,066 [INFO] Adjusting estimated size by 0.40 to 526.35GB\n2015-06-01 01:20:02,066 [INFO] Starting backup[xtrabackup\/20150601_012002] via plugin xtrabackup\n2015-06-01 01:20:02,067 [DEBUG] * Executing: \/bin\/gzip -4\n2015-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\n2015-06-01 01:20:02,071 [INFO]   > \/backup\/xtrabackup\/20150601_012002\/backup.tar.gz 2 > \/backup\/xtrabackup\/20150601_012002\/xtrabackup.log\n2015-06-01 08:45:58,084 [WARNING] Skipping --prepare\/--apply-logs since backup is streamed\n2015-06-01 08:45:58,086 [INFO] Final on-disk backup size 166.07GB\n2015-06-01 08:45:58,086 [INFO] 12.62% of estimated size 1.29TB\n2015-06-01 08:45:58,086 [DEBUG] Writing out config to \/backup\/xtrabackup\/20150601_012002\/backup.conf\n2015-06-01 08:45:58,087 [INFO] Backup completed in 7 hours, 25 minutes, 56.06 seconds\n2015-06-01 08:46:00,480 [INFO] Purged xtrabackup\/20150530_012001\n2015-06-01 08:46:00,481 [INFO] 1 backups purged\n2015-06-01 08:46:00,485 [INFO] Released lock \/etc\/holland\/backupsets\/xtrabackup.conf\n2015-06-01 08:46:00,485 [INFO] --- Ending backup run ---\n<\/pre>\n\n\n\n
    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.<\/p>\n\n\n\n
    Notes on configuration items:<\/h2>\n\n\n\n
    Most common backup configuration options are included in the conf files, I’d still cover few of the options here.<\/p>\n\n\n\n
    backupsets\/xtrabackup.conf<\/strong><\/p>\n\n\n\n
      \n
    •  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.<\/li>\n\n\n\n
    • 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!)<\/li>\n\n\n\n
    • 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!<\/li>\n<\/ul>\n\n\n\n
      provider\/xtrabackup.conf<\/strong><\/p>\n\n\n\n
        \n
      • pre-command – Executed before the xtrabackup command. You might want to have, say mysqldump of data-definitions!<\/li>\n\n\n\n
      • slave-info – This enables the \u2013slave-info innobackupex option.<\/li>\n\n\n\n
      • method – gzip is the default compression method for xtrabackup. We may choose pigz or other.<\/li>\n<\/ul>\n\n\n\n
        Hope this helps. Do comment if you face any issues setting things up or need clarity.<\/p>\n\n\n\n
        Happy Backup, MySQLer!<\/p>\n\n\n\n
        PS: I’d thank my friend Andy Moore for his tutorial for Holland backup ages ago. <\/p>\n\n\n\n
        No longer maintained: http:\/\/blog.mysqlboy.com\/2013\/05\/holland-backup-manager.html<\/p>\n\n\n\n
        Reference: http:\/\/docs.hollandbackup.org<\/p>\n","protected":false},"excerpt":{"rendered":"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…\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[8,377],"tags":[484,581,580,475,579,369],"class_list":["post-2206","post","type-post","status-publish","format-standard","category-mysql","category-mysql-articles","tag-backup-configuration","tag-backups","tag-holland-backup-framework","tag-mysql-backup","tag-setup-mysql-backup","tag-xtrabackup"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t