Bash script for interactive ssh and mysql commands

Question

I'm studying MySQL, and every time I have to

Enter ssh XXX@XXX command, and enter my password to the school server.
Enter mysql -u XXX -p command, and enter MySQL password.

I want to create a Bash script for performing the steps above automatically.

I can accomplish the first step with this code:

#!/usr/bin/expect -f    
set address xxx.com   
set password xxx  
set timeout 10   
spawn ssh xxx@$address   
expect {    "*yes/no" { send "yes\r"; exp_continue}    "*password:" { send "$password\r" }  }  
send clear\r 
interact

But I don't know how to automatically input the next command (mysql -u xxx -p) and the password.

How can I do this?

Ruslan Osmanov · Accepted Answer · 2016-11-05 04:17:59Z

5

You don't need such a complex script to just enter the MySQL console on remote machine. Use the features of the ssh tool:

ssh -tt user@host -- mysql -uuser -ppassword

The -t option forces pseudo-terminal allocation. Multiple -t force tty allocation, even if ssh has no local tty (see man ssh). Note the use of -p option. There must be no spaces between -p and password (see the manual for mysql).

Or even connect via mysql directly, if the MySQL host is accessible from your local machine:

mysql -hhost -uuser -p

Don't forget to adjust the shebang:

#!/bin/bash -

edited Nov 5, 2016 at 4:17

answered Nov 5, 2016 at 3:21

Ruslan Osmanov

21.8k8 gold badges52 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

J.Pei Over a year ago

What if I want to create a bash file for running these steps, I mean I just open the file, than all the commands will be correctly input in terminal.

Ruslan Osmanov Over a year ago

@J.Pei, you can put the command into a file, of course. Don't forget to adjust the she-bang line accordingly.

J.Pei Over a year ago

So, the effectiveness of "-tt" is combine the 2 commands(ssh ... and mysql ...) to one command line?@Ruslan Osmanov

Ruslan Osmanov Over a year ago

@J.Pei, no, -tt forces pseudo-terminal allocation. And you need a pseudo-terminal for interactive session. Otherwise, the session may stall waiting for input, or even fail.

Lucky Singh · Accepted Answer · 2016-11-05 05:04:25Z

-1

Use my.cnf to store your password securly like ssh keys.

https://easyengine.io/tutorials/mysql/mycnf-preference/

Same way ssh is also possible through ssh -i parameter and passing the private key path of the remote host.

Best of luck!

answered Nov 5, 2016 at 5:04

Lucky Singh

11 bronze badge

Collectives™ on Stack Overflow

Bash script for interactive ssh and mysql commands

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related