Mysql – Executing local scripts against remote server

MySQLremote

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?

Best Answer

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.

Related Question