Taming MySQL Administration

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

Free book: Jump Start HTML5 Basics

Grab a free copy of one our latest ebooks! Packed with hints and tips on HTML5's most powerful new features.

  • kevincheung

    I’m just looking for an easy-to-use MySQL administration tool.

    Blane, thanks for your review and shed me a light.

    Before, i have tried using macsql and other applications, and none of them can provide all the features that I need. I especially like the multiple-database connection capabilities, database backup/ restore and remote database connection feature. More, the import/export wizard really save me lots of time to migrate data from different file formats.

    I am considering to purchase a Windows version for my pc and am comparing Navicat with phpmyadmin to see which one can optimize my daily administration work.

    Kevincheung

  • http://www.sanisoft.com tariquesani

    Also try DBdesigner from http://www.fabforce.net/dbdesigner4/ If you are looking for a tool to handle multiple database engines (and conversion of data to and from them) – is an Open Source Project available for Microsoft Windows

  • Tim

    What about this admin tool at mysql.com which is freely available?

    http://www.mysql.com/products/administrator/index.html

  • http://www.practicalapplications.net bwarrene

    There are numerous MySQL admin tools that can be utilized – many of which I have blogged here. Navicat is another excellent option, as is MySQL Administrator – which was covered here in its alpha state, for the job of database administration.

    I believe it really comes down to each user choosing a feature set that works for their platform and environment.

  • KillBill3

    i had a look at this about 18 months ago but prefered phpmyadmin for simplicity.

    but thanks for bringing it to my attention again, i will be taking another look at it soon :)

  • -void-

    I personally like having just the right tool for the job. I’ve been using DBDesigner4 for the better part of 2004 now and it is great for visually working out the planning and normalization.

    Navicat comes in, for me at least, when I’m putting together the application to draw from this web of tables. The visual query builder is among one of the best that I have used. For some sites where I have 30 or so tables and close to 100 different SELECT queries this tool is invaluable.

    If you like to see your data and relationships speak to you as graphs then that pair can’t be beat. Toss in the report builder and the other DB tools in Navicat and it’s a winner in my book.

  • kevincheung

    __________________________________________
    i had a look at this about 18 months ago but prefered phpmyadmin for simplicity.

    but thanks for bringing it to my attention again, i will be taking another look at it soon :)
    ___________________________________________

    I have just came across a very useful article that compares the strengths and weaknesses of Navicat and phpMyAdmin in different aspects including Interface, Speed, System Requirements, Functionalities, Performance and Cost.

    This artilce can be read here: http://www.geocities.com/mchuthor/review.html

    I think it can help all of you to get some insight.

    Kevincheung

  • Pingback: SitePoint Blogs » Blog Archive » Maturing Development and Management Tools

  • Jumanjee

    hey friends, everyone thinking of stable db management tool with all features in one. then just go for sqlyog which has even free community version. it has even good gui. highly recommendable ….just try it

  • terrina

    When reviewing my database integration classes from a PHP based application, I noticed that the method used to register a new user employed three individual queries: one to check if there already was a duplicate username in the system, another to check if there already was a project name assigned against the one requested, and one to insert the user’s inputted data. Granted, not all three would execute every time a user tried to register, but all three were required for a successful registration. Combine this with n amount of failed attempts, each resulting in either one or two added queries, and you can see that this particularly simple registration process was adding a lot of unnecessary overhead.
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    terrina
    administration jobs

  • terrina

    When reviewing my database integration classes from a PHP based application, I noticed that the method used to register a new user employed three individual queries: one to check if there already was a duplicate username in the system, another to check if there already was a project name assigned against the one requested, and one to insert the user’s inputted data. Granted, not all three would execute every time a user tried to register, but all three were required for a successful registration. Combine this with n amount of failed attempts, each resulting in either one or two added queries, and you can see that this particularly simple registration process was adding a lot of unnecessary overhead.
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    terrina
    administration jobs

  • terrina

    When reviewing my database integration classes from a PHP based application, I noticed that the method used to register a new user employed three individual queries: one to check if there already was a duplicate username in the system, another to check if there already was a project name assigned against the one requested, and one to insert the user’s inputted data. Granted, not all three would execute every time a user tried to register, but all three were required for a successful registration. Combine this with n amount of failed attempts, each resulting in either one or two added queries, and you can see that this particularly simple registration process was adding a lot of unnecessary overhead.
    ~~~~~~~~~~~~~~~~~~~~~~~~
    administration jobs