mysql show global_自己写的一个MYSQL监控show global status工具用于查看指定时间内变化的指标...

工具用于查看指定时间内show global status指标的变化，能够帮助运维人员了解系统负载的走势

本工具在5.5 5.6 5.7中测试通过

源码我放在百度云盘了

http://pan.baidu.com/s/1mhIeKp6

源码一共5个文件

conmysql.c main.c findv.c other.c type.h

如果源码编译使用如下方法

1、建立一个MYSQL用户用于监控，不需要什么权限只要能够show global status 即可

mysql> create user mmon@'localhost' identified by 'test123';

Query OK, 0 rows affected (0.30 sec)

这样就可以了

2、找到你的MYSQL库文件

一般在你的安装目录lib 目录下

如/mysqldata/mysql5.7/lib

export LD_LIBRARY_PATH=/mysqldata/mysql5.7/lib

3、编译源文件

原版

gcc conmysql.c main.c findv.c other.c -L/mysqldata/mysql5.7/lib -L/mysqldata/mysql5.7/lib -lmysqlclient -I/mysqldata/mysql5.7/include -o mmon  -W -fPIC

percona

gcc conmysql.c main.c findv.c other.c -L/dbdata/mysql3307/lib  -lperconaserverclient -I/dbdata/mysql3307/include -o mmon  -W -fPIC

4、帮助

[root@testmy mysqlmon]# ./mmon -help

Welcome use this mon tool!

This mon tool is calc status delta!

Author:gaopeng QQ:22389860

This mon tool is calc status delta, interval time is paramter -l define default is 10 seconds

but cant's less than 3 seconds

-t define how many times defualt is 1440 times

If -10 appear means is that no values find!

Usage::./mmon -h -u -p -P/-s [-l] [-t]

-h:host

-u:username

-p:password

-P:port

-s:socket

-l:interval time of seconds

-t:how many times

-l 是时间间隔默认是10秒，指定时间最小为3秒，小于3秒会报错。

-t 是你想采样的此时默认是1440次，不能小于1.

5、使用

./mmon -h=localhost -u=test11 -p=gelc123 -s=/mysqldata/mysql5.7/mysqld3307.sock -l=5 -t=3

按ctrl+c 即可以退出

注意-p=lc1\$23 这里的特殊字符用一下转义\

输出为：

Welcome use this mon tool!

This mon tool is calc status delta!

Author:gaopeng QQ:22389860

Your input paramter is: -h,localhost

Your input paramter is: -u,mmontest

Your input paramter is: -p,Lc123

Your input paramter is: -s,/dbdata/mysql5800/mysql.sock

Your input paramter is: -l,5

Your input paramter is: -t,3

Parameter counter is: 5

Mmonitor:host is:localhost,user is:mmontest,passwd is:gelc123,socket is:/dbdata/mysql5800/mysql.sock,port is 0

Mmonitor:Connect mysql server success

Mmonitor:status rows : 402,field : 2

------------------------------------------MMON--------------------------------------------

SysTime: Mon Sep 12 12:25:28 2016

Uptime:2312561    Connections:1       Aborted_clients:0       Max_used_connections:44

--------------------------------------MYSQLINFO-------------------------------------------

Threads_connected:12          Threads_running:1           Qcache_free_memory:1031328

Qcache_hits:0                 Qcache_not_cached:0         Qcache_inserts:0

Handler_read_first:0          Handler_read_key71          Handler_read_next0

Handler_read_last:0           Handler_read_prev:0         Handler_read_rnd:71

Handler_read_rnd_next:403

Sort_rows:0                   Select_full_join:0          Select_scan:1

Com_commit:177                Com_rollback:0

Handler_commit:455            Handler_rollback:0

U:70      D:1       I:207     S:0       S/(U+D+I+S)(%):0.00

TIM(temp table in mem lager)(%):100.00       SOD(sort on disk small)(%):0.00

TOH(table open hint lager)(%):100.00         BOD(binglog on disk samll)(%):0.00

--------------------------------------INNODBINFO------------------------------------------

Innodb_row_lock_time:0              Innodb_row_lock_waits:0

Innodb_log_writes:181               Innodb_log_waits:0

Innodb_buffer_pool_wait_free:0      Innodb_buffer_pool_pages_misc:380

Innodb_buffer_pool_pages_flushed:84

Innodb_dblwr_pages_written:84       Innodb_dblwr_writes:8

Innodb_os_log_pending_fsyncs:0      Innodb_os_log_pending_writes:0

Innodb_os_log_written:384512

Innodb_data_reads:21                Innodb_data_read:344064

Innodb_data_writes:273              Innodb_data_written:3137024

Innodb_buffer_pool_bytes_data:32071745536

U(rows):70      D(rows):1       I(rows):273     S(rows):71

BPH(buffer cache hint)(%):100.00    BFP(free buffer pct)(%):0.42

BDP(dirty buffer pct)(%):0.68

---------------------------------------TOTALINFO------------------------------------------

Bytes_received:283146         Bytes_sent:11968

QPS:55        TPS:55        IOPS:96

---------------------------------------ENDL-----------------------------------------------

大部分保留了原有的status名字而没有使用简写，目的在于大家能够更快在文档中找到相应的解释

明白其中的含义。

关于指标问题，任何show global status中的都可以非常简单加入到工具中，可以联系我加入。

部分错误码

Mmonitor(16):connect mysql server error

Mmonitor(15):mysql port is number type!" ??

Mmonitor:(14)parameters must -h -u -p -P -s [-l] "

Mmonitor:(13)parameter len must>=4"

Mmonitor(12):miss -parameter= ="

Mmonitor(11):use only -u -h -p -P -s [-l]"

Mmonitor(10):miss -parameter= -"

Mmonitor(17):data global status failed no pri?

Mmonitor(18):fetch data failed!"

Mmonitor(21): cycle is number type!"

Mmonitor(22): Mmonitor cycle must lager than 3 sec!

关于内存泄露：

通过工具 valgrind 检测

==5999== HEAP SUMMARY:

==5999==     in use at exit: 51,863 bytes in 718 blocks

==5999==   total heap usage: 18,489 allocs, 17,771 frees, 2,250,709 bytes allocated

==5999==

==5999== LEAK SUMMARY:

==5999==    definitely lost: 0 bytes in 0 blocks

==5999==    indirectly lost: 0 bytes in 0 blocks

==5999==      possibly lost: 34,651 bytes in 17 blocks

==5999==    still reachable: 17,212 bytes in 701 blocks

==5999==         suppressed: 0 bytes in 0 blocks

==5999== Rerun with --leak-check=full to see details of leaked memory

==5999==

==5999== For counts of detected and suppressed errors, rerun with: -v

可以看到没有内存泄露

==5999==      possibly lost: 34,651 bytes in 17 blocks

==5999==    still reachable: 17,212 bytes in 701 blocks

可以写shell脚本

是我固定非配的一点内存CTRL+C后释放

如果写脚本可以如下：

export LD_LIBRARY_PATH=/dbdata/mysql3307/lib

DATE_TAG=`date +%Y%m%d`

int=1

while(( $int<=288 ))

do

/root/mmon/mmon  -h=localhost -u=mmontest -p=gelc123 -s=/dbdata/mysql5800/mysql.sock -l=60 -t=5 >>/root/mmon/mmon_${DATE_TAG}.log

let "int++"

done

代表每次抽取为1分钟 会采样288*5=1440 次就是 刚好一天的。

1 00 * * * /root/mmon.sh

然后这样挂载脚本，就可以每天1分钟采样一次记录下所有的信息变化。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7728585/viewspace-2124827/，如需转载，请注明出处，否则将追究法律责任。