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 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 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 Installing dependencies:<\/p>\n\n\n\n – OR –<\/p>\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 Holland framework is now installed. You can check the version as:<\/p>\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 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 Backup user:<\/strong> Sample configuration for holland:<\/p>\n\n\n\n <\/p>\n\n\n\n Sample configuration for backup-set:<\/p>\n\n\n\n Sample configuration for provider:<\/p>\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 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 (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 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 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 provider\/xtrabackup.conf<\/strong><\/p>\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":{"0":"post-2206","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-mysql","7":"category-mysql-articles","8":"tag-backup-configuration","9":"tag-backups","10":"tag-holland-backup-framework","11":"tag-mysql-backup","12":"tag-setup-mysql-backup","13":"tag-xtrabackup"},"aioseo_notices":[],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/2206","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/comments?post=2206"}],"version-history":[{"count":9,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/2206\/revisions"}],"predecessor-version":[{"id":3441,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/posts\/2206\/revisions\/3441"}],"wp:attachment":[{"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/media?parent=2206"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/categories?post=2206"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kedar.nitty-witty.com\/blog\/wp-json\/wp\/v2\/tags?post=2206"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}Holland Backup Framework:<\/h2>\n\n\n\n
Xtrabackup:<\/h2>\n\n\n\n
Setting up Holland:<\/h2>\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
yum install MySQL-python\nyum install python-setuptools\nyum install mysql-devel.x86_64\n<\/pre>\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 --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
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
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
We’ll create the specific MySQL database user for holland backup as follows:<\/p>\n\n\n\nmysql> 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
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
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
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
holland -d backup --dry-run xtrabackup<\/pre>\n\n\n\n
holland -d backup xtrabackup\n<\/pre>\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
Notes on configuration items:<\/h2>\n\n\n\n
\n
\n