Groesbeek, view of the 'National Liberation Museum 1944-1945' in Groesbeek. © Ton Kersten
Fork me on GitHub

New MySQL backup

2010-12-03 (80) by Ton Kersten, tagged as mysql

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=""

Comments