2007-12-10

MySQL Development Tools - The Good, The Bad and The Ugly

One of the first things I did when I started learning MySQL is to find a decent GUI tool to administer the server, since I didn't think that the bundled MySQL Query Browser/MySQL Administrator is what most people use for complex work. Luckily, I was right.
There is a huge variety of MySQL development tools - some better and some worse. I'd prefer if I only had one tool that does it all - but if that's impossible, the important thing now is to pick the right one for the job.

Here it is, the (nearly) complete list of 3rd party tools. It took me a while to find out about all of them - so I hope this saves people quite some time there.

Here are the best ones I use on a regular basis, each one is good for different set of tasks:

  • SQLyog by Webyog (commercial, with free community version)
    Best combination - simple, fast, does just what it should and doesn't get in your way. Plus, the community version is free (except a small nag-screen). I use it most of the time and highly recommend it for most uses. Something that really annoys me though is that it looks like it has hanged when running long queries - though it doesn't really.
  • Toad for MySQL by ToadSoft/Quest Software (freeware)
    Ultra feature rich, and free. This one has it all. Very, very, very rich with features, from debugger to custom script generator ala SQL Server DTS.
    The problem: it has it all. Takes time to understand how it all works, and it's overkill for most users. It's completely riddled with bugs. Version 3.1 helped the stability yes still the mean-time-between-crashes is about 30 minutes. I really hope that some future version 3.x will make this one stable. When it doesn't crash, it makes good use of it's multi-threaded architecture, and it's very responsive when executing long queries.
  • MyDeveloper Studio by Core Lab (commercial)
    High quality, "Visual Studio"-like UI. Fast and efficient. Great solid and fast user interface, pleasure to use.
    We've actually got a license to this one, since it's the best program that includes a built-in debugger, and it was before ToadSoft released a version with a debugger as well. I use it all the time when I'm writing complex Stored Procedures.
  • MySQL Administrator
    It may look simple at first, but this little puppy is very powerful.
    I recommend you download this XML definition file for the health monitor - it really helps a lot to understanding what's going on inside your server.

I've tried these ones out, but didn't like them:

  • HeidiSQL (freeware)
    Lacks some advanced features, but stable and friendly.
  • MySQL DreamCoder (commercial, with freeware version)
    Doesn't do anything special, could be used instead of SQLyog I guess.
  • Navicat for MySQL (commercial, with a free "lite" version)
    I didn't like the interface, but overall the program is quite solid.
  • SQL-Front (commercial)
    Seems too rough round the edges. On the plus side, it is one of the few tools I've seen that support partitions. Problem is, if you need partitions your schema's data is too big to be edited using this tool (or almost any other) and you just need a SQL command prompt which any tool can give you.
  • DBArtisan by Embarcadero Technologies (commercial)
    Bit of a bloat-ware. A 165MB install just doesn't make sense to me.
    You can control any database you want, including Sybase, SQL Server, Oracle and whatever. Problem is with tools of this kind is that they tend to be too general and not really tailored to MySQL.
  • Aqua Data Studio (commercial)
    Another one of those control-all-your-different-databases-from-one-tool approaches.
  • Database Workbench (commercial, with lite version coming soon?)
    Functional but not exceptional. This is another tool that can administer all your databases from a single point. Has some nice touches like auto-generating Stored Procedures statements for DML, and the GUI is fast and clean. The free version could prove to be a SQLYog replacement.

Web based tools (I don't use them myself):

I didn't even try these ones out, since the website and the screenshots/descriptions didn't convince me I should:

That's really a lot of different tools. I hope I got all the important ones here.

Now, you can be one of them Open Source hippies and say that "It's amazing how the open source world nurtured an entire thriving ecosystem of wonderful free and commercial software around MySQL to enhance productivity and promote cultural diversity for the good of the world and preservation of endangered species" and all that stuff.
But if you're like me, you'd prefer there to be just one tool, that does everything just right. I know I do. Perhaps it's because I was born in communist Russia and I like the unified-centralistic approach to things? Perhaps it's because none of them is really feature-complete, stable and usable the way I'd like it to be? I guess we'll never know.
SQL Server Management Studio - I miss you.

Labels:

9 comments:

  1. SQLyog is a reasonable choice in that the existing feature-set works and is robust to boot. The problem is that the same feature-set is quite small, and usability is lackluster (there are no obvious keyboard shortcuts to move through the different panes, and that's just one example). Out of the GUI tools it's my favorite.

    Toad might seem impressive at first, but it's completely unusable -- 30 minuets MTBF is ridiculously optimistic with this software, as it consistently crashes and/or throws exceptions even on the most mundane of tasks (such as trying to edit a stored procedure). I'll revisit the application in a year or so if I'm still working with MySQL, but in the meanwhile I'd stay away from it.

    For me thus far the primary tool is the command line client, simply because it doesn't crash and does precisely what I tell it to. Obviously it's a crappy way to work, but until an SQL Server-esque admin tool comes out (or perhaps a reasonably low-cost version of SQL Navigator for MySQL?) I'll stick with it.
    ReplyDelete
  2. the one tool approach is good in theory,what happen when you only have one tool and it is a crappy tool? you get stuck with that crap,at least here you have a choice of the one who fit you most,by the way not everyone like SQL Server Management Studio(i like it as a developer but other may not like it).

    for example the only de facto server for hosting asp.net 2.0 application is IIS and most of the people who work with it will tell you that it is a huge crap and how many time they type iisrest per day(if not hour).
    ReplyDelete
  3. Sure, that's why I said "just one tool, that does everything just right". Notice the "does everything just right" part :) I prefer one thing that works, over 100 that don't.
    Of course I wouldn't want one that sucks, with no alternatives.

    By the way - there are many alternative tools for SQL Server as well. There is a free Toad version for SQL Server as well, there is ApexSQL that provide a nice set of tools too. you just probably never heard of most of them since the bundled tools is just great by itself for most of the tasks.
    ReplyDelete
  4. Good post. I've had pretty good luck with TOAD so far. A couple of error but it recovered itself without me having to restart.

    I have also used EMS SQL Manager. It's a decent, stable tool but not really different from any of the free ones.

    LewisC
    ReplyDelete
  5. Martijn ToniesFeb 20, 2008 04:30 AM
    I'm sorry to see you left out Database Workbench at www.upscene.com

    It's a multi-dbms development tool also supporting MySQL and very capable.
    ReplyDelete
  6. Martijn:
    I didn't find this tool in my original internet-sweep. I'll take a look at it and add it to the list.
    Thanks :)
    ReplyDelete
  7. Martijn ToniesFeb 21, 2008 07:16 AM
    Thanks for that. I can reveal that we will be releasing a free lite version for MySQL soon, it will be restricted in its feature-set, but very nice nevertheless.
    ReplyDelete
  8. Yes, I have to agree one toool does not meet all your needs, I am primarily a web developer and I use the following

    a) Navicat (my primary tool) for editing and designing database tables, editing content. It has an excellent interface for sorting anf filtering tables without having to write queries, exporting to other formats Excel, XML, CSV. The SQL script export is terrible, does not include drop table etc

    b) PhpMyAdmin - comes with the Wamp Server install for exporting the database structure and data into scripts

    c) SQL-yog - database comparisons and generating change scripts for upgrading schemas

    d) Core-Lab - excellent for developing stored procedures

    e) MySQL Query - comparing resultsets for different queries

    f) Console - when deploying to hosting environmentts
    ReplyDelete
  9. no doubt great post, try sqlyog, it offers cool db management tool & is under open source which has made backing up MySQL databases far easier than using the CLI interface. Its layout and UI is also a tremendous improvement over the Administrator GUI from MySQL AB themselves. i was suggested by few of my collegues. u should also go with this. highly recommendable.
    ReplyDelete

Subscribe to: Post Comments (Atom)