MySQL is a very feature rich relational database management system (RDBMS) which has more than 11 million active installations and 60,000+ downloads/day. MySQL is commonly known to be an open source project available for free under GNU General Public License – this version of MySQL is called Community Server. MySQL Community Server is very often used in a development stacks consisting of Apache with the popular mod_php module interpreting PHP code running on Linux (LAMP – Apache HTTP Server MySQL PHP). All very successful free open source projects that together serve millions of websites today and the number is growing rapidly. Popular open source systems like WordPress, Joomla, Drupal, PHPbb and many more all run on this software stack.
MySQL is also offered in a version called Enterprise Server allowing companies to get Enterprise scale database software at a reasonable price (subscription based). See a comparison table here. The free community server is an excellent RDBMS but the tools you need as an DBA/Web site administrator or developer are only available if you subscribe to the Enterprise Server. Even though MySQL Enterspise Server is cheat compared to other high end database servers it is typically not an option for a blog or a small comapany website. Luckily there are other options and you can easily find free or cheap software providing the features you need.
If you’re using the MySQL Community Server or if you’re expecting to start using ityou need to find yourself some tools to support your management and development of your databases. As MySQL as open source roots there are many high quality 3rd party tools available and if you find the right match for yourself it may give significant advantages. It is always useful to know what tools you have available and what features they offer.
In the overview below tripwire magazine present some of the most useful and most popular MySQL Admin and Development Tools that both beginners and professionals can benefit of. Please drop a comment if an useful MySQL-tool is missing in the list and it will be added ASAP.
[exec]$filestr = file_get_contents(‘http://tripwiremagazine.com/googleadsensebelowmoretag.inc’);
phpMyAdmin is very popular and found out of the box in solutions like WAMP, LAMP, cPanel etc. It is very rich on features, easy to use tool, written in PHP (obviously) and then it is free. It has been built to handle the full administration of MySQL. It can create and drop databases, create/drop/alter tables, delete/edit/add fields, execute any SQL statement, manage users and permissions, and manage keys on fields. A nice feature from this legendary tool is that you can download and change the theme (not exactly a monumental feature, but a unique feature), download themes here: phpMyAdmin Themes (remember themes are version specific). phpMyAdmin is released free under GNU General Public License, version 2.
MySQL Workbench (Win, Linux, Mac)
This application is a fully featured visual database design package that includes database design, modeling, creation and maintenance bundled into a single environment for the MySQL database system. The free version is the Community Edition, which is loaded with features that will meet almost all your requirements. If you feel adventurous you could always go for the commercial version (Standard Edition).
MySQL Workbench is a cross-platform, visual database design tool developed by MySQL. It is the highly anticipated successor application of the DBDesigner4 project. MySQL Workbench will be available as a native GUI tool on Window, Linux and OS X. MySQL Workbench is available free under GNU General Public License
Aqua Data Studio (Win, Linux, OS X, Solaris)
This complete IDE, has four major areas of functionality: Database query and administration tools; a suite of compare tools for databases, source control and file systems; integrated source control client for Subversion (SVN) and CVS; and a powerful database modeler. The Studio has a single consistent interface to all major relational databases, this allows the developer to simultaneously tackle several tasks from within a single application. Avilable under Commercial license.
EMS SQL Manager (win)
EMS SQL Manager is a fairly high performance tool for database administration and development. Features include MySQL triggers, views, stored procedures and functions, InnoDB foreign keys, it allows you to create/edit all MySQL database objects, design databases visually, run SQL scripts, import and export database data, manage users and their privileges and has many other useful features. There is a commercial version, but download the free version, its fairly powerful and offers plenty of features.
MYSQL Front (win)
This graphical GUI for the MySQL database, is a “real” application, it can offer a more refined user-interface than is possible with systems built on PHP and HTML. Response is immediate, as there is no delay of reloading HTML-pages. If your provider allows it, MySQL-Front can make direct contact with the database. Otherwise, only one small script needs to be installed on the publishing website. Login information are stored on your hard disk, so you no longer have to log on to different web interfaces. Commercial license.
Heidi SQL (Win, Linux)
Heidi SQL is lightweight and fast with a flexible interface, it allows you to browse and edit your databases databases, manage tables, view and edit records, manage user-privileges, import data from text files, run SQL queries, synchronize tables between two databases and export selected tables to other databases or SQL scripts, and all this from an intuitive Windows interface.
You can also download a small app for the iPhone called jHeidi Mobi, whicht is the only application to offer Oracle and MySQL access in a mobile application. jHeidi Mobi. Licence free.
Sequel Pro (Mac OS X)
This MAC OSX database management app gives you direct access to your MySQL databases on local and remote servers with support for importing and exporting data from popular files including SQL, CSV and XML. Sequel Pro started as a fork of the open source CocoaMySQL project. Sequel Pro gives you direct access to your MySQL databases on local and remote servers.
- Easily set up a connection to the local MySQL server on your Mac
- Full table management including indexes
- Support for MySQL Views
- Use multiple windows to work with multiple databases/tables at once
Sequel Pro is a free open-source app
Navicat Lite MySQL Admin Tool (Win, OS X, Linux)
Navicat is a fast, reliable and popular Database Administration tool purpose-built for simplifying database management and reducing administrative costs. Designed to meet the needs of database administrators, developers, and small and medium businesses, Navicat is built with an intuitive GUI which lets you create, organize, access and share information in a secure and easy way. Navicat for MySQL is a powerful Database administration and development tool for MySQL. It works with any MySQL Database Server from version 3.21 or above, and supports most of the latest MySQL features including Trigger, Stored Procedure, Function, Event, View, and Manage User, etc. Navicat Lite is available as a free download only for non-commercial customers
Toad MySQL (win)
Toad for MySQL is a database development and administration tool that provides productivity features for rapidly creating and executing queries and managing complex database systems. It provides utilities to compare, extract and search for objects, manage projects, import/export data and administer the database. Changes to the interface recently has been the the introduction of the “Tabbed Document Interface”, this means you can view a fully maximized window without the need for you to constantly resize, arrange, or tile the windows. Toad is free.
SQLyog is another popular SQL management tool that has some useful features such as intelligent code completion, visual schema designer, visual query builder and a data / structure synchronization. It support HTTP / HTTPs / SSH tunnelling and SSL connections. There is a free Community version and a commercial Enterprise edition. You should also be aware of MONyog, a MySQL Monitor and Advisor. Commercial License
SQL Buddy (Web-App)
SQL Buddy is a great lightweight ajax powered database management tool It is as easy to install as it gets. All you need to do is unzipping the folder to your server – it doesn’t get simpler! You can do most of the operations that the majority of users will need. There is also a useful range of keyboard shortcuts, which you can view here: SQL Buddy Keyboard Shortcuts.
MyCon – MySQL GUI (win)
MyCon MySQL GUI is a very powerful windows based graphical user interface, for administering and working with MySQL server databases. This easy to use MySQL GUI front-end client includes all the functionality and tools you will need to be productive in MySQL as a DBA, developer, manager, report writer or a general end-user. Free and commercial versions available.
MySQL Sidu (web app)
MySQL Sidu is a FREE MySQL web based client, it is simple, intuitive and an easy database tool to use. Sidu is does not have as many features as most of the other tools but the fundamentals are inclueded and easy to use. It is ideal and mainly designed for performing Selects, Inserts, Deletes and Updates – exactly what the name represents (sido).
You can also use a Firefox Extension in conjunction with Sidu that enables you to insert data from text fields & textareas in a webpage. Firefox addon for MySQL Sidu.
DreamCoder for MySQL (win)
DreamCoder works with all MySQL versions from 3.23 to 6.0 and it supports all MySQL features including tables, views, procedures, functions, triggers, InnoDB Tables, foreign keys , UDFS, BLOB Types, etc. It also includes the most advanced options for data manipulation, build queries visually, execute queries, script execution, reports generation, database monitoring and export and import data to/from most popular file formats. DreamCoder for MySQL is easy to use for the novice MySQL user and is powerful enough for any expert user. Commercial license.
DBManager is a fairly powerful application data management data management. With builtin support for MySQL, PostgreSQL, Interbase/Firebird, SQLite, DBF tables, MSAccess, MSSQL Server, Sybase, Oracle and ODBC database engines, making it one of the most advanced database application. The free edition (Standard Edition) has a minimun set of features that makes it ideal for new database users to perform basic database management, you can use this for non commercial use.
WinSQL Lite is a database client that can connect to any database through ODBC. It provides an interface that allows you to run queries, create tables, browse data, edit/insert records and more. The multi-tabbed interface allows you to manage multiple queries and switch between the results. A Pro version is also available that offers some additional features
MyDB Studio (win)
MyDB Studio is a complete collection of powerful tools for MySQL Server administration. MyDB Studio (freeware) is designed for MySQL developers and administrators to rapidly manage, create and execute queries and develop SQL and Php code more efficiently. Keep an eye out for the upcoming Adobe Air version of MyDB Studio, that will be cool.
SQL Cheat Sheets
Lars started tripwire magazine back in January 2009. He is really passionate about web design, web development and loves to look into new technologies, techniques, tools etc. and to write articles for his readers.