Posted Jan 5, 2011

MySQL Workbench - Top 13 things you should know

By Chris Schneider

MySQL Workbench has come a long way and keeps getting better! I honestly don't think that millage may vary on this one as it is one of the best no-cost GUI tools created for MySQL.

I've always been pro command line for MySQL administration; however, there have always been powerful GUI tools that can aid administration, development and other tasks. Some great tools that I have used in the past are Toad, Navicat, phpmyadmin, and WebYog. Although all of the tools listed above are good I have had a lot of success with MySQL Workbench, plus it's free.

I should also note that when I do use MySQL Workbench it's more for my QA, test and development MySQL instances. Call me crazy but I think it's just too easy to inadvertently damage production with how easy this tool is to work with. Plus, in most environments you don't want a flock of users looking at production servers anyway. That said, I do recommend MySQL Workbench to my developers' and sometimes even a savvy manager as long as they are off my production servers.

A full feature list can be found here. If you do venture to the feature list page, you should notice that there are few differences between the MySQL Workbench Community Edition (Open Source (GPL License)) and the commercial software version, MySQL Workbench Standard Edition (commercial software). Support for the standard addition is also offered on all levels of MySQL Support. There is a lot inside the MySQL Workbench so this makes it more of a set of GUI tools.

Here are the three main tools:

SQL Development

Data Modeling

Server Administration

SQL Development

1. The Visual SQL Editor

Who loves colored syntax? I do! Anyway, being able to visualize what you are doing in a convenient GUI is a great way to accomplish many tasks, both as a database administrator and database developer. The visual SQL Editor aids the user in building complex queries, editing and running queries, creating and editing data along with viewing and exporting the results. Fortunately you can also generate EXPLAIN plans on your queries; however, the EXPLAIN output is still the same. For all you PostgreSQL users out there, you know what I'm talking about, explain analyze! I digress…

Like the mysql command line client utility, MySQL Workbench has a full history panel, which provides complete session history. This makes it very easy for a user to review, re-run and modify previously executed SQL statement(s). Along with that, multiple queries can be executed at the same time while their results can be viewed in individual tabs. You can also have multiple SQL tabs open at the same time.

The Table Editor and SQL Snippet panel makes it easy to edit and or modify data. It also has and exporter so the user can export their data into CSV/TSV and other common formats.

As a DBA, I always have a common set of SQL I like to run, you may as well. If you do, the SQL Snippet panel is for you. This panel enables the user to save and reuse their commonly run SQL statements with a click of a mouse.

2. Connection Management

If you have a lot a development, test and QA machines there is an easy way to store multiple connections in the Database Connections Panel. You can also use the Wizard to create new connections for new server you'd like to maintain with MySQL Workbench. The Connections Panel enables the user to create, manage and most importantly organize database connections.

3. Object Management

The Object Browser is not something I typically use but it is worth mentioning. The Object Browser enables the user to visually select tables and columns to query, edit tables, create new tables and databases, and drop tables and databases.

Data Modeling

The MySQL Workbench brings power through visualization to DBAs, developers and or data architects. The main reasons why I like MySQL Workbench is that it's free, easy to install and easy to use. It allows individuals to design, model, generate, and manage databases in a single convenient software package. To be a bit more specific, a user can create simple to complex ER diagrams, forward and reverse engineering of physical database designs, Schema Synchronization and Comparison utilities, and DBDoc the point-and-click database documentation tool. Needless to say, all of these features are designed to make your day-to-day tasks fast and easy!

4. Forward and Reverse Engineering

Visio is a great tool for creating ER diagrams but when it comes to implementing your visual design on a target server it comes up short. MySQL Workbench has the built in functionality to turn visual ER diagrams into SQL statements' then run those statements on the server of your choosing saving the user a lot of time. With MySQL Workbench, you can transform a visual data model into a set of DDL and DML statements then push it to your target MySQL Server with just a few clicks. Having this feature enables the user to run the schema correctly initially instead of having to deal with a manual error-prone process. On the flip side you can turn this feature around and gather a visual model from a target database server or packaged application. MySQL Workbench can also import SQL files to build visual models and export these models to DDL files, which can be run later.

5. Change Management

NOTE: This is a Standard Edition feature so if you don't see it in your version and would like to know more please see MySQL's "MySQL Workbench Schema Validation Plugins (Commercial Version)" documentation.

Maintaining different versions of database schemas' and manually modifying them can be a difficult and complex process. If you don't use or have a system like Subversion or GIT this process can be even more error prone. Fortunately, MySQL Workbench aids users with change management through the built in the Schema Synchronization and Comparison utilities.

These tools give a user the ability to compare two live databases or a model and a live database. The user can even view the two schemas visually and see the difference or lack thereof. If something is not correct in one or the other, the user can perform synchronization between a model and a live database or vice versa. Overall, this tool is a great starting point if you don't already have a comparison tool in place!

6. Database Documentation

NOTE: This is a Standard Edition feature so if you don't see it in your version and want an idea of what it can do please click here for a great demo.

I encourage you to check out the demo if you are curious about this feature. The basic principles of it are deliver point-and-click database documentation; meaning, ERD models can be documented in either HTML or plain text format. This is a great tool to have if you want to create a fast and easy to read Wiki page or to send off to your team in an email.

Server Administration

NOTE: For full administration, you will need to have a user with the correct privileges initially to accomplish the following.

7. User Administration

User account management has been made a bit easier. From MySQL Workbench you can easily view account information for all users on the MySQL server, add and remove users, grant and revoke privileges, change passwords and modify both global and database permissions. This can be a lot easier than running multiple "CREATE USER" or "GRANT" statements on a MySQL server.

8. Server Configuration

If you want to quickly edit and tune your MySQL instance you can use the "Configuration" tab. MySQL Workbench has even separated variables into groups, for example, MyISAM Parameters, Performance, Log Files, Security, etc.

9. Server Connections

Basically, "show processlist" with auto-refresh. Nice to have.

10. Server Logs

Unfortunately, MySQL Workbench can only view logs if your log destination is set to TABLE. Setting the log destination to TABLE on MySQL can pose a performance problem and, in my opinion, should only be used in a development environment. If you do set your log destination to TABLE you will be able to view all the MySQL log files including error logs, binary logs, and InnoDB logs from MySQL Workbench. This is very convenient when trying to diagnose server problems quicker and track database changes.

11. Export/Import

This feature is basically a Visual tool for MySQLDump. Again, be careful with this tool, as it would be very easy to try to dump or load large tables to and from your MySQL instance. You can export or import data from the Object Browser with a point and click. There are also advanced configurations for concurrency controls, backup type, and output formatting.

12. Service Control

Start and stop MySQL servers and view corresponding log messages accompanied with these actions.

13. Server Status

A quick RRD like graph set at the top of your Server Administration session displaying server load, memory usage, connection usage, traffic, query cache hit rate and key efficiency. In the future, I would hope that these graphs would be customizable, and, possibly, include a full graph set in its own tab.

Closing Thoughts

MySQL Workbench has come a long way and keeps getting better! I honestly don't think that millage may vary on this one as it is one of the best no-cost GUI tools created for MySQL. There is plenty of solid documentation and tutorials on MySQL Workbench so you should have little trouble implementing and at least trying this powerful set of tools.

» See All Articles by Columnist Chris Schneider