If you are a database administrator or designer you know that the right tools can make your job remarkably easier. Now many dbas and dbds prefer the good ol’ command line? After all, that’s how MySQL was intended to be used, right? Right? Although MySQL works exceptionally well from the command line (it’s not as hard as one would think), having a solid GUI tool just opens the user up to learning more powerful tasks and getting more work done faster.
The one GUI tool that most people are accustomed to (with regards to MySQL) is the MySQL GUI Tools Bundle. It’s a very user-friendly GUI that really does make the task of database creation/administration easy. But that tool is going away (read the full EOL announcement here). Upon first glance I thought the MySQL team had made a big mistake. After all, how could they replace MySQL Administrator with something even better to use? Well they could and they did. Not only did they replace the Administrator, they replaced all of the “Bundle” tools with a single, cross-platform, user-friendly tool. MySQL Workbench.
The MySQL Workbench tool is a power-user’s dream come true. But before you delve into the serious power of this tool, it’s best to know some of the basics of the tool. In this article I am going to show you how the task of creating a MySQL database is made simple using the MySQL Workbench.
Installation
Naturally, before you embark on using the tool, you have to install the tool. And of course, before you install the Workbench, you will need MySQL installed. You can install all of the dependencies with Synaptic. Follow these steps:
- Open Synaptic.
- Search for “mysql” (no quotes).
- Mark mysql-client and mysql-server for installation.
- Click Apply.
That’s it.
Now to the Workbench installation. Most likely you won’t find a MySQL Workbench entry in Synaptic or gnome-package-kit. Instead you will have to download a pre-compiled binary from the MySQL Workbench download page. You will want to go to the Development Release tab in order to find a binary for Fedora, Ubuntu, SuSE, Mac, or Windows. To get to that page you will first have to register (it’s free) on the MySQL site. Now, from that same tab you can also download the source code. For the purposes of this article I will be installing on Ubuntu 10.04. The steps for this installation are:
- Download the binary for Ubuntu (they only offer 9.04 binary as of this writing) and you can do one of two things with it: Save it and install with dpkg or allow GDebi to install it automatically.
- If you decide to save the file and install manually, the command is sudo dpkg -i mysql-workbench-XXX.deb Where XXX is the release number.
That’s it. You should now find a new sub-menu entry in your Applications menu called Programming. Within that sub-menu you will find an entry for MySQL Workbench. Click that to start up the tool.
Creating Your First Database
The MySQL Workbench tool actually makes the creation of databases simple…once you know how. At first glance, the task might elude you. Let’s walk through the steps.
Step 1: Create a connection to your server
The MySQL Workbench tool can serve as a single point with which you can manage multiple database servers. You could manage an entire database farm from one machine using the Workbench. But to make this simple we are going to connect to localhost as the server. NOTE: You will have had to install the MySQL Server on your local system in order to connect to localhost.
To connect to the server click the New Connection link in the MySQL Workbench main screen (see Figure 1). When you do this a new window will open where you will need to enter the following information:
- Name: Give the connection a name.
- Hostname: For localhost this will be 127.0.0.1.
- Port: The port is (unless otherwise configured) 3306.
- Username: A username that has admin privileges on the db server.
- Password: The password for the above username.
That is the bare minimum you need in order to make your connection. After entering the information, click the Test Connection button. If the results are successful you can go ahead and click the OK button to make the connection.
When your connection is successful you will find yourself back at the main window, only your new connection will be listed in the Open Connection window.
Step 2: Connect to your server
From the list of servers (in the main window), double-click the server you want to connect to. This will open up a new tab in the Workbench window (see Figure 3) where you can begin to administer that database server. Let’s create a database.
Step 3: Create a database
Now it’s time to create your database. Poking around this window doesn’t reveal all that much, with regards to adding a new database. You can see two databases already there (information_schema and mysql). But how do you create a new database? Do you see that small “+” symbol in the upper right section of the lower middle pane? That’s the trick. Click on that button and a new window will open ready for you to create a new database (see Figure 4).
There isn’t too much information you need to add in order to create the new database. Here is what you need to add:
Name: The name of the database.
Default collation: UTF8 Default collation generally works. But if your database has special needs (as dictated by an external application for example) select the collation here.
Comments: These are optional.
When you are finished adding your information click Apply and a new window will open. This new window will inform you of all the changes that are about to take place. Click Apply Changes and the necessary SQL statements will execute to create your database.
At first you will not see your database listed. In order to see your database you must click the Refresh button in the toolbar. When you do this your new database will be listed and ready to go.
You have just created your first database using the very powerful MySQL Workbench tool. Congratulations!
Final Thoughts
I was quite fond of the MySQL Administrator tool. But now that I have seen how much more powerful and useful the MySQL Workbench is, I no longer pine for the simpler days of MySQL Administrator. MySQL Workbench is by far one of the best database administrator tools around. And you are now ready to tackle more complicated tasks. Keep coming back to Linux.com…we’ll touch base with MySQL Workbench and tackle more tasks with this powerful tool.
