How to set up a remote MySQL connection

Last modified: Friday August 12th, 2022


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

Choose a remote MySQL connection method

You can use either of the following methods to access your MySQL databases remotely:

  • 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 set up a direct connection between your local computer and the remote MySQL server. Although it is easier to configure, it is not as secure.

After you have set up a remote MySQL connection, you can use a MySQL client application to manage your databases. For more information, please see this article.

Method #1: 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 local computer’s operating system.

Microsoft Windows

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

To set up an SSH tunnel on a computer running Microsoft Windows:

  1. Start PuTTY.
  2. In the Category pane, expand Connection, expand SSH, and then click Tunnels.
  3. In the Source port text box of the Port Forwarding section, type 3306.  This is the local port to forward.In the Destination text box, type localhost:3306.
  4. Confirm that the Local and Auto radio buttons are selected.
  5. Click Add:                                                                   How to set up a remote MySQL connection kb mysql remote tunnel settings
  6. In the Category pane, click Session.
  7. In the Host Name (or IP address) text box, type your web site’s domain name or IP address.
  8. In the Port text box, type 7822.
  9. Confirm that the Connection type radio button is set to SSH.
  10. Click Open:                                                          How to set up a remote MySQL connection kb mysql remote session settings
  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 then type your password.
  13. 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

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 type the following command at the command line. 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. Type your password, and then press Enter. 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.

Method #2: 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 configure a remote ODBC connection:

  1. Launch the ODBC Data Source Administrator.
  2. Click Add to add a new data source.
  3. In the Create New Data Source dialog box, click the MySQL ODBC driver.
  4. Click Finish to start the MySQL Connector/ODBC Data Source Configuration.
  5. On the MySQL Connector/ODBC Data Source Configuration dialog box, in the Data Source Name text box, type a descriptive name for the data source.
  6. Confirm that TCP/IP Server is selected.
  7. Do one of the following:
    • 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. In the Port text box, type 3306.
  9. In the User text box, type a database username that can connect to the remote database.
  10. In the Password text box, type the password for the username.
  11. To test the connection, click Test:                                                How to set up a remote MySQL connection kb mysql remote odbc settings
  12. Optionally, you can use the Database list box to select an initial database for the connection.
  13. To complete the configuration, click OK.

Troubleshooting

If you are doing development work on the local computer, a local copy of MySQL may already be running on port 3306. In this case, the following connection error appears:

How to set up a remote MySQL connection kb mysql remote fail with local mysql

To work around this issue, create a tunnel with an alternate local port, and then connect to the remote MySQL server on that port.

  1. If you use Putty to create the tunnel, use an alternate Source port on the Tunnels configuration page. In this example, port 3307 is being used:                                       How to set up a remote MySQL connection kb mysql remote tunnel 3307                                                                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. Create the ODBC connection as previously described, but type 3307 in the Port text box (instead of 3306).How to set up a remote MySQL connection kb mysql remote odbc 3307
  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.

Was this article helpful?
Views: 193
Sidebar

Beehosting.pro website uses cookies

We use cookies to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners who may combine it with other information that you’ve provided to them or that they’ve collected from your use of their services.

Menu