cytopia/mysqldump-secure

Secure mysqldump script with encryption, compression, logging, blacklisting and Nagios monitoring integration

mysqldump-secure

ATTENTION, _(*1)

Please upgrade to the latest version in order to be notified about the OpenSSL SMIME Bug in case you are affected., _(*2)

GIT NOTE:, _(*3)

WHEN CLONING VIA GIT, MAKE SURE TO ALWAYS CHECK OUT THE LATEST TAG. THE MASTER BRANCH IS ALWAYS UNDER DEVELOPMENT AND THEREFORE UNSTABLE., _(*4)

Features | Installation | Configuration | Usage | Documentation | Contribution | License | Version, _(*5)

, _(*6)

Mysqldump-secure is a POSIX compliant wrapper script for mysqldump with many features and very strong security in mind. It will backup every available database (which is readable by the specified user) as a separate file with the possibility to opt out via blacklisting. Dumped databases can optionally be piped directly to gzip or openssl in order to compress and/or encrypt the backup. Encryption is done before the file is written to disk to avoid possible race conditions (See documentation for more info about security measurements)., _(*7)

Find the project website at https://mysqldump-secure.org Find the whole post at https://www.everythingcli.org, _(*8)

Runs on

, _(*9)

_{(If the script runs on any other system not mentioned here, please drop me a note.)}, _(*10)

Rock-stable and well tested

Every push to mysqldump-secure triggers travis-ci which will run hundreds of all kinds of tests against the new code and stress the tool in every possible way with every possible config., _(*11)

You can find the tests within the test folder including an automated setup to get a master-slave server with SSL encryption setup. Thoses tests can also be run locally., _(*12)

See https://travis-ci.org/cytopia/mysqldump-secure for checks in action and test for a description about the tests., _(*13)

1. Features

Primary Features

• Encryption (hybrid encryption: RSA and AES via openssl smime - no password needed)
• Compression (gzip, pigz, bzip2, pbzip2, lzop, lzma, xz)
• Tmpwatch integration (tmpwatch or tmpreaper)
• Transaction-safe / Consistent backups across tables (for DBs with: InnoDB only-, mixed- and non-InnoDB- tables)
• Conditional mysqldump options (e.g.: apply --quick only on DBs > 200MB)
• Security (various built-in checks and precautions)
• SSL Remote Backups
• Nagios / Icinga monitoring integration (via check_mysqldump-secure)

Secondary Features

• Custom mysqldump options
• Master/Slave recognition
• Blacklisting
• Whitelisting
• File logging
• Self validation
• Rock stable (see travis for hundreds of checks)
• 100% POSIX compliant

2. Installation

2.1 Linux, BSD and OSX

wget https://github.com/cytopia/mysqldump-secure/archive/0.16.5.tar.gz -O - | tar -xz
cd mysqldump-secure
./configure
make
sudo make install

2.2 OSX

brew tap cytopia/tap
brew install mysqldump-secure

For more detailed instructions go to the Install guidelines, _(*14)

3. Configuration

There are two separate configuration files:, _(*15)

• /etc/mysqldump-secure.cnf
• /etc/mysqldump-secure.conf

The first one is to setup the MySQL credentials and the second one configures the behavior of how to backup the databases., _(*16)

You do not need to worry about file permissions or directories. The script will auto-check the following options and adjust them as required:, _(*17)

• Logfile exists
• Logfile is writeable
• Auto creation of logfile
• Logging turned off automatically
• Destination dir exists
• Destination dir is writeable
• Auto creation of destination dir
• Required system binaries exist
• MySQL credentials are valid

For more detailed instructions go to the Setup guidelines, _(*18)

4. Usage

4.1 Usage

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

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

  --conf          Pass a 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 info, debug and trace output.

  --test          Test requirements and exit.
                  Combine with -v or -vv for more verbose output.

  -v              Show debug output.
                  Specify twice (-vv) to also show trace output.
                  Can be combined with --conf  and --test

  -vv             Show debug and trace output.
                  Can be combined with --conf  and --test

  --help          Show this help screen.

  --version       Show version information.


Exit codes

  0               All fine, no fatals, no errors and no warnings occured.
  1               Warnings occured, but all dumps were successfull.
  2               Errors occured, but all dumps were successfull.
  3               Failed. Mysqldump encountered errors.
  4               Abort. The program aborted, due to missing requirements,
                  wrong arguments or a misconfiguration.

Further reading

See 'man mysqldump-secure' for more help.
Visist http://mysqldump-secure.org and browse documentation.

4.2 Default

Test if everything is configured correctly:, _(*19)

mysqldump-secure --test -vv

Manual run from commmand line:, _(*20)

mysqldump-secure -v

Run from within cron, _(*21)

mysqldump-secure --cron

4.3 Custom config

It is possible to have multiple instances of mysqldump-secure on your machine via different config files. Imagine the case you want to have some sensitive dumps encrypted and others should be dumped in plain. You can achieve this by using two configuration files and the IGNORE blocks of each respective config to exclude the other ones., _(*22)

Test if everything is configured correctly in the specified config:, _(*23)

mysqldump-secure --test --config=/etc/mysqldump-secure.encrypted.conf

Manual run from commmand line:, _(*24)

mysqldump-secure --config=/etc/mysqldump-secure.encrypted.conf

Run from within cron, _(*25)

mysqldump-secure --cron --config=/etc/mysqldump-secure.encrypted.conf

4.4 Test run

$ mysqldump-secure --test -v
[INFO]  (OPT): Logging enabled
[DEBUG] (OPT): Log level: 3
[DEBUG] (OPT): Logfile: /var/log/mysqldump-secure.log
[DEBUG] (CFG): Destination dir: /var/mysqldump-secure
[DEBUG] (CFG): Using file Prefix: 2016-03-09_10-07__
[INFO]  (OPT): MySQL SSL connection enabled
[DEBUG] (OPT): MySQL SSL arguments: --ssl-ca=/etc/mysql.ca.pem.
[INFO]  (OPT): Compression enabled
[DEBUG] (OPT): Compression arguments: gzip -9 --stdout
[INFO]  (OPT): Encryption enabled
[DEBUG] (OPT): Encryption algorithm: -aes256
[INFO]  (OPT): Deletion enabled
[DEBUG] (OPT): Deleting files older than 2 days. Using: tmpwatch
[INFO]  (OPT): Nagios log enabled
[DEBUG] (OPT): Nagios logfile: /var/log/mysqldump-secure.nagios.log
[DEBUG] (SRV): MySQL server connection: 127.0.0.1 via TCP/IP
[DEBUG] (SRV): MySQL server connection: Using SSL (Cipher in use is DHE-RSA-AES256-GCM-SHA384)
[DEBUG] (SRV): MySQL server version:    MariaDB 10.1.11-MariaDB-log Homebrew
[DEBUG] (SRV): MySQL server hostname:   notebook.home.lan:3306
[DEBUG] (SRV): MySQL server rep type:   master

4.5 Example run

$ mysqldump-secure -v
[INFO]  (OPT): Logging enabled
[DEBUG] (OPT): Log level: 3
[DEBUG] (OPT): Logfile: /var/log/mysqldump-secure.log
[DEBUG] (CFG): Destination dir: /var/mysqldump-secure
[DEBUG] (CFG): Using file Prefix: 2016-03-09_10-13__
[INFO]  (OPT): MySQL SSL connection enabled
[DEBUG] (OPT): MySQL SSL arguments: --ssl-ca=/etc/mysql.ca.pem.
[INFO]  (OPT): Compression enabled
[DEBUG] (OPT): Compression arguments: gzip -9 --stdout
[INFO]  (OPT): Encryption enabled
[DEBUG] (OPT): Encryption algorithm: -aes256
[INFO]  (OPT): Deletion enabled
[DEBUG] (OPT): Deleting files older than 2 days. Using: tmpwatch
[INFO]  (OPT): Nagios log enabled
[DEBUG] (OPT): Nagios logfile: /var/log/mysqldump-secure.nagios.log
[DEBUG] (SRV): MySQL server connection: 127.0.0.1 via TCP/IP
[DEBUG] (SRV): MySQL server connection: Using SSL (Cipher in use is DHE-RSA-AES256-GCM-SHA384)
[DEBUG] (SRV): MySQL server version:    MariaDB 10.1.11-MariaDB-log Homebrew
[DEBUG] (SRV): MySQL server hostname:   notebook.home.lan:3306
[DEBUG] (SRV): MySQL server rep type:   master
[DEBUG] (SQL): Retrieving list of databases... 9
[INFO]  (SQL): 1/9 Dumping:  db_InnoDB_and_MyISAM (19.90 MB) (compressed) (encrypted) (--lock-tables) (--skip-quick) 0 sec
[INFO]  (SQL): 2/9 Dumping:  db_InnoDB_only (10.52 MB) (compressed) (encrypted) (--single-transaction) (--skip-quick) 1 sec
[INFO]  (SQL): 3/9 Dumping:  db_MyISAM_only (9.38 MB) (compressed) (encrypted) (--lock-tables) (--skip-quick) 0 sec
[INFO]  (SQL): 4/9 Skipping: information_schema (ignored)
[INFO]  (SQL): 5/9 Skipping: my_empty_db1 (DB is empty)
[INFO]  (SQL): 6/9 Skipping: my_empty_db2 (DB is empty)
[INFO]  (SQL): 7/9 Dumping:  mysql (0.62 MB) (compressed) (encrypted) (--lock-tables) (--skip-quick) 0 sec
[INFO]  (SQL): 8/9 Skipping: performance_schema (ignored)
[INFO]  (SQL): 9/9 Skipping: test (DB is empty)
[DEBUG] (RUN): Dumping finished (OK: 4 dbs, IGN: 5 dbs, ERR: 0, TOTAL: 9)
[DEBUG] (RUN): Took 2 seconds
[DEBUG] (RUN): Total size dumped: 40.42 MB
[INFO]  (RUN): Deleting files older than 2 days ... 4
[INFO]  removing file /var/mysqldump-secure/2016-03-07_01-52__db_InnoDB_and_MyISAM.sql.gz.enc
[INFO]  removing file /var/mysqldump-secure/2016-03-07_01-52__db_InnoDB_only.sql.gz.enc
[INFO]  removing file /var/mysqldump-secure/2016-03-07_01-52__db_MyISAM_only.sql.gz.enc
[INFO]  removing file /var/mysqldump-secure/2016-03-07_01-52__mysql.sql.gz.enc
[DEBUG] (RUN): Writing nagios logfile
[OK]    Finished successfully

5. Documentation

6. Contribution

Contributors are welcome., _(*26)

If the script runs on an operating system productively, which is currently not yet included at the top of this document, please let me know, so I can add it for reference., _(*27)

If you use the script, star it or let me know somehow., _(*28)

If you like have a look at the Contributing Guidelines and see if there is anything you would like to take care of., _(*29)

7. License

, _(*30)

8. Version

For a complete list of verion see CHANGELOG, _(*31)