MacHighway does not allow remote access directly to MySQL (port 3306) as it is a security risk, but if you need to access your MySQL database from a remote location using an application such as Navicat, Workbench or Dreamweaver, you can do so using an SSH tunnel. SSH tunnels are painless to set up, and require no digging or blasting.
An SSH tunnel takes traffic from a port, in this case port 3306, encrypts it, and forwards it to the remote server through another port. This allows traffic from valid users (you, our customer) to go through our firewall, but does not leave the MySQL server open to the Internet at large (a security risk).
How to set up an SSH tunnel:
For Mac and Linux Users start at “A. For Mac and Linux Users”
For Windows users, skip down to “B. For Windows Users.”
A1. Open a terminal window (in Mac OS you can find the terminal in the “Utilities” folder) and type the following command at the prompt. Replace username with your cPanel username, and example.com with your site's domain name:
ssh -p 22222 username@example.com -L 3306:localhost:3306
on some servers you may have to use “22” in place of “22222”. Press RETURN
A2. You will be prompted to enter your password, so type your FTP/cPanel password, and then press RETURN.
A3. When the remote server's command line prompt appears, the SSH tunnel is established and you can use your MySQL client applications on the local computer. If you are already running a MySQL server on your local computer, you may have to change the first 3306 in the command above to another port number, such as 3307, in order to avoid port conflicts.
A4. Now that you have your SSH tunnel set up, you need to make sure you have allowed remote connections to your MySQL database. Please see “C. Enable Your Computer as an Access Host.” below.
B1. In order to set up an SSH tunnel Windows machine, you will need an SSH client program such as PuTTY (download here). Once you have downloaded and installed PuTTy, continue
B2. Start PuTTY
B3. Under “Category,” click on Connection > SSH > Tunnels. In the “Source port:” box, type 3306. If you are already running a MySQL server on your local computer, you may have to change the source port to another port number, such as 3307, in order to avoid port conflicts.
B4. Type localhost:3306 in the “Destination:” box.
B5. Click Add.
B6. In the “Category” pane, click “Session.”
B7. In the “Host Name (or IP address)” text box, type your web site's domain name or IP address.
B8. In the Port text box, type 22222 (or 22 if 22222 does not work). Make sure that the Connection type radio button is set to “SSH”.
B9.Click the “Open” button. If you see a PuTTY security alert window pop up about the server's host key, just click "Yes".
B10. When the “login as” prompt appears, type your cPanel username, and then when prompted, type your cPanel password.
When the remote server's command line prompt appears, the SSH tunnel is established and you can use your MySQL client applications on the local computer.
To verify that PuTTY is forwarding ports correctly, you can click the icon in the top-left corner of the PuTTY session window, and then click Event Log. If port forwarding is working correctly, you see a line similar to:
Local port 3306 forwarding to localhost:3306
B11. Now that you have your SSH tunnel set up, you need to make sure you have allowed remote connections to your MySQL database. Please see “C. Enable Your Computer as an Access Host.”
C. Enable Your Computer as an Access Host
C1. Log into cPanel.
C2. Under Databases, click the “Remote MySQL” icon.
C3. In the “Host” field, type in the your IP address.
If you need help determining your IP address, you can visit: http://www.whatismyip.com. Note that if you do not have a dedicated IP address from your Internet provider, your IP address may change ijn the future, and you will then have to add in the new IP address.
C4. Click Add Host.
C5. In your remote MySQL application the Host or Server address should be just:
localhost
and not your site's domain name becuase you have set up tunelling from your local machine to the remote server already. Information on locating your MySQL username and password can be found in our Knowledgbase article, “Know your MySQL database details” here.
