Features

All features in a nutshell with their corresponding configuration flags (click on the links to jump directly to the description):

Detailed explanation

Encryption

Encryption is optional and implemented by using openssl's hybrid encryption feature. It combines RSA private/public key with symmetric AES encryption in order to make use of the the security of public/private keys and the performance of symmetric encryption. The usage is similar to pgp: you will only need to store your public key on the server, which is used to encrypt. Decryption can only be achieved with the private key-pair, which should be located somewhere save.

# Encrypt database dumps?
# 1: yes
# 0: no (Default)
ENCRYPT=0

# Only applicable if ENCRYPT = 1
# OpenSSL Public key in PEM format without passphrase
OPENSSL_PUBKEY_PEM="${_INSTALL_PREFIX}/etc/mysqldump-secure.pub.pem"

# Only applicable if ENCRYPT = 1
# Must be valid openssl encryption algorithm
OPENSSL_ALGO_ARG="-aes256"

Compression

Compression is also an optional feature and can be enabled/disabled via config file. If enabled, dumps will be piped to a compression tool before data is writen disk. If encryption is enabled as well, compression is done prior encryption (due to efficiency - compressing encrypted data is almost useless). The configuration file offers compression presets for almost all major tools such as: gzip, pigz, bzip2, pbzip2, xz, lzma and lzop.

# Use compression?
# 1: yes
# 0: no (Default)
COMPRESS=1

# Gzip
COMPRESS_BIN="gzip"
COMPRESS_ARG="-9 --stdout"
COMPRESS_EXT="gz"

Blacklisting

By default mysqldump-secure will detect all databases the specified user can read and dump them all one by one. You can however exclude certain databases by name that you do not wish to backup.

# Opt out
# Databases not to be dumped.
# (space separated)
IGNORE="information_schema performance_schema"

Whitelisting

The whitelisting option will make sure that some databases must be backed up for sure. If databases specified in the whitelist are not found or not dumped, mysqldump-secure will inform you with an error message after the whole backup procedure. This is a security measurement which is also embedded in the nagios plugin.

# A list of databases that are explicitly required.
# If any of the specified required databases is not existent
# in the MySQL database or specified in IGNORE,
# (hence not dumped to disk), the script will write an error to stdout and LOG.
#
# Additionally, this information is also used by the nagios plugin and will automatically
# throw an error in nagios when any of the below specified databases were not found and/or not dumped.
#
# NOTE: The here specified databases are not guaranteed to be dumped,
#       because they might not even exist, it is rather
#       a method of letting you know, that they could not be dumped.
REQUIRE="mysql"

Tmpwatch / Tmpreaper

If you want this tool to take care about deleting old files, you can do so with either tmpwatch (RedHat, CentOS, Fedora, etc) or tmpreaper (Debian, Ubuntu, etc). Age of files to be deleted can be specified via the config file.

# Delete old files
# 1: yes
# 0: no (Default)
DELETE=0

# Choose the binary to use for deletion
# Possible values:
# * tmpwatch
# * tmpreaper
DELETE_METHOD="tmpwatch"

# Force deletion for read-only files.
# If your $DUMP_FILE_CHMOD is set to create read-only database dumps,
# tmpwatch/tmpreader will not be able to delete those files, unless you
# specify to force it via -f (--force)
#
# From manpage:
# Remove files even if EUID doesn’t have write access.
# Normally, files owned by the current EUID, with no write bit set are not removed.
DELETE_FORCE=0

# Only applicable of DELETE = 1
# Delete all files recursively which modification time
# is older than this
# e.g.
# DELETE_IF_OLDER=720	# 30 days
#
# NOTE: Only positive integers greater than zero are allowed
#       Everything else will disable deletion.
DELETE_IF_OLDER=720		# older than 30 days

Logging

You can have all output go to a logfile instead of stdout. This might be handy especially for running from cron where you do not want any debug output.

# Log output to logfile
# 1: yes
# 0: no (Default)
LOG=1

# File permission of mysqldump-secure log.
# NOTE: Should be very strict to not give away information.
LOG_CHMOD="0600"

# Only applicable if LOG = 1
# NOTE: Must be writeable by the user who runs the script
# NOTE: Must be chmod 600
LOGFILE="${_INSTALL_PREFIX}/var/log/mysqldump-secure.log"

Mysqldump options

Mysqldump options can be customized via config file. The current bundled defaults will make sure that events, triggers and routines are dumped as well:

# MySQLDump Options
# --default-character-set=utf8 (make sure to use utf8)
# --routines (Off by default)
# --events   (Off by default)
# --triggers (On by default)
# --hex-blob (be on the safe side)
# --complete-insert (be on the safe side)
# --extended-insert (save spaced)
# --compress (try to compress client/server communication if both support it)
# --opt      (On by default)
#   Included in --opt:
#    --add-drop-table
#    --add-locks
#    --create-options
#    --disable-keys
#    --extended-insert
#    --lock-tables
#    --quick
#    --set-charset
MYSQL_OPTS="--default-character-set=utf8 --events --triggers --routines --single-transaction --hex-blob --complete-insert --extended-insert --compress --opt"

Security aspects

A couple of security measurements have been integrated into mysqldump-secure. These include:

  • Password stealing via process list
  • umask before writing files to disk vs. chmod after writing files to disk.
  • Unsafe default values
  • Exit codes in piped commands

Please read all about them here: Security.

Self testing

mysqldump-secure does various self-tests including file and folder permissions and creates or corrects them according to the settings in the config as well as checking against mysql connectivity and various other checks. All output will optionally also or only go to the logfile as well.

Nagios / Icinga integration

There is also a Nagios / Icinga plugin specifically designed for this tool which is capable of checking if dumping was successful. The plugin will also be able to check if every single setting within the config file was applied. For example, if you want all dumps to be encrypted and compressed, the Nagios plugin will throw an error if dumps are written to disk unencrypted. For more information see check_mysqldump-secure.

# Enable Nagios Logfile
# 1: yes
# 0: no (Default)
NAGIOS_LOG=0

# Only applicable if NAGIOS_LOG = 1
#
# NOTE:
# If you want to change the location, do not alter the value
# of $_INSTALL_PREFIX, just remove it and specify your full path here.
NAGIOS_LOGFILE="${_INSTALL_PREFIX}/var/log/mysqldump-secure.nagios.log"

# File permission of Nagios log.
# NOTE: A 'nagios user' should be able to read the file.
NAGIOS_LOG_CHMOD="0644"

POSIX compliant

mysqldump-secure is fully POSIX compliant, which means it will run flawlessly on all standard unix systems without the need of special GNU versions of software.

Open source

It's open-source. Visit the project at github.com, express your wishes or report bugs here.

MIT licensed

MIT has very little restrictions. Read more about it at wikipedia.