Strong MySQL Support Tools
 

Motivation: New support tools thrust MySQL into new application domains


Premium Software's Navicat - A new Breed of Database Tool ?

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.
1)The cost per seat of databases:
2)the complexity and size of enterprise caliber databases;
3)the shortage of trained DBAs to properly maintain and support databases.
Open Source databases like MySQL, PostgreSQL, Firebird, and the newly "freed" Ingres and Cloudscape certainly address the first and part of the second issue. But the the third issue, even with outsourcing, is just not being addressed adequately. More often than not it is the binding constraint in database usage. Something is going to have to give - DBAs are going to have become more productive. Or developers and users are going to have to become more like DBAs.

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.

Enter Premium 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 year.

What NaviCat Does

Navicat runs in Linux, Mac and most versions of Windows. It supports only the MySQL database but most versions: 3.x, 4.x and the new 5.x series. The screenshot at the top shows Navicat linked into one local and two remote databases (both over the Internet at remote websites including theOpenSourcery.com). So within the exclusive MySQL framework Navicat is quite versatile and goes beyond PHPMyAdmin in its alit to reach remote databases.Yet from the popup menu it is evident that Navicat has a full compliment of database tools. users are able to add, delete. and alter tables, indexes, databases, plus a full range of security nd user role management.
In addition, Navicat has a robust set of query building and data display/editing tools. The screenshot at the left shows the Query Builder tool in action. the tool is similar and smart like the Microsoft Access query builder - making joins, grouping, and complex sorts much more approachable. Of course for SQL savvy developers and DBAs, the Query Editor tab allows complete command line editing of the SQL. And as can be seen from the toolbar it is easy to save and retrieve various queries. Navicat goes well beyond PHPMyADmin and challenges DBArtisan for ease of query building.

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.

In general, this is the attraction of Navicat. It provides a broad range of tools that cover most admin, querying and reportwriting needs for MySQL - such that a developer or power users can realistically become a sub-DBA meeting - 80-90% of the needs for DBA support on a MYSQL database. In sum it broadens the reach and possibilities for using MySQL in diverse database settings.

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.
The second advantage of PHPMyAdmin is that it goes in to more depth offering more control and.or admin options than Navicat.

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