Tunneling MySQL over SSH


#1

Anyone have any insight regarding this Gotcha?

I can access MySQL from my shell account (on yoda). I can access it from home, when I add my external hostname as a valid host in “Goodies > Manage MySQL”. Via telnet I can see that my port forward described here works. But adding localhost as a valid host doesn’t seem to work. MySQL refuses to allow access.

Here is the error (duplicated from the wiki entry):

ERROR 1045 (28000): Access denied for user ‘db_user_name’@‘localhost’ (using password: YES)

-B…


#2

Don’t use localhost as your hostname for command line. Use the actual hostname. MySQL databases aren’t located on the same box as your website, which prevents the ability to use ‘localhost’.

Command should look like: mysql -u -h -p

And let me add you’re not Tunneling MySQL over SSH. All you’re doing is using the MySQL command line client to connect to the MySQL server. No Tunneling at all.


yerba# rm -rf /etc
yerba#


#3

I’m familiar with the wiki aritcle you mention. I suspect that it has somehting to do with the custom error message Dreamhost set up for this. It ‘sees’ localhost and returns the error message regardless.

I don’t see the point though. Why not just put the appropiate hostname into the config for your app?

–Matttail
art.googlies.net - personal website


#4

Perhaps I should’ve more clearly stated the context. I want to avoid directly connecting to the MySQL server, since that is a plaintext connection.

I’m tunneling via SSH through my DH server to connect to the DH MySQL server from my laptop. Tunneling means I’ll be connecting as localhost to localhost (the forwarded port). And as the error message shows, MySQL sees me as coming from ‘localhost’. Using my actual hostname would appear to be moot, in this case, as I am not connecting directly.

Thanks,
-B…


#5

I just got this working yesterday.

First, I connected to my shell account like this:
ssh -L8888:mydbhost.mydomain.com:3306 myshelluser@mydomain.com

Then I connected to their server like this:
mysql -u mydbuser -p -h 127.0.0.1 -P 8888

These parameters also worked using a GUI frontend (CocoaMySQL beta for MacOS X). You have to use 127.0.0.1 instead of ‘localhost’ or the server will refuse to let you in.

I hope this helps!


#6

Thanks, Shiva! That did the trick. I’ve updated the wiki page.

-B…


#7

i’m trying to use phpMyAdmin over SSH tunnel. using ‘mysql’ client apps work, but not (yet) the GUI :-/

i’ve four machines in my setup,

(1) local desktop on home_lan (2) web server on home_lan apache2 phpmyqdmin mysql client (3) shared account @ DH host, dynamic IP, no ssl (4) DB @ DH host i’ve setup ‘ssh_config’ on “(2) web server”,

[code] Host my_DH_domain.com
HostName my_DH_domain.com
User my_DH_user
LocalForward 127.0.0.1:3307 my_db_host.my_DH_domain.com:3306
LocalForward web.server’s.lan.IP:3307 my_db_host.my_DH_domain.com:3306
IdentityFile /usr/local/etc/ssh/ssh.server.rsa

PreferredAuthentications publickey
PubkeyAuthentication yes
PasswordAuthentication no
Compression no
ForwardX11 no
ForwardX11Trusted no
[/code]and added the “(2) web server” pubkey to ‘~/.ssh/authorized_keys’ @ “(3) shared account @ DH”

at this point, i can, @ “(2) web server” shell,

[code] ssh -F /usr/local/etc/ssh/ssh_config my_DH_domain.com
netstat -na | grep 3307
tcp 0 0 web.server’s.lan.IP:3307 0.0.0.0:* LISTEN
tcp 0 0 127.0.0.1:3307 0.0.0.0:* LISTEN

mysql -u my_user -p -h 127.0.0.1 -P 3307 dh_test
Enter password:
[/code]and i’m connected to the remote DB,

[code] mysql >

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 305283
Server version: 5.0.67-log MySQL Community Server (GPL)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.

mysql> quit
Bye
[/code]now trying to connect via phpmyadmin over the tunnel …

in my ‘phpmyadmin/config.inc.php’ @ “(2) web server”,

$i = 1 ... (direct, no-tunnel connection to local mysql DB/server on home lan) ... $i = 2 $cfg['Servers'][$i]['host'] = '127.0.0.1'; $cfg['Servers'][$i]['port'] = '3307'; $cfg['Servers'][$i]['socket'] = '/var/run/mysqld/mysqld.sock'; $cfg['Servers'][$i]['connect_type'] = 'tcp'; $cfg['Servers'][$i]['DHension'] = 'mysql'; $cfg['Servers'][$i]['compress'] = FALSE; ... accessing phpmyadmin @ “(2) web server” from a browser open on “(1) local desktop”, i can connect no problem to my local, Server [1].

but, when I attempt to connect to the remote server @ DH (Server [2]) with phpmyadmin over the SSH tunnel, in browser i get,

Server error! The server encountered an internal error and was unable to complete your request. Error message: Premature end of script headers: index.php clearly you’ve gotten it working with CocoaMySql, so, i presume, this should work as well …

any hints as to what’s misconfigured on my end?


#8

Hi PGNet,

I’m not sure what the problem could be, as I’ve never configured phpmyadmin before (and haven’t done much else since that posting). It looks like you are more advanced at this stuff than I am, anyway!

This is probably a posting glitch, but your configuration includes this line:
$cfg[‘Servers’][$i][‘DHension’] = ‘mysql’;

which probably should have ‘extension’ instead of ‘DHension’. Maybe try ‘mysqli’ as the value as well, since the MySQL is newer than 4.1.

Good luck tracking down the problem. Sorry I can’t help any more…