So being raised 99% on SQL Server and its tools, it has been interesting having to pick up support on Linux and MySQL. Nothing against either of them, but I guess I've either been spoiled or lazy with not having to do much with command prompts if I don't want to. This week was my first time trying to get connected to mysql that had all connections blocked except for localhost. So for all you other SQL Server DBA's that have to connect to MySQL and don't have a clue where to start, here's a cheatsheet!

First step- you have to establish your connection to the mysql server. If you are lucky you can download the MySQL GUI administrative tool and get connected, piece of cake. I won't even get into the details on that. However, a lot of you are going to see all remote connections disabled except the localhost connection. This poses somewhat more of a challenge for connecting. Essentially is what you end up doing is tricking the MySQL gui to thinking that it is connecting to your own MySQL instance, not a remote one.

So to communicate with the MySQL server you have to set up a "tunnel" to the server. This tunnel is how you will control the MySQL Server. To set it up, you will need putty.exe. This allows you to create a SSH (Secure SHell) connection. Inside of that connection you create a tunnel that forwards communication on a given port from your computer to the MySQL server.

Download putty.
Once you have it downloaded, on the main screen you need to expand the SSH menu so you can see the Tunnels option. In the source port(your machine port) put 3306. In the Destination field put 127.0.0.1:3306 and then click Add. This will save the tunnel information in your connection. Then open your SSH connection.



Once your SSH connection with the tunnel to the MySQL ports is set up you can open the MySQL Admin tool. In the server host you put your localhost (127.0.0.1) and the port number 3306. (Unless you are using a different port. As long as this port number matches with the one you put in the "Source Port" field in the SSH Tunnel). It is also noteworthy that you might have a different login you use with SSH and MySQL- Make sure you the username and password is your database login. Here's a screenshot:


Then click connect and you should be in!

Labels: how to connect, mysql, sql server, SSH, tunnel

posted by solutionsguy at 2:36 PM 0 Comments