In the twelve years since first being introduced to MySQL, I've spent a substantial part of my professional life interacting with the database. Like any long-term relationship, my success using MySQL can largely be attributed to how well I've been able to communicate effectively with the database. Thanks to a number of well-designed tools, MySQL simply is a very easy database to "talk to," a convenience which is particularly important because developers often are tasked not only with constructing very complex schemas and queries, but also with monitoring overall server health and performance. The latter often is a unique blend of art and science.
In this article, I introduce you to five indispensable MySQL tools, including some that I've used for more than a decade and one that I've been using for only about two weeks yet am already wondering how I ever got along without it.
Of the dozens of MySQL-oriented tools, phpMyAdmin is not only the most popular among the MySQL community, but having been downloaded more than 20 million times, it is also one of the most popular open source projects of all time. A web-based MySQL administration tool, phpMyAdmin is the de facto MySQL administration tool offered by web hosting providers. It offers novice MySQL users an easy way to create tables and manage data, while sporting a feature set capable of satisfying even the most experienced administrator.
In fact, phpMyAdmin's considerable capabilities make it possible to manage nearly every aspect of the database's lifecycle, including table creation, data insertion and updates, and backups. Many operations can be carried out with the click of the mouse, greatly reducing the amount of SQL syntax knowledge that would otherwise be expected of a user.
Figure 1 offers an example of phpMyAdmin's point-and-click capabilities, presenting phpMyAdmin's table schema review interface. The seven buttons to the right of each column give the user the ability to browse column values, modify the column, delete the column, make the column a primary key, make the column a unique key, add a column index, and designate the column as a fulltext index, respectively.
Figure 1. PHPMyAdmin Makes It Easy to View and Edit Table Schemas
Table columns can be edited with a minimum of effort, as shown in Figure 2. Just specify a field name, choose an appropriate data type, and set column attributes as desired!
Figure 2. Modifying a Table Column Is Easy Using phpMyAdmin
If you'd like to begin using phpMyAdmin and are already working with a web-hosting provider, then it's almost a certainty phpMyAdmin is already installed and at your disposal. Consult the hosting provider's support site for more information. Otherwise, you can download phpMyAdmin from the phpMyAdmin web site. Not a native English speaker? Not to worry, as the interface has been translated into 57 languages!
MySQL long suffered from the lack of a definitive data modeling tool until the MySQL development team introduced MySQL Workbench. Available for Linux, Mac OS X, and Windows, I've found Workbench to be an incredibly easy tool to use not only for designing graphical representations of a database, but also for generating SQL statements that can then be used to create the database. While Workbench continues to be under heavy developmenteven five years after its inception, this fantastic tool is a great addition to your development environment.
Designing a new database is simple using the diagram creation feature, as shown in Figure 3. Just create the tables and columns, and then use the palette to formalize the table relations. This particular diagram indicates two one-to-many relationships formalized between the account table and the state and country tables.
Figure 3. Designing a New Database Using Workbench's Diagram Feature
When complete, you can forward an engineer the diagram and execute a SQL script containing all of the database creation statements, as shown in Figure 4.
Figure 4. Creating a Database from a Workbench Diagram Couldn't Be Easier
MySQL Workbench is available in two editions. The Community Edition is freely available, while the enhanced Standard Edition comes at a cost of just $99 and includes advanced features not available in the Community Edition, such as model and schema validation.
The SQLyog MySQL management tool is admittedly the newest addition to my developer toolbox; so new in fact that I'm still running the 30-day trial, having installed it on the recommendation of a colleague. I was introduced to SQLyog during a recent contract, which required a tedious conversion of a fairly large corporate contact database (almost 500 tables totaling over 1 million records) to a new data management solution. The project involved writing dozens of stored procedures, views, and countless complex table alterations, updates, and insertions. SQLyog's incredibly streamlined interface made browsing this inordinately large number of tables almost painless, and ultimately greatly reduced the amount of time otherwise required to complete this project (to the benefit of both myself and the client).
Everything a developer typically requires when working with MySQL is available at a mouse-click, with a query result set, query profiler, server messages, table data, table information, and query history always available via the tabbed interface shown in Figure 5. In addition, it's easy to create views and stored procedures, a feature I personally have returned to time and again in recent weeks.
Figure 5. SQLYog Provides Easy Access to Crucial Analysis Tools
Thanks to a well-designed interface for browsing result sets, it's easy to page through even sets containing tens of thousands of rows. This interface is shown in Figure 6.
Figure 6. Browsing Even Large Data Sets Is Fast Using SQLyog
A community version of SQLyog is available, but I undoubtedly will purchase a license in order to support the long-term availability of this software. Several licensing options are available, starting at $69 for the professional edition and rising to $179 for the Ultimate Edition, which contains every feature a seasoned administrator could possibly desire.