Production MySQL Management with Navicat 2004

A bridge was constructed between the existing Apple user community and open source when OS X was released. It has not only helped migrate traditional Mac users to OS X, but also has generated a Switcher group of developers accustomed to working either from a UNIX variant or Windows-based workstation.

MySQL has clearly 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 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 just what the best path to database management is. This becomes an even more critical conversation in production environments with multiple servers and considerations of backup, performance and business continuity.

A first look at Navicat comes from an alternative need for offline or localhost access to tools as well as Internet-connected administration. This would rule out some powerful but pure browser-based alternative solutions. Second, users working from multiple platforms have the need for a tool that would span Linux, Macintosh, and Windows systems. Finally, when some of the production management involved servers holding sensitive data -- the need for an optional secure connection is evident.

If you come from a command line background, the obvious alternative here is the traditional MySQL client run in a Terminal over an SSH connection. However, if there is also a need for high productivity, automation and open-ended import and export capabilities -- this leads on a circuitous road back to Navicat where a majority of requirements can be met.

Navicat was founded in 2001, and according to company officials, central in their mission was 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-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).

Central to the database administrator is control, recovery and performance measurement. Navicat affords all of these responsibilities through a single interface.

In getting started, multiple connections to local and remote databases can be configured and accessed within the primary application window in Finder-style, cascading open to databases, then tables and ultimately data.

Support for SSL connections and SSH tunneling translates to raising the security level of Internet-based administration sessions. If there is a weakness in Navicat -- this may be one area as the SSH tunneling is only supported on the Windows release (currently) and SSL spans only to Windows and OS X. This leaves the Navicat client and user short of more comprehensive security during these sessions if working from a Linux workstation.

A Navicat spokesperson did confirm that users can expect SSH tunneling support on both Linux and Macintosh version in early 2005.

For multi-user environments, access control is granular as expected of any tool, including user, database and host configurations and the ability to flush privileges and refresh.

However, two key capabilities jump when considering those wearing a system administratoris hat. For starters, monitoring the performance of the server is central, regardless if your method is reactionary or proactive. Navicatis Server Monitor allows for review of server status, process list and server variables. The first two monitors being available in real time and with auto-refresh for troubleshooting and tracking (this should also be appealing to developers wanting to test query impact and performance).

Secondly, the capability to set and forget backups and data transfers is essential. Backups should occur on the schedule that meets your needs and transfers much the same. Navicat includes GUI and command line capability for backups, allowing scheduling of backups among or between 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 / disaster recovery architecture.

There are many occasions when data is shared within a company from disparate MySQL databases, or perhaps agreements exist with partners or even clients to route data sets to them on a schedule. Navicat allows for export to numerous formats (including delimited, SQL and XML) and for automating this process. It also supports the opposite if the end user is the partner or client pulling in data on a regular basis.

For those seeking to utilize 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 then a production server. Queries, reports, control of indexes and backups can be centrally stored and managed with a GUI package or again on the command line -- 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 was a nice touch, as were the abilities to schedule query execution, schedule batches of queries, manage stored procedures and compliance with MySQL 5.x.

From a reporting perspective, the Report Viewer included with Navicat enables 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 Microsoftis Query Analyzer or IBMis querying interface to DB2. It also allows for environments where reports are extracted and viewed offline instead of in a Web browser (i.e. analyzing data extracted into a spreadsheet application).

In any environment where third-parties develop tools for administration and management, there will be feature comparisons and contrasts against 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 get further in-depth with features and see how they compare to MySQL Administrator (see above) -- you can evaluate Navicatis scope online.

Navicat is currently available in English, Japan and Korean. The company is currently completing a German language release.