==== A tool to mimic SQL*PLus but for MySQL ====
* The script below is called "msql" and the syntax:
* msql username/password@instance:schema
* Location: /usr/local/bin
* TNS File location: /etc/mysql/network/admin/tnsnames.mysql
** Usage **
msql /@entry:schema
** An example entry **
db01,db =
(
(connect_data =
(host = db1.mikelivolsi.com)
(schema = some_schema)
(port = 3306)
)
)
** The code to make it all work **
#!/bin/bash
#-------------------------------------------------------------------------------------------
# Program: msql (wrap around to MySQL )
# Author : Mike LiVolsi
#-------------------------------------------------------------------------------------------
typeset -i verboseFlag=0 # Verbosity
typeset -i listOnlyFlag=0 # only show the command, not actually invoke it
declare -A argArray #
typeset -i argCount=0
silent=""
#-------------------------------------------------------------------------------------------
function syntax {
clear
cat << EOF
msql
syntax: msql username/password@entry:dbname
-v : enables verbose output
-s : silent flag
-l --list : list only
Notes: If MYSQL_ADMIN isn't specified, the default is /etc/mysql/network/admin
If the entry '@' isn't specified, then this script looks for the environment
variable MYSQL_TASK.
If dbname is specified on the command line, it will supersede the entry
in tnsnames
Database entries are specified in the tnsnames.mysql file.
The default is: /etc/mysql/network/admin/tnsnames.mysql
EOF
}
#-------------------------------------------------------------------------------------------
# Parse all the command line arguments
#-------------------------------------------------------------------------------------------
COMMAND="mysql"
while (( $# > 0 ))
do
arg1=$1
if (( $verboseFlag == 1 )); then
echo "[v] processing argument: $1"
fi
case $arg1 in
"-?")
syntax
return
;;
"-v")
verboseFlag=1;
;;
"-l" | "--list")
COMMAND="echo mysql"
listOnlyFlag=1
;;
"-s")
silent="-s"
;;
*)
argArray[$argCount]=`echo $arg1 | tr [A-Z] [a-z]`
argCount=$argCount+1
;;
esac
shift
done
argList=""
typeset -i looper=0
while (( $looper == 0 ))
do
argList+=${argArray[$looper]}
looper=$looper+1
done
if (( $verboseFlag == 1 )); then
echo "[v] arguments: $argList"
fi
#-------------------------------------------------------------
# The user should pass /@
# so it's similar to Oracle's connect string
# the following then parses that
#-------------------------------------------------------------
userInfo=`echo $argList | cut -d '@' -f1`
dbInfo=`echo $argList | cut -d '@' -f2`
uName=`echo $userInfo | cut -d '/' -f1`
uPwd=`echo $userInfo | cut -d '/' -f2`
entry=`echo $dbInfo | cut -d':' -f1`
schemaArg=""
typeset -i colon=`echo $dbInfo | grep ':' | wc -c`
if (( $colon > 0 )); then
schemaArg=`echo $dbInfo | cut -d':' -f2`
fi
if (( $verboseFlag == 1 )); then
echo "[v] entry : $entry"
echo "[v] username : $uName"
echo "[v] password : $uPwd"
echo "[v] schema : $schemaArg"
fi
#------------------------------------------------------
# MYSQL_TASK - Which DB to connect to in the tnsnames
#------------------------------------------------------
if (( $verboseFlag == 1 )); then
echo "[v] MYSQL_TASK: $MYSQL_TASK - ENTRY: ${entry}"
fi
if (( ${#MYSQL_TASK} == 0 )) && (( ${#entry} == 0 )); then
echo "Error: you must specify the environment variable MYSQL_TASK or connect string '@' on the command line"
return 1
fi
#------------------------------------------------------
# MYSQL_ADMIN - Where to find stuff
#------------------------------------------------------
if (( ${#MYSQL_ADMIN} == 0 )); then
MYSQL_ADMIN="/etc/mysql/network/admin"
fi
if [ ! -d ${MYSQL_ADMIN} ]; then
echo "No such directory specified : $MYSQL_ADMIN"
return 1
fi
if [ ! -f ${MYSQL_ADMIN}/tnsnames.mysql ]; then
echo "File not found: $MYSQL_ADMIN/network/admin/tnsnames.mysql"
return 1
fi
#---------------------------------------------------------
# MYSQL_DB or variable '@xxxxx' - which db in the tnsnames
#---------------------------------------------------------
if (( ${#entry} == 0 )); then
if (( ${#MYSQL_DB} == 0 )); then
echo "You did not specify the parameter: MYSQL_DB"
return 1
fi
else
MYSQL_DB=$entry
fi
if (( $verboseFlag == 1 )); then
echo "[v] processing file: ${MYSQL_ADMIN}/tnsnames.mysql"
fi
typeset -i entryFlag=0
typeset -i portFlag=0
typeset -i hostFlag=0
typeset -i schemaFlag=0
typeset -i lineNum=0
typeset -i bracketCount=0
IFS=''
while read -r line || [[ -n "$line" ]]; do
lineNum+=1;
line=`echo $line | tr [A-Z] [a-z]`
bracketCount=${bracketCount}+$(echo $line | awk -F"(" '{print NF-1}')
bracketCount=${bracketCount}-$(echo $line | awk -F")" '{print NF-1}')
if (( $bracketCount > 0 )); then
name=`echo $line | cut -d'(' -f2 | cut -d '=' -f1 | xargs echo -n`
value=`echo $line | cut -d'=' -f2 | cut -d ')' -f1 | xargs echo -n`
if (( $entryFlag == 1 )); then
case $name in
'port')
if (( $portFlag == 1 )); then
echo "Error[1]: Syntax error at line ${lineNum} in tnsnames.mysql"
return
else
port=$value;
portFlag=1
fi
;;
'host')
if (( $hostFlag == 1 )); then
echo "Error[2]: Syntax error at line ${lineNum} in tnsnames.mysql"
return
else
host=$value;
hostFlag=1
fi
;;
'schema')
if (( $schemaFlag == 1 )); then
echo "Error[3]: Syntax error at line ${lineNum} in tnsnames.mysql"
return
else
schema=$value;
schemaFlag=1
fi
;;
esac
fi
else
#-------------------------------------------------------------------------------------
# let's look FOR an entry entry (we haven't found it yet). If it has multiple entries, go through the list
#-------------------------------------------------------------------------------------
if (( ${#line} != 0 )) && (( ${entryFlag} == 0 )) ; then # Also note that braketCount= 0
line=`echo $line | cut -d'=' -f1` # everything left of the '=' sign
firstChar=`echo $line | cut -c1` # look for hash (comment)
firstChar=${firstChar:-'"'}
if [ $firstChar != '#' ] && [ $firstChar != ')' ]; then
OIFS=${IFS}
IFS=","
for x in ${line}
do
x=$(echo $x | sed -e 's/[[:space:]]*$//')
if [ $x == $MYSQL_DB ]; then
entryFlag=1
fi
done
fi
IFS=${OIFS}
fi
fi
#---------------------------------------------------
# We have everything. Let's not waste time
#---------------------------------------------------
if (( ${portFlag} == 1 )) && (( ${hostFlag} == 1 )); then # we have port and host
if (( ${#schemaArg} > 0 )); then
schema=$schemaArg
schemaFlag=1
break;
else
if (( ${schemaFlag} == 1 )); then
break;
fi
fi
fi
done < ${MYSQL_ADMIN}/tnsnames.mysql
#----------------------------------------------------------------------------------------------------
# Done processing tnsnames
#----------------------------------------------------------------------------------------------------
if (( $verboseFlag == 1 )); then
echo "[v] entry : $entry"
echo "[v] username : $uName"
echo "[v] password : $uPwd"
echo "[v] schema : $schema"
echo "[v] host : $host"
echo "[v] port : $port"
fi
if (( $entryFlag == 0 )); then
echo "Could not find entry in tnsnames.mysql"
else
if (( (( ${portFlag} == 0 )) || (( ${hostFlag} == 0 )) || (( $schemaFlag == 0 )) )); then
echo "Error with the tnsnames.mysql - Not all fields were specified for the entry"
else
if (( $verboseFlag == 1 )); then
echo "[v] mysql -u${uName} -p${uPwd} -h ${host} -D${schema} -P${port}"
fi
${COMMAND} -A -u${uName} -p${uPwd} -h ${host} -D${schema} -P${port} ${silent}
fi
fi