Connecting to a database using a third-party program

Overview

The following describes how to connect to your DreamHost database(s) using a third-party program.

If you have a DreamPress plan, you can only access your associated MySQL database(s) using either of the following methods:

Updating your Allowable Hosts field in the panel

You must first give your local machine permission to connect to the database server. This can be done by adding information to the Allowable Hosts field in your panel. 

To grant permission for MySQL user accounts:

  1. Navigate to the MySQL Databases page in the panel.
  2. Under the Database(s) on this server: section, find the desired database, then click the Users with access link for the user you wish to grant access.
  3. Scroll down to the Allowable Hosts section and enter the domain or subdomain your local machine identifies itself as on the public Internet. You must include one of the following:
    • Your public IP address
    • Your public originating domain/subdomain (such as rr.com, mindspring.com, comcast.com, and so on)

    To find your public IP address, go to What is My IP?.

    Your IP address is also displayed in the panel after editing your MySQL username. Under the Allowable hosts section it says:

    Your current computer is: XXX.XXX.XXX.XXX

    You can copy and paste this IP address into the Allowable Hosts field.

    • Enabling %.example.com does not enable the hostname 'example.com' by itself.
    • If you receive an "access denied to username@xxx.xxx.xxx.xxx-ip.provider.info" error, try adding a wildcard to the end of your ip (such as xxx.xxx.xxx.xxx%).
  4. When finished, click the Modify [username] now! button.
  5. Launch your third-party program and enter your database credentials to connect.

Connecting using a third-party program and secure SSH tunnel

This example uses PuTTY and MySQL Workbench to connect using an SSH tunnel.

This method is preferred over less secure methods as it provides point-to-point encryption and does not expose your MySQL account to potential hacks from allowed networks.

Step 1 — Enable SSH on your account

View the following article for instructions on allowing SSH access to your website user.

Step 2 — Configure Passwordless login

View the following article to enable Passwordless login in PuTTy. This step uploads the public key to your server and downloads the private key in OpenSSH format.

Step 3 — Launch PuTTY

  1. Download and launch PuTTy.
    putty configuration
  2. In the category tree on the left, click Connection > SSH > Tunnels.
  3. Enter 3306 in the source port.
  4. Enter your MySQL database hostname and port in the destination field (e.g., mysql.example.com:3306).
  5. Leave the radio buttons for Local and Auto selected.
  6. Click the Add button to save.
    putty configuration
  7. Click Session in the category tree.
  8. Enter your server URL in the Host Name field (e.g., servername.dreamhost.com or psxxxxxx.dreamhostps.com).
  9. Set the port to 22.
  10. Set the Connection type to SSH.
  11. Give your session a name under Saved Sessions.
  12. Click Save.
    The new session name appears in the list.
    putty configuration
  13. Click the new session name to highlight.
  14. Click the Open button.
    Enter your shell user and password in the terminal prompt to log in.

Step 4 — Use a third-party tool to log in

Open the third-party tool, such as MySQL WorkBench.

putty configuration

Configure the following:

  • Connection Method: Leave it set as Standard (TCP/IP).
  • Hostname: Leave this set as 127.0.0.1. This will use the PuTTY connection you just created. If your third-party application does not allow the URL, try localhost instead.
  • Port: 3306.
  • Username: Enter your database username.
  • Password: Enter your database username's password.
  • Click OK and you should be logged in to your database.
  • Your PuTTY session must be active and you must be logged in for the tunnel to work.
  • If you are running a local MySQL db, you can specify a different port for your tunnel. Otherwise, you cannot access your local mysql connection while the tunnel is active.
To specify a different port:
  1. Enter a different number (e.g., 3307) in the source port field under Tunnels in PuTTY.
  2. Use the same number as your port in the MySQL Administrator Port field.
Your destination port must remain 3306. The idea is that you're sending data to port 3307 on your end, the data is sent through the tunnel, and then is shunted to port 3306 on DreamHost's end.

Connecting using passwordless login with a third-party program

This method uses PuTTY and MySQL Workbench with SSH keys to log into your database via passwordless login.

Step 1 — Enable SSH on your account

View the following article for instructions on allowing SSH access to your website user.

Step 2 — Configure Passwordless login

View the following article to enable Passwordless login in PuTTy. This step uploads the public key to your server and downloads the private key to your computer.

The private key you load into MySQL Workbench must be in OpenSSH format or you will not be able to connect. MySQL Workbench states the following:

MySQL Workbench does not accept default PuTTY keys directly. However, you can convert an existing PuTTY Private Key (ppk) file to OpenSSH format using the PuTTY Key Generator (PuTTYGen) utility.

Step 3 — Use a third-party tool to log in

Open the third-party tool, such as MySQL WorkBench.

putty configuration

Configure the following:

  • Connection Method: Choose Standard TCP/IP over SSH from the dropdown.
  • SSH Username: Enter the shell username you just enabled passwordless login for.
  • SSH Password: Do NOT enter your SSH user password.
  • SSH Key File: Upload the OpenSSH formatted private key you previously created when configuring passwordless login.
  • MySQL Hostname: The hostname you would use to log into your database (e.g., mysql.example.com). If your third-party application does not allow the URL, try localhost instead.
  • MySQL Server Port: Enter 3306 as the port.
  • Username: Enter your database username.
  • Password: Enter your database username's password.
  • Click OK and you should be logged into your database.

Connecting using SSH

If you are running a local MySQL server on port 3306, you must set up port forwarding in your ~/.ssh/config, as follows:

  1. Enter the following settings:
    Host your_nickname_for_your_host
         HostName        your_dh_host_name.dreamhost.com
         User            your_shell_account_user_name
         LocalForward    3307 your_full_db_host_name:3306
    
  2. Enter the following to connect:
    [server]$ ssh your_nickname_for_your_host
    [server]$ mysql -h 127.0.0.1 -P 3307 -u db_user_name -p
  3. Optionally, use the following single command line:
    [server]$ ssh -4fNg -L 3307:your_full_db_host_name:3306 your_shell_account_user_name@your_dh_host_name.dreamhost.com 
  4. Connect with mysql as above.
  • Despite adding localhost as a valid host for your user on the MySQL Databases page, you may encounter this error message:
ERROR 1045 (28000): Access denied for user 'db_user_name'@'localhost' (using password: YES)
Use 127.0.0.1 instead of localhost (it's blocked by DH).
  • If connecting via command line, you must use the MySQL -P flag and specify port 3307 as in the following:
[server]$ mysql -u username -p -h 127.0.0.1 -P 3307

Connecting using Sequel Pro (Mac OSX)

If you're using Apple Mac OSX, you can use the open source Sequel Pro software to connect to a DreamHost MySQL database.

To connect with your DreamHost database using Sequel Pro:

  1. Use the Standard connection tab and enter the following:
    • Name: [optional]
    • Host: [your MySQL hostname: mysql.example.com]
    • Username: [your database user name]
    • Password: [your database user password]
    • Database: [optional]
    • Port: [3306]
  2. Click Connect.

Once you are connected, select the database from the drop-down menu on the top left.

A more secure method is to use the SSH tab when creating the connection/favorite. You must first configure a user for SSH access on the Manage Users page in your DreamHost panel. With these details in hand, you can configure the following additional options:

  • SSH Host: [SSH hostname]
  • SSH User: [Username of an SSH enabled user]
  • SSH Password: [Password for the account]
  • SSH Port: [22]

Connecting to your MySQL database using DreamHost's phpMyAdmin at your own domain

See the phpMyAdmin article for further details.

See also

 

Did this article answer your questions?

Article last updated PST.

Still not finding what you're looking for?