MySQL bash template with logging and etc

mysql_bash_template.sh

#!/usr/bin/env bash

#
# Program: MySQL bash template with logging and etc <mysql_bash_template.sh>
#
# Author: Mikhail Grigorev <sleuthhound at gmail dot com>
#
# Current Version: 1.0
#
# Revision History:
#
#  Version 1.0
#    Initial Release
#
# License:
#  This program is distributed in the hope that it will be useful,
#  but WITHOUT ANY WARRANTY; without even the implied warranty of
#  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

# MySQL connection settings
# 0 - Use host/port/login/password
# 1 - Use login path (mysql_config_editor set --login-path=ZZZZZ --host=HOST --user=USER --password)
# 2 - Use $HOME/.my.cnf
MYSQL_CONNECT_METHOD=0
# 0 - Use host/port/login/password
MYSQL_HOST=127.0.0.1
MYSQL_PORT=3306
MYSQL_USER=root
MYSQL_PASSWORD=XXXXX
# 1 - Use login path
MYSQL_LOGIN_PATH=ZZZZZ
# 2 - Use $HOME/.my.cnf
MYSQL_MYCNF_PATH="$HOME/.my.cnf"
# MySQL additions options
MYSQL_ADDITIONS_OPTS="--ssl-mode=DISABLED"

# Don't edit this config
SOURCE="${BASH_SOURCE[0]}"
while [ -h "$SOURCE" ]; do
	DIR="$(cd -P "$(dirname "$SOURCE")" && pwd)"
	SOURCE="$(readlink "$SOURCE")"
	[[ $SOURCE != /* ]] && SOURCE="$DIR/$SOURCE"
done
SCRIPT_DIR="$(cd -P "$(dirname "$SOURCE")" && pwd)"
SCRIPT_NAME=$(basename "$0")

# Log file path + name
LOG_FILE=/var/log/${SCRIPT_NAME%.*}.log
# Stop file path + name
STOP_FILE=${SCRIPT_DIR}/${SCRIPT_NAME%.*}.stop
# Realtime config path + name
RT_CONF_FILE=${SCRIPT_DIR}/${SCRIPT_NAME%.*}.conf

# Check command exist function
_command_exists() {
	type "$1" &>/dev/null
}

# Don't edit this config
LOG_FILE_WRITABLE=1
LOG_FILE_EXIST_WRITABLE=1
DATE_START=$(date +"%s")

# Logging function
_logging() {
	local MSG=${1}
	local ENDLINE=${2:-"1"}
	if [[ "${ENDLINE}" -eq 0 ]]; then
		printf "%s: %s" "$(date "+%d.%m.%Y %H:%M:%S")" "${MSG}" 2>/dev/null
	else
		printf "%s: %s\n" "$(date "+%d.%m.%Y %H:%M:%S")" "${MSG}" 2>/dev/null
	fi
	if [[ ! -f "${LOG_FILE}" && "${LOG_FILE_WRITABLE}" -eq 1 ]]; then
		touch "${LOG_FILE}" >/dev/null 2>&1
		if [ $? -ne 0 ]; then
			printf "%s: %s\n" "$(date "+%d.%m.%Y %H:%M:%S")" "WARNING: Log file '${LOG_FILE}' is not writable." 2>/dev/null
			LOG_FILE_WRITABLE=0
		fi
	fi
	if [[ -w "${LOG_FILE}" ]]; then
		printf "%s | %s: %s\n" "$(date "+%d.%m.%Y %H:%M:%S")" "$$" "${MSG}" 1>>"${LOG_FILE}" 2>&1
	else
		if [[ "${LOG_FILE_EXIST_WRITABLE}" -eq 1 &&  "${LOG_FILE_WRITABLE}" -eq 1 ]]; then
			printf "%s: %s\n" "$(date "+%d.%m.%Y %H:%M:%S")" "WARNING: Log file '${LOG_FILE}' is not writable." 2>/dev/null
			LOG_FILE_EXIST_WRITABLE=0
		fi
	fi
}

# Calculate duration function
_duration() {
	local DATE_START=${1:-"$(date +'%s')"}
	local FUNC_NAME=${2:-""}
	local DATE_END=$(date +"%s")
	local D_MSG=""
	local DATE_DIFF=$((${DATE_END} - ${DATE_START}))
	if [ -n "${FUNC_NAME}" ]; then
		local D_MSG=" of execute function '${FUNC_NAME}'"
	fi
	_logging "Duration${D_MSG}: $((${DATE_DIFF} / 3600)) hours $(((${DATE_DIFF} % 3600) / 60)) minutes $((${DATE_DIFF} % 60)) seconds"
}

# Fail, log and exit script function
_fail() {
	local MSG=${1}
	_logging "${MSG}"
	_duration "${DATE_START}"
	_logging "End script '${SCRIPT_DIR}/${SCRIPT_NAME}'. Goodbye ;)"
	exit 2
}

# trap ctrl-c and call ctrl_c()
trap ctrl_c INT

function ctrl_c() {
	_fail "** Trapped CTRL-C"
	exit 2
}

_logging "Starting script '${SCRIPT_DIR}/${SCRIPT_NAME}'"

if [[ -f "${RT_CONF_FILE}" ]]; then
	_logging "Read config file..."
	source "${RT_CONF_FILE}" 1>>"${LOG_FILE}" 2>&1
fi

_logging "Current script configuration:"
_logging "LOG_FILE: ${LOG_FILE}"
_logging "RT_CONF_FILE: ${RT_CONF_FILE}"
_logging "STOP_FILE: ${STOP_FILE}"

if [[ -f "${STOP_FILE}" ]]; then
	rm -f "${STOP_FILE}" 1>>"${LOG_FILE}" 2>&1
fi

if _command_exists mysql; then
	MYSQL_BIN=$(which mysql)
else
	_fail "ERROR: Command 'mysql' not found."
	exit 2
fi

_exec_sql() {
	local MYSQL_OPTS=${1:-"-h 127.0.0.1 -P 3306 -u root"}
	local MYSQL_SQL=${2:-"SELECT 1;"}
	local DATE_START=$(date +"%s")
	_logging "Exec ${FUNCNAME[0]} '${MYSQL_OPTS}' '${MYSQL_SQL}'"
	${MYSQL_BIN} ${MYSQL_OPTS} -BNe "${MYSQL_SQL}" 1>>"${LOG_FILE}" 2>&1
	if [ $? -eq 0 ]; then
		_logging "Done."
	else
		_logging "WARNING: SQL query not complete, see log file."
	fi
	_duration "${DATE_START}" "${FUNCNAME[0]}"
}

case "${MYSQL_CONNECT_METHOD}" in
0)
	MYSQL_CONN_STRING="-h ${MYSQL_HOST} -P ${MYSQL_PORT} -u ${MYSQL_USER} -p${MYSQL_PASSWORD} ${MYSQL_ADDITIONS_OPTS}"
	;;
1)
	MYSQL_CONN_STRING="--login-path=${MYSQL_LOGIN_PATH} ${MYSQL_ADDITIONS_OPTS}"
	;;
2)
	MYSQL_CONN_STRING="--defaults-file=${MYSQL_MYCNF_PATH} ${MYSQL_ADDITIONS_OPTS}"
	;;
*)
	MYSQL_CONN_STRING="-h ${MYSQL_HOST} -P ${MYSQL_PORT} -u ${MYSQL_USER} -p${MYSQL_PASSWORD} ${MYSQL_ADDITIONS_OPTS}"
	;;
esac

if ! $(echo 'exit' | ${MYSQL_BIN} "${MYSQL_CONN_STRING}" -s 1>>"${LOG_FILE}" 2>&1); then
	_fail "ERROR: Cannot connect to MySQL, please check connection settings and log file '${LOG_FILE}'."
fi

_exec_sql "${MYSQL_CONN_STRING}" "SELECT 1;"

_logging "All done."
_duration "${DATE_START}"

_logging "End script '${SCRIPT_DIR}/${SCRIPT_NAME}'. Goodbye ;)"