4 Best tool for monitoring MySQL in Linux

There are a lot of tools for monitoring MySQL performance and troubleshoot a server, but they don’t always a perfect match for a MySQL developer or administrator for their common needs, or may not work in certain situations, including remote or over the web monitoring.

There is variety. On the other hand, it’s very difficult to locate these tools via web searches, that’s the reason we have compiled 4 command line tools to monitor MySQL database uptime, load, and performance in Linux.

Uptime means the database has been running its last shutdown or restart. Getting information about uptime is crucial in several situations, as it helps system administrators to check the status of MySQL database about many queries per second that MySQL database server, threads, a lot and slow queries of interesting statistics.

1.Mytop

Mytop is one of my classic open source and free console-based (non-gui) monitoring tool. Mytop runs in a terminal and displays statistics about threads, queries, slow queries, uptime, load, etc. in tabular format, much similar to the Linux top program. Which helps the administrators to optimize and improve performance of MySQl decrease server load and to handle large requests.

First we need to install Mytop package

1

apt-get install mytop-y

then create .mytop file in your root directory and this file contain with user and password your MySQL Database.

1

nano.mytop

this file contain with these information of your MySQL Database

1

2

3

4

5

6

7

8

9

10

11

user=root

pass=PASSWORD

host=localhost

db=mysql

delay=1

port=3306

socket=

batchmode=0

header=1

color=1

idle=1

Now you can run mytop tools to examine performance MySQL Database

1

2

3

4

5

6

7

ySQL on localhost(5.5.47-MariaDB)up0+00:47:45[06:36:47]

Queries:1.2kqps:0Slow:0.0Se/In/Up/De(%):56/06/13/01

qps now:2Slow qps:0.0Threads:1(1/0)00/00/00/00

Key Efficiency:76.9%Bps in/out:260.6/400.4Now in/out:48.1/11.2k

Id User Host/IP DB Time Cmd Query orState

--------------------------------

25root localhost mysql0Query show full processlist

2.Mtop

mtop is another MySQL monitoring tools free for monitoring MySQL Database performance, this tool is written in Perl language. the information is displayed in tabular format, this tool like mytop.

You can use mtop to monitoring which queries consume much resources system and you can kill those heaviest long running queries after those queries after specified time. But mtop may not work properly on new mySQL database installation.

First you need to install mtop along with dependencies

1

apt-get install mtop

after you’ve finished mtop installation then you need to grant privileges to mtop.

1

2

3

4

5

mysql-uroot-p

mysql>grant super,reload,process on *.*tomysqltop;

mysql>grant super,reload,process on *.*tomysqltop@localhost;

mysql>flush privileges;

mysql>quit;

Finally, you can run mtop and get result like this.

1

2

3

4

5

6

load average:0.11,0.13,0.15mysqld5.5.36up0day(s),0:04hrs

5threads:1running,0cached.Queries/slow:13/0Cache Hit:98.95%

Opened tables:0RRN:330TLW:1SFJ:0SMP:0QPS:0

ID USER HOST DB TIME COMMAND STATE INFO

57mysqltop localhost Query show

3.Innotop

Innotop is the free monitoring MySQL performance with real time advanced command line to monitoring MySQL database running InnoDB engine only. Innotop provide us many features and options which help us to quick and easy to monitor all aspect of MySQL performance and you can find what wrong in MySQL Database.

After you install Innotop, then you need to established connection to your MySQL Database, please check the following command for connecting Innotop to MySQL Database.

1

innotop-h127.0.0.1-uroot-pyourpassword

and after configure Innotop you can open Innotop Dashboard view and get the result like this.

4.MySQLadmin

Maybe you’ve known this tool, this is default tool MySQL client and this tool is preinstalled with MySQL packages, using this tool you can perform monitoring processes, reloading privileges of MySQL database, setting root password and create or drop your database of course much more you feature you can get using this tool.