Example usage

1. General usage

mysqldump-secure offers two modes for running. The normal mode (without any arguments), which produces debug output to stdout and the cron mode (--cron) which only produces stderr output to prevent cron from sending mails on sucessful run.

Usage: mysqldump-secure [--conf] [--cron] [--test] [--help] [--version]
       mysqldump-secure [--conf]
       mysqldump-secure --cron [--conf]
       mysqldump-secure --test [--conf]
       mysqldump-secure --help
       mysqldump-secure --version

When invoked without any arguments, it will start dumping databases as
defined in mysqldump-secure.conf.

--conf            Pass different configuration file than the default one.
                  E.g.: --conf=/etc/mysqldump-secure-alt.conf

--cron            Use for cron run. It will only output errors and warnings
                  and will silence all debug output.

--test            Test requirements and exit.

--help            Show this help screen.

--version         Show version information.

Cron mode and normal mode can be combined with --conf in order to specify an alternative configuration file. You can have multiple configuration files for multiple use cases. One file could dump all databases encryptedly, whereas another configuration could dump all databases unencryptedly. See Using two configuration files

Click on the image to see live usage from travis-ci with all features turned on.

3. Running as non-root user

By default mysqldump-secure requires root or sudo privileges, just because mysqldump-secure.conf and mysqldump-secure.cnf are only readable by root. It can be run by a non-root user as well. The only requirement for this is that mysqldump-secure.conf and mysqldump-secure.cnf must be readable by that user.

4. Running via cron

In cron-mode all stdout debug information will be surpressed so that cron does not send emails on successful run. It would be wise to keep logging on for this mode, so that you can always check by looking at your logs what was going on.

To run in cron-mode, simply append --cron as an argument:

mysqldump-secure --cron

5. Using two configuration files

Imagine you have 4 databases: secure1, secure2, public1 and public2.

secure1 and secure2 should be dumped encrypted and all other databases should be dumped plain.

For this to accomplish, you will need two separate configurations:

/etc/mysqldump-secure.conf

The default configuration will dump all databases without encryption and will not dump (ignore) secure1 and secure2.

_INSTALL_PREFIX=""
DUMP_PATH="${_INSTALL_PREFIX}/var/mysqldump-secure"
DUMP_FILE_PRE="$(date '+%Y-%m-%d')_$(date '+%H-%M')__"
DUMP_DIR_CHMOD="0700"
DUMP_FILE_CHMOD="0400"

MYSQL_CNF_FILE="${_INSTALL_PREFIX}/etc/mysqldump-secure.cnf"
MYSQL_OPTS="--default-character-set=utf8 --events --triggers --routines --single-transaction --hex-blob --complete-insert --extended-insert --compress --opt"

# Ignore: secure1 and secure2
IGNORE="information_schema performance_schema secure1 secure2"
REQUIRE="mysql"

LOG=1
LOG_CHMOD="0600"
LOGFILE="${_INSTALL_PREFIX}/var/log/mysqldump-secure.log"

COMPRESS=1
COMPRESS_BIN="gzip"
COMPRESS_ARG="-9 --stdout"
COMPRESS_EXT="gz"

ENCRYPT=0
OPENSSL_PUBKEY_PEM="${_INSTALL_PREFIX}/etc/mysqldump-secure.pub.pem"
OPENSSL_ALGO_ARG="-aes256"

DELETE=0
DELETE_METHOD="tmpwatch"
DELETE_FORCE=0
DELETE_IF_OLDER=720

NAGIOS_LOG=0
NAGIOS_LOGFILE="${_INSTALL_PREFIX}/var/log/mysqldump-secure.nagios.log"
NAGIOS_LOG_CHMOD="0644"

/etc/mysqldump-secure.encrypted.conf

The second configuration will only dump secure1 and secure2 encryptedly and will ignore all others.

_INSTALL_PREFIX=""
DUMP_PATH="${_INSTALL_PREFIX}/var/mysqldump-secure"
DUMP_FILE_PRE="$(date '+%Y-%m-%d')_$(date '+%H-%M')__"
DUMP_DIR_CHMOD="0700"
DUMP_FILE_CHMOD="0400"

MYSQL_CNF_FILE="${_INSTALL_PREFIX}/etc/mysqldump-secure.cnf"
MYSQL_OPTS="--default-character-set=utf8 --events --triggers --routines --single-transaction --hex-blob --complete-insert --extended-insert --compress --opt"

# Ignore all databases except secure1 and secure2
IGNORE="information_schema performance_schema mysql public1 public2"
REQUIRE=""

LOG=1
LOG_CHMOD="0600"
LOGFILE="${_INSTALL_PREFIX}/var/log/mysqldump-secure.log"

COMPRESS=1
COMPRESS_BIN="gzip"
COMPRESS_ARG="-9 --stdout"
COMPRESS_EXT="gz"

# ENABLE ENCRYPTION
ENCRYPT=1
OPENSSL_PUBKEY_PEM="${_INSTALL_PREFIX}/etc/mysqldump-secure.pub.pem"
OPENSSL_ALGO_ARG="-aes256"

DELETE=0
DELETE_METHOD="tmpwatch"
DELETE_FORCE=0
DELETE_IF_OLDER=720

NAGIOS_LOG=0
NAGIOS_LOGFILE="${_INSTALL_PREFIX}/var/log/mysqldump-secure.nagios.log"
NAGIOS_LOG_CHMOD="0644"

Now you would setup your cronjob to use two instances with different config files:

# .---------------- minute (0 - 59)
# |  .------------- hour (0 - 23)
# |  |  .---------- day of month (1 - 31)
# |  |  |  .------- month (1 - 12) OR jan,feb,mar,apr ...
# |  |  |  |  .---- day of week (0 - 6) (Sunday=0 or 7) OR sun,mon,tue,wed,thu,fri,sat
# |  |  |  |  |
# *  *  *  *  * user-name  command to be executed

# Dump MySQL Databases at 03:15 every day
  15 3  *  *  * /usr/bin/mysqldump-secure --cron
  15 3  *  *  * /usr/bin/mysqldump-secure --cron --conf=/etc/mysqldump-secure.encrypted.conf