Prepared by Ronald Bradford
29 October 2009
Version: 1.0

Revision History

Draft 1 – 26 October 2009
Draft 2 – 29 October 2009

Log files will have a default extension of .log See section 2.3 for updated information on accessible convenience defaults
Current scripts have been changed to use LOG_EXT and DEFAULT_LOG_FILE where necessary
Additional detailed information of logging output and examples in new section 3.4.1 including practices for detecting alerting conditions.
Added additional information on common.sh and details of file within version control. See section 3.3.4
Refactored code and added documentation on stopping scripts with STOP_FILE. See Section 4.1
Added standards for MySQL authentication. See Section 4.2
Updated other typographical errors

1. Overview

This document describes the standards used for all new written scripts managed under version control for the MySQL DBA group.
By default, all DBA scripts should be written in shell programming language (/bin/sh).

2. File Standards

2.1 Projects

A project is defined as a set of files that perform a specific purpose.
Projects enable a means of packaging via the Release Management system
Projects enable the included files to be released at various different release schedules independently of other projects.

The default projects at this time as specified in Version Control are:

admin – Administration scripts used to create and manage systems
operations – DBA support scripts for normal DBA operations
monitoring – Specific DBA monitoring product support

2.2 Directory Standards

Within each project the following default top level directories are in place:

/scripts

/etc

/doc

/log

The definition of each directory is:

/scripts holds all scripts that are written for this project
/etc holds all configuration files that are used for this project, by default a configuration file should exist for each script
/doc holds all supporting documentation for the scripts or other programs in this project
/log holds by default an log files produced from this project. This directory is to not contain any files under version control, the directory exists only for minimum deployment requirements.

It is possible that other directories exist for a given file type as determined for the project. For example:

/sql holds all SQL scripts

2.3 File Naming Standards

File names used for scripts, configuration, logs and other files within projects are to follow the following file naming standards:

All Shell scripts are to have the extension .sh
All Configuration files are to have the extension .cnf
Log files are to have the default extension .log
If a script produces an output file of a specific format, the extension should reflect the file format, e.g. .csv, .tsv etc.

All filenames are to be in lowercase only.
The underscore ‘_’ character is to be used as a word boundary.
All scripts are to be defined with a name that adequately describes the script without need to investigate further the purpose of the script.
Filenames may include letters and numbers in addition to underscore ‘_’.
Spaces are never allowed in any filenames.
The period ‘.’ is to only be used for extensions, or for specific sub-categories of information within the filename.
Configuration files when designed to be customized per server, are to have the version control file suffixed with .sample to ensure they do not overwrite any deployed scripts

The following are examples of filenames in use:

checklist.sh
checklist.mysql.cnf, checklist.system.cnf
ip_poller.sh

These are INVALID filenames:

ESM_monitoring.sh
my_sample_script
CamelCaseNames.sh

2.3 Log File Standards

Log files are always to include a date/time stamp that enable a list of log files for the given type of script in a chronological order. This is defined with the following common system variable that is specified at the start of all scripts.

DATE_TIME=`date +%Y%m%d.%H%M`

A log file name must include the following components.

Script name (i.e. SCRIPT_NAME)
Execution Date/Time (i.e. DATE_TIME)
.log extension, defined as LOG_EXT

There is no specific format for log files, however it is recommended you following the following format:

DEFAULT_LOG_FILE=”${LOG_DIR}/${SCRIPT_NAME}.${DATE_TIME}.${SHORT_HOSTNAME}${LOG_EXT}”

If you wish to use this format, you simply need to:

LOG_FILE=${DEFAULT_LOG_FILE}

3. Scripting Standards

3.1 Script Layout

All scripts are to be written to support the default bourne shell. All scripts are to commence with the following first line.

#!/bin/sh

All scripts are to have as the last line the following comment.

# END

Other then variable declarations and the call to main, all scripting code is to be defined within functions.
The default indentation is 2 spaces.
Tab characters are not allowed for formatting indentation.
All content within scripts are to be in English only, including names, variables and comments.

3.2 Variable Coding Standards

Variables are used thought scripts. These should use the following requirements:

All variables are to be in UPPERCASE

Within functions, local is be used to restrict the scope of variables

3.2.1 Mandatory Script Variables

All scripts must define the following variables as part of the Script Definition section at the top of the script.

SCRIPT_NAME=`basename $0 | sed -e “s/.sh$//”`

SCRIPT_VERSION=”0.01 DD-MMM-YYYY $Id$”

SCRIPT_REVISION=”$Revision$”

The script version includes a human managed version number and date for readability within the –version and –help options.

NOTE: $Id$ and $Revision$ are standard RCS tags that the current version control system supports. See http://kb.perforce.com/UserTasks/ManagingFile..Changelists/UsingRcsKeywords for more information.

3.2.2 Global Script Variables

A number of pre-defined global variables are used and available to all scripts:

Directories

BASE_DIR This is the base directory of the project that can be used to reference other files
SCRIPT_DIR This is the /scripts project directory
CNF_DIR This is the /etc project directory
LOG_DIR This is the /log project directory
TMP_DIR This is a temporary working directory, currently this defaults to /tmp

When creating global variables for important paths, allow for override of these by the controlling shell environment. For example.

[ ! -z “${TMP_DIR}” ] && TMP_DIR=”/tmp” # Correct definition

TMP_DIR=”/tmp” # Incorrect defintion

Files

TMP_FILE A pre-defined unique temporary file that is auto removed on completion
STOP_FILE A pre-defined file to stop script processing in loops (only if used in functions)
DEFAULT_CNF_FILE A pre-defined standard /etc config file name
DEFAULT_LOG_FILE A pre-defined standard /log log file name

Variables

DATE_TIME – The date/time of the script execution
DATE_TIME_TZ – The date/time/timezone of the script execution
USER_ID – The running user id
FULL_HOSTNAME – The full and qualified hostname
SHORT_HOSTNAME – The short hostname
LOG_DATE_FORMAT – The Date Format used for all log files

Other Variables

QUIET – Quiet Logging, ERROR and WARN only
USE_DEBUG – Enable Debugging

3.2.3 Variable Usages

When using variables, they are always to be enclosed in curly brackets.

${TMP_FILE} is acceptable
$TMP_FILE is NOT acceptable

When displaying variables in stdout, they should always be included in single quotes (‘) to ensure actual value can be determined.

info “Exiting with status code of ‘${EXIT_CODE}'”

3.2.4 Unacceptable practices

Hardcoding values into scripts that are configuration values, e.g. email distribution list values
Using hardcoded constants when a function or command can be used to obtain the value, e.g. hostname
Repeating common strings of combined values, these are to be refactored into one variable

echo “The log file is ${LOG_DIR}/${SCRIPT_NAME}…${DATE_TIME}.txt”

echo “start” > ${LOG_FILE}/${SCRIPT_NAME}…${DATE_TIME+.txt”

Should be rewritten as:

LOG_FILE=${LOG_DIR}/${SCRIPT_NAME}…${DATE_TIME}.txt

echo “The log file is ${LOG_FILE}”

echo “start” > ${LOG_FILE}

3.3 Function Coding Standards

All code is to be written within a self contained function. The purpose is to allow for the testability and re-use of these functions.
The following standards apply to individual functions within all scripts:

All functions are to be prefixed with a minimum 3 line header comment
The first line is 80 characters long, and the function name is right aligned surrounded with a single space and two (2) trailing dashes ‘–‘
The second line is a one line short summary of the functions purpose
The third line is a single comment ‘#’ to separate the comments from the function code
The function name is to preceed on the following line and include the suffix of () {
All function names are to be in lower case, and use a underscore ‘_’ as a word boundary.
A single blank line is to separate functions
A function should always return an integer value

3.3.1 Function Coding Example

#———————————————————————– help –

# Display Script help syntax

#

help() {

  …

  return 0

}

There is no main function within a shell script. In this instance, all scripts will define a main() function, and this is the only execution entry point. This is to be the last physical executable line of code of the script

#———————————————————————– main –

# Main Script Processing

#

main() {

  …

  return 0

}

3.3.2 Mandatory Script Functions

All scripts are to have the following default functions:

bootstrap
help
process_args
main

bootstrap

The bootstrap function is to be identical in all scripts, this is used to source necessary common functions used by all scripts. The current format of this function is, and must be included physically in all scripts:

#—————————————————————— bootstrap –

# Essential script bootstrap

#

bootstrap() {

DIRNAME=`dirname $0`

COMMON_SCRIPT_FILE=”${DIRNAME}/common.sh”

[ ! -f “${COMMON_SCRIPT_FILE}” ] && echo “ERROR: You must have a matching ‘${COMMON_SCRIPT_FILE}’ with this script ${0}” && exit 1

. ${COMMON_SCRIPT_FILE}

set_base_paths

return 0

}

The function performs the following operations.

Sources the necessary companion common.sh script
Defines the BASE_DIR variable if not defined by the shell. Used by all scripts
Defines the LOG_DIR variable if not defined by the shell
Defines the TMP_DIR variable if not defined by the shell
Defines the internal CNF_DIR and SCRIPT_DIR variables

help

The help function is to display the usage of the function and then exit. The usage needs to specific all command line arguments, and client identify mandatory and optional arguments. See the example.sh below for the syntax of the help function.

process_args

This function is used to process the command line arguments for scripts. The getopt function is used for processing arguments however this only support single character options (e.g. -v -h -p etc). Scripts should be written for only single character options.

To improve the namespace, as well as provide a difference between operational parameters and information parameters the following two word options are used.

–help Display script help and exit
–version Display single line script version and exit

These options are managed by a common.sh function that should be executed as the first line in the process_args functions.

check_for_long_args $*

See the example.sh for a working example the syntax for this function.

main

The main function is to include at minimum the following code:

#———————————————————————– main –

# Main Script Processing

#

main () {

  [ ! -z “${TEST_FRAMEWORK}” ] && return 1

  bootstrap

  process_args $*

  commence

  complete

  return 0

}
main $*
# END

3.3.3 Function examples

Each function should perform the following in the defined order.

Check for required number of arguments
Assign local variables to arguments
Check for individual arguments
provide debugging or information output on operation
perform operation
return success

For example.

sample() {

  [ $# -ne 1 ] && fatal “sample() This function requires one argument.”

  local VAL=$1

  [ -z “${VAL}” ] && fatal “sample() $VAL is not defined”

  info “Running sample function with ‘${VAL}'”
  # DO WORK HERE
  return 0

}

3.3.4 Common Functions

A small set of common functions are defined in the common.sh script. This is required for all scripts and is included as part of the bootstrap function.
The current list as at version 0.03 is:

set_base_paths – This function sets the base directory variables BASE_DIR,CNF_DIR,SCRIPT_DIR as well as various DEFAULT files
version - This function returns script standard version information and exits.
check_for_long_args – This function pre checks for –help and –version options
commence – This function writes a starting script INFO line and starts timing
complete – This function writes a ending script INFO line and shows total execution time
check_stop_file – This function exists a long running script nicely if STOP_FILE exists.

fatal – This function logs a FATAL message in the standard format and exits
error - This function logs a ERROR message in the standard format and exits
warn - This function logs a WARN message in the standard format
info - This function logs a INFO message in the standard format if not in quiet mode
debug - This function logs a DEBUG message in the standard format if debug is enabled
log - This function performs the low level standard logging

cleanup_exit - This function logs a message base on the edit code, cleans up any temporary files and exits

Refer to Version control /admin/scripts/common.sh for a current version of this script.

3.4 Output Display Standards

Standard output is never to be sent using the echo command. The defined function info is used to provide a consistent and reproducible output.
There are 5 different level of output these are:

fatal – Used for internal misconfigurations only. Reported as FATAL
error – Used to indicate a terminating error of the script. The script will stop processing. Reported as ERROR
warn – Used to indicate a warning that should be investigated. Reported as WARN
info – Used to display valuable information. Reported as INFO
debug – Used for debugging purposes. Reported as DEBUG

All output from these functions is sent to stdout. By default, scripts should not redirect default script output to a log file. This should be determined on a case by case basis and also the encapsulation of any script within other scripts.

3.4.1 Output Display Examples

The default output is INFO,WARN,ERROR,FATAL. You can override this with two options.

For production operation running in quiet mode with the -q option will only display errors and warnings, this will suppress INFO messages. By default, your script should not produce warnings or errors, so a non empty output in quiet mode can be determine as an unsuccessful execution. You can use this condition in script management.

Debugging output is only shown if the -v option is provided to a script, and this in turn enables the USE_DEBUG environment variable. This variable can also be set by the calling environment or shell. This output can be verbose and is only for debugging purposes. Production scripts should never be run in debug. If additional information is required, consider adding additional info calls.

Logging output includes the following components.

Date & Time & Timezone
Output Level (FATAL,ERROR,WARN,INFO,DEBUG)
Script name
Message

Logging output follows the following standard.

YYYYMMDD HH:MM:SS -TZTZ XXXXX [scriptname]

The first 30 characters of data will always be of set format and this rule can be use for log file parsing.

A successful execution of a script will also return at least two lines, the identifier of the script from the commence function call, and the completion including execution time with the complete function call. For example:

$ ./example.sh -X x

20091029 13:24:54 -0400 INFO [example] Script started (Version: 0.03 DD-MMM-YYYY $)
20091029 13:24:54 -0400 INFO [example] Script completed successfully (0 secs)

This can be suppressed as indicated via the quiet mode.

./example.sh -X x -q

The benefit of quiet mode is that a successful script should return a zero exit code and have no stdout. Either of these conditions can trigger an alerting process.

An error condition will be shown as ERROR, and will always return a non zero error code. For example:

$ ./example.sh
20091029 13:27:07 -0400 ERROR [example] You must specify a sample value for -X. See –help for full instructions.
20091029 13:27:07 -0400 INFO [example] Exiting with status code of ‘1’
$ echo $?
1

3.5 Script Template

The following example.sh as defined in version control acts as the current template for all new scripts

3.5.1 example.sh

#!/bin/sh

#

#——————————————————————————-

# Name:     example.sh

# Purpose: Example Shell Script with minimum requirements

# Author:   Ronald Bradford http://ronaldbradford.com/mysql-dba

#——————————————————————————-

#——————————————————————————-

# Script Definition

#

SCRIPT_NAME=`basename $0 | sed -e “s/.sh$//”`

SCRIPT_VERSION=”0.01 DD-MMM-YYYY $Id$”

SCRIPT_REVISION=”$Revision”

#——————————————————————————-

# Constants – These values never change

#

#——————————————————————————-

# Script specific variables

#

#—————————————————————– bootstrap –

# Essential script bootstrap

#

bootstrap() {

DIRNAME=`dirname $0`

COMMON_SCRIPT_FILE=”${DIRNAME}/common.sh”

[ ! -f “${COMMON_SCRIPT_FILE}” ] && echo “ERROR: You must have a matching ‘${COMMON_SCRIPT_FILE}’ with this script ${0}” && exit 1

. ${COMMON_SCRIPT_FILE}

set_base_paths

return 0

}

#———————————————————————– help –

# Display Script help syntax

#

help() {

echo “”

echo “Usage: ${SCRIPT_NAME}.sh -X <example-string> [ -q | -v | –help | –version ]”

echo “”

echo ” Required:”

echo ”    -X         Example mandatory parameter”

echo “”

echo ” Optional:”

echo ”    -q         Quiet Mode”

echo ”    -v         Verbose logging”

echo ”    –help     Script help”

echo ”    –version Script version (${SCRIPT_VERSION}) ${SCRIPT_REVISION}”

echo “

echo ” Dependencies:”

echo ” common.sh”

return 0

}

#————————————————————– process_args –

# Process Command Line Arguments

#

process_args() {

check_for_long_args $*

debug “Processing supplied arguments ‘$*'”

while getopts X:qv OPTION

do

    case “$OPTION” in

      X) EXAMPLE_ARG=${OPTARG};;

      q) QUIET=”Y”;;

      v) USE_DEBUG=”Y”;;

    esac

done

shift `expr ${OPTIND} – 1`

[ -z “${EXAMPLE_ARG}” ] && error “You must specify a sample value for -X. See –help for full instructions.”

return 0

}

#———————————————————————– main –

# Main Script Processing

#

main () {

[ ! -z “${TEST_FRAMEWORK}” ] && return 1

bootstrap

process_args $*

commence

complete

}

main $*

# END

3.5.2 example.sh Output

Default invocation

$ ./example.sh 

20090922 10:44:29 -0700 ERROR [example] You must specify a sample value for -X. See –help for full instructions.

20090922 10:44:29 -0700 INFO  [example] Exiting with status code of ‘1’

Help with –help

$ ./example.sh –help

Usage: example.sh -X <example-string> [ -v | –help | –version ]

Required:

    -X         Example Mandatory parameter

Optional:

    -v         Vebose logging

    –help     Script help

    –version Script version 0.01 DD-MM-YYYY

Version with –version

./example.sh –version

Name: example.sh  Version:  0.01 DD-MMM-YYYY

Correct invocation (using mandatory argument)

$ ./example.sh -X xxx

20090922 10:44:35 -0700 INFO  [example] Script started (Version: 0.01 DD-MMM-YYYY)

20090922 10:44:35 -0700 INFO  [example] Script completed successfully (0 secs)

3.5.2 example.sh Usage

This is to be used when creating any new scripts. If the example.sh script is modified, the change should be manually reflected in ALL existing scripts at the time of the change to ensure a level of consistency of all scripts.

4. General Standards

4.1 Long running scripts

A built in function check_stop_file is to be used for long running loops. This enables the user to gracefully stop processing by creating what is called a STOP_FILE.

For example.

$ admin/scripts/run_cmd.sh -h admin/etc/servers.mysql.txt -c hostname

20091029 19:47:29 +0000 INFO [run_cmd] Script started (Version: 0.02 28-OCT-2009 $)
20091029 19:47:29 +0000 INFO [run_cmd] Log file for host output can be found in ‘admin//log/run_cmd.20091029.1947.nsh1.log’
20091029 19:47:30 +0000 INFO [run_cmd] Processing hosts in file ‘admin/etc/servers.mysql.txt’
20091029 19:47:30 +0000 INFO [run_cmd] Running Command ‘hostname’
20091029 19:47:30 +0000 INFO [run_cmd] . . . prdmydb1.example.com
20091029 19:47:30 +0000 INFO [run_cmd] . . . prdmydb2.example.com
20091029 19:47:30 +0000 INFO [run_cmd] . . . betamysql01.example.com
20091029 19:47:30 +0000 INFO [run_cmd] . . . bfprdmydb1a.example.com
20091029 19:47:31 +0000 INFO [run_cmd] . . . bfprdmydb1b.example.com
20091029 19:47:31 +0000 INFO [run_cmd] . . . bfprdmydb2a.example.com
20091029 19:47:31 +0000 INFO [run_cmd] . . . bfhbetamydb1a.beta.example.com
20091029 19:47:31 +0000 INFO [run_cmd] . . . bfhbetamydb1b.beta.example.com
…

Rather then killing the process, you can:

$ touch /tmp/run_cmd.stop

which results in

…
20091029 19:47:34 +0000 INFO [run_cmd] . . . cemwcmsmysql01.eao.abn-sjc.ea.com
20091029 19:47:37 +0000 WARN [run_cmd] A stop file was provided to stop script processing.
20091029 19:47:37 +0000 INFO [run_cmd] Script completed successfully (8 secs)

The script will gracefully remove the file when completed, so the process can re-commence without incident. However if a file is found, then this results in an error when starting the script.

$ admin/scripts/run_cmd.sh -h admin/etc/servers.mysql.txt -c hostname
20091029 19:47:48 +0000 ERROR [run_cmd] An existing stop file ‘/tmp/run_cmd.stop’ exists, remove this to start processing
20091029 19:47:48 +0000 INFO [run_cmd] Exiting with status code of ‘1’

4.2 MySQL Standards

4.2.1 Environment Variables

MYSQL_HOME is never to be specified in a script by default. This should always be defined and accepted by the calling environment.
The MySQL binaries should always be included within the default path and not specifically included in any script.

This information is managed by the standard profile. All servers should have the following file defined.

/etc/profile/mysql.sh

This file should define the following variables.

MYSQL_HOME=/opt/mysql
PATH=${MYSQL_HOME}/bin:$PATH

4.2.2 Authenticated Access

A ~root/.my.cnf with a password for the MySQL ‘root’ super user should never be defined. This gives anybody with Operating System root access, and without any knowledge or understanding of MySQL full power to do anything in MySQL. The MySQL ‘root’ user, especially the users with WITH GRANT OPTION privileges should never be used for automated processing.
For processing of information when a specific task can be performed without a user requiring SUPER privilege it is sufficient to defined the username and password within a script. This user must also have only localhost permissions. For example, to view the processes with SHOW FULL PROCESSLIST, a localhost only user can be defined with the PROCESS privilege.
For scripts requiring SUPER privileges greater care is necessary. The mysql user should be restricted to localhost permissions, which requires the script to be run locally. A ~user/.my.cnf file can be used when a separate user providing additional operating system security including the user with no login permissions exists, and scripts can be executed via root with su – user command or via sudo command. While ideal, this is impractical for EA at this time due to delegated security among groups.
There is no other authentication model appropriate for MySQL access, the need to supply a username and password is the only option. After restricting access of scripts to given requirements, and physical execution access, the current ~mysql/.my.cnf #eadba approach is a sufficient implementation of password obfuscation.
If a global non localhost SUPER privileged user be required for database administration, it’s password should not be in a file as a general security precaution.

The admin/scripts/mysql.sh script should be used as this is a central location of authentication management for MySQL SUPER access. This script was specifically written to enable a change in the model of authenticated access in a single location. This scripts accepts a single SQL Statement, and can also if specified with -t, provide a single value output for ease of use in calling scripts.

$ ./mysql.sh –help

Usage: mysql.sh [ -t | -q | -v | –help | –version ] ‘SQL COMMAND’

  Required:

  Optional:
   -q Quiet Mode. Errors and Warnings only
   -t Tail output to last line. Used for checklist
   -v Verbose logging
   –help Script help
   –version Script version (0.01 22-OCT-2009 $)