Connecting to a database using a third-party program

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

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?.

   

   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%).
   • If that doesn't work, add the full URL of your provider in the message.

   

4. When finished, click the Modify [username] now! button.
5. Launch your third-party program and enter your database credentials to connect.

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.

• Creating a user with Shell (SSH) access 

Step 2 — Launch MySQL Workbench

1. Download MySQL Workbench.
2. Launch the application.

   

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.

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 website name.
• SSH Username: Enter your SSH username.
• SSH Password: Click the Store in Keychain button to enter your SSH user's password.
• 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 4 — Confirm the connection

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

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

• 'The server's host key is unknown' warning

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

Finally, you'll see the successful connection message.

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.

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 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.

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 FTP 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:

• 'The server's host key is unknown' warning

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

See the phpMyAdmin article for further details.