MySQL GUIs — Navicat, MySQL Query Browser and phpMyAdmin

In the past few weeks I have been experimenting to see if a GUI interface to MySQL would make my life as a DBA any easier.

phpMyAdmin (available at http://www.phpmyadmin.net)

Navicat (available at http://www.navicat.com

MySQL Query Browser and MySQL Administrator (available at http://dev.mysql.com/downloads/gui-tools/5.0.html)

  • The Basics
  • Connecting

    • Navicat and MySQL Query browser both allow connecting and saving connections for future use. Both allow for saving the passwords for the database connection, although you have to specifcally tell MySQL Query Browser to do so. But MySQL Query Browser allows you to choose what format you’d like to save the passwords in — plaintext, obscured or OS specific.

    MySQL Query Browser lets you specify a default schema, and Navicat allows you to test a connection before saving.

    Navicat allows for all sorts of different proxies and options: SSH tunnel, HTTP tunnel, HTTP proxy, SSL and compression. Navicat allows saving passwords, for the proxies and tunnels as well as the database user.

  • Querying

    • Navicat has a “Design Query” tab as well as an “Edit Query” tab. In the “Design Query” tab, a user can design their query in the SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY … LIMIT syntax. Everything is point and click, with menus and checkboxes to select tables, fields, functions, etc. This is useful for a lazy DBA or someone not familiar with the tables. I’ve found that it’s easier for me to type in the query, although being able to click a check box and scroll a menu for the correct field name is useful. You can even change the indentation if you want.

    It’s smart enough to take what you type in the query editor and translate it to the design editor, so if you want to do a hybrid of typing and selecting, you can. And the query editor tab shows syntax highlighting — strings in red, numbers in green, and MySQL commands in blue.

    Navicat allows you to save and load queries. Whenever you run a query, it shows you the explain output as well, which is a win over the commandline, where you’d have to type it out first. There’s no button to just do an explain, which would be nice, but you can just do it the old-fashioned way and type “EXPLAIN” at the beginning.

    Results can be exported in numerous different ways (see screenshot), with different delimiters and fields, and the option to include the field headers — the latter is something that SELECT…INTO OUTFILE cannot do.

    Three buttons allow you to see large text, hex and images properly — “memo”, “hex”, and “image”. These toggle a frame at the bottom (see screenshot) which show the different fields in their entirety — this is very handy for images stored in the database.

    I found Navicat extremely useful for giving database access to our Quality Control team, which deals with creating and enforcing data policies. The team was always asking me for one-off reports (ie, “can you send me a list of usernames of all users who haven’t logged in for 6 months?”), and they would then take that information and make an Access database of it. So they knew some SQL before using Navicat. I set them up with a proxy, saving the passwords so I did not have to create separate logins on the proxy or database servers. Now, they come to me and ask questions like, “Can you mark all users who haven’t logged in for 6 months for deletion?” — I gave them read-only access.

    Basically, for Navicat you really have to know SQL to do anything complex. But it’s great for folks just starting out, who want to learn more SQL, especially in a heterogeneous database environment, or somewhere they want centralized access.

  • Reporting/Scripting

    • The report feature on Navicat has Filemaker-style reports — this is great for a company that does not need an expensive tool like Crystal Reports but does not want to do all of their databases and reports in-house using a programming language. Reports in Navicat can be formatted to suit periodic reporting requirements in nice-looking reports that upper management will appreciate for the information and the look.

    Scripts in Navicat are very lacking — in that you can only run one MySQL command at a time. You can save multiple queries together but you cannot “run a script” with Navicat (at least, not that I found).

  • Data Manipulation (DML)

    • Both Navicat and MySQL allow you to write DML in the query editor. Navicat does not give you syntax for UPDATE nor DELETE. MySQL Query Browser has links in the bottom right corner that allow you to see the commands and then click through to offline manual pages. This “Inline Help” is very valuable for DBA’s who know approximately what they need but just need a little help with syntax.

    Both Navicat and MySQL Query Browser allow you to double-click on a result and change it, although with MySQL Query Browser you need to

  • Data Definition (DDL, Creating and Editing Schemas)

    • Navicat allows for changing table types, fields, indexes, foreign keys, character sets, collations, and table comments easily.

  • GRANTs

    • Navicat allows easy, clickable GRANT access. There’s not much to say about this, it’s full featured point-and-click, including complex user features such as max_queries_per_hour. Again, you need the knowledge that % is a wildcard to be able to fill in blocks correctly, a wizard to do that for you would be nice. Then again, it’s not a tutorial or replacement for knowledge, rather just a tool.

    MySQL Query Browser does not do user administration, it leaves that to the MySQL Administrator GUI.

  • Advanced

    • Navicat allows the creation of stored procedures and views. Views have a nice “builder” like the table builder, which is point and click, but the stored procedure does not help walk you through anything.

    With MySQL Query Browser you can right-click on a table to create a view or stored procedure, and then edit the template, which is basically a script, and you can do the same dragging and dropping that you can while building a query. Both Navicat and MySQL Query Browser allow you to see and edit stored procedures easily.

    For both tools, it would be neat if there were easy “insert cursor” features, or some kind of wizard to help create stored procedures and/or common functions.

In the past few weeks I have been experimenting to see if a GUI interface to MySQL would make my life as a DBA any easier.

phpMyAdmin (available at http://www.phpmyadmin.net)

Navicat (available at http://www.navicat.com

MySQL Query Browser and MySQL Administrator (available at http://dev.mysql.com/downloads/gui-tools/5.0.html)

  • The Basics
  • Connecting

    • Navicat and MySQL Query browser both allow connecting and saving connections for future use. Both allow for saving the passwords for the database connection, although you have to specifcally tell MySQL Query Browser to do so. But MySQL Query Browser allows you to choose what format you’d like to save the passwords in — plaintext, obscured or OS specific.

    MySQL Query Browser lets you specify a default schema, and Navicat allows you to test a connection before saving.

    Navicat allows for all sorts of different proxies and options: SSH tunnel, HTTP tunnel, HTTP proxy, SSL and compression. Navicat allows saving passwords, for the proxies and tunnels as well as the database user.

  • Querying

    • Navicat has a “Design Query” tab as well as an “Edit Query” tab. In the “Design Query” tab, a user can design their query in the SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY … LIMIT syntax. Everything is point and click, with menus and checkboxes to select tables, fields, functions, etc. This is useful for a lazy DBA or someone not familiar with the tables. I’ve found that it’s easier for me to type in the query, although being able to click a check box and scroll a menu for the correct field name is useful. You can even change the indentation if you want.

    It’s smart enough to take what you type in the query editor and translate it to the design editor, so if you want to do a hybrid of typing and selecting, you can. And the query editor tab shows syntax highlighting — strings in red, numbers in green, and MySQL commands in blue.

    Navicat allows you to save and load queries. Whenever you run a query, it shows you the explain output as well, which is a win over the commandline, where you’d have to type it out first. There’s no button to just do an explain, which would be nice, but you can just do it the old-fashioned way and type “EXPLAIN” at the beginning.

    Results can be exported in numerous different ways (see screenshot), with different delimiters and fields, and the option to include the field headers — the latter is something that SELECT…INTO OUTFILE cannot do.

    Three buttons allow you to see large text, hex and images properly — “memo”, “hex”, and “image”. These toggle a frame at the bottom (see screenshot) which show the different fields in their entirety — this is very handy for images stored in the database.

    I found Navicat extremely useful for giving database access to our Quality Control team, which deals with creating and enforcing data policies. The team was always asking me for one-off reports (ie, “can you send me a list of usernames of all users who haven’t logged in for 6 months?”), and they would then take that information and make an Access database of it. So they knew some SQL before using Navicat. I set them up with a proxy, saving the passwords so I did not have to create separate logins on the proxy or database servers. Now, they come to me and ask questions like, “Can you mark all users who haven’t logged in for 6 months for deletion?” — I gave them read-only access.

    Basically, for Navicat you really have to know SQL to do anything complex. But it’s great for folks just starting out, who want to learn more SQL, especially in a heterogeneous database environment, or somewhere they want centralized access.

  • Reporting/Scripting

    • The report feature on Navicat has Filemaker-style reports — this is great for a company that does not need an expensive tool like Crystal Reports but does not want to do all of their databases and reports in-house using a programming language. Reports in Navicat can be formatted to suit periodic reporting requirements in nice-looking reports that upper management will appreciate for the information and the look.

    Scripts in Navicat are very lacking — in that you can only run one MySQL command at a time. You can save multiple queries together but you cannot “run a script” with Navicat (at least, not that I found).

  • Data Manipulation (DML)

    • Both Navicat and MySQL allow you to write DML in the query editor. Navicat does not give you syntax for UPDATE nor DELETE. MySQL Query Browser has links in the bottom right corner that allow you to see the commands and then click through to offline manual pages. This “Inline Help” is very valuable for DBA’s who know approximately what they need but just need a little help with syntax.

    Both Navicat and MySQL Query Browser allow you to double-click on a result and change it, although with MySQL Query Browser you need to

  • Data Definition (DDL, Creating and Editing Schemas)

    • Navicat allows for changing table types, fields, indexes, foreign keys, character sets, collations, and table comments easily.

  • GRANTs

    • Navicat allows easy, clickable GRANT access. There’s not much to say about this, it’s full featured point-and-click, including complex user features such as max_queries_per_hour. Again, you need the knowledge that % is a wildcard to be able to fill in blocks correctly, a wizard to do that for you would be nice. Then again, it’s not a tutorial or replacement for knowledge, rather just a tool.

    MySQL Query Browser does not do user administration, it leaves that to the MySQL Administrator GUI.

  • Advanced

    • Navicat allows the creation of stored procedures and views. Views have a nice “builder” like the table builder, which is point and click, but the stored procedure does not help walk you through anything.

    With MySQL Query Browser you can right-click on a table to create a view or stored procedure, and then edit the template, which is basically a script, and you can do the same dragging and dropping that you can while building a query. Both Navicat and MySQL Query Browser allow you to see and edit stored procedures easily.

    For both tools, it would be neat if there were easy “insert cursor” features, or some kind of wizard to help create stored procedures and/or common functions.