Mytop is a free tool for monitoring MySQL performance. It runs on the Linux command line, similar to top, except that Mytop displays MySQL records. Mytop can continuously monitor MySQL's online status, current processes, query records, user behavior, and other real-time information, helping us optimize MySQL's performance. This help is based on a CentOS cloud server and demonstrates how to install, set up, and use the Mytop tool.

Installing Mytop

First, install the EPEL software library, which is a free open source software package version library that provides a series of additional software packages for the Linux operating system.

yum install epel-release -y

Then, install Mytop with a single command.

yum install mytop -y

Setting up Mytop

To simplify the use of Mytop, we can create a configuration file for Mytop: /root/.mytop. When we run Mytop, the configuration file will be automatically called. If we want to be called by a non-root user, we can put the configuration file in the corresponding user directory.

The following is an example of a mytop file.

user=root
pass=
host=localhost
db=mysql
delay=5
port=3306
socket=
batchmode=0
header=1
color=1
idle=1

The above code provides the default parameters for Mytop runtime, reducing the trouble of manual input. However, if we enter the above parameters manually when running Mytop, they will override the default parameters in the.mytop configuration file.

The parameters in the mytop configuration file are as follows.

• user: database username.
• pass: database password. For security purposes, it is recommended to leave this field blank and enter it manually on the command line.
• host: database host.
• db: database name.
• delay: refresh interval in seconds.

For a full explanation of other parameters, see the help manual.

man mytop

Using Mytop

As mentioned above, Mytop uses both configuration files and command line parameters, and the latter overrides the former.So after preparing the mytop configuration file, we only need to enter a few parameters on the command line.

Example 1: If you want to enter the database password on the command line for security purposes, execute the following command.The database host, database name, and database username will use the parameters in the configuration file.

sudo mytop --prompt

Example 2: If you want to monitor a database, execute the following command, where yourdatabasename is replaced by the database name.

sudo mytop -d yourdatabasename --prompt

The results are as follows.

MySQL on localhost (5.6.26-log)                                                  up 0+08:36:33 [12:07:15]
 Queries: 921.0  qps:    0 Slow:     0.0         Se/In/Up/De(%):    00/00/00/00
             qps now:    0 Slow qps: 0.0  Threads:    1 (   1/   0) 00/00/00/00
 Key Efficiency: 100.0%  Bps in/out:   0.8/140.7   Now in/out:   9.7/ 1.9k

      Id      User         Host/IP         DB      Time    Cmd Query or State
       --      ----         -------         --      ----    --- ----------
       14      root       localhost      mysql         0  Query show full processlist

The first four lines are MySQL basic information, and the following is the current list of MySQL processes.To exit monitoring, press q.

This is how we installed, configured, and used Mytop. We can use the information collected by Mytop to optimize our MySQL database.