October 25, 2014
Hot Topics:
RSS RSS feed Download our iPhone app

Five Indispensable MySQL Tools

  • February 26, 2010
  • By Jason Gilmore
  • Send Email »
  • More Articles »

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.


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!

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


Tags: open source, MySQL, database, SQL, database administrator



Page 2 of 2



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Sitemap | Contact Us

Rocket Fuel