What is MySQL Workbench?
MySQL Workbench is a tool that allows you to connect to a MySQL database and run queries within an interface that is a bit nicer and more user friendly than relying on a command-line shell. If you are in the MSBA program, it is the “official” tool you’ll be using to build and run your queries.
Fortunately, installing MySQL workbench and connecting to databases is pretty straightforward.
In what is hopefully becoming a common refrain, if you’re using OSX and using homebrew this is going to be extremely easy. Fire up a terminal and type:
brew cask install mysqlworkbench
If you’re on Windows you can download and install from the website
Setting up a connection
To connect to a database click the plus icon and then enter in the relevant host, port, username and password information in the dialog box
Note: If the connection info you’ve been provided doesn’t include a port number, it is most likely 3306, the default port for mysql
Once you’re connected you’ll be taken to a screen where you can begin typing queries.
A sample connection for you
If you’re in the MSBA program you will have a mysql server made available to you. However, it may be desirable to have a local testing environment. We could set up a mysql server to run off our localhost, but it’s a bit cleaner to use Vagrant to automagically spin up a mysql server for us. All you need is a Vagrantfile to handle the provisioning, and I happen to have one for you right here! The README in the repo has the connection information.
So, there you have it. MySQL workbench up and running. If you’re in the MSBA program this is the official tool you’ll be using to connect to your program databases. All done!
you still here?
wanna know a secret?
I think MySQL Workbench sucks. Too many bells and whistles, the actual SQL connections are buggy, and it only works for MySQL databases which are not incredibly common as data warehouses in the real world.
There are many different clients out there and you can try them all. I’ll tell you that my personal favorite is also probably the most simple: SQL Workbench/J
In order to get it working you’ll need Java… technically you can get by with a simple JRE (Java Runtime Environment), but as burgeoning developers you’ll probably want to get a JDK (Java Development Kit) installed. If you’re using OSX we can once again use homebrew:
brew cask install java
Otherwise, you can start the process here.
Workbench uses JDBC drivers (Java Database Connectivity) to interact with databases. This requires that you get an appropriate JDBC driver and connect it with Workbench. The driver can be found here
Unzip the archive and you’ll find it contains a jar. You need to point Workbench to this jar.
After launching Workbench, select “Manage Drivers” from the Connection Window. In the next dialogue box select the new driver button in the upper left, then click the browse folder on the right. Navigate to the jdbc jar you just downloaded. The Classname should auto-populate. Click OK.
In order to connect you’ll need to provide a valid connection string. The format for this is:
An example using our Vagrant database:
The nice thing about Workbench is, once the appropriate JDBC driver has been installed it will work for any type of database. JDBC connections, in my experience, tend to be faster and less error prone than other types of connections so I think if you go to the trouble of setting it up you’ll find that Workbench offers a superior (if somewhat bare-bones) SQL client experience.