connecting to mysql over ssh

Question

All the computers involved are Mac OS X 10.6.x machines.

I am on computer A, and have mysql running on it, and can connect to it as A~ $ mysql -u punkish -p database and so on.

I have access to computer B that also has mysql running on it. I can ssh into B over port 2200 with ssh key pairs set up, and once in, I can connect to the db like so

A~ $ ssh -p 2200 punkish@B
B~ $ mysql -u punkish -p database

However, I want to set up port forwarding over ssh on my computer so all requests made for mysql on port 3307 on my computer A are actually sent to port 3306 on B. This will allow me to use a gui program (Quantum GIS) that wants to connect to mysql, but can't do it over a tunnel. So, I do the following

A~ $ ssh -p 2200 -L 3307:B:3306 punkish@B
B~ $

The above logs me right into B. I am not sure if the tunnel is established, but I am assuming it is. However, the following fails (in another terminal

A~ $ mysql -P 3307 -u punkish -p
Enter password:
ERROR 1045 (28000): Access denied for user 'punkish'@'localhost' (using password: YES)

Here is the interesting thing -- I have another gui program called Sequel Pro (a desktop MySQL client for Mac OS X), and that is able to make a connection to mysql@B over ssh just fine. So, I know something works... I just don't know how.

Answering my own question (because I can't answer in less than 8 hours)

I figured out a solution, but I don't understand why. Here goes --

I was using the following command to make a tunnel

user@compt_A ~$ssh -p 2200 -L 3307:compt_B:3306 ssh_user@compt_B cat -

I have to change the above to

user@compt_A ~$ssh -p 2200 -L 3307:127.0.0.1:3306 ssh_user@compt_B cat -

Now the following works

user@compt_A ~$mysql -P 3307 -p -h 127.0.0.1 -u <db user@compt_B>

The reason it doesn't make sense to me is because the first command says, "establish an SSH tunnel over port 2200, forwarding local port 3307 to port 3306 on computer B" and that makes more sense than, "establish an SSH tunnel over port 2200, forwarding local port 3307 to port 3306 on 127.0.0.1"

Nevertheless, it works.

Oh, fwiw, if I use the first command, then the mysql command fails with the message

ERROR 1130 (HY000): Host 'compt_A' is not allowed to connect to this MySQL server

Answer 1

Use telnet to test if the tunnel is open. "telnet HOSTNAME PORTNUMBER" if it connects, the port is accessible from the machine you initiated the connection from.

You may also care about the "-g" option for ssh clients:

-g Allows remote hosts to connect to local forwarded ports.

I often find if a tunnel can't be opened, then when I get a shell on the remote machine, I see a message reported right before the terminal prompt is first printed that states that the tunnel could not be established.

Answer 2

Maybe you don't need to ssh into the machine at all, all you need to do is let MySQL know that clients from your network are allowed access to that machine. Have a look at the grant and create user statements.

http://dev.mysql.com/doc/refman/5.1/en/grant.html
http://dev.mysql.com/doc/refman/5.1/en/create-user.html

At our office, if I create a database on machine A and I want every body on the network 172.16.0.1/24 or 192.168.0.1/24 to be able to access that machine if they know the user name "office_user" and password "office_pass". I need to grant statement that will let me use the mysql client on there machine at the office to connect to what ever database I give it access to.

This is the quick and insecure way of creating a user at the office that can do any thing on the MySQL database from any of the networks. Use this only when you are doing simple testing and on a non production server. It gives the "office_user" user nearly complete access to all databases on the MySQL server.

CREATE USER 'office_user'@'172.16.0.%' IDENTIFIED BY 'office_pass';
CREATE USER 'office_user'@'192.168.0.%' IDENTIFIED BY 'office_pass'; GRANT ALL PRIVILEGES ON . TO 'office_user'@'172.16.0.%' IDENTIFIED BY 'office_pass';
FLUSH PRIVILEGES;

Note that this user won't work on the localhost as I did not include a

CREATE USER 'office_user'@'localhost' IDENTIFIED BY 'office_pass';

No you can log in to your mysql server hosted at ip address 172.16.0.20

mysql -u office_user -h 172.16.0.20 -p"office_pass"

More securely you can give your self access with privileges only on the database "office_db" you need for you GIS software. Where your ip address is 172.16.0.20.

CREATE USER 'office_user'@'172.16.0.20' IDENTIFIED BY 'office_pass'; GRANT ALL PRIVILEGES ON office_db.* TO 'office_user'@'172.16.0.20' IDENTIFIED BY 'office_pass';
FLUSH PRIVILEGES;

You use the same login, but it will only have access to the specified database

mysql -u office -h 172.16.0.20 -p"office_pass" office_db

You can make this even more secure by only giving your self the privileges you need like select and drop. Go and read the grant statement documentation