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!
PlanetMySQL Voting:
Vote UP /
Vote DOWN