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
» 
Comment and Contribute
