Five Indispensable MySQL Tools
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.
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.
Figure 8. Searching the
INFORMATION_SCHEMAfor a Column Name
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.
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!
About the Author
Jason Gilmore is founder of EasyPHPwebsites.com. He is the author of several popular books "Easy PHP websites with the Zend Framework", "Easy PayPal with PHP", and "Beginning PHP and MySQL, Third Edition".
Page 2 of 2