Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
share|improve this question
add comment

2 Answers 2

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.

share|improve this answer
    
ok, I tried telnet localhost 3307 and I think it worked because I immediately got a connection, and it also told be it was using OpenSSH. However, if I try mysql -P 3307 -p -h 127.0.0.1 -u punkish I am prompted for a password, and then I get the error ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0 –  punkish Jul 8 '11 at 19:44
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.