New MySQL backup


I was playing around and configuring the MySQL backup on a customers database server and I ran into little problems. One of the defaults was not parsed correctly and a configuration setting made things run amok.

So I decided to streamline things and create some extra program options to set and show the default settings and the settings after the configuration file.

The help now shows:

mysqlbackup version 1.50
Syntax: mysqlbackup [ -h|-v|-s|-c|-d|]
	-h			Display this help and exit
	-v			Display the version number and exit
	-s			Show the defaults and their values
	-d			Show a description of the defaults and their values
	-c			Show a default configuration file
	-a			Show settings after the config file is processed
	configfile	Use this file as the configuration file
				instead of the default.
				Default: /usr/local/bin/mysqlbackup.conf

Come and g[ei]t it. It’s in the download section or on GitHub.

Or clone it with:

git clone https://github.com/tonk/MySQL-Backup.git

The output of the command mysqlbackup -s looks like

USERNAME: "root"
PASSWORD: "password"
DBHOST: "localhost"
OWNER: "root"
GROUP: "root"
DBNAMES: "all"
BACKUPDIR: "/backup/mysql"
MAILCONTENT: "stdout"
MAXATTSIZE: "4000"
MAILADDR: "root@localhost"
DOMONTHLY: "yes"
MONTHLY_DBNAMES: "all"
DBEXCLUDE: "none"
CREATE_DATABASE: "yes"
DROP_DATABASE: "yes"
DROP_TABLE: "yes"
SEPDIR: "yes"
DOWEEKLY: "6"
COMP: "gzip"
COMMCOMP: "no"
LATEST: "yes"
LOCK_TABLES: "yes"
RUN_CHECK: "yes"
CHECK_OPTIMIZE: "yes"
CHECK_UPGRADE: "yes"
CHECK_REPAIR: "no"
MAX_ALLOWED_PACKET: ""
SOCKET: ""
PREBACKUP: ""
POSTBACKUP: ""

and mysqlbackup -d like

Database username              -> USERNAME             = "root"
Database password              -> PASSWORD             = "password"
Database host                  -> DBHOST               = "localhost"
Backup owner                   -> OWNER                = "root"
Backup group                   -> GROUP                = "root"
Backup databases               -> DBNAMES              = "all"
Backup location                -> BACKUPDIR            = "/backup/mysql"
Mail logging                   -> MAILCONTENT          = "stdout"
Maximum attachment size        -> MAXATTSIZE           = "4000"
E-mail address for logging     -> MAILADDR             = "root@localhost"
Make monthly backups           -> DOMONTHLY            = "yes"
Monthly backup databases       -> MONTHLY_DBNAMES      = "all"
Exclude databases              -> DBEXCLUDE            = "none"
Include 'CREATE DATABASE'      -> CREATE_DATABASE      = "yes"
Include 'DROP DATABASE'        -> DROP_DATABASE        = "yes"
Include 'DROP TABLE'           -> DROP_TABLE           = "yes"
Use seperate directories       -> SEPDIR               = "yes"
Weekly backups on day (1=Mon)  -> DOWEEKLY             = "6"
Use compression (gzip/bzip2)   -> COMP                 = "gzip"
Compress MySQL communication   -> COMMCOMP             = "no"
Keep 'latest' backup copy      -> LATEST               = "yes"
Lock tables during backup      -> LOCK_TABLES          = "yes"
Run database checks            -> RUN_CHECK            = "yes"
Run database optimize checks   -> CHECK_OPTIMIZE       = "yes"
Run database upgrade checks    -> CHECK_UPGRADE        = "yes"
Run database repair            -> CHECK_REPAIR         = "no"
Maximum buffer size            -> MAX_ALLOWED_PACKET   = ""
UNIX socket path               -> SOCKET               = ""
Pre backup script              -> PREBACKUP            = ""
Post backup script             -> POSTBACKUP           = ""

and after parsing the configuration file the command mysqlbackup -a shows

Processed config file /home/tonk/develop/mysqlbackup/mysqlbackup.conf
================================================================================
Database username              -> USERNAME             = "root"
Database password              -> PASSWORD             = "azerty"
Database host                  -> DBHOST               = "localhost"
Backup owner                   -> OWNER                = "root"
Backup group                   -> GROUP                = "root"
Backup databases               -> DBNAMES              = "all"
Backup location                -> BACKUPDIR            = "/backup/mysql"
Mail logging                   -> MAILCONTENT          = "stdout"
Maximum attachment size        -> MAXATTSIZE           = "4000"
E-mail address for logging     -> MAILADDR             = "root@localhost"
Make monthly backups           -> DOMONTHLY            = "yes"
Monthly backup databases       -> MONTHLY_DBNAMES      = "mysql all"
Exclude databases              -> DBEXCLUDE            = ""
Include 'CREATE DATABASE'      -> CREATE_DATABASE      = "yes"
Include 'DROP DATABASE'        -> DROP_DATABASE        = "yes"
Include 'DROP TABLE'           -> DROP_TABLE           = "yes"
Use seperate directories       -> SEPDIR               = "yes"
Weekly backups on day (1=Mon)  -> DOWEEKLY             = "6"
Use compression (gzip/bzip2)   -> COMP                 = "gzip"
Compress MySQL communication   -> COMMCOMP             = "no"
Keep 'latest' backup copy      -> LATEST               = "yes"
Lock tables during backup      -> LOCK_TABLES          = "yes"
Run database checks            -> RUN_CHECK            = "yes"
Run database optimize checks   -> CHECK_OPTIMIZE       = "yes"
Run database upgrade checks    -> CHECK_UPGRADE        = "yes"
Run database repair            -> CHECK_REPAIR         = "no"
Maximum buffer size            -> MAX_ALLOWED_PACKET   = ""
UNIX socket path               -> SOCKET               = ""
Pre backup script              -> PREBACKUP            = ""
Post backup script             -> POSTBACKUP           = ""

See how the PASSWORD changed. The first is the default in the program itself, the second one is defined in the configuration file.

And last but not least, mysqlbackup -c

# Database username
USERNAME="root"
# Database password
PASSWORD="password"
# Database host
DBHOST="localhost"
# Backup owner
OWNER="root"
# Backup group
GROUP="root"
# Backup databases
DBNAMES="all"
# Backup location
BACKUPDIR="/backup/mysql"
# Mail logging
MAILCONTENT="stdout"
# Maximum attachment size
MAXATTSIZE="4000"
# E-mail address for logging
MAILADDR="root@localhost"
# Make monthly backups
DOMONTHLY="yes"
# Monthly backup databases
MONTHLY_DBNAMES="all"
# Exclude databases
DBEXCLUDE="none"
# Include 'CREATE DATABASE'
CREATE_DATABASE="yes"
# Include 'DROP DATABASE'
DROP_DATABASE="yes"
# Include 'DROP TABLE'
DROP_TABLE="yes"
# Use seperate directories
SEPDIR="yes"
# Weekly backups on day (1=Mon)
DOWEEKLY="6"
# Use compression (gzip/bzip2)
COMP="gzip"
# Compress MySQL communication
COMMCOMP="no"
# Keep 'latest' backup copy
LATEST="yes"
# Lock tables during backup
LOCK_TABLES="yes"
# Run database checks
RUN_CHECK="yes"
# Run database optimize checks
CHECK_OPTIMIZE="yes"
# Run database upgrade checks
CHECK_UPGRADE="yes"
# Run database repair
CHECK_REPAIR="no"
# Maximum buffer size
MAX_ALLOWED_PACKET=""
# UNIX socket path
SOCKET=""
# Pre backup script
PREBACKUP=""
# Post backup script
POSTBACKUP=""
mysql 

See also