How to set up a remote MySQL connection

This article describes how to use your local computer to connect to your MySQL databases stored remotely on Beehosting servers.

Choose a remote MySQL connection method

To access MySQL databases remotely use one of this methods:

• SSH tunnel: This is the more secure method. You set up an SSH tunnel that forwards a port on your local computer to the remote MySQL server. The MySQL traffic is encrypted by the SSH tunnel.
• Direct connection:  You can establish a direct connection between your local computer and a remote MySQL server. While easier to set up, it’s not as secure.

Once you have established a remote connection to MySQL, you can use the MySQL client application to manage your databases. See this article for more information.

1 Method: Set up an SSH tunnel

The procedure you follow to set up an SSH tunnel between your local computer and the Beehosting server depends on the operating system of the local computer.

Microsoft Windows

To use an SSH tunnel on a computer running Microsoft Windows, you will need an SSH client program. Beehosting recommends using PuTTY, which you can download here. Once you have downloaded the PuTTY executable to your local machine, you can set up an SSH tunnel.

To set up an SSH tunnel on a computer running Microsoft Windows, please follow these steps:

1. Start PuTTY.
2. Expand Connection in the Category pane, expand SSH, and then click Tunnels.
3. Type 3306 in the Source port text box of the Port Forwarding section.  This is the local port to forward. Type localhost:3306 in the Destination text box.
4. Confirm that the Local and Auto radio buttons are selected.
5. Click Add:                                                                 
6. Click Session in the Category pane.
7. Type your website’s domain name or IP address in the Host Name (or IP address) text box.
8. Type 7822 in the Port text box.
9. Confirm that the Connection type radio button is set to SSH.
10. Click Open:                                                       
11. If a PuTTY security alert about the server’s host key appears, click Yes.
12. When the login as prompt appears, type your Beehosting username, and your password.
13. When the remote server command prompt appears, the SSH tunnel will be established and you will be able to use the MySQL client applications on your 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

Mac OS X and Linux

To establish an SSH tunnel on a computer running Mac OS X or Linux:

1. Open a terminal window and enter the following command at the command prompt. Replace username with your Beehosting username, and replace example.com with your site’s domain name:

   ssh -p 7822 username@example.com -L 3306:localhost:3306

2. Enter your password and press Enter. When the remote server command prompt appears, the SSH tunnel will be established and you will be able to use the MySQL client applications on your local computer.

2 Method: Set up a direct connection

To set up a direct connection between your local computer and the MySQL server, you must enable remote MySQL access in cPanel. For information about how to do this, please see this article.

Example ODBC connection

The following procedure demonstrates how to set up a remote ODBC connection:

1. Launch the ODBC Data Source Administrator.
2. Click Add to add a new data source.
3. Click the MySQL ODBC driver in the Create New Data Source dialog box.
4. Click Finish to start the MySQL Connector/ODBC Data Source Configuration.
5. Type a descriptive name for the data source on the MySQL Connector/ODBC Data Source Configuration dialog box, in the Data Source Name text box.
6. Confirm that TCP/IP Server is selected.
7. Do one of the following steps:
   • If you are using an SSH tunnel, in the TCP/IP Server text box, type localhost.
   • If you are using a direct connection, in the TCP/IP Server text box, type the domain or IP address of the remote server.
8. Type 3306 in the Port text box.
9. Type a database username in the User text box that can connect to the remote database.
10. Type the password for the username in the Password text box.
11. Click Test to test the connection:                                              
12. Optionally, you can use the Database list box to select an initial database for the connection.
13. Click OK to complete the configuration.

Troubleshooting

If you are developing on a local computer, it is possible that a local copy of MySQL is already running on port 3306. In this case, you receive the following connection error:

To fix this problem, create a tunnel with an alternate local port, and then connect to the remote MySQL server on that port. Follow these steps to do this:

1. If you are using Putty to create a tunnel, use the alternate Source port on the Tunnel configuration page. In this example, port 3307 is being used:                                                                                                      The session configuration does not change.
2. For Mac OS X or Linux, use the following SSH command to create the tunnel with a local port of 3307. Replace username with your Beehosting username, and replace example.com with your site’s domain name:

   ssh -p 7822 username@example.com -L 3307:localhost:3306

3. Ss previously described create the ODBC connection, but type 3307 in the Port text box (instead of 3306).
4. You should now be able to connect.

More Information

To view the online documentation for PuTTY, please visit http://the.earth.li/~sgtatham/putty/0.60/htmldoc/index.html.