Monitor a MySQL Server with mytop
Everyone who runs a Linux server knows the top command for process monitoring.
The mytop utility does the same thing,
but for a MySQL server.
mytop is only an apt-get away:
sudo apt-get install mytop
The basic command for running mytop is as follows:
mytop -u username -p password -d databasename
Some other useful arguments include -s (time in seconds before refreshing the
display), -P (specify a non-standard MySQL port to connect to), and -h (connect
to a remote host). If you don’t want to have to remember your options, you can
create a ~/.mytop file to store arguments in this format:
user=myuser
pass=mypassword
db=mydatabase
The mytop display is split into two sections. The first few lines show the hostname, server version, uptime, queries per second (qps) overall, current qps, number of threads, and a lot more. The lower part lists all the threads and their current query.
Press the q key to quit mytop.
I watched mytop on my own server for a while and found that the WP-Cache plugin Tombuntu runs leaves the MySQL server without much to do. But when a dynamic page is served it causes several queries. If you need to know what your database server is doing, mytop is an easy way to find out.
Archived Comments
coferm
I was wondering the same thing, please comment if this works to monitor remote servers.
coferm
Follow up: mytop -u -p -P -h will connect it to a remote machine.
Tom
mkwerner & coferm,
I just tried it, and I can’t get mytop to connect remotely.
‘mytop -u username -p password -d databasename -h xxx.xxx.xxx.xxx -P 3306’
results in:
‘Can’t connect to MySQL server ‘
Perhaps there is a setting in MySQL that stops this from working.
Chris
mytop works fine on remote databases.
But there’s a tweak to be done.
In the mysql database, in the user table, the Host value must be “%” for the
User “root”
A simple:
update user set Host=“%” where User=“root”;
flush privileges;
will do it.
Enjoy!
Chris
mkwerner
Tom,
Have you tried mytop with a remote database? The manpage is very vague on whether it works remotely or not.
After reading your post, I attempted to monitor a mysql db on my web server, to no avail. I specified host, port, etc…but it kept crapping out.
Just curious!
Thanks as always!
m.