When MySQL server is setup to accept only localhost connection through bind_address in /etc/mysql/mysql.cnf (configuration)
or you want to connect as "root" to mysql and use GUI clients like Workbench or Toad, etc. you can use SSH tunneling.
In operating systems that provide built-in ssh support it is made little easier. Mac OS is one such OS, which I will use as an example below.
Assuming you have already setup ssh connection, run the following command from the local terminal. If you have not setup the ssh, see this brief introductory ssh article. Or run > man ssh for more details.
3307 - The port that you will use on the other clients that need to connect to remote mysql. For example, Workbench.
localhost:3306 - The remote login details as if you had connected to it through ssh and connecting to mysql instance running on port 3306. If you had successfully, ssh-ed to remote host at the prompt you would have run > mysql -h localhost -u user_name -P 3306
user@remote.com - Remote user name and remote host
Now start Workbench and setup new connection string by providing the details as if you are connecting to a mysql instance on your local machine running on port 3307. See figure 1. ssh will automatically tunnels your connection to remote host's mysql! See figure 2. On ending the tunneling setup either my closing the terminal where you were running "ssh -L" command and killing it will disconnect the connection to Workbench.
Figure 1.
Figure 2.
Note: Same technique can be used to for other client applications to connect to any of remote applications/databases over SSH.
Have fun,
Shiva
# Instead of skip-networking the default is now to listen only on # localhost which is more compatible and is not less secure. bind-address = 127.0.0.1
or you want to connect as "root" to mysql and use GUI clients like Workbench or Toad, etc. you can use SSH tunneling.
In operating systems that provide built-in ssh support it is made little easier. Mac OS is one such OS, which I will use as an example below.
Assuming you have already setup ssh connection, run the following command from the local terminal. If you have not setup the ssh, see this brief introductory ssh article. Or run > man ssh for more details.
prompt> ssh -L 3307:localhost:3306 user@remote.comand then let this terminal running. Here I am using ssh -L [bind_address:]port:host:hostport parameter and skipped bind_address which is needed only when you have more than one IP attached (multi-IP) to your local system which is typically not the case.
3307 - The port that you will use on the other clients that need to connect to remote mysql. For example, Workbench.
localhost:3306 - The remote login details as if you had connected to it through ssh and connecting to mysql instance running on port 3306. If you had successfully, ssh-ed to remote host at the prompt you would have run > mysql -h localhost -u user_name -P 3306
user@remote.com - Remote user name and remote host
Now start Workbench and setup new connection string by providing the details as if you are connecting to a mysql instance on your local machine running on port 3307. See figure 1. ssh will automatically tunnels your connection to remote host's mysql! See figure 2. On ending the tunneling setup either my closing the terminal where you were running "ssh -L" command and killing it will disconnect the connection to Workbench.
Figure 1.
Have fun,
Shiva
