MySQL has become one of the central benchmarks for open source software that has crossed the bridge from exploratory to serious corporate player. During that time period, not only has an entire cottage industry of MySQL-related tools and services emerged, but several top-tier, commercial database providers have open sourced solutions. The latter has very likely occurred as companies seek to catch the wave as open architecture becomes a watermark for project planning in many organizations.
Watch any web forum related to MySQL and ultimately the discussion shifts to the best path to database management. This becomes an even more critical conversation in production environments with multiple servers and considerations of backup, performance, and business continuity.
My first look at Navicat came from a need for offline or localhost access to tools, as well as Internet-connected administration. This ruled out some powerful but purely browser-based alternative solutions. Working from multiple platforms, the need for a tool that would span Linux, Macintosh, and Windows systems was clear. Finally, some of the production management involved servers holding sensitive data--so I needed the possibility of an optional secure connection.
As I come from a command-line background, the obvious alternative here is the traditional MySQL client run in a terminal over an SSH connection. However, I also have a need for high productivity, automation, and open-ended import and export capabilities. This led me on a circuitous road back to Navicat, which met the majority of my requirements.
Navicat was founded in 2001. According to company officials, central in their mission was the need to fill a void in commercial management tools for open source.
The group jumped in with both feet, releasing the first iteration of the MySQL administration tool in January 2002. The company follows a refresh cycle of 90 to 120 days for incremental releases (i.e., 6.0 to 6.1) and every 12 to 18 months for major release revisions (6.0 to 7.0).
Vital components for the database administrator are control, recovery, and performance measurement. Navicat affords all of these responsibilities through a single interface.
When starting, the primary application window allows the configuration of and access to multiple connections to local and remote databases in Windows Explorer style, cascading open to databases, then tables, and ultimately data.
Figure 1. Main Navicat Explorer window
Support for SSL connections and SSH tunneling raises the security level of Internet-based administration sessions. If there is a weakness in Navicat, this may be one area, as only the Windows release currently supports SSH tunnelling, and SSL spans only to Windows and Mac OS X. This leaves the Navicat client and user short of more comprehensive security during these sessions when working from a Linux workstation.
A Navicat spokesperson did confirm that users can expect SSH tunneling support in both Linux and Macintosh versions in early 2005.
For multi-user environments, access control is as granular as one might expect of any tool, including user, database and host configurations, and the ability to flush privileges and refresh.
However, two key capabilities jumped out me when wearing my system administrator's hat. For starters, monitoring the performance of the server is vital, regardless of whether your method is reactionary or proactive. Navicat's Server Monitor allows the review of server status, process list, and server variables. The first two monitors are available in real time, with auto-refresh for troubleshooting and tracking. This should also appeal to developers wanting to test query impact and performance.
Figure 2. Navicat Server Monitor window
Secondly, I want to set and forget backups and data transfers. These should occur on schedules that meet your needs. Navicat includes GUI and command-line capability for backups, allowing the scheduling of backups across multiple servers. In addition, the capabilities to extract SQL from a database backup set or simply dump data to a file can prove invaluable for business continuity and disaster-recovery architecture.
Figure 3. Navicat backup window
There are many occasions when data is shared within a company from disparate MySQL databases. Perhaps you have agreements with partners or even clients to route data sets to them on a schedule. Navicat allows for exporting to numerous formats (including delimited files, SQL, and XML) and for automating this process. It also supports the opposite, if you are the partner or client pulling in data on a regular basis.
For those using replication to populate multiple databases or work with third parties, the wait will only be a few short months. Navicat expects to have replication support in early 2005.
From a development standpoint, software-driven MySQL management can come in handy. In many scenarios, a developer can have a local MySQL database, a staging server, and a production server. A central point of management, whether GUI- or command-line-based, allows the storage of queries and reports and control of indexes and backups in a single place--something not always convenient or feasible using a browser-based solution.
Second, access to some specific features will surely appeal--including transferring architecture and data changes to databases upstream. At the functional level, the a BLOB viewer/editor is a nice touch, as are the abilities to schedule query execution, schedule batches of queries, and manage stored procedures, as well as compliance with MySQL 5.x.
Figure 4. Navicat SQL editor
From a reporting perspective, the Report Viewer included with Navicat enables the trial testing of queries prior to conversion into queries for web applications. Additionally, for advanced query development, the combination of a raw SQL pad and a visual query builder introduces functionality similar that of Microsoft's Query Analyzer or IBM's querying interface to DB2. It also allows for extracting and viewing reports offline instead of in a web browser (for example, analyzing data extracted into a spreadsheet application).
Figure 5. Navicat Query Builder
In any environment where third parties develop tools for administration and management, there will be feature comparisons and contrasts to those management tools released by the product vendor. In this case, the folks at MySQL AB have also released a fine tool, aptly named MySQL Administrator, targeting system administrators and developers using their flagship platform. However, Navicat clearly presents some advantages--especially bridging multiple platforms. To go further in depth with features and see how they compare to MySQL Administrator (see above), you can evaluate Navicat's scope online.
Pentium II or better, 32MB RAM available for the applications, and Windows 98 or higher
Macintosh OS X Requirements:
Mac OS X 10.1 or later
Supports the Linux 2.2 kernel or later, and works with both KDE and Gnome
Navicat is currently available in English, Japanese, and Korean, with a German language release underway.
The price ranges from $85 to $135. There is an online purchase available, with the option to have CD media shipped.
Blane Warrene is a technologist, writer, and researcher focusing on Apple and open source technologies.
Return to ONLamp.com.
Copyright © 2009 O'Reilly Media, Inc.