Tutorials

Configure Holland to Backup MariaDB on CentOS 7

Table of Contents

Introduction

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.

Install

Add Repository

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

Passing the -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 (vi, 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 home_holland-backup repository.

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

Install Packages

For this tutorial we will use the holland.noarch, holland-common.noarch, and holland-mysqldump.noarch packages. Lets install them using yum:

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:holland-backup@build.opensuse.org>"
 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!

Configure

Now that we have Holland installed, we need to configure it. The configuration files are stored in /etc/holland/, with holland.conf being the primary configuration file. Two subdirectories, backupsets and providers, contain additional configuration information. Since we installed holland-mysqldump, a mysqldump.conf file will be present in /etc/holland/providers/. We should not have to modify it for this tutorial.

Looking at /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 /var/log/holland/holland.log.

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

Since 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 defaults-extra-file to /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.

Test

Since 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.

Verify

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.

Schedule

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.

Summary

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.

 
  • Play the most exiting slotomania free online game, this game is famous in Asia need free coins slotomania game but many users has played this game in India slotomania free online so we have to play this game i sure like it.

Log In, Add a Comment