Remote MySQL Development on DreamHost Using MySQL WorkBench CE

An alternative to CLI and phpMyAdmin

Introduction

You can develop a MySQL database from phpMyAdmin or from a shell account but there is another alternative offered by the MySQL WorkBench. Using this tool you can remotely develop a MySQL database on a Dreamhost server as well as other hosted accounts. The same limitations in the DreamHost MySQL installation apply of course, you can't fully create/delete a database from the tool. You have to do it from the control panel.

This install assumes you've already created a database and user from the control panel in DreamHost. For instructions on how to do this see the DreamHost Wiki

Enable Remote connections for your Database

Each database you want to enable remote access to must be enabled individually from the DreamHost control panel by doing the following:

• Login to your Dreamhost Control Panel
• Click the MySQL Databases link under the Toolbox section at the top left of the control panel screen.
• In the list of "Database(s) on this server:" section, find the database and click the username you want to add remote access for.
• In the listbox labeled "Allowable Hosts", type in the domain/subdomain/IP your local machine identifies itself as on the public internet. To find your public IP address, look just below the listbox box and you'll see 'Your current computer is:' ###.###.###.###. Just cut and paste the numbers supplied. You must enable each domain/subdomain, %.example.com does not enable the example.com .
• Click the Modify [UserNameHere] now! button.

Get the WorkBench and Connect

Download the MySQL Workbench from MySQL.com and install.

Setup a connection in the Workbench by doing the following:

• Create a New Connection under SQL Development
• Enter a descriptive name for the connection, I use something like DomainName (HostName)
• Keep the Connection Method as "Standard TCP/IP" unless you know you can use a different protocol setting
• Type in the Host Name. It'll probably be something like mysql.domain.tld, where domain.tld is the name of one of your domains hosted on DreamHost. When you create your first database on DreamHost you'll have to setup at least one domain for hosting MySQL. I generally set aside one domain for this purpose with nothing else on it, no website, no Joomla, no Drupal nothing but MySQL.
• Leave the port as 3306 which is standard for MySQL unless you know it's different
• Type in the Username, which should match the one in the DreamHost control panel
• Type in the Default Schema, also should match the DreamHost control panel
• Click the Test Connection button
• You should get a prompt for the Password and a success message. If not check your values against those in the DreamHost Control Panel.
• Once you've connected successfully click the OK button and you'll now have a connection you can open in the WorkBench.
• Now all you have to do to connect is double click the Connection in the list.

A word of caution though if you're using an IP address to connect with. Don't leave this setting permanently enabled in the DreamHost control panel since IP's, especially if they are from your ISP, change or can be spoofed. I tend to enable this setting in the DreamHost control panel when and if I need it.

That's it. Next time I'll walk you through the setup of a connection with SSH