How to Monitor MySQL Using Mytop?

Mytop is an open source, command line tool which is used for monitoring MySQL performance. It is most similar to the Linux monitoring tool TOP. We can use Mytop to monitor MySQL real-time performance, uptime and queries and will also show the details of database users.

 

Installing Mytop

1) Log in to the server as root user via SSH.

 

For centOS

2) Install EPEL repository using the below command.

# yum install epel-release

To verify the yum repository list updated or not we can use below command

# yum repolist

As we have updated the EPEL repository on the server install the Mytop package using the EPEL repository with the below command.

# yum install mytop –y

After the installation, we need to configure Mytop. We can configure the Mytop using the customized file .mytop to create the configuration file,  open that file and add the below details to it.

 

For Ubuntu

To install Mytop please run the below command.

# apt-get install mytop

# vi ~/.mytop

host=localhost

db=mysql

delay=10

port=3306

socket=

batchmode=0

color=1

idle=2

 

Now we have successfully configured Mytop. You can also make changes to this configuration file depending on your needs. For example, the delay option specifies the amount of time in seconds between display refreshes.

If you enter delay=5 then monitor tab will refresh in every 5 seconds.

The idle parameter will allow sleeping threads to appear in the list in Mytop display screen. The default is to show idle threads. If you want to omit the idle threads then set idle=0, then the default sorting order is reversed so that the longest running queries appear at the top of the list.

Connecting to my Top

Below is the command used to check the performance of the mysql where –prompt is used to prompt the Mytop for the mysql database password. Enter the password then you can monitor the mysql.

# mytop –prompt

Now we will use the specific user to run the Mytop command

# mytop -u root –p

If you would like to monitor particular database, then use the -d option as shown below. For example, the below command will monitor database “db”.

# mytop –prompt -d db

You can also find more switches from the below command.

#man mytop

You should now have a good understanding of how to use Mytop to monitor your MySQL server. It is also a starting point for finding problem SQL queries and optimizing them, thus increasing the overall performance of the server.

 

If you need any further help please do reach our support department.