How do I remotely access a mysql database with ssh on windows?

July 7, 2009 8:06 AM

How do I remotely access a mysql database with ssh on windows?

Please assume that I have virtually no idea about the above process. Although I can use various web languages, including mysql, I have so far only used them on my own machine. Now I wish to try and connect to a database hosted remotely.

I have checked around and found plenty of advice for linux but how do I accomplish this in Windows? What exactly do I type in and where exactly do I type it in to?

The more steps you give me, the better! Thanks! Also the database has already been set up to allow this connection.

posted by Fluffy654 to computers & internet (7 comments total)

Windows doesn't come with a built-in ssh application -- you'll want to download the free app PuTTY.

When you run it, you'll want to enter the remote host name you're connecting to, select ssh as the connection method, and then click on "open" to open the session. You'll then be asked for your username and password on the remote host. That's it -- you'll then be connected to the command line shell on the remote host.

You should then be able to enter into mysql on the command line by simply typing mysql (or perhaps mysql -u -p, hitting enter, then entering your password if you've got a special username and password for the database).

The assumption with the above instructions, of course, is that the remote machine is prepped both with an ssh daemon as well as command-line mysql. If that's not the case, you may need to get specific instructions from the individuals who maintain that server.
posted by eschatfische at 8:24 AM on July 7

SSH is not a mysql client. Are you saying you want to SSH into the database server and run the command line client there? Or are you trying to make software running on your Windows desktop connect to a remote database? Or are you trying to do SSH port redirection and then use a local client of some sort over the encrypted channel?

"What exactly do I type in and where exactly do I type it in to?"

I'm pretty sure you can get an answer with this much detail, but you'll have to describe what you want to do first. The vagueness with which you ask this question makes it impossible to tell you the steps you should take. You can fix that by describing what it is you are trying to accomplish.
posted by majick at 8:26 AM on July 7

eschat's suggestion lets you log into a shell on the MySQL server, then access MySQL from that remote machine. If that satisfies your needs then it's the easiest way.

A second option is to use ssh port forwarding to create a tunnel from your Windows machine to the database server. Then you connect to the local end of the tunnel, something like localhost:3306. There's a reasonable set of explanations for how to do this on this forum post.

There's no specific need to use ssh, by the way. MySQL can be configured to accept remote database connections very easily. It's less easy to make it secure, though, and ssh tunnels are one workaround. Note that ssh adds overhead on both ends and is not suitable for very high traffic installations.
posted by Nelson at 8:28 AM on July 7

The more steps you give me, the better! Thanks! Also the database has already been set up to allow this connection.

Who manages this database? Are they your web host? Are you sure you're asking the right question? Typically you would run your web applications on their end and connect to their database. In your code, you would still treat "their" database as a local database, since your application is located at the same place as the database.
posted by odinsdream at 8:32 AM on July 7

As others have mentioned, we need more information to fully get at what you want -- the main question would be, are you trying to simply remotely access and manipulate the MySQL process yourself, or do you want some application to be able to remotely connect to and use the process? The methods for accomplishing the two are very different.

I'm guessing that it's the former, which would probably mean that you have a web hosting account and the server has SSH and MySQL running and you want to create/modify a MySQL database under that hosting account that will operate with a web site that also is under that hosting account.

If that's the case, then what I would recommend is the following process (what I use on a Windows machine to do it):

Install cygwin, which is a fairly straightforward process except when it comes to selecting which components to install, though in this case all you'll really need is SSH. There is a brief tutorial here on getting OpenSSH working with cygwin. (The earlier PuTTy suggestion will work as well, but I personally prefer this method.)

Once this is up and running, then you'll just launch cygwin, which will open a terminal window and you enter the command ssh [username]@[host], then you'll be prompted for a password.

With that connection established, you'll be operating on the remote host (your web server), which is almost certainly running Linux. At that point, all of the Linux commands that you've been reading about will be relevant. Here's a rundown of MySQL commands, which you'll use to launch the program, create a new database, etc. How you launch the MySQL process will depend on where it's installed and how the server is configured, but you should be able to get that information from your web host easily enough.

You will also need to know the username and password for the MySQL process, which you can get from the web host. They may be the same as your general account username/password but they might be different, so don't assume that your account or FTP login details will automatically work. It might be that the first time you use MySQL, you will have to log in as a root user and create another user.

All of this will get you up and running with your remote process, but there is another option for remotely managing a MySQL process via a web-based interface that you may find simpler. There is a program called phpMyAdmin, which you can install on your server and then run via any web browser. Here's a tutorial on installing and running phpMyAdmin.

It will allow you to run your MySQL databases without having to do any command-line stuff and just using a GUI to create, query, modify, import, export, etc.
posted by camcgee at 10:01 AM on July 7

Seconding phpMyAdmin. Even if you know your way around the command line and speak in SQL, phpMyAdmin or another administration tool makes handling your database easy and straightforward while still letting you run straight SQL commands if you want to.
posted by ThatRandomGuy at 10:33 AM on July 7

If you have mysql installed on your local machine, you can just use it to connect to the remote host,

Here's the documentation for the tool. You may have to add your IP address to the allowed hosts to connect on the server first.
posted by wongcorgi at 2:39 PM on July 7