1

Below is the process I am doing manually and I want to automate it in Python.

  1. I am opening SSH terminal to MySQL server using below command
cf ssh -L 63380:100.120.11.22:3380 appname
  1. Once this SSH is established I am doing ctrl+t to open a new terminal tab and run below command to create DB objects
mysql -u myuser -h 0 -pmypassword -D mydbname -P 3380 < init_db/mysql/schema.ddl

Now I have written Python code for step 1 like below

process = subprocess.Popen("cf ssh -L 63380:100.120.11.22:3380 appname",shell = True)

It opens SSH terminal but when ran second step using below code it does not connect to opened SSH terminal it says not able to connect to MySQL server.

pro = subprocess.Popen("mysql -u myuser -h 0 -pmypassword -D mydbname -P 3380 < init_db/mysql/schema.ddl", stdout=subprocess.PIPE,shell=True, preexec_fn=os.setsid)

I have searched and tried other options like writing step 2 in different program and calling from first program after SSH is established but of no use.

Kindly let me know if any solution is available in Python for this. Env: mac, pcf.

ggorlen
  • 44,755
  • 7
  • 76
  • 106
Vivian
  • 11
  • 1
  • You shold take a look at the answers [here](https://stackoverflow.com/questions/9470584/python-paramiko-run-command) – yorodm Aug 24 '18 at 18:16
  • My query is bit different. I have looked at the previous problems raised and most of them are related to open ssh terminal and run commands in that terminal but my problem is I need to open different terminal to run a command. – Vivian Aug 24 '18 at 18:28
  • Sorry, do you mean you need to automate interacting with the UI too? – yorodm Aug 24 '18 at 18:31
  • No, just open ssh terminal and run command in different terminal. – Vivian Aug 24 '18 at 18:34
  • So your command doesn't need to be run over the ssh connection? – yorodm Aug 24 '18 at 18:36
  • Not the opened terminal(SSH) but in another terminal it will connect to opened terminal and run command – Vivian Aug 24 '18 at 18:46

2 Answers2

0

It looks to me like this command:

cf ssh -L 63380:100.120.11.22:3380 appname

ssh's to your CloudFoundry application server and forwards any connection on your local machine's port 63380 to the remote port 3380. So it seems like your mysql connection should be:

mysql -u myuser -h localhost -pmypassword -D mydbname -P 63380 < init_db/mysql/schema.ddl

where init_db/mysql/schema.ddl is on your local machine.

I'm not completely sure if this will work with CloudFoundry, but with ssh you can run a remote command like this:

ssh username@<host or ip> <command>
#for example:
ssh myname@example.com ls -al

So you might try putting ~/init_db/mysql/schema.dll on the server and use this command:

cf ssh appname mysql -u myuser -pmypassword -Dmydbname -P 3380 < ~/init_db/mysql/schema.dll

You should be able to drop that command into python's subprocess.Popen().

nbwoodward
  • 2,816
  • 1
  • 16
  • 24
  • Thank you for looking at the issue. But it is not working. First I need to open ssh and then in second command I need to fire mysql command. I can not combine both into one. Atleast in case of CF it is not working. More details of steps can found here. https://github.com/pivotal-cf/PivotalMySQLWeb. I want to achieve this in python. – Vivian Aug 24 '18 at 19:48
  • I have not tried to do ls -al but to run mysql command and it did not work. – Vivian Aug 24 '18 at 20:24
  • If you can run `cf ssh myapp ls -al` then you can find a mysql command that will work. – nbwoodward Aug 24 '18 at 22:58
  • Just to clarify, you are suggesting to run only one command or two commands? If two commands then problem remains the same. If I run ssh command and then inside ssh I will not find mysql client. – Vivian Aug 26 '18 at 12:42
  • What I'm suggesting is you should be able to run a command over ssh on the remote server. Is the database on the server, or on your local machine? – nbwoodward Aug 27 '18 at 14:26
  • Database is again a pcf service. We connect to that service from local machine to import ddl script. – Vivian Aug 27 '18 at 14:49
0

I found way of avoiding the ssh to mysql. There is a cf mysql plugin by which we can connect to cf mysql services and execute the queries. More details can be found at: https://github.com/andreasf/cf-mysql-plugin

Vivian
  • 11
  • 1