Five Indispensable MySQL Tools

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.


phpMyAdmin
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

Related Articles

• Using JDBC with MySQL, Getting Started
• Manage Your MySQL Database with Rails' Migrations
• Managing MySQL Table Relations with Rails' Active Record Association Types

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 Workbench
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 development—even 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.


SQLyog
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.


mytop

Even the greenest of Linux system administrators quickly learns the system command top, which presents a well-organized snapshot of tasks currently running on the operating system and the amount of resources required by each, in addition to an overall summary of system uptime, memory, and CPU usage.

MySQL too is akin to an operating system in the sense that it must manage multiple resource requests, possibly originating from multiple users.

Thus, when working in environments involving a great deal of data throughput, MySQL developers will often refer to the SHOW PROCESSLIST and SHOW STATUS commands, which show information about currently executing threads and the status of the database server, respectively.

However, because unlike top these commands cannot run continuously, developers often find themselves running these commands repeatedly when diagnosing a suspected problem.

Enter mytop, a command-line MySQL monitoring tool written in homage to top. Created by Jeremy Zawodny, who as a Craigslist developer is no stranger to working with large data sets (he actually created mytop while working for Yahoo!).


mytop summarizes MySQL threads and overall system health much as top does, presenting a resource usage summary followed by a breakdown of each task, as depicted in Figure 7.

Figure 7. mytop Makes It Easy to Monitor Queries

As you can see from the image, mytop offers a bevy of useful information, including server uptime, key efficiency, total number of queries executed, active threads, and data throughput.

Like top, you can use a number of shortcut keys to change its behavior. Consult the main page for more information.

Supported on a number of operating systems, and requiring just Perl and a few Perl packages, you can download and begin using mytop right now by downloading a copy from here.

When installed, you can login to mytop in a fashion similar to logging into the mysql client, passing a hostname, username, password, and database:

%>mytop -h 192.168.1.101 -d easyphpwebsites_com -u webuser -p secret

Alternatively, you can store the connection information in a configuration file, hiding the password from prying eyes.


The mysql Client
While huge strides are clearly being made with MySQL's graphically-based administration tools, sometimes the easiest way to get a particular job done is by accessing MySQL directly through the native mysql client.

Whether it's creating a database, granting new user privileges, or quickly searching for the existence of a column name within the server (see Figure 8), chances are you'll be able to perform certain tasks much faster via the keyboard than by using a graphical interface.


Related Articles

• Using JDBC with MySQL, Getting Started
• Manage Your MySQL Database with Rails' Migrations
• Managing MySQL Table Relations with Rails' Active Record Association Types

Figure 8. Searching the INFORMATION_SCHEMA for a Column Name

The mysql client is just one of several native clients available to MySQL users, among them the mysqladmin, which facilitates the completion of various administration tasks such as database creation.

Be sure to check out the MySQL documentation for a complete list of what's available.


Conclusion
With so many great free and low-cost tools at our disposal, these really are the times of great fortune for MySQL developers around the globe.

Do you use a tool not mentioned here? Tell us about it in the user comments!