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:

Prerequisites

Make sure you have the following information before proceeding.

If you're running DreamPress, you can locate your credentials using the following articles:

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 user name link for the user you wish to grant access.
  3. Scroll down to the Allowable Hosts section. You must include one of the following:
    • Your public IP address
    • Your public originating domain/subdomain

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

    MySQL Allowable Hosts field

    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
    MySQL Allowable Hosts field

    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%).
    MySQL Allowable Hosts field
  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 secure SSH tunnel 

This example uses 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 for your website user

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

Step 2 — Launch MySQL Workbench

  1. Download MySQL Workbench.
  2. Launch the application.
    MySQL Workbench
  3. Click the + icon to create a new connection.

Step 3 — Enter your SSH and database credentials

The following window opens for you to create a new connection.

MySQL Workbench

Configure the following:

Step 4 — Confirm the connection

The first time you connect, you may see a warning about the authenticity of the connection.

MySQL Workbench

This is normal the first time you connect as you can read about here.

Confirm that the fingerprint matches the one in your panel, then click the OK button.

MySQL Workbench

Finally, you'll see the successful connection message.

Connecting using passwordless login

This example uses MySQL Workbench to connect using 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 articles to enable Passwordless login. 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 for PuTTY keys:

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 — Launch MySQL Workbench

  1. Download MySQL Workbench.
  2. Launch the application.
    MySQL Workbench
  3. Click the + icon to create a new connection.

Step 4 — Enter your SSH and database credentials

The following window opens for you to create a new connection.

MySQL Workbench

Configure the following:

  • Connection Name: Enter anything you like to name this new connection.
  • Connection Method: Select Standard TCP/IP over SSH.
  • SSH Hostname: Enter your server name.
  • SSH Username: Enter your SSH username.
  • SSH Password: Do not click Store in Keychain.
  • SSH Key File: Click the ... button and search for your private key on your computer.
  • MySQL Hostname: Enter the hostname for your database.
  • Port: 3306.
  • Username: Enter your database username.
  • Password: Click the Store in Keychain button to enter your database username's password.
  • Click the OK button to log in to your database.

Step 5 — Confirm the connection

The first time you connect, you may see a warning about the authenticity of the connection.

MySQL Workbench

This is normal the first time you connect as you can read about here.

Confirm that the fingerprint matches the one in your panel, then click the Ok button.

MySQL Workbench

Finally, you'll see the successful connection message.

Connecting using SSH

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

  1. Edit the file.
    [server]$ nano ~/.ssh/config
  2. 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
    
  3. 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
  4. 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 
  5. 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 Files 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 phpMyAdmin

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?