mysql report displays mysql status changes in a friendly way. In fact, it reports almost all the states. Unlike show status, which only shows more than 100 state values, mysql report interprets and formats these state values in a humane way, greatly increasing their readability.
The advantage of MySQL report is that it can quickly view various state parameter groups to understand the server's running state without manual calculation from the results of show status. For example, the index read ratio is an important parameter, but it is not shown in the show status; it is an inference value (the ratio of key_reads to key_read_requests).
The following instructions are brilliantly used on the mysql command line to get the real-time status of the current database
mysql>show status; mysql>show innodb status;
But their results are not very friendly, and we need better and more human analysis results, rather than piled up a bunch of numbers. mysqlreport is a third-party Mysql status reporting tool, which processes mysql's show status and show innodb status results in a series of post-processing to make them more readable and friendly.
mysql report is written in perl language, so to run it, you need to install perl environment first; it also needs to connect with mysql database, so you need to install database interface DBI and database driver DBD-MySQL.
Installation of perl environment
[root@linux-node2 ~]# yum -y install perl perl-devel
[root@linux-node2 ~]# perl -v #If the perl version is displayed, the perl environment is installed
Installation of DBI
[root@linux-node2 ~]# cd /usr/local/src/
[root@linux-node2 src]# wget https://cpan.metacpan.org/authors/id/T/TI/TIMB/DBI-1.636.tar.gz
[root@linux-node2 src]# tar zxvf DBI-1.636.tar.gz
[root@linux-node2 src]# cd DBI-1.636
[root@linux-node2 DBI-1.636]# perl Makefile.PL
[root@linux-node2 DBI-1.636]# make
[root@linux-node2 DBI-1.636]# make install
Installation of DBD-mysql
[root@linux-node2 ~]# cd /usr/local/src/
[root@linux-node2 src]# wget http://search.cpan.org/CPAN/authors/id/C/CA/CAPTTOFU/DBD-mysql-4.020.tar.gz
[root@linux-node2 src]# tar zxvf DBD-mysql-4.020.tar.gz
[root@linux-node2 src]# cd DBD-mysql-4.020
[root@linux-node2 DBD-mysql-4.020]# perl Makefile.PL --mysql_config=/usr/local/mysql/bin/mysql_config
[root@linux-node2 DBD-mysql-4.020]# make
[root@linux-node2 DBD-mysql-4.020]# make install
Remarks:
1. The latest version of DBD-mysql is 4.042. Download address:
http://www.cpan.org/authors/id/M/MI/MICHIELB/DBD-mysql-4.042.tar.gz
2. If there is an error, the content is
PERL_DL_NONLAZY=1 /usr/bin/perl "-MExtUtils::command::MM" "-e" "test_harness(0, 'blib/lib', 'blib/Arch')" t/*.t t/00base....................ok 1/6
# Failed test 'use DBD::mysql;' t/00base....................NOK 2
# in t/00base.t at line 21.
# Tried to use 'DBD::mysql'.
# Error: Can't load '/usr/local/src/DBD-mysql-4.020/blib/arch/auto/DBD/mysql/mysql.so' for module DBD::mysql: libmysqlclient.so.16: cannot open shared object file: No such file or directory at /usr/lib64/perl5/5.8.8/x86_64-Linux-thread-multi/DynaLoader.pm line 230.
# at (eval 6) line 2 # Compilation failed in require at (eval 6) line 2.
# BEGIN failed--compilation aborted at t/00base.t line 21.
FAILED--Further testing stopped: Unable to load DBD::mysql make: *** [test_dynamic] Error 9 Solution
cp /usr/local/mysql/lib/mysql/* /usr/lib
cp /usr/local/mysql/lib/mysql/* /usr/lib64/ (If64Bit Otherwiselib)Installation of MySQL Report
It is recommended to install directly with yum
[root@linux-node2 ~]# yum -y install mysqlreport4. Use of myslreport
1. Help information and options
[root@linux-node2 ~]# mysqlreport --help
2. Output Information
[root@linux-node2 ~]# mysqlreport --user root --password 123456 -h 192.168.88.134 --no-mycnf --flush-status --outfile ~/123MySQL 5.6.35 uptime 0 0:58:2 Fri Jun 9 23:19:01 2017
__ Key _________________________________________________________________
Buffer used 1.00k of 8.00M %Used: 0.01
Current 1.46M %Usage: 18.25
Write hit 0.00%
Read hit 0.00%
__ Questions ___________________________________________________________
Total 229 0.1/s
Com_ 153 0.0/s %Total: 66.81
DMS 42 0.0/s 18.34
COM_QUIT 34 0.0/s 14.85
Slow 10 s 0 0/s 0.00 %DMS: 0.00 Log:
DMS 42 0.0/s 18.34
SELECT 42 0.0/s 18.34 100.00
REPLACE 0 0/s 0.00 0.00
DELETE 0 0/s 0.00 0.00
INSERT 0 0/s 0.00 0.00
UPDATE 0 0/s 0.00 0.00
Com_ 153 0.0/s 66.81
set_option 98 0.0/s 42.79
show_status 11 0.0/s 4.80
show_variab 10 0.0/s 4.37
__ SELECT and Sort _____________________________________________________
Scan 49 0.0/s %SELECT: 116.67
Range 0 0/s 0.00
Full join 2 0.0/s 4.76
Range check 0 0/s 0.00
Full rng join 0 0/s 0.00
Sort scan 7 0.0/s
Sort range 0 0/s
Sort mrg pass 0 0/s
__ Query Cache _________________________________________________________
Memory usage 16.81k of 1.00M %Used: 1.64
Block Fragmnt 100.00%
Hits 0 0/s
Inserts 1 0.0/s
Insrt:Prune 1:1 0/s
Hit:Insert 0.00:1
__ Table Locks _________________________________________________________
Waited 0 0/s %Total: 0.00
Immediate 0 0/s
__ Tables ______________________________________________________________
Open 68 of 2000 %Cache: 3.40
Opened 75 0.0/s
__ Connections _________________________________________________________
Max used 2 of 151 %Max: 1.32
Total 36 0.0/s
__ Created Temp ________________________________________________________
Disk table 1 0.0/s
Table 60 0.0/s Size: 16.0M
File 0 0/s
__ Threads _____________________________________________________________
Running 1 of 1
Cached 1 of 9 %Hit: 94.44
Created 2 0.0/s
Slow 0 0/s
__ Aborted _____________________________________________________________
Clients 0 0/s
Connects 0 0/s
__ Bytes _______________________________________________________________
Sent 234.47k 67.3/s
Received 12.34k 3.5/s
__ InnoDB Buffer Pool __________________________________________________
Usage 3.58M of 127.98M %Used: 2.80
Read hit 77.76%
Pages
Free 7.96k %Total: 97.20
Data 229 2.80 %Drty: 0.00
Misc 0 0.00
Latched 0.00
Reads 1.03k 0.3/s
From file 230 0.1/s 22.24
Ahead Rnd 0 0/s
Ahead Sql 0/s
Writes 1 0.0/s
Flushes 1 0.0/s
Wait Free 0 0/s
__ InnoDB Lock _________________________________________________________
Waits 0 0/s
Current 0
Time acquiring
Total 0 ms
Average 0 ms
Max 0 ms
__ InnoDB Data, Pages, Rows ____________________________________________
Data
Reads 249 0.1/s
Writes 5 0.0/s
fsync 5 0.0/s
Pending
Reads 0
Writes 0
fsync 0
Pages
Created 0 0/s
Read 229 0.1/s
Written 1 0.0/s
Rows
Deleted 0 0/s
Inserted 0 0/s
Read 3 0.0/s
Updated 0 0/s