Forums

Hi guys,

I'm looking at doing some DB work using a visual tool like MySQL Workbench on my laptop. My question is whether or not this is possible/supported, and what the external server address would be to do so ("mysql.server" is obvious a local address mapping).

Thanks in advance for the help.

G

You could try tunnelling the ports over SSH?

I'll take that as a "not easily" ;) and stumble along using the console for now.

Well, I wouldn't say it's trivial, but I'm pretty sure it's possible. Here's a reasonable guide:

http://www.revsys.com/writings/quicktips/ssh-tunnel.html

Basically, when you SSH into a machine, you can also ask the SSH protocol, as well as giving you a console, to map connect up one of the server's ports to one of your local ports - so that you get to see things from the server's point of view, as it were.

So what you'd want to do something like map your local port 3306 to the server's view of mysql.server:3306 (3306 is the standard MySQL port).

If you're on windows and don't have SSH, you can download PuTTy, which will also let you configure that sort of a tunnel.

Alternatively, keep using the console! it's good practice ;-)

Not only possible, but also really easy. As long as you're not running a MySQL instance locally, just invoke SSH like this:

ssh -L 3306:mysql.server:3306 geoffham@ssh.pythonanywhere.com

That -L option means "forward LOCAL port 3306 to REMOTE host mysql.server port 3306" (the port numbers can be different, but in this case the standard MySQL port would be easiest). You can also use -R to cause remote connections to be forwarded back to you, but that's a pretty unusual thing to do for most people.

REMEMBER that you need to keep your SSH session open at all times! As soon as that closes, your forwarded connection is also lost.

At this point, you should be able to run MySQL as normal. One thing to watch out for, however - many MySQL clients treat the hostname localhost as special, meaning "connect to the local server over a domain socket". What you want to do is force it to connect to your local machine on port 3306, and you can do this by specifying 127.0.0.1 for the host instead of localhost. For example, to use the command-line mysql client you'd invoke it like this:

mysql -h 127.0.0.1 -u geoffham -p

Finally, if you are running a MySQL server locally and hence port 3306 is already in use, you can modify your SSH invocation to use any other port:

ssh -L 3333:mysql.server:3306 geoffham@ssh.pythonanywhere.com

However, you'd then need to configure your MySQL client to use this other port, as this is not the default for MySQL.

Hey, thanks Cartroo! That's a really neat guide.

is it possible to gain access to my db from my Android application? External access. I would need the ip-adress and such.

In principle I supsect yes, but I'm not sure Android has the wherewithal to open SSH tunnels like that. I also don't know how much effort it is getting the MySQL libraries on Android. I suspect your life would be much easier writing a simple HTTP API as a wrapper around your database and hosting that here on PA. This is also rather more likely to traverse things like home router NAT gateways gracefully.

Direct access to the DB always sounds like an easy option, but long-term your life is usually made a lot easier by putting a simple interface (HTTP or otherwise) in between.

@ulrikuhrebrink as Cartroo says, you could get it working using SSH tunnels -- though you'd need a paid account for that. Writing a web app as an interface is probably a better option.

Although a paid account is always a good idea...☺

:-)

Hi,

I am having some trouble connecting to my database using SSH. Truthfully, I am seriously inexperienced with all this stuff. My goal is to do local development on my Flask web app (I am using the Flask-SQLAlchemy plugin), which would mean I need to be able to simulate my app and talk to the database from a local computer.

I have managed to talk to the database using an SSH tunnel through MySQL workbench, but I cannot get it working with my app. I get the following error: OperationalError: (OperationalError) (2005, "Unknown MySQL server host 'mysql.server' (0)") None None

My database URI is of the following structure: 'mysql://hendris:<password>@myswl.server/hendris$<dbname>'

I am unsure if I need to change the URI structure or if this is possible (surely it's possible?). I would really appreciate any help on this. Thanks!

Hi there,

To get that working from your web app (as opposed to MySQL workbench), you'll need to follow the "Manual SSH Tunnelling" steps on this page. The database URI will then have to be something like this:

'mysql://hendris:<dbpassword>@127.0.0.1/hendris$<dbname>'

I can recommend you to follow this OpenVPN over SSH stpes http://www.vpnanswers.com/openvpn-over-ssh-tunnelling-tutorial-step-by-step/. It is easy and secure with no need to root your device.

@nono515 -- I don't think openvpn is relevant here...

Yes. It's possible.

1. You need paid account.
2. Set SSH KEY between your local machine and PA
3. Select Connection Method on mysql workbench: Standard TCP/IP over SSH
4. Fill all information about SSH, Mysql
5. Test connection will be success.

Thanks! There are some other useful details on this help page.

Hi, I am using PuTTY on a windows machine and trying to follow Cartoo's instructions. However, whenever I use the "-L" command I get a response which says "-bash: -L: command not found". Is there an alternative command to use in PuTTY or is this feature simply unavailable?

Are you using the commandline with putty? If not, does this help?

Hi there, just to make sure this link is out there, we have a specific help page on ssh tunnelling for remote mysql access now...