MySQL Support Tools

 

 

Review: MySQL Support tools have improved remarkably over the past 5 years
Feature: They still have a long way to go to be competitive with their competitors

Anybody who has developed with MySQL for any length of time, and especially on Windows, knows the poor man's tools that MySQL has presented to database administrators and developers especially for its 3.x and 4.x. versions. In the past three years, MySQL has dramatically improved its admin, design and query development tools putting on not only a GUI interface but also making access to help and guide's instruction a lot more accessible.

The screenshot below shows before and after. First there is the old and rococo WinAdmin utility for MySQL for use in Windows. Don't get me wrong - WinAdmin was halfway useful; it had the problem of just stopping well short of being really helpful. For example, as seen in the screenshot below you could view the MySQL table schemas but not change or create new ones.

Likewise the MY.ini setup exposed your root password and required for you to remember all the various .ini file options and settings. Also the Server info was short on performance and network status information. In short - major rework rework was required.

And MySQL developers have delivered a much improved MySQL Administrator as seen in the screenshot below. Its best feature is that it works with 3.x, 4.x and 5.x versions of MySQL.

Note that developers have an admin area that handles Server Info, Service, Control, User Administration, Health/Performance Monitors, Logs and Replication control, plus Backup and Restore all from the same basic interface that expose different properties and controls when a ribbon operation is highlighted. For example, the screenshot above shows the new MySQL Administrator set for the Schema view. Note that now developers can view all the tables and their properties plus edit a table's definitions, do maintenance on a table (check indexes and its the tables integrity) or create a new table.

And users do not have to remember the sometimes esoteric command syntax to do these operations. However, for the manly DBA men, the MySQL command line utility is a convenient Tool | Command Line menu item and so readily available so users can do all of these changes with ALTER TABLE and other DCL commands. But let me assure you, admin is quicker and more convenient to do here for the bulk of your DBA work with the Command Line serving in a pinch. The Command Line advantage is that you can enter

In fact this reviewer is at a loss to explain why the MySQL Administrator is not installed with the MySQL database. It currently is a separate download. However, if you look at the control panel at the left hand side of the screen you can see the revamped and enhanced admin capabilities that make MySQL Administrator an essential part of any MySQL installation:
Server Information - provides quick essential info on the MySQL database instance
Service Control - provides essential control for MySQL start-up and shutdown steps
Server Variables - provides detailed viewing and control of the startup my.ini settings
User Administration - truly simplifies the user GRANT and privilege settings
Service Connections - allows kill control of all active user connections to the database. This could be nicer, allowing messaging to the user and direct link to a user's privileges view.

Health - provides graphical view of the connections and memory usage (see above)
Server Logs - much improved access to log data with index and search capability
Replication Status - shows status of replication service essential in n-tier apps

Backup - is full service backup facility with scheduling (see screenshot above)
Restore - is much improved control over restore operation could still use better op control
Catalogs - allows viewing and updating schemas; missing new database creation/maintenance
Notably missing from this list is any control over user created journals/logs and any tools for managing stored procedures, triggers, constraints, etc. Despite some of these shortcomings, this a vastly improved core administration facility that makes MySQL much more credible as an enterprise caliber database.

Command Line and Query Facilities

For all developers and database administrators MySQL preserves their hard-learned command line skills (Oracle users thing SQL*Plus, SQL Server users think ISQL), as shown below.

If you look at the Health screenshot (2 above this), MySQL Administrator has the Command Line utility as a convenient pulldown item. And its is convenient because users don't have to navigate to the directory to access it and you are immediately signed in with the same user account used for getting into the MySQL Administrator. This is quick and handy for doing things like creating a database or setting some specialized GRANTs that are unavailable in the MySQL Administrator. As well I often tryout SQL functions and queries here. However, the new MySQL Query Browser has improved tremendously and is worth a look see.

For a long time the joke was how primitively bad the Windows MySQL Query Browser was - no longer. As can be seen from the screenshot below the Query Browser is fully armed. Users can

can develop SELECT, INSERT, UPDATE, CREATE commands with drag and drop ease. Yes, it takes a little getting used to how the buttons work. You have to have the Schemata tab opened and then consider that each button starts or adds to its respective clause a field or table name. At first, I thought - what is the advantage of this ? Then after being caught out by the Query Browser for misspelled field or table names (or wrong syntax), I caught on - these buttons really do help create queries and so I switch to the Command Line utility less often. There are three additional big reasons for sticking with the Query Browser.

First, the display of data from queries is in a user customizable data grid. Users can narrow or widen any field or drag and drop it into a new column position. Scrollbars allow users to see all the fields laid out in a row - not the mishmash that frequently occurs with the Command Line browser. Second when users do a SELECT * FROM MYTABLE; the data displayed in the grid can be edited and updated. You cannot add new data;but this is a major convenience.

Third, Query Browser provides very helpful inline help by means of the Help panel at the lower

right of the Window(see screenshot above). The tabs in the Help panel allow users to choose from Syntax, Function or Parameters. Given the proliferation of options in database commands this is a unique innovation which all the database vendors will be imitating with their Query tools - hopefully sooner rather than later.

As one can see from the screenshot above I have used the Help panel to get the syntax of the UPDATE command absolutely right the first time. The one drawback in the Query Browser is that it does not provide syntax help for the new triggers and stored procedures of MySQL 5.x. However, it does provide help for the new Views and SubQueries.

The final feature of the MySQL Query Browser that we cover here is the Scripting interface.

Clearly this is intended for the new triggers and stored procedures as well. And there is a smidgen of documentation in Query Browser's Help files about stored procedures. But we found some of the critical features did not work and there is no Inline Help on either triggers or stored procedures. However, the Scripting interface is available also for executing large and small SQL scripts. We strongly recommend using the File | Open Script menu commands for any script greater than 2-3 SL commands. Why ? Because if you use Continue button (see screenshot above), users are helped if the script bombs on a script statement.

In the screenshot we have illustrated this by deliberately changing DROP TABLE to CROP TABLE and the Scripting engine has stopped on the offending statement and given us a diagnostic error message at the bottom of the screen. All I have to do to is correct the offending error and press the Continue button and the script will continue along from this point. Very Very useful.

Summary

As can be seen from the screenshots and description above, MySQL has certainly improved its database query and administration tools. But also note from our remarks there many areas for improvement. For example, there are third party tools such as Navicat or SQL Yogi that provide better data import facilities, more exporting options, basic report writing and direct connect to remote MySQL databases for distributed support among other features. Also, I was surprised that MySQL did not take a cue from IBM and Oracle. Both provide OS platform neutral support tools. IBM DB2 Command Center is not only quite powerful but because its Java-based it runs on every OS platform that DB2 supports and in a highly uniform fashion. Ditto for Oracle but they use a Web browser interface for their Enterprise Manager and SQL*Plus Query tool. mySQL's tools are ported only to RedHat Linux, MacOS and Windows.

Nonetheless, kudos to MySQL for some nifty innovations in its database tools. The Administrator has many nice touches in displaying and controlling System Variables and Backups. The Command Line utility is made more accessible thus preserving existing developers skill sets. And the Query browser is doing innovative things with the click/drag and drop query buttons, the inline Help panel, and the debug-enabled scripting interface. As noted above, these tools bring MySQL another big step closer to enterprise caliber functionality.

(C)JBSurveyer  Home  SQL Overview