Mysql – Enabling both local and remote connections to a thesql server on linux (ubuntu)

Do:

netstat -an|grep 3306 | grep LISTEN

If something similar to the following line is returned:

tcp        0      0 0.0.0.0:3306                  0.0.0.0:*                   LISTEN      

.. it means that it's listening on all interfaces.

If something similar to the following line is returned, and no other lines:

tcp        0      0 127.0.0.1:3306               0.0.0.0:*                   LISTEN

.. it's already configured to only listen on localhost.

If there are lines with other IP addresses before the :3306, it means that it's listening on those interfaces.

To change MySQL to only listen on localhost, edit your configuration file (usually /etc/my.cnf), add the following:

bind-address = 127.0.0.1

Restart the service and voila!

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.