2

I have access to a MySQL database through ssh,

Could someone direct me to a MySQL-python code that will let me do this?

I need to save my query results on my local WINDOWS computer,

Thanks,

user2333346
  • 1,083
  • 4
  • 21
  • 40
  • Have you tried using [paramiko](https://github.com/paramiko/paramiko)? – Air Aug 19 '13 at 19:07
  • Paramiko is the best python module to do ssh tunneling. I have added here the code to do exactly what you require: https://github.com/paramiko/paramiko/blob/master/demos/forward.py – yeaske May 30 '14 at 17:08

2 Answers2

0

You can use SSH port forwarding to do this.. in fact first google hit looks to walk you through this exact thing:

http://www.howtogeek.com/howto/ubuntu/access-your-mysql-server-remotely-over-ssh/

And since you're on windows, translate that to using PuTTY:

https://intranet.cs.hku.hk/csintranet/contents/technical/howto/putty-portforward.jsp

You'll then connect to localhost:3306 with your python script, SSH will forward that over to the other machine and you'll end up connecting to the remote mysql instance.

hexist
  • 5,151
  • 26
  • 33
  • I apologize; it seems my question is unclear, I can manually ssh to the database and run queries, I was wondering if you can lead me to a sample MySQL-PYTHON code that will do this for me? Something [sample code] that has the whole connection + Query in it Thanks – user2333346 Aug 19 '13 at 16:43
  • There's nothing special to do, simply open a mysql connection in python and do stuff. That's the beauty of ssh port forwarding, it makes it look like the mysql server is running locally. – hexist Aug 19 '13 at 16:46
  • But how can I direct the output to my local computer > output.txt, wont the output be stored on the server? – user2333346 Aug 19 '13 at 16:50
  • Nope, you'll be running your script on your local computer, so any output (or redirection of output) from that script will be on your local computer – hexist Aug 19 '13 at 16:53
  • I just realized you were on windows.. you'll have to use PuTTY to do the port forwarding.. it works the same, but you have that pretty GUI to work with. I updated the answer to point you to instructions on how to setup port forwarding using PuTTY - use port 3306 – hexist Aug 19 '13 at 16:57
  • To be honest with you, that was not my question, I was hoping to get some sample code that I could start from, Something that shows me how to ssh and query, I’m using Github to connect and its working fine with me, – user2333346 Aug 19 '13 at 17:01
  • OK, I managed to get a code example, and I got it to work on my system, thanks everyone – user2333346 Aug 19 '13 at 20:10
0

You need to open up an SSH Tunnel to your sql server and then you can run paramiko to connect locally to the port you are using locally. This is done quite easily in *nix systems and I am sure you can download ssh command line too for windows. Try putty or plink, see here. What I do is I run a shell script like so, then I execute my paramiko python script, then I kill the 

ssh -N remote_server@54.221.226.240 -i ~/.ssh/my_ssh_key.pem -L 5433:localhost:5432 
python paramiko_connect.py
kill pkill -f my_ssh_key.pem  # kill using the pattern, 
#see ''ps aux | grep my_ssh_key.pem'' to see what it will kill

-N means don't execute any commands, -L is the local port to tunnel from, followed by the remotes server port, assuming you are connected to that server already.

Works like a charm for me for my postgres server & I did try it on mysql too.

Community
  • 1
  • 1
radtek
  • 34,210
  • 11
  • 144
  • 111