Taming MySQL AdministrationBy Blane Warrene
Taming the management of your MySQL Server may have just become a little easier with the most recent release of Navicat.
This GUI administration tool for MySQL servers has some advantages for the developer — for starters it runs on Linux, Macintosh and Windows with fair consistency (as far as equivalent feature sets go). A feature matrix is available online.
(to see the screen shots included with this post please see the Open Sourcery blog at http://www.sitepoint.com/blog-post-view.php?id=200601)
Several existing and new features will appeal to power users, including:
- Import from ODBC source (Windows only)
- Stored Procedure design for MySQL 5 (all versions)
- Batch Job and Scheduling (all versions)
- Data Transfer from MySQL to MySQL or to a file (all versions)
- Manage indexes and foreign key configuration
- User privileges management
I kicked around Navicat on both Mac OS 10.3.5 and Windows XP Pro using both development and production database servers and found it easy to use in function and interface. Some of the areas of interest are below.
The main view of Navicat, as shown below, is a clean workspace with support for multiple sql server connections.
Interaction with connections and tables can be done via double clicks and right click contextual menus.
To get started with testing, I moved some data from a production server to a development system via Navicat’s data transfer, which could surely become an invaluable tool for various purposes.
I then did some querying both through the programs visual query builder —
— and some simple sql tests via the raw sql pad also available.
Finally, one can see the results on demand within the program.
The included Export Wizard allows for export to delimited, XML and Dbase file formats on Mac OS X and up to 18 formats on Windows. Unfortunately the Linux version does not currently support exports from query results.
As with any database administrator’s routine, monitoring the performance of your databases is essential. The Server Monitor within Navicat allows for one or more active MySQL connections to run a “show processlist;” within the GUI. Tools include a static capture of running processes or an active refresh for ongoing tracking.
Additionally, with a click one can see the results of the “show status;” and “show variables;” mysql commands.
The ability to kill processes is available within Server Monitor.
Finally, a feature I like personally for use during development, Navicat’s Backup/Restore allows for hot backups and restoration of MySQL database/tables.
For smaller MySQL deployments this will also work as a production backup routine. However, in my case I leave production backup running via an automation script (not Navicat) on my various servers, dumping all backups to a separate consolidated backup server.
Navicat ranges in price from $45 US to $125 depending upon your licensing needs and can be purchased online from the company’s store.
Product inquiries can be directed to sales at navicat.com