Holland — бекапы MySQL/PostgreSQL без головной боли

в 8:47, , рубрики: mysql, postgresql, базы данных, резервное копирование, метки: ,

Holland LogoВ один из дней мне надоело использовать самописные скрипты для создания резервных копий баз данных. Не важно, разработаны они были мной или найденны где-то на просторах интернета. Исходя из принципа, что время является самым дорогим ресурсом системного администратора (инженера, архитектора), было найдено решение, отвечающее следующим требованиям: простая установка, быстрая настройка и, как сумма предыдущих требований, быстрое введение в эксплуатацию.

Согласно официальному сайту, Holland — фреймворк с открытым исходным кодом для создания резервных копий, разработанный Rackspace и написанный на языке Python. Проект преследует цель создания бекапов с большой гибкостью настройки, логичной структурой и простотой использования. В данный момент Holland работает с MySQL и PostgreSQL, однако в будущем будет включать большее разнообразие баз данных, и даже приложения, никак не относящимся к базам данных. Благодаря модульной структуре Holland может быть использован для создания резервных копий чего угодно, как угодно.

Представим себе, что наш сценарий предусматривает ежедневный бекапа одной базы MySQL (утилитой mysqldump) с ротацией семи копий.
И для начала сабж надо скачать и установить на сервер. В пакетных дистрибутивах это не должно вызвать трудностей. Также представим, что у нас CentOS.

Скачать

Holland существует в репозиториях у:

  • Debian 6/7
  • Centos 5/6
  • RHEL 4/5/6
  • Ubuntu 10.04/11.10/12.04/12.10/13.04

Также присутствует на github.com

Предполагаю, при таком выборе не должно возникнуть проблем с установкой даже на «старые“ сервера. Пакетный менеджер дистрибутива, с которым придется работать, поможет в этом деле. Следуя поставленной задаче, вводим в консоль, при необходимости добавив повысив себе привилегии:

yum install -y holland holland-mysqldump

Следующим этапом, что логично, будет настройка.

Настроить

Структура /etc/holland проста и понятна. В директории с конфигурацией программы помимо конфигурационного файла с основными параметрами работы есть две директории:
providers содержит шаблоны с настройками для работы с утилитами типа mysqldump или xtrabackup
backupsets содержит конкретные планы резервного копирования с параметрами типа: кол-во копий, метод и степень сжатия и т.п.

Я счел нужным представить примеры конфигураций в нетронутом виде для быстрого ознакомления читателем.

/etc/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 <config_dir>/backupsets/<name>.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

providers/mysqldump.conf

## Global settings for the mysqldump provider - Requires holland-mysqldump
##
## Unless overwritten, all backup-sets implementing this provider will use
## the following settings.

[mysqldump]

## Override the path where we can find mysql command line utilities
#mysql-binpath       = /usr/bin/mysqldump

## One of: flush-lock, lock-tables, single-transaction, auto-detect, none
##
## flush-lock will place a global lock on all tables involved in the backup
## regardless of whether or not they are in the backup-set. If 
## file-per-database is enabled, then flush-lock will lock all tables 
## for every database being backed up. In other words, this option may not
## make much sense when using file-per-database.
##
## lock-tables will lock all tables involved in the backup. If
## file-per-database is enabled, then lock-tables will only lock all the
## tables associated with that database.
##
## single-transaction will force running a backup within a transaction.
## This allows backing up of transactional tables without imposing a lock
## howerver will NOT properly backup non-transactional tables.
##
## Auto-detect will choose single-transaction unless Holland finds
## non-transactional tables in the backup-set.
##
## None will completely disable locking. This is generally only viable
## on a MySQL slave and only after traffic has been diverted, or slave
## services suspended.
lock-method         = auto-detect

## comma-delimited glob patterns for matching databases
## only databases matching these patterns will be backed up
## default: include everything
#databases           = "*"

## comma-delimited glob patterns to exclude particular 
## databases
#exclude-databases   = 

## only include the specified tables
#tables              = "*"

## exclude specific tables
#exclude-tables      = ""

## Whether to dump routines explicitly
## (routines are implicitly included in the mysql database)
dump-routines       = no

## Whether to dump events explicitly.
## Note that this feature requires MySQL 5.1 or later.
dump-events			= no

## Whether to stop the slave before commencing with the backup
stop-slave          = no

## Whether to record the binary log name and position at the time of the
## backup.
bin-log-position    = no

## Whether or not to run FLUSH LOGS in MySQL with the backup. When FLUSH
## LOGS is actually executed depends on which if database filtering is being
## used and whether or not file-per-database is enabled. Generally speaking,
## it does not make sense to use flush-logs with file-per-database since the 
## binary logs will not be consistent with the backup.
flush-logs			= no

## Whether to run a separate mysqldump for each database. Note that while
## this may initially sound like a good idea, it is far simpler to backup
## all databases in one file, although that makes the restore process
## more difficult when only certain data needs to be restored.
file-per-database   = no

## any additional options to the 'mysqldump' command-line utility
## these should show up exactly as they are on the command line
## e.g.: --flush-privileges --reset-master
additional-options  = ""

## Compression Settings
[compression]

## compress method: gzip, gzip-rsyncable, bzip2, pbzip2, or lzop
## Which compression method to use, which can be either gzip, bzip2, or lzop.
## Note that lzop is not often installed by default on many Linux 
## distributions and may need to be installed separately.
method              = gzip

## Whether to compress data as it is provided from 'mysqldump', or to
## compress after a dump has finished. In general, it is often better to use
## inline compression. The overhead, particularly when using a lower 
## compression level, is often minial since the entire process is often I/O
## bound (as opposed to being CPU bound).
inline              = yes

## What compression level to use. Lower numbers mean faster compression, 
## though also generally a worse compression ratio. Generally, levels 1-3
## are considered fairly fast and still offer good compression for textual
## data. Levels above 7 can often cause a larger impact on the system due to
## needing much more CPU resources. Setting the level to 0 effectively 
## disables compresion.
level               = 1

## If the path to the compression program is in a non-standard location,
## or not in the system-path, you can provide it here.
##
## FIXME: Currently not implemented, compression binary is looked up by
## which.
##
#bin-path           = /usr/bin/gzip

## MySQL connection settings. Note that Holland will try ot read from
## the provided files defined in the 'defaults-extra-file', although 
## explicitly defining the connection inforamtion here will take precedence.
[mysql:client]
defaults-extra-file  = /root/.my.cnf,~/.my.cnf,
#user                = hollandbackup
#password            = "hollandpw"
#socket              = /tmp/mysqld.sock
#host                = localhost
#port                = 3306

backupsets/mysqldump.conf

## Holland mysqldump Example Backup-Set
##
## This implements a vanilla backup-set using the mysqldump provider which,
## in turn, uses the 'mysqldump' utility.
##
## Many of these options have global defaults which can be found in the
## configuration file for the provider (which can be found, by default
## in /etc/holland/providers).

[holland:backup]
plugin = mysqldump
backups-to-keep = 1
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]

## Override the path where we can find mysql command line utilities
#mysql-binpath       = /usr/bin/mysqldump

## One of: flush-lock, lock-tables, single-transaction, auto-detect, none
##
## flush-lock will run a FLUSH TABLES WITH READ LOCK prior to the backup
##
## lock-tables will instruct 'mysqldump' to lock all tables involved
## in the backup.
##
## single-transaction will force running a backup within a transaction.
## This allows backing up of transactional tables without imposing a lock
## howerver will NOT properly backup non-transacitonal tables.
##
## Auto-detect will choose single-transaction unless Holland finds
## non-transactional tables in the backup-set.
##
## None will completely disable locking. This is generally only viable
## on a MySQL slave and only after traffic has been diverted, or slave
## services suspended.
lock-method         = auto-detect

## comma-delimited glob patterns for matching databases
## only databases matching these patterns will be backed up
## default: include everything
databases           = "*"

## comma-delimited glob patterns to exclude particular 
## databases
#exclude-databases   = 

## only include the specified tables
tables              = "*"

## exclude specific tables
#exclude-tables      = ""

## Whether to dump routines explicitly
## (routines are implicitly included in the mysql database)
dump-routines       = no

## Whether to dump events explicitly.
## Note that this feature requires MySQL 5.1 or later.
dump-events			= no

## Whether to stop the slave before commencing with the backup
stop-slave          = no

## Whether to record the binary log name and position at the time of the
## backup.
bin-log-position    = no

## Whether or not to run FLUSH LOGS in MySQL with the backup. When FLUSH
## LOGS is actually executed depends on which if database filtering is being
## used and whether or not file-per-database is enabled. Generally speaking,
## it does not make sense to use flush-logs with file-per-database since the 
## binary logs will not be consistent with the backup.
flush-logs			= no

## Whether to run a separate mysqldump for each database. Note that while
## this may initially sound like a good idea, it is far simpler to backup
## all databases in one file, although that makes the restore process
## more difficult when only certain data needs to be restored.
file-per-database   = no

## any additional options to the 'mysqldump' command-line utility
## these should show up exactly as they are on the command line
## e.g.: --flush-privileges --reset-master
additional-options  = ""

## Compression Settings
[compression]

## compress method: gzip, gzip-rsyncable, bzip2, pbzip2, lzop, or xz
## Which compression method to use, which can be either gzip, bzip2, or lzop.
## Note that pbzip2 and lzop are not often installed by default on many Linux 
## distributions and may need to be installed separately.
method              = gzip

## Whether to compress data as it is provided from 'mysqldump', or to
## compress after a dump has finished. In general, it is often better to use
## inline compression. The overhead, particularly when using a lower 
## compression level, is often minial since the entire process is often I/O
## bound (as opposed to being CPU bound).
inline              = yes

## What compression level to use. Lower numbers mean faster compression, 
## though also generally a worse compression ratio. Generally, levels 1-3
## are considered fairly fast and still offer good compression for textual
## data. Levels above 7 can often cause a larger impact on the system due to
## needing much more CPU resources. Setting the level to 0 effectively 
## disables compresion.
level               = 1

## If the path to the compression program is in a non-standard location,
## or not in the system-path, you can provide it here.
#bin-path           = /usr/bin/gzip

## MySQL connection settings. Note that these can be inherited from the
## provider itself allowing for global defaults. Providing connection 
## information for a backup-set can often be helpful when, for instance
## a backup-set is backing up a remote MySQL server.
#[mysql:client]
#user                = hollandbackup
#password            = "hollandpw"
#socket              = /tmp/mysqld.sock
#host                = localhost
#port                = 3306

И, не смотря на многообразие переменных, которыми изобилуют конфиги, для осуществления задумки нам достаточно лишь:

  1. Указать имя сценария в holland.conf
    backupsets = mysqldump
    

  2. Скопировать сценарий из /usr/share/doc/holland-*/examples/mysqldump.conf в /etc/holland/backupsets
    cp /usr/share/doc/holland-*/examples/mysqldump.conf /etc/holland/backupsets/
    

  3. Указать в сценарии mysqldump.conf количество копий, нужные базы, и доступ с достаточными правами
    backups-to-keep = 7
    databases = «somedb»
    user = hollandbackup
    password = «hollandpw»
    socket = /tmp/mysqld.sock
    

  4. Добавить в планировщик (например, cron) запись о ежедневном выполнении команды
    holland backup
    


Прочее

Конфигурация для PostgreSQL будет отличаться лишь другим установленным плагином (holland-postgresql) и другим скопированным примером. Впрочем, файлы примеров меня заинтересовали даже просто своим названием, взгляните:

  • maatkit.conf
  • mysqldump.conf
  • mysqldump-lvm.conf
  • mysqlhotcopy.conf
  • mysql-lvm.conf
  • random.conf
  • sqlite.conf
  • xtrabackup.conf

Не забудьте зайти на сервер через несколько дней и проверить, выполняется ли план резервного копирования, его успешность.

Надеюсь, эти несколько шагов помогут вам сэкономить время и силы на столь непопулярном занятии, как резервное копирование.

Автор: linjan

Источник


* - обязательные к заполнению поля


https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js