Select Git revision
-
Hahn Axel (hahn) authoredHahn Axel (hahn) authored
mysql.sh 7.43 KiB
# ================================================================================
#
# LOCALDUMP :: MYSQL / MARIADB
# create gzipped plain text backups from each scheme
#
# --------------------------------------------------------------------------------
# ah - Axel Hahn <axel.hahn@iml.unibe.ch>
# ds - Daniel Schueler <daniel.schueler@iml.unibe.ch>
#
# 2016-11-10 ah,ds v0.8 needs to be testet
# 2017-03-28 ..... v1.0 added restore
# 2022-01-20 ah v1.1 fixes with shellcheck
# 2022-02-14 ah v2.0 rewrite with class like functions
# 2022-02-18 ah v2.1 WIP: added counters
# 2022-03-17 v1.2 WIP: add lines with prefix __DB__
# ================================================================================
if [ -z "$LOCALDUMP_LOADED" ]; then
echo "ERROR: you cannot start $(basename $0) directly. Start localdump.sh instead."
rc+=1
exit 1
fi
# --------------------------------------------------------------------------------
# CONFIG
# --------------------------------------------------------------------------------
# flag: service was foound locally? One of 0|1
mysql_FOUND=0
# counters
typeset -i mysql_COUNT_CREATE=0
typeset -i mysql_COUNT_DUMPS=0
typeset -i mysql_COUNT_DB=0
typeset -i mysql_COUNT_ERRORS=0
SOURCE_DIR=/var/lib/mysql
# --------------------------------------------------------------------------------
# PRIVATE FUNCTIONS
# --------------------------------------------------------------------------------
# make checks if a service is available on this machine
# it sets mysql_FOUND as flag
function mysql._check(){
j_requireBinary "mysql" 1
j_requireBinary "mysqldump" 1
j_requireProcess "mysqld|mariadb" 1
if [ ! -d $SOURCE_DIR ]; then
echo "INFO: directory $SOURCE_DIR doees not exist."
rc=$rc+1
fi
# set flag and reset return code
test $rc -eq 0 && mysql_FOUND=1
test $rc -eq 0 || mysql_COUNT_ERRORS+=1
rc=0
}
# --------------------------------------------------------------------------------
# PUBLIC FUNCTIONS I :: DB SCHEME - METHODS LOW LEVEL
# --------------------------------------------------------------------------------
# create a database scheme
# param string name of the dabase scheme
function mysql.db.create(){
local _dbname=$1
echo "CREATE DATABASE IF NOT EXISTS ${_dbname};" | mysql
fetchrc >/dev/null
test $myrc -eq 0 && mysql_COUNT_CREATE+=1
test $rc -eq 0 || mysql_COUNT_ERRORS+=1
}
# dump [database] --> [file]
# dump a single database into given file
# param string name of database to dump
# param string name of output file
function mysql.db.dump(){
local _dbname=$1
local _dumpfile=$2
mysqldump --opt \
--default-character-set=utf8 \
--flush-logs \
--single-transaction \
--no-autocommit \
--result-file="$_dumpfile" \
"$_dbname"
fetchrc >/dev/null
test $myrc -eq 0 && mysql_COUNT_DUMPS+=1
test $rc -eq 0 || mysql_COUNT_ERRORS+=1
test $rc -eq 0
}
# import [file] --> [database]
# import a single db dump into a given db
# param string name of file
# param string name of target db scheme
function mysql.db.import(){
local _dumpfile=$1
local _dbname=$2
zcat "$_dumpfile" | mysql "${_dbname}"
fetchrc >/dev/null
test $myrc -eq 0 && mysql_COUNT_IMPORT+=1
test $rc -eq 0 || mysql_COUNT_ERRORS+=1
}
# show a list of existing databases
function mysql.db.list(){
# mysql -Ee "show databases ;" | grep "^Database:" | awk '{ print $2 }'
local _result=$( mysql -Ee "show databases ;" )
fetchrc >/dev/null
test $myrc -eq 0 && mysql_COUNT_DB=$( echo "$_result" | grep -c "^Database:" )
test $myrc -eq 0 && echo "$_result" | grep "^Database:" | awk '{ print $2 }'
test $rc -eq 0 || mysql_COUNT_ERRORS+=1
}
# --------------------------------------------------------------------------------
# PUBLIC FUNCTIONS II :: HIGH LEVEL
# --------------------------------------------------------------------------------
# return result is the current service available
# USAGE: to abort a function if not available:
# mysql.available || return
function mysql.available(){
typeset -i local _rc=(1-$mysql_FOUND)
return $_rc
}
# make checks if the current service is available on this machine
# param bool flag: silence; if any parameter is set it shows no output
function mysql.check(){
if [ -n "$1" ]; then
mysql._check >/dev/null 2>&1
else
echo
echo Details:
mysql._check
echo
fi
}
# start database backup of all schemes of this service
# no prameters
function mysql.backup(){
# abort if service is not available
mysql.available || return
local _dbname
local _outfile
create_targetdir
for _dbname in $( mysql.db.list )
do
echo -n "__DB__${SERVICENAME} backup $_dbname ... "
_outfile="${BACKUP_TARGETDIR}/$(get_outfile ${_dbname}).sql"
mysql.db.dump "$_dbname" "$_outfile"
db._compressDumpfile "$_outfile"
done
}
# show help
function mysql.help(){
# local _bShow=false
# tac "$0" | while read line
# do
# if echo $line | grep "^function mysql\.[a-z]" >/dev/null; then
# _bShow = true
# fi
# if echo $line | grep "^# " >/dev/null; then
# _bShow = true
# fi
# done
cat <<EOHELP
Help for MYSQL-DOT functions
(1) high level functions
mysql.available silent; exitcode is 0 if mysql is available
mysql.check [0|1] check if mysql is available; shows missing checks
mysql.backup backup all databases
mysql.restore [FILE [DBNAME]] restore database
(2) functions on database level
mysql.db.create DBNAME create database
mysql.db.dump DBNAME OUTFILE dump given database to output file
mysql.db.import FILE DBNAME import file into existing database
mysql.db.list list existing databases
EOHELP
}
# restore database dump file into database
# param string database dump file (gzipped)
# param string optional: database to import; default: database is parsed from file
function mysql.restore(){
# abort if service is not available
mysql.available || return
local _infile=$1
local _dbname=$2
if [ -z "$_dbname" ]; then
h2 "analyze dump $_infile"
_dbname=$(guessDB $_infile)
echo "detected db schema from file: [${_dbname}]"
else
echo "db schema from param 2: [${_dbname}]"
fi
echo
echo import to "$_dbname"...
h2 ensure that database exists ...
color cmd
# echo "CREATE DATABASE IF NOT EXISTS ${_dbname};" | mysql
mysql.db.create "${_dbname}"
color reset
h2 import ...
ls -l "$_infile"
echo "import to database [${_dbname}]"
color cmd
# zcat "$_infile" | mysql "${_dbname}"
mysql.db.import "$_infile" "${_dbname}"
fetchrc
color reset
}
# WIP: show status
function mysql.status(){
h2 "WIP: Status"
h3 "Databases (max. 15)"
mysql.db.list | head -15
h3 "Counters"
cat <<EOSTATUS
found Dbs: $mysql_COUNT_DB
created : $mysql_COUNT_CREATE
dumped : $mysql_COUNT_DUMPS
ERRORS : $mysql_COUNT_ERRORS
EOSTATUS
}
# --------------------------------------------------------------------------------
# MAIN
# --------------------------------------------------------------------------------
"${SERVICENAME}".check 1
if ! "${SERVICENAME}".available; then
echo "__DB__$SERVICENAME SKIP: service [$SERVICENAME] is not avilable on this machine."
fi
action=$1
shift 1
"${SERVICENAME}.$action" $*
echo "__DB__$SERVICENAME INFO: $0 $action $* [$SERVICENAME] final returncode rc=$rc"
# --------------------------------------------------------------------------------