# mysql-based aliases and functions ---------------------------------------------
mysqlRootUser='root'
mysqlRootPassword='XXXXXXXXXXXXXXX'
mysqlUser='timeless'
mysqlPassword='XXXXXXXXXXXXXXX'
# portlist to try for mAll and sssAll
mysqlPortList='3306 3307 3308 3309 30306 30307'
# connect as root user
alias mRoot='mysql -u'$mysqlRootUser' --password="'$mysqlRootPassword'"'
# connect to the socket - hope ps has it
alias mSock='mRoot -S `ps auxww | grep mysqld | grep socket | awk -F "socket=" '\''{print $2}'\''`'
# connect to all MySQLs on the machine
function mAll {
	# usage: mAll <server>
	noMySQL=" + No MySQL ports: "
	for i in $mysqlPortList ; do
		mysql -u$mysqlUser -p$mysqlPassword -h $1 -P$i --prompt="$1:$i> " 2>/dev/null
		if [ $? != 0 ] ; then
			noMySQL="$noMySQL$i "
		fi
	done
	echo "$noMySQL"
}
function mOne {
	# usage: mOne <server[:port[:db]]>
	if [[ $1 =~ ^(.*?):(.*?):(.*?)$ ]] ; then
		hostname=${BASH_REMATCH[1]}
		port=${BASH_REMATCH[2]}
		database=${BASH_REMATCH[3]}
	elif [[ $1 =~ ^(.*?):(.*?)$ ]] ; then
		hostname=${BASH_REMATCH[1]}
		port=${BASH_REMATCH[2]}
	else
		# a sensible default - someday hopefully this will be 3306
		hostname=$1
		port=3306
		database=''
	fi

	if [ "xxx$hostname" == "xxx" ] ; then hostname=127.0.0.1 ; fi
	if [ "xxx$port" == "xxx" ] ;     then port=3306 ; fi
	if [ "xxx$database" == "xxx" ] ; then database='' ; fi

	mysql -u$mysqlUser -p$mysqlPassword -h $hostname -P $port --prompt="$hostname:$port> " $database
}
function sss {
	# usage: sss <server[:port]>
	echo 'show slave status\G' |
	mOne $1 |
	egrep '_Err|Seconds|Master_Log_Pos|Master_Host|_State' |
	egrep -v 'Err..: $|Err..: 0$'
}
function sssExtended {
	# usage: sssExtended <server[:port]>
	echo 'show slave status\G' | mOne $1
}
function sssAll {
	# usage: sssAll <server>
	noMySQL=" + No MySQL ports: "
	for i in $mysqlPortList ; do
		# don't show blank/0 errors, but do show blank/0 Seconds behind master etc
		oput=`echo 'show slave status\G' | mOne $1:$i 2>/dev/null |
		      egrep '_Err|Seconds|Master_Log_Pos|Master_Host|_State' |
		      egrep -v 'Err..: $|Err..: 0$'`
		if [ $? == 0 ] ; then
			echo " + ===== port:$i"
			echo "$oput"
		else
			noMySQL="$noMySQL$i "
		fi
	done
	echo "$noMySQL"
}
function sts {
	# usage: sts <server[:port[:db]]>
	echo 'select concat(table_schema,'\''.'\'',table_name) as tbl,
	             engine,table_rows,data_length,index_length
	        from information_schema.tables
	       where table_schema not in ('\''information_schema'\'', '\''mysql'\'')' | mOne $1
}
function sct {
	# usage: sct <server[:port[:db]]> <table>
	echo "show create table $2" | mOne $1 | sed -e 's/\\n/\n/g'
}