|Strong MySQL Support Tools|
Motivation: New support tools thrust MySQL into new application domains
MySQL already has some of the best tools for supporting its database. PHPMyAdmin is an Open Source tour de force. For the price of a download, PHPMyAdmin allows users to not only maintain their MySQL databases but do elementary queries and browsing, data clean-ups plus a whole range of administration operations: alter tables, index tunings, back ups and recovery, bulk exports and/or imports of data. This is all a MySQL database pro could ask for - isn't it ?
The New DBA
Databases are growing like weeds. But they really should be spreading
like wildfire. Three things are holding database usage back.
Whatever the case, support tools for databases are going to have to become a lot easier to use and more expansive/capable - they are going to have to go beyond the DBArtisan product. There is nothing wrong with Embarcadero's DBArtisan product. It supports DB2, Oracle, SQL Server, and Sybase databases with database/schema management, security/roles, database space management, sophisticated query tuning. These basic capabilities are often supplemented by add-on or support tools that for example allow for SQL Profiling or advanced job scheduling and back up support. The problem with DBArtisan mirrors the databases it serves - its cost per seat is well above $100 and the complexity of some of the tools are reserved for sophisticated DBA's.
Software's Navicat. This has all the services and functionality of PHPMyAdmin
and then some. In fact Navicat has functionality that challenges DBArtisan
in several arenas - so it earns its $100 per seat cost. But Navicat appears
to have targeted the sweet SQL suite spot for midrange database functionality.
It goes beyond database administration and makes querying, creation of reports,
and the myriad of data import/export tasks very approachable. In short, with
a tool like this and a savvy developer or power user, one could feel confident
that a database has most of the support its going to need 365 days of the
However, Navicat certainly does not challenge DBArtisan for query tuning tools - especially given DBArtisan's companion SQL Profiler tool. But Navicat does offer add an important plus with its Reportwriter which brings banded reportwriting to SQL for an added $25. We did not test the report writer in full but found it to more than credible in producing both detail and summary reports. It certainly goes far beyond Oracle's SQL*Plus or SQL Server's ISQL.
However it was in the export and import arena where Navicat really impressed us. DBAs and developers find that their time is constantly being eaten up with a constant stream of requests for snapshots of sliced out of the database for use locally. No matter how many times you explain to the user how simple it is to do it themselves - you inevitably get the request.
Fortunately, Navicat comes to the rescue with a very robust set of options for exporting data to over two dozen desktop formats including Dbase, html, Excel in 2 formats, Word, XML, PDF and even to the Windows clipboard(very handy). Navicat does not offer some of the detailed control available in PHPMyAdmin; but it offers a broader selection of output formats.
Likewise, Navicat does not offer the sophisticated controls and scheduling capabilities of DBArtisan and its Job Scheduler companion tool; but Navicat does offer more flexibility on export and import of data. For example, on the import side, Navicat offers more file import formats and remarkably more options for linking to remote servers include SSL and SSH tunneling options. This reviewer was delighted to find that MySQL databases that did not have local PHPMyAdmin support were not accessible through Navicat.
For example, theOpenSourcery.com site does have PHPMyAdmin. But I have had problems updating memo fields with the modified version offered by our ISP. It has been a relief to be able to get to the site and make changes with Navicat's remote link capability with full memo field editing.
Also for a couple of projects it has been important to be able to monitor
during the course of the day the overall MySQL database status. Again, Navicat's
SQL Monitor capability proved to be very useful in tracking down some anomalous
behavior - a devious bug in our own code.
Other Versatile MySQL Tools
By no means is Navicat alone as a good support tool for MySQL. We have already mentioned PHPMyAdmin and this is a very powerful tool for MySQL administration and simple querying tasks. In fact, some would argue that in the Admin arena PHPMyAdmin is to be preferred for a couple of salient reasons.
First, PHPMyAdmin's tabbed interface and visual layout is dead simple to follow
and use effectively. All of the major options for daily administration of a
database have been taken into account. For developers, glancing through the
source code which is available on the website,
provides some very interesting insights into PHP and MySQL coding.
Another tool that deserves mention as useful for doing MySQL querying, viewing and admin work is Webyog's SQLyog. This tool is half the price of Navicat but certainly not half the functionality. SQLyog provides a Windows GUI interface to MySQL primarily for querying and browsing. But SQLyog also has a strong set of import and especially export tools. For users familiar with iSQL from Sybase this will be a comfortable tool as it replicates a lot of the features while adding some nice added touches such as as History tab and the Database synchronization capability (including remote database links). As well SQLyog has wide selection of keyboard shortcuts that many database developers and users find very helpful. But perhaps the most important SQLyog attribute is its blazing speed. SQLyog makes PHPMyAdmin's page refreshes look glacial. It also manages to outshine Navicat on many query and display functions - although we have yet to do a complete and thorough set of testing. SQLyog is fast and versatile and again provides a savvy developer with a broad range of DBA functionality that allows ready maintenance of a database.
The MySQL Payoff
MySQL offers developers and users more options in the support of medium to large scale databases in an organization. MySQL's own administration tools have improved but leave room for improvement. And tools such as SQLyog, PHPMyAdmin, and Navicat have certainly done so. In fact they have done so much that they make MySQL ever more attractive as the solution to the standalone database changeovers . You know - the group process scheduling or equipment inventory or vacation management databases that serve an immediate need but inevitably get called upon eventually to supply reports and/or data to regional or head office systems. Or these are Quattro Pro or Excel spreadsheets that really need a database for the systems they feed into and from. These mid-level databases used to be anathema because they quickly became data vaults - locked up and inaccessible but to a devoted few. Not so anymore, with the support tools we have have just described, MySQL can be safely removed from the Endangered of Becoming an Application Silo list. No small feat when database interoperability through SQL or if you can't, XML; or if you can't, Web Services is becoming the draconian marching orders of the day. Instead, MySQL plus supporting cast offers some right size and capability tools.
Top of Page Tutorials Home SQL References