How do I connect to my database using a third-party program?

Connecting to your MySQL database using a third-party program

To connect to your MySQL Database remotely using a third-party program like MySQL WorkBench, you must first give your local machine permission to connect to the database server. This must be done for all MySQL user accounts you plan on logging in with from your local machine.

To grant permission for MySQL user accounts:

  1. Log in to your DreamHost Control Panel.
  2. Select the MySQL Databases link under the Goodies section.
  3. Under the Database(s) on this server section, find the desired database and click the username you wish to grant access.
  4. Under the section titled Allowable Hosts, enter the domain or subdomain your local machine identifies itself as on the public Internet. Follow the formatting conventions described on the web page. You must include either your public IP address or your public originating domain/subdomain (like 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 as needed.

    • Enabling %.example.com does not enable the hostname example.com by itself.
    • If you are being denied with an error like "access denied tousername@xxx.xxx.xxx.xxx-ip.provider.info" try adding a wildcard to the end of your ip (such as xxx.xxx.xxx.xxx%).
  5. Click Modify [username] now!
  6. Launch your third-party program, enter the database and login information, and then connect.

If you're using a third-party program via a secure SSH tunnel to connect

Connecting using PuTTY v0.63

You can use PuTTy to access your MySQL database via a secure 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.

The following three phases set up a secure SSH tunnel:

  • Enabling SSH on your account
  • Launching PuTTy
  • Using the third-party tool

Enabling SSH on your account

  1. Go to your DreamHost Account Control Panel.
  2. Edit the user you wish to grant SSH access.
  3. Under "User Type:", choose "Shell user".
  4. Make sure /bin/bash is your shell type.
  5. Click Save Changes.

Launching PuTTY

  1. Download and launch PuTTy.
  2. In the category tree on the left, click Connection:SSH:Tunnels near the bottom.
  3. Enter 3306 in the source port.
  4. Enter your MySQL database address and port in the destination field (e.g., mysql.example.com:3306).
  5. Use all other defaults (local, auto) and click Add.
  6. Click Session in the category tree.
  7. Enter your shell account address in the Host Name field (e.g., psxxxxxx.dreamhostps.com).
  8. Ensure that port 22 and SSH are specified under prototcol.
  9. Give your session a name and click Save.
  10. Log into your shell account using your newly created session.
  11. Minimize the window.

Using the third-party tool

  1. Open the third-party tool, such as MySQL WorkBench.
  2. Enter localhost under Server Host.
    Note: Entering your actual address (e.g., mysql.example.com) does not work here. You must use localhost.
  3. Enter 3306 as the port.
  4. Enter your db username and password.
  5. Click OK.

You should be in!

  • 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 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, you can 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.
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 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 host name: 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 under Users -> Manage Users in your DreamHost panel. With these details in hand, you can configure the following additional options:

  • SSH Host: [SSH host name]
  • 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

Please see phpMyAdmin.

See also

Did this article answer your questions?

Article last updated .