Holland — backup of MySQL/PostgreSQL without headache
one of these days I'm tired to use samopisnye scripts to create database backups. No matter, they were developed by me or found somewhere on the Internet. Based on the principle that time is the most expensive resource of the system administrator (engineer, architect), a solution was found that meets the following requirements: easy installation, quick setup, and, as the sum of the previous requirements, fast operation.According to official website, Holland — freymvork open source for creation of backup copies, designed by Rackspace and written in Python. The project aims to create backups with great flexibility, logical structure and ease of use. At the moment Holland works with MySQL and PostgreSQL, but in the future will include a greater variety databases, and even applications not related to databases. Due to the modular structure of Holland can be used to create backup copies of anything as you like.
Let us imagine that our scenario is a daily backup of one MySQL database (mysqldump utility) with the rotation of seven copies.
And to begin with the subject you need to download and install on the server. In the batch distributions, this should not cause difficulties. Let's assume that we have the CentOS.
the
Download
Exists in Holland repositories:
the
-
the
- Debian 6/7 the
- Centos 5/6 the
- RHEL 4/5/6 the
- Ubuntu 10.04/11.10/12.04/12.10/13.04
Also present at github.com
I assume this choice should have no problems installing even on "old" servers. The package Manager of the distribution, which will have to work, will help in this case. Following the task input to the console, optionally adding increasing privileges:
the
yum install -y holland holland-mysqldump
The next step, logically, will be setup.
the
Configure
The structure of the /etc/holland simple and straightforward. In the directory configuration in addition to configuration file with basic parameters there are two directories:
providers contains templates with settings for work with utilities of type mysqldump or xtrabackup
backupsets provides specific backup plans with type parameters: the number of copies, the method and degree of compression, etc.
I found it necessary to provide examples of configurations intact for quick reference by the reader.
/etc/holland.conf
## config file Root holland
[holland]
## Holland Paths where 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 the 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
##
## None will completely disable locking. This is generally only viable
## on a MySQL slave and only after traffic has been diverted, or slave
## services is suspended.
lock-method = auto-detect
## comma-delimited glob patterns for matching databases
## only the databases matching these patterns will be backed up
## default: include everything
#databases = "*"
## comma-delimited glob patterns to exclude particular
## databases
#exclude-databases =
## include only the specified tables
#tables = "*"
## exclude specific tables
#exclude-tables = ""
## Whether to dump the 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 the filtering database 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, the 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
## Example Holland Backup mysqldump-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 the 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 the '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 is suspended.
lock-method = auto-detect
## comma-delimited glob patterns for matching databases
## only the databases matching these patterns will be backed up
## default: include everything
databases = "*"
## comma-delimited glob patterns to exclude particular
## databases
#exclude-databases =
## include only the specified tables
tables = "*"
## exclude specific tables
#exclude-tables = ""
## Whether to dump the 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 the filtering database 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
## e.g.: --flush-privileges --reset-master
additional-options = ""
## Compression Settings
[compression]
## compress method: gzip gzip-rsyncable, bzip2, pbzip2, lzop, xz or
## 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
And despite the diversity of variables, which abound in the configs, for the implementation of the ideas we need only:
-
the
- Enter the scenario name holland.conf
thebackupsets = mysqldump
the - Copy script in /usr/share/doc/holland-*/examples/mysqldump.conf in /etc/holland/backupsets
thecp /usr/share/doc/holland-*/examples/mysqldump.conf /etc/holland/backupsets/
the - Enter in the script mysqldump.conf the number of copies needed database, and access with sufficient rights
thebackups to keep = 7 databases = "somedb" user = hollandbackup password = "hollandpw" socket = /tmp/mysqld.sock
the - Add to the scheduler (e.g., cron) a record of the daily run the command
holland backup
Miscellaneous
Configuration for PostgreSQL will be different just other installed plugin (holland-postgresql) and others copied the example. However, the example files I was interested in even just his name, take a look:
the
-
the
- maatkit.conf the
- mysqldump.conf the
- mysqldump-lvm.conf the
- mysqlhotcopy.conf the
- mysql-lvm.conf the
- random.conf the
- sqlite.conf the
- xtrabackup.conf
Don't forget to log on to the server in a few days and check whether the backup plan, its success.
I hope these few steps will help you save time and energy for such an unpopular class as backup.
Комментарии
Отправить комментарий