If you have multiple database servers with strange names, or if you have to hop over multiple machines to connect to any mysql database server, then you know what a pain it can be to administer such a setup. Thanks to some scripting, you can automate such tasks as follows:

Create an expect script:
/path/to/sshmysql.exp

#!/usr/bin/expect -f
#script by darren cassar
#mysqlpreacher.com

set machine [lindex $argv 0]

set timeout -1

spawn ssh username@$machine
match_max 100000
expect -exact “assword: “
send — “password\r”
send — “sudo -k; sudo su – mysql\r”
expect -exact “sudo -k; sudo su – mysql”
expect -exact “assword:”
send — “password\r”
interact

# you should change the word password in ‘send — “password\r”‘ to your login password
# if you have the same password for each environment you could also script logging into mysql directly from the same expect script BUT that is not recommended.

Create a bash script:
/path/to/login.sh

#!/bin/bash
#script by darren cassar
#mysqlpreacher.com

sm=’/path/to/sshmysql.exp’

menu() {
echo ” 101 – dev.databaseserver1 “
echo ” 102 – dev.databaseserver2 “
echo ” 103 – dev.databaseserver3 “
echo ” 201 – qa.databaseserver1 “
echo ” 301 – uat.databaseserver1 “
echo ” 302 – uat.databaseserver2 “
echo ” 401 – prod.databaseserver1 “
echo ” “
}

ARGUMENT=notmenu

if [ -z "$1" ]
then
ARGUMENT=menu
else
choice=$1
fi

if [ $ARGUMENT = "menu" ]
then
menu
else
case “$choice” in
101|dev.databaseserver1 ) $sm dev.databaseserver1;;
102|dev.databaseserver2 ) $sm dev.databaseserver2;;
103|dev.databaseserver3 ) $sm dev.databaseserver3;;
201|qa.databaseserver1 ) $sm qa.databaseserver1;;
301|uat.databaseserver1 ) $sm uat.databaseserver1;;
302|uat.databaseserver2 ) $sm uat.databaseserver2;;
401|prod.databaseserver1 ) $sm prod.databaseserver1;;
* ) echo “Wrong value passed to script”
menu ;;
esac
fi

alias l=’/path/to/login.sh’

Output:

[darrencassar@mymachine ~ ]$ l
101 – dev.databaseserver1
102 – dev.databaseserver2
103 – dev.databaseserver3
201 – qa.databaseserver1
301 – uat.databaseserver1
302 – uat.databaseserver2
401 – prod.databaseserver1

Output:
The below command would log you into the first development database server as mysql user.

[darrencassar@mymachine ~ ]$ l 101

On each machine place aliases for each instance in the .profile

alias use3306=’mysql -u root -p -h 127.0.0.1 -P 3306 –prompt=”mysql \D> “‘

The above setup can be used using any client/server OS: Linux, Solaris, MAC OS or Windows(running Cygwin)

NOTE: If you store the password in clear text inside the expect script, you should at least save the scripts inside an encrypted partition on your machine and make sure that folder is not shared or accessible by anyone. Another way of doing it would be to use either SSHKeys OR save the password inside a file and encrypt it using OpenSSL

Enjoy!