Connecting to a database using a third-party program

 

Overview

The following describes connecting 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%).
    • If that doesn't work, add the full URL of your provider in the message.
    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 cannot 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 the 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 Ace (Mac OSX)

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

To connect with your DreamHost database using Sequel Ace:

  1. Use the TCP/IP connection tab and enter the following:
    • Name: optional
    • Host: your MySQL hostname: mysql.example.com
    • Username: your database username
    • Password: your database user password
    • Database: optional
    • Port: 3306
  2. Click Connect.

You may see the following error when connecting.

If so, ensure you've added your IP or ISP's hostname to the Allowable Hosts field, as mentioned above.

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

Connecting with SSH

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 SFTP Users & Files page. With these details in hand, you can configure the following additional options:

  • SSH Host: SSH hostname
  • SSH User: SSH username
  • SSH Password: SSH user password
  • SSH Port: 22

When connecting you may see the following error:

The authenticity of host 'example.com' can't be established. 
ED25519 key fingerprint is
SH256:#########
This key is not known by any other names

If so, view the following article to confirm the key you're connecting to is accurate:

Once confirmed, you can click the Yes button to connect.

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?