mysqldump Provider Configuration [mysqldump]

Backs up a MySQL database using the mysqldump tool.

[mysqldump]

mysql-binpath = /path/to/mysql/bin

Defines the location of the MySQL binary utilities. If not provided, Holland will use whatever is in the path.

lock-method = flush-lock | lock-tables | single-transaction | auto-detect | none

Defines which lock method to use. By default, auto-detect will be used.

  • flush-lock

    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

    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

    Forces the use of --single-transaction which enabled semi-transparent backups of transactional tables. Forcing this can cause inconsistencies with non-transactional tables, however. While non-transactional tables will still lock, they will only lock when they are actually being backed up. Use this setting with extreme caution when backing non-transactional tables.

  • auto-detect

    Let Holland decide which option to use by checking to see if a database or backup-set only contains transactional tables. If so, --single-transaction will be used. Otherwise, --lock-tables will be used.

  • none

    Does absolutely no explicit locking when backing up the databases or backup-set. This should only be used when backing up a slave and only after the slave has been turned off (ie, this can be used with the stop-slave option).

exclude-invalid-views = yes | no (default: no)

Whether to automate exclusion of invalid views that would otherwise cause mysqldump to fail. This adds additional overhead so this option is not enabled by default.

When enabled, this option will scan the INFORMATION_SCHEMA.VIEWS table and execute SHOW FIELDS against each view. If a view is detects as invalid, an ignore-table option will be added to exclude the table. Additionally, the plugin will attempt to save the view definion to ‘invalid_views.sql’ in the backupset’s backup directory.

New in version 1.0.8.

dump-routines = yes | no (default: yes)

Whether or not to backup routines in the backup set directly. Routines are stored in the ‘mysql’ database, but it can sometimes be convenient to include them in a backup-set directly.

Changed in version 1.0.8: This option now enabled by default.

dump-events = yes | no

Whether or not to dump events explicitly. Like routines, events are stored in the ‘mysql’ database. Nonetheless, it can sometimes be convenient to include them in the backup-set directly.

Note: This feature requires MySQL 5.1 or later. The mysqldump plugin will automatically disable events if the version of mysqldump is too old.

Changed in version 1.0.8: This option is now enabled by default

stop-slave = yes | no

Stops the SQL_THREAD during the backup. This means that writes from the master will continue to spool but will not be replayed. This helps avoid lock wait timeouts among things while still allowing data to be spooled from the master.

Note that previous versions of Holland prior to 1.0.6 simply ran a STOP SLAVE instead, which suspends both replication threads.

bin-log-position = yes | no

Record the binary log name and position at the time of the backup. The information provied by this option is collected just before locking the database.

Note that if both ‘stop-slave’ and ‘bin-log-position’ are enabled, Holland will grab the master binary log name and position at the time of the backup which can be useful in using the backup to create slaves or for point in time recovery using the master’s binary log. This information is found within the ‘backup.conf’ file located in the backup-set destination directory (/var/spool/holland/<backup-set>/<backup> by default). For example:

[mysql:replication]
slave_master_log_pos = 4512
slave_master_log_file = 260792-mmm-agent1-bin-log.000001

flush-logs = yes | 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.

file-per-database = yes | no

Whether or not to split up each database into its own file. Note that it can be more consistent an efficient to backup all databases into one file, however this means that restore a single database can be difficult if multiple databases are defined in the backup set.

arg-per-database = JSON object

If file-per-database is enable this argument is ued to specify mysqldump arguments per database. It takes a JSON object with the database names as keys. Example: {“employee1”: “–no-data”} Adds the ‘–no-data’ argument to the mysqldump command when backing up the ‘employee1’ database

New in version 1.0.9.

additional-options = <mysqldump argument>[, <mysqldump argument>]

Can optionally specify additional options directly to mysqldump if there is no native Holland option available. This option accepts a comma delimited list of arguments to pass on the commandline.

extra-defaults = yes | no (default: no)

This option controls whether mysqldump will only read options as set by holland or if additional options from global config files are read. By default, the plugin only uses optons as set in the backupset config and includes authentication credentials only from the [client] section in ~/.my.cnf.

estimate-method = plugin | const:<size> (default: plugin)

This option will skip some of the heavyweight queries necessary to calculate the size of tables to be backed up. If a constant size is specified, then only table names are evaluated and only if table filtering is being used. Valid units for size are: K, M, G, T, P, E (case-insensitive). Additionally, engines will be looked up via SHOW CREATE TABLE if lock-method = auto-detect, in order for the plugin to determine if tables are using a transactional storage engine. With ‘plugin’, the default behavior of reading both size information and table names from the information schema is used, which may be slow particularly for a large number of tables.

Database and Table filtering

[compression]

Specify various compression settings, such as compression utility, compression level, etc.

method = gzip| gzip-rsyncable | pigz | bzip2 | pbzip2 | lzop | lzma | gpg | zstd

Define which compression method to use. Note that some methods may not be available by default on every system and may need to be compiled or installed.

For gpg compression, a key should already exist(gpg --gen-key) and default-recipient must be configured in ~/.gnupg/gpg.conf.

inline = yes | no

Whether or not to pipe the output of the backup command into the compression utility. Enabling this is recommended since it usually only marginally impacts performance, particularly when using a lower compression level.

level = 0-9

Specify the compression ratio. The lower the number, the lower the compression ratio, but the faster the backup will take. Generally, setting the lever to 1 or 2 results in favorable compression of textual data and is noticeably faster than the higher levels. Setting the level to 0 effectively disables compression.

bin-path = <full path to utility>

This only needs to be defined if the compression utility is not in the usual places or not in the system path.

options = <string>

Add commandline options to the configuration compression command.

options = “-Q4”

split = yes | no

Defautls to no. If set the backup will be piped through the split command. This may be useful for user’s with large databases, as some backup systems perform better with many smaller files instead of 1 large one. This defaults to 1GB file size, so this option isn’t helpful if your dumps are smaller than that.

For python2.6, this option will be disabled if the subprocess32 module isn’t avaiable.

New in version 1.1.13.

MySQL connection info [mysql:client]

These are optional and, if left undefined, Holland will try to login using the standard .my.cnf conventions.

user = <user>

The user to connect to MySQL as.

password = <password>

The password for the MySQL user

socket = <socket>

The socket file to connect to MySQL with.

host = <host>

This would be used for connecting to MySQL remotely.

port = <port>

Used if MySQL is running on a port other than 3306.