Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am in a situation where I would like to execute a number of sql scripts that are on my local machine against a remote MySQL instance. I found this question which seems to point me in the right direction. After reading this I tried the following:

$ ssh -L 12341:127.0.0.1:3306 root@myServer &
$ mysql -h 127.0.0.1 -p 12341

But it's been throwing an "Access denied..." error for 'abe'@'localhost'. What I would like to do is connect as root from the destination server. So instead of running as abe@localhost I'd like to run as root@myServer. I tried:

$ mysql -h 127.0.0.1 -p 12341 -u root@myServer

but this only returns the same error for user, 'root@myserver.com'@'local'. I also tried -u root and got access denied again.

Is there any way for me to specify that I want to connect at root on the destination server in my above example?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

Obligatory security disclaimer: You shouldn't be ssh-ing into a remote machine as root, neither should you be connecting to MySQL as root. But I digress. At least the question implies that the remote MySQL server isn't directly accessible from the Internet.

It's a little bit difficult to be sure whether you're doing this incorrectly or you simply made an error in typing the question.

  -p, --password[=name]
                      Password to use when connecting to server. If password is
                      not given it's asked from the tty.
  -P, --port=#        Port number to use for connection or 0 for default to, in
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
                      /etc/services, built-in default (3306).

You need a capital 'P', not a lowercase 'p', before the port number on the 2nd line... otherwise, you're probably connecting to your local mysql server on port 3306, not the remote one tunneled back to port 12341.

Assuming for a moment that the ssh tunnel is actually being set up correctly, the correct syntax would be:

$ mysql -h 127.0.0.1 -P 12341 -u root -p

That is, connect to the MySQL server listening at 127.0.0.1 on port 12341 with username 'root' and a password which will be prompted for. Since your local port 12341 is tunneled to the far-end machine, and the destination address in the tunnel is 127.0.0.1, you will be known to the far-end machine as 'root@localhost' where "localhost" means the far end machine -- not your machine. It's "that" localhost, not "this" localhost.

Now, let's go back and look at the tunnel. The correct form would be this, assuming ssh version 2:

$ ssh -N -L 127.0.0.1:12341:127.0.0.1:3306 root@remoteserver &

The -N option stops ssh from spawning a remote shell, which you don't need, and which may cause problems when you try to send it to the background with &.

Your environment might be different, but if I omit the -N, my tunnel doesn't work, and I see the following:

[1]+  Stopped                 ssh -L ... 

After the -L, the first mention of 127.0.0.1 means "only listen on 127.0.0.1 of my local machine" as opposed to listening on all ipv4 interfaces. If you don't do this, then every IP address on your machine is exposing the remote MySQL server to anybody who connects on your port 12341.

share|improve this answer
    
Looks like I was way off, thanks for the help! –  Abe Miessler May 29 '13 at 15:21

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.