Table of Contents
Holland is an open source backup framework written in Python. It can be used to back up several different database types using a number of methods based on the plugins you choose to install. This tutorial is focused on getting Holland installed and using it to back up MariaDB or MySQL databases using the common
mysqldump method. Additional details about Holland can be located in the Holland Documentation.
First we need to enable access to a repository that contains the Holland packages. These packages are available in the EPEL repository. If you already have the EPEL repository enabled on your system, I would suggest using it and moving on to the next step: Install Packages.
We can see the enabled repositories by running
sudo yum repolist or get a list of all repositories with
sudo yum repolist all.
If you do NOT have the EPEL repository and you would like to install it, run:
sudo rpm -ivh http://dl.fedoraproject.org/pub/epel/7/x86_64/e/epel-release-7-5.noarch.rpm
If you do NOT have the EPEL repository configured and do NOT want to use it, we have the option to add a repository that contains just the Holland packages.
We can accomplish this by adding a new file to
/etc/yum.repos.d/. There are a couple options at this point. If
wget is installed we can use it:
sudo wget -O /etc/yum.repos.d/holland-backup.repo http://download.opensuse.org/repositories/home:/holland-backup/CentOS_7/home:holland-backup.repo
-O option, followed by a filename, instructs
wget to save the output into the supplied filename, rather than creating a file based on the URL. This way we can have a repository named "holland-backup.repo" instead of "home:holland-backup.repo".
Another option would be to use a text editor (
nano, etc.) to create
/etc/yum.repos.d/holland-backup.repo and place the following contents into it:
[home_holland-backup] name=holland-backup's Home Project (CentOS_7) type=rpm-md baseurl=http://download.opensuse.org/repositories/home:/holland-backup/CentOS_7/ gpgcheck=1 gpgkey=http://download.opensuse.org/repositories/home:/holland-backup/CentOS_7/repodata/repomd.xml.key enabled=1
Now we can take a quick at the packages available in the
sudo yum --disablerepo "*" --enablerepo "home_holland-backup" list available Loaded plugins: fastestmirror Loading mirror speeds from cached hostfile Available Packages holland.noarch 1.0.10-2.1 home_holland-backup holland-common.noarch 1.0.10-2.1 home_holland-backup holland-example.noarch 1.0.4-2.1 home_holland-backup holland-mysqldump.noarch 1.0.10-2.1 home_holland-backup holland-mysqllvm.noarch 1.0.10-2.1 home_holland-backup holland-pgdump.noarch 1.0.10-2.1 home_holland-backup holland-random.noarch 1.0.10-2.1 home_holland-backup holland-sqlite.noarch 1.0.10-2.1 home_holland-backup holland-xtrabackup.noarch 1.0.10-2.1 home_holland-backup
For this tutorial we will use the
holland-mysqldump.noarch packages. Lets install them using
sudo yum install holland holland-common holland-mysqldump
yum will resolve dependencies and present us a list of packages to install. The output you see may vary slightly from this, depending on what is already installed, which repository you configured, and what needs to be added to satisfy dependencies.
Loaded plugins: fastestmirror Loading mirror speeds from cached hostfile * base: mirror.lax.hugeserver.com * extras: mirror.lax.hugeserver.com * updates: repo1.ash.innoscale.net Resolving Dependencies --> Running transaction check ---> Package holland.noarch 0:1.0.10-2.1 will be installed ---> Package holland-common.noarch 0:1.0.10-2.1 will be installed ---> Package holland-mysqldump.noarch 0:1.0.10-2.1 will be installed --> Finished Dependency Resolution Dependencies Resolved ====================================================================================================== Package Arch Version Repository Size ====================================================================================================== Installing: holland noarch 1.0.10-2.1 home_holland-backup 249 k holland-common noarch 1.0.10-2.1 home_holland-backup 78 k holland-mysqldump noarch 1.0.10-2.1 home_holland-backup 105 k Transaction Summary ====================================================================================================== Install 3 Packages Total download size: 432 k Installed size: 1.7 M Is this ok [y/d/N]: y Downloading packages: warning: /var/cache/yum/x86_64/7/home_holland-backup/packages/holland-common-1.0.10-2.1.noarch.rpm: Header V3 DSA/SHA1 Signature, key ID 984d0514: NOKEY Public key for holland-common-1.0.10-2.1.noarch.rpm is not installed (1/3): holland-common-1.0.10-2.1.noarch.rpm | 78 kB 00:00:01 (2/3): holland-1.0.10-2.1.noarch.rpm | 249 kB 00:00:01 (3/3): holland-mysqldump-1.0.10-2.1.noarch.rpm | 105 kB 00:00:00 ------------------------------------------------------------------------------------------------------ Total 194 kB/s | 432 kB 00:00:02 Retrieving key from http://download.opensuse.org/repositories/home:/holland-backup/CentOS_7/repodata/repomd.xml.key Importing GPG key 0x984D0514: Userid : "home:holland-backup OBS Project <home:email@example.com>" Fingerprint: 5b31 4003 95d8 8ef2 fca5 5ecf 40a4 02af 984d 0514 From : http://download.opensuse.org/repositories/home:/holland-backup/CentOS_7/repodata/repomd.xml.key Is this ok [y/N]: y
Review the list of packages that will be installed. If it looks reasonable, then press y to continue the installation.
Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : holland-1.0.10-2.1.noarch 1/3 Installing : holland-common-1.0.10-2.1.noarch 2/3 Installing : holland-mysqldump-1.0.10-2.1.noarch 3/3 Verifying : holland-common-1.0.10-2.1.noarch 1/3 Verifying : holland-1.0.10-2.1.noarch 2/3 Verifying : holland-mysqldump-1.0.10-2.1.noarch 3/3 Installed: holland.noarch 0:1.0.10-2.1 holland-common.noarch 0:1.0.10-2.1 holland-mysqldump.noarch 0:1.0.10-2.1 Complete!
Now that we have Holland installed, we need to configure it. The configuration files are stored in
holland.conf being the primary configuration file. Two subdirectories,
providers, contain additional configuration information. Since we installed
mysqldump.conf file will be present in
/etc/holland/providers/. We should not have to modify it for this tutorial.
/etc/holland.conf there are a few significant items to review. Look for the
backup_directory value and confirm this is where you would like to have your backup files stored. While we will stick with the default location of
/var/spool/holland, you may want to adjust this based on your particular server configuration.
## Top level directory where backups are held backup_directory = /var/spool/holland
Backing up to a separate storage volume could be advantageous. Please verify that you have sufficient storage space for your backup files available before proceeding.
The next section lets us know which backup sets are enabled. In this case, one called
default is enabled.
## List of enabled backup sets. Can be comma separated. ## Read from <config_dir>/backupsets/<name>.conf # backupsets = example, traditional, parallel_backups, non_transactional backupsets = default
We can also see that logs related to what Holland is doing will be stored in
[logging] ## where to write the log filename = /var/log/holland/holland.log
holland.conf has an enabled backup set called
default, we need to create
/etc/holland/backupsets/default.conf in order to run our first backup.
This creates an example
default.conf taken from the Holland-Backup GitHub Repository and adjusted slightly.
cat << EOF > /etc/holland/backupsets/default.conf ## Default Backup-Set ## ## Backs up all MySQL databases in a one-file-per-database fashion using ## lightweight in-line compression and engine auto-detection. This backup-set ## is designed to provide reliable backups "out of the box", however it is ## generally advisable to create additional custom backup-sets to suit ## one's specific needs. [holland:backup] plugin = mysqldump backups-to-keep = 7 auto-purge-failures = yes purge-policy = after-backup estimated-size-factor = 1.0 # This section defines the configuration options specific to the backup # plugin. In other words, the name of this section should match the name # of the plugin defined above. [mysqldump] file-per-database = yes #lock-method = auto-detect #databases = "*" #exclude-databases = "foo", "bar" #tables = "*" #exclude-tables = "foo.bar" #stop-slave = no #bin-log-position = no # The following section is for compression. The default, unless the # mysqldump provider has been modified, is to use inline fast gzip # compression (which is identical to the commented section below). [compression] method = gzip options = "--rsyncable" [mysql:client] defaults-extra-file = /root/.my.cnf EOF
For the tutorial these settings will work fine. You may wish to tweak various values in this file based on the type of data in your database. For example, the value for
estimated-size-factor can be adjusted based on the output logged from a successful backup run. We will take a look at this further in a bit.
You'll notice that the newly created
default.conf file is setting
/root/.my.cnf. We need to create that file and put our database credentials into it.
cat << EOF > /root/.my.cnf [client] user=root password=your_root_mysql_password_here EOF
With those changes in place, we should be able to proceed and generate our first backup.
mysqldump backups will lock tables during the back up process, make sure you are NOT doing this testing during a busy time. Hopefully you are testing this out in a non-production environment. If you are backing up a production database, any website applications that rely on the database could be affected while the backup is running.
/usr/sbin/holland -q bk
We will need to wait for that to finish and then we can check out the results.
We can examine the log stored in
/var/log/holland/holland.log to see what happened during our backup run.
sudo more /var/log/holland/holland.log 2016-04-27 19:23:42,948 [INFO] Holland 1.0.10 started with pid 18754 2016-04-27 19:23:42,961 [INFO] --- Starting backup run --- 2016-04-27 19:23:42,965 [INFO] Creating backup path /var/spool/holland/default/20160427_192342 2016-04-27 19:23:43,003 [INFO] Estimating size of mysqldump backup 2016-04-27 19:23:43,062 [INFO] Estimated Backup Size: 205.04MB ... 2016-04-27 19:23:43,080 [INFO] Executing: /usr/bin/mysqldump --defaults-file=/var/spool/holland/default/20160427_192342/my.cnf --flush-privileges --max-allowed-packet=128M --single-transaction classicmodels 2016-04-27 19:23:43,141 [INFO] Executing: /usr/bin/mysqldump --defaults-file=/var/spool/holland/default/20160427_192342/my.cnf --flush-privileges --max-allowed-packet=128M --single-transaction employees ... 2016-04-27 19:23:53,457 [INFO] Final on-disk backup size 47.46MB 2016-04-27 19:23:53,457 [INFO] 23.15% of estimated size 205.04MB 2016-04-27 19:23:53,458 [INFO] Backup completed in 10.46 seconds 2016-04-27 19:23:53,463 [INFO] No backups purged 2016-04-27 19:23:53,468 [INFO] Released lock /etc/holland/backupsets/default.conf 2016-04-27 19:23:53,468 [INFO] --- Ending backup run ---
That partial log output looks great. The ~48MB backup job completed successfully in just under 11 seconds. Remember the default.conf parameter of
estimated-size-factor = 1.0? Based on the info here, we could change that from
1.0 to a more accurate value of
.25. This may not matter much when you have a small, very-compressible dataset and a lot of backup space available. As you have more data to backup and less space to back it up to, the ability to accurately estimate the backup size becomes more useful.
If we change the
estimated-size-factor value and re-run the backup job, we can compare the log output and see the effect.
2016-04-27 19:36:44,006 [INFO] Holland 1.0.10 started with pid 18810 2016-04-27 19:36:44,019 [INFO] --- Starting backup run --- 2016-04-27 19:36:44,024 [INFO] Creating backup path /var/spool/holland/default/20160427_193644 2016-04-27 19:36:44,080 [INFO] Estimating size of mysqldump backup 2016-04-27 19:36:44,132 [INFO] Estimated Backup Size: 205.04MB 2016-04-27 19:36:44,132 [INFO] Adjusting estimated size by 0.25 to 51.26MB
Holland will use the "adjusted estimated size" value of "51.26MB" to determine if there is enough free space available to run the backup job. If it does not see at least that amount of space available, then the backup job will terminate immediately.
While having log data is reassuring, we can also take a look at our backup directory and verify it has the expected contents.
sudo ls -ltr /var/spool/holland/default total 12 drwxrwx---. 3 root root 4096 Apr 26 20:51 20160426_205138 drwxrwx---. 3 root root 4096 Apr 27 19:23 20160427_192342 drwxrwx---. 3 root root 4096 Apr 27 19:36 20160427_193644 lrwxrwxrwx. 1 root root 42 Apr 27 19:36 oldest -> /var/spool/holland/default/20160426_205138 lrwxrwxrwx. 1 root root 42 Apr 27 19:36 newest -> /var/spool/holland/default/20160427_193644
Since the backup has run a couple of times now, we have three directories and some helpful symlinks in place.
Looking into the newest backup directory, we see the compressed
.sql files. There is one file for each database as configured in
/etc/holland/backupsets/default.conf with the option:
file-per-database = yes
sudo ls -ltr /var/spool/holland/default/newest/backup_data total 48616 -rw-rw----. 1 root root 188 Apr 27 19:36 MANIFEST.txt -rw-rw----. 1 root root 69105 Apr 27 19:36 classicmodels.sql.gz -rw-rw----. 1 root root 48570312 Apr 27 19:36 employees.sql.gz -rw-rw----. 1 root root 1028 Apr 27 19:36 menagerie.sql.gz -rw-rw----. 1 root root 172493 Apr 27 19:36 mysql.sql.gz -rw-rw----. 1 root root 846722 Apr 27 19:36 sakila.sql.gz -rw-rw----. 1 root root 467 Apr 27 19:36 test.sql.gz -rw-rw----. 1 root root 99358 Apr 27 19:36 world.sql.gz -rw-rw----. 1 root root 470 Apr 27 19:36 world_x.sql.gz
Currently, our backup set is configured to retain seven backups. If we run the backup job manually a few more times, we can see what happens when the time for the eighth backup arrives. Here is what things look like after seven backups have completed:
ls -ltr /var/spool/holland/default total 28 drwxrwx---. 3 root root 4096 Apr 26 20:51 20160426_205138 drwxrwx---. 3 root root 4096 Apr 27 19:23 20160427_192342 drwxrwx---. 3 root root 4096 Apr 27 19:36 20160427_193644 drwxrwx---. 3 root root 4096 Apr 27 19:48 20160427_194859 drwxrwx---. 3 root root 4096 Apr 27 19:49 20160427_194957 drwxrwx---. 3 root root 4096 Apr 27 19:50 20160427_195033 drwxrwx---. 3 root root 4096 Apr 27 19:52 20160427_195244 lrwxrwxrwx. 1 root root 42 Apr 27 19:52 oldest -> /var/spool/holland/default/20160426_205138 lrwxrwxrwx. 1 root root 42 Apr 27 19:52 newest -> /var/spool/holland/default/20160427_195244
After running the eighth backup, the oldest one (from 2016-04-26) is no longer present.
ls -ltr /var/spool/holland/default total 28 drwxrwx---. 3 root root 4096 Apr 27 19:23 20160427_192342 drwxrwx---. 3 root root 4096 Apr 27 19:36 20160427_193644 drwxrwx---. 3 root root 4096 Apr 27 19:48 20160427_194859 drwxrwx---. 3 root root 4096 Apr 27 19:49 20160427_194957 drwxrwx---. 3 root root 4096 Apr 27 19:50 20160427_195033 drwxrwx---. 3 root root 4096 Apr 27 19:52 20160427_195244 drwxrwx---. 3 root root 4096 Apr 27 19:54 20160427_195448 lrwxrwxrwx. 1 root root 42 Apr 27 19:54 oldest -> /var/spool/holland/default/20160427_192342 lrwxrwxrwx. 1 root root 42 Apr 27 19:54 newest -> /var/spool/holland/default/20160427_195448
Looking at the log output, we can see that the oldest backup was purged:
sudo more /var/log/holland/holland.log 2016-04-27 19:54:48,412 [INFO] Holland 1.0.10 started with pid 19019 2016-04-27 19:54:48,421 [INFO] --- Starting backup run --- 2016-04-27 19:54:48,424 [INFO] Creating backup path /var/spool/holland/default/20160427_195448 2016-04-27 19:54:48,451 [INFO] Estimating size of mysqldump backup 2016-04-27 19:54:48,490 [INFO] Estimated Backup Size: 205.04MB 2016-04-27 19:54:48,490 [INFO] Adjusting estimated size by 0.25 to 51.26MB 2016-04-27 19:54:48,490 [INFO] Starting backup[default/20160427_195448] via plugin mysqldump .... 2016-04-27 19:54:58,712 [INFO] Final on-disk backup size 47.46MB 2016-04-27 19:54:58,713 [INFO] 23.15% of estimated size 205.04MB 2016-04-27 19:54:58,714 [INFO] Backup completed in 10.26 seconds 2016-04-27 19:54:58,741 [INFO] Purged default/20160426_205138 2016-04-27 19:54:58,741 [INFO] 1 backups purged 2016-04-27 19:54:58,756 [INFO] Released lock /etc/holland/backupsets/default.conf 2016-04-27 19:54:58,756 [INFO] --- Ending backup run ---
The purge happened after the backup job completed successfully. This behavior is controlled by the configuration parameter
purge-policy = after-backup. There is the possibility that you would want to purge the oldest backup before making a new one, perhaps due to available disk space.
Holland provides a number of other options to control your backups. Two that I have found particularly helpful are
exclude-databases = "foo", "bar" and
exclude-tables = "foo.bar". You may have some databases or tables that contain data that is relatively static. You could design a backup strategy where these large static tables are only backed up weekly. They could be excluded from the default backup set that runs more frequently. This would reduce the time that the default backup set takes to run, as well as the space that the backup files consume.
We can use
cron to schedule Holland. A typical daily backup could be configured to run as
root by editing root's crontab. One way to do this is:
sudo crontab -e -u root
Then place a line like this into the file:
10 1 * * * /usr/sbin/holland -q bk
That would schedule
cron to run a Holland backup at 1:10 AM server time each day. Since we have Holland configured to retain seven backups, this configuration essentially provides us with a week of a daily database backups.
Holland provides a customizable method of generating backups of MariaDB or MySQL databases. We have covered a basic implementation of Holland's
mysqldump plugin. The end result is that daily database backups are being stored on the local server. Future articles may cover other Holland plugins and different backup strategies.
Restoring a database or table from a Holland backup is a manual process. We will discuss the manual restoration process in a future article.
If you have questions or comments on this tutorial, they can be brought up below, or in the ProfitBricks DevOps Community section of this site.