如何使用自己开发的MYMON工具监控MYSQL运行状态

这篇文章主要介绍如何使用自己开发的MYMON工具监控MYSQL运行状态，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！

一、此工具展示方式：
工具将show global status中一些重要的值按照用户输入的描述和次数不断的打印到前台或者打印到
指定文件中，用于帮助DBA进行性能诊断或者瓶颈判断。
二、此工具运行原理：
将show global status的值根据用户输入的秒数记录，前一次的值为oldvalue,新的值为newvalue，存储
在内存中，然后判断是否为累计值还是当前值，如果是累计值则newvalue-oldvalue，如果为当前值则
输出，这样则保证了输出的正确性
三、工具特点：
1、监控账号低权限只需要useage权限
2、远程连接功能
3、根据用户需要将数据写入到指定文件
4、运行日志记录，检测出错输入日志
5、5.6 5.7支持、没有测试5.5
6、核心源码开源
7、可以根据需求随时定制输出，当然需要联系本人

注意：
WINDOWS不支持，编译版本只有LINUX 64BIT.
并且此工具依赖MYSQL连接动态库文件

四、、工具使用帮助
[root@testmy 6]# ./mymon
Author: gaopeng QQ:22389860 Mail: gaopp_200217@163.com
Info:This tool collect data from ‘show global status’ and show interval (-t) seconds data change
help DBA to diagnose performance problem,when error or (-d) run log is at current dir name
is mymon.log.
WIN32 platform is not support
Useage:./mymon [-u username] [-p password] -S socketfile/{-h hosname -P port} -t seconds -n times [-l logfile] [-d]
[-?] :help info;
[-u username] :connect to mysqld username,when no username requisite is optional;
[-p password] :connect to mysqld password,when no password requisite is optional;
[-S socketfile]:connect to mysqld socket file;
[-h hostname] :connect to mysqld host;
[-P port] :connect to mysqld port;
socketfile or [hostname port] must;
[-t seconds] :seconds of interval,this is necessary;
[-n times] :how many times you watch,this is necessary;
[-l logfile] :when you want to write information to a logfile provide a logfile name
when a logfile provide no information at foreground,is optional;
[-d] :more running log at logfile,otherwise error log in mymon.log;

注意本地连接用-S连接指定socketfile即可，远程使用 -h -P连接使用IP和端口连接 ,-l 可以直接把输出写入到指定的文件，
-d就是debug模式一般不用本工具会记录一个日志在当前目录下名字为：mymon.log，这是运行日志如果有错误会记录到日志，
开启-d 会计入更多的信息，但是一般不需要用，主要是我调试代码的时候用的.当然-t 和 -n就像帮助说的是间隔的秒数和
持续的次数，使用中如果输出有值为-1则代表值没有采集到.
一般只需要在线上一台服务器上放上本工具，使用远程连接连接到如何服务器即可，如：
./mymon -h 192.168.190.91 -P3306 -t 5 -n 100 -ummon2 -pgelc123 -l /root/mysqlog/20170123mysql.log
表示连接到服务器192.168.190.91 3306端口 5秒输出一次输出100次，输出保留到文件/root/mysqlog/20170123mysql.log
但是注意SysTime: Sun Jan 22 16:13:30 2017 输出是运行程序系统的当前时间，但是Uptime当然就是MYSQLD服务器的时间

四、如果获得和使用
获取：
我一共编译好了4个版本
5.7 社区版本 mymon5.7c
5.7 percona版本 mymon5.7p
5.6 社区版本 mymon5.6c
5.6 perconna版本 mymon5.6p
百度云盘如下：
http://pan.baidu.com/s/1jIa6OfG

如果要使用更老的版本，请联系我。

使用：
下载后上传到 LINUX操作系统,
mkdir mon
cp mymon5.7p ./mon/
chmod 755 mymon5.7p
然后必须找到相应的库文件位置一般在安装位置的lib目录下，如：
[root@testmy 9]# ./mymon5.7c
./mymon5.7c: error while loading shared libraries: libmysqlclient.so.20: cannot open shared object file: No such file or directory
[root@testmy 9]# export LD_LIBRARY_PATH=/mysqldata/mysql5.7/lib
[root@testmy 9]# ./mymon5.7c
Author: gaopeng QQ:22389860 Mail: gaopp_200217@163.com
就可以正常运行，顺便说一下5.6为 so.18 5.7为so.20跟老的为5.6以前版本

关于MYSQL监控用户权限：监控部需要什么权限，只要建立一个用户即可
mysql> create user moni@’%’ identified by ‘gelc123’;
Query OK, 0 rows affected (0.22 sec)
mysql> show grants for moni@’%’ ;
+———————————-+
| Grants for moni@% |
+———————————-+
| GRANT USAGE ON *.* TO ‘moni’@’%’ |
+———————————-+
1 row in set (0.06 sec)

这也最小化了顾虑，因为这个账户基本没有什么权限，如果不放心程序，可以将程序放到远程连接到线上数据库如：
./mymon5.6c -umoni -pgelc123 -h292.168.190.93 -P3307 -n 1 -t 1 -l /tmp/mysqlmon.log

这样这个程序既不会有权限而且也不在线上数据库服务器，而且可以集中管理，但是一点注意：
SysTime: Sun Jan 22 16:13:30 2017 输出是运行程序系统的当前时间，但是Uptime当然就是MYSQLD服务器的时间

五、此工具展示内容：
展示的内容并不是固定，后期根据需求可以进行简单的加入指标即可，当前输出为如下：
——————————————MYMON——————————————–
SysTime: Sun Jan 22 16:13:30 2017
Uptime:6963226 Connections:1 Aborted_clients:0 Max_used_connections:1319
————————————–MYSQLINFO——————————————-
[###Threads info and qc and :]
Threads_connected:252 Threads_running:4 Qcache_free_memory:0
Qcache_hits:0 Qcache_not_cached:0 Qcache_inserts:0
[###Table read info:]
Handler_read_first:211 Handler_read_key:8804 Handler_read_next:42346
Handler_read_last:0 Handler_read_rnd:73 Handler_read_rnd_next:717694
[###Sorts and temp tables info:]
Sort_rows:0 Sort_merge_passes:0
Created_tmp_tables:163 Created_tmp_disk_tables:152
[###Table join info:]
Select_full_join:1 Select_scan:207
[###Open table cache and binlog cache info:]
Table_open_cache_hits:4938 Table_open_cache_misses:0
Binlog_cache_use:820 Binlog_cache_disk_use:0
[###Hanler_xxx and Com_xxx info:]
Handler_delete:34574 Handler_update:675 Handler_write:1364
Handler_commit:5833 Handler_rollback:10
Com_commit:156 Com_rollback:5
U(counts):631 D(counts):159 I(counts):47 S(counts):4955
————————————–INNODBINFO——————————————
[###Innodb_lock info:]
Innodb_row_lock_time:0 Innodb_row_lock_waits:0
Innodb_row_lock_current_waits:38
[###Innodb_buffer_pool info:]
Innodb_buf_pool_pages_total:1965840 Innodb_buf_pool_pages_dirty:19154
Innodb_buf_pages_data:1940433 Innodb_buf_pages_free:8116
Innodb_buffer_pool_wait_free:0 Innodb_buffer_pool_pages_misc:17291
Innodb_dblwr_pages_written:1188 Innodb_dblwr_writes:80
Innodb_buffer_pool_pages_flushed:2249
read_ahead:0 ahead_evict:0 ahead_rnd:0
[###Innodb_log info:]
Innodb_log_writes:796 Innodb_log_waits:0
Innodb_os_log_pending_fsyncs:0 Innodb_os_log_pending_writes:0
Innodb_os_log_written:44073472
[###Innodb_data read/writes info:]
Innodb_data_reads:684 Innodb_data_read:11206656
Innodb_data_writes:2067 Innodb_data_written:63603200
Innodb_data_fsyncs:1017 Innodb_data_pending_fsyncs:0
Innodb_buffer_pool_reads(physics reads):423
Innodb_buffer_pool_read_requests(logic reads):2869823
[###Innodb_data rows info:]
U(rows):675 D(rows):34575 I(rows):194 S(rows):767094
—————————————TOTALINFO——————————————
Bytes_received:911280 Bytes_sent:8653956

六、监控值说明：
(本部分，带有作者自己的理解，如果有误请指出)

文中
当前值输出：保持show global status的当前值输出
累计值相减输出：用上一次的值减去下一次的值输出，间隔为用户指定的秒数

第一部分：
SysTime: Sun Jan 22 16:13:30 2017
Uptime:6963226 Connections:1 Aborted_clients:0 Max_used_connections:1319

Uptime:The number of seconds the MySQL server has been running.(当前值输出单位秒)
Connections:The number of connection attempts (successful or not) to the MySQL server.(累计值相减输出单位次数)
Aborted_clients:The number of connections that were aborted because the client died without closing the connection
properly.(累计值相减输出，单位连接数)
Max_used_connections:The maximum number of connections that have been in use simultaneously since the server started.
(当前值输出，单位连接数)
第二部分：
[###Threads info and qc and :]
Threads_connected:252 Threads_running:4 Qcache_free_memory:0
Qcache_hits:0 Qcache_not_cached:0 Qcache_inserts:0

Threads_connected：The number of currently open connections.(当前值输出单位连接数)
Threads_running：The number of threads that are not sleeping.(当前值输出单位连接数)
Qcache_free_memory:The amount of free memory for the query cache.(当前值输出单位字节数)
Qcache_hits:The number of query cache hits.(累计值相减输出单位次数)
Qcache_not_cached:The number of noncached queries.(累计值相减输出单位次数)
Qcache_inserts:The number of queries added to the query cache.(累计值相减输出单位次数)

第三部分：
本部分全部是(累计值相减输出单位次数)
[###Table read info:]
Handler_read_first:211 Handler_read_key:8804 Handler_read_next:42346
Handler_read_last:0 Handler_read_rnd:73 Handler_read_rnd_next:717694

Handler_read_first:
/*索引中第一条记录被读的次数。如果较高，它表明服务器正执行大量全索引扫描；例如，SELECT * order by id，假定id列有索引。
* eg:
* +—-+————-+——-+————+——-+—————+———+———+——+——+———-+——-+
* | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
* +—-+————-+——-+————+——-+—————+———+———+——+——+———-+——-+
* | 1 | SIMPLE | testj | NULL | index | NULL | PRIMARY | 276 | NULL | 19 | 100.00 | NULL |
* +—-+————-+——-+————+——-+—————+———+———+——+——+———-+——-+
*/
Handler_read_key:
/*根据索引读一行的请求数。如果较高，说明查询和表的索引正确。本值可能是ref或者const也可能是range，只是代表索引KEY命中使用了索引
* eg:
* mysql> explain select * from testshared3 where id>1;
* +—-+————-+————-+————+——-+—————+——+———+——+——+———-+———————–+
* | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
* +—-+————-+————-+————+——-+—————+——+———+——+——+———-+———————–+
* | 1 | SIMPLE | testshared3 | NULL | range | id | id | 5 | NULL | 1 | 100.00 | Using index condition |
* +—-+————-+————-+————+——-+—————+——+———+——+——+———-+———————–+
*/
Handler_read_next:
/*按照索引顺序读下一行的请求数,如果索引不唯一或者使用唯一索引的前缀或者范围都会触发这个值,简单的说只有唯一命中不触发这个值
* 这个值如果比较大说明需要连续访问下一个key的次数非常多
*
*eg:
*mysql> explain select * from testshared3 where id=1;
*+—-+————-+————-+————+——+—————+——+———+——-+——–+———-+——-+
*| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
*+—-+————-+————-+————+——+—————+——+———+——-+——–+———-+——-+
*| 1 | SIMPLE | testshared3 | NULL | ref | id | id | 5 | const | 523260 | 100.00 | NULL |
*+—-+————-+————-+————+——+—————+——+———+——-+——–+———-+——-+
*
*| Handler_read_key | 49 |
*| Handler_read_next | 897698 |
*/
Handler_read_last:
/*
* 查询读索引最后一个索引键的请求数。当使用ORDER BY时，服务器优先发出使用第一个索引的请求，之后顺序往后扫描索引。当使用ORDER BY DESC时，
* 服务器优先发出使用最后一个索引的请求，之后向前扫描索引。
* 例如：select … order by id desc limit 10;
*/

Handler_read_rnd,Handler_read_rnd_next:这两个值较大则可能遇到了全表扫描及type=ALL

这一部分则可以大体掌握当前数据库全表扫描和索引使用的大概情况
第四部分：
[###Sorts and temp tables info:]
Sort_rows:0 Sort_merge_passes:0
Created_tmp_tables:163 Created_tmp_disk_tables:152

Sort_rows:The number of sorted rows.(累计值相减输出单位行数)
Sort_merge_passes:The number of merge passes that the sort algorithm has had to do.(累计值相减输出单位次数)
Created_tmp_tables:The number of internal temporary tables created by the server while executing statements.(累计值相减输出单位次数)
Created_tmp_disk_tables:The number of internal on-disk temporary tables created by the server while executing statements.(累计值相减输出单位次数)

很明显这一部分说明了排序和临时表使用情况，可以帮助DBA进行判断

第五部分：
[###Table join info:]
Select_full_join:1 Select_scan:207

Select_full_join:The number of joins that perform table scans because they do not use indexes.
If this value is not 0, you should carefully check the indexes of your tables
(累计值相减输出单位次数)
Select_scan:The number of joins that did a full scan of the first table. (累计值相减输出单位次数)

这两个值则说明了jion的时候是否使用到了索引，特别是Select_full_join，应该比较低，否则说明被驱动表没有使用到索引
应该引起重视，优化语句，一般来讲Select_scan比较高是正常的因为驱动表如果没有谓词条件使用全表扫描是正常的方式

第六部分：
[###Open table cache and binlog cache info:]
Table_open_cache_hits:4938 Table_open_cache_misses:0
Binlog_cache_use:820 Binlog_cache_disk_use:0

Table_open_cache_hits:The number of hits for open tables cache lookups.(累计值相减输出单位次数)
Table_open_cache_misses:The number of misses for open tables cache lookups. (累计值相减输出单位次数)
Binlog_cache_use: The number of transactions that used the binary log cache. (累计值相减输出单位事物数)
Binlog_cache_disk_use:The number of transactions that used the temporary binary log cache but that exceeded the value
of binlog_cache_size and used a temporary file to store statements from the transaction.(累计值相减输出单位事物数)
明显这部分说明了binlog_cache_size和 table_open_cache是否合适

第七部分：
本部分全部是(累计值相减输出单位次数)
[###Hanler_xxx and Com_xxx info:]
Handler_delete:34574 Handler_update:675 Handler_write:1364
Handler_commit:5833 Handler_rollback:10
Com_commit:156 Com_rollback:5
U(counts):631 D(counts):159 I(counts):47 S(counts):4955

U(counts):631 D(counts):159 I(counts):47 S(counts):4955
这一行是Com_updateCom_deleteCom_insertCom_select的简写

Handler_xxx:这一批值说明的是相应操作处理的次数
Com_xxx:这一批值说明是相应操作对应语句发出的次数

比如Handler_commit 和 Com_commit这不同，如果是隐含提交和自动提交不用发起commit，但是
commit是处理了的，那么Handler_commit增加而Com_rollback不变，又比如手动执行一个commit
Com_commit增加Handler_commit不变，因为虽然发起了commit命令并不需要处理实验如下：

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

第八部分：
[###Innodb_lock info:]
Innodb_row_lock_time:0 Innodb_row_lock_waits:0
Innodb_row_lock_current_waits:38

Innodb_row_lock_time:The total time spent in acquiring row locks for InnoDBtables, in milliseconds.(累计值相减输出单位毫秒)
Innodb_row_lock_waits:The number of times operations on InnoDB tables had to wait for a row lock.(累计值相减输出单位次数)
Innodb_row_lock_current_waits:The number of row lockscurrently being waited for by operations on InnoDB tables.(当前值输出单位个数)

明显这一部分是对innodb 锁的状态的监控

第九部分：

[###Innodb_buffer_pool info:]
Innodb_buf_pool_pages_total:1965840 Innodb_buf_pool_pages_dirty:19154
Innodb_buf_pages_data:1940433 Innodb_buf_pages_free:8116
Innodb_buffer_pool_wait_free:0 Innodb_buffer_pool_pages_misc:17291
Innodb_dblwr_pages_written:1188 Innodb_dblwr_writes:80
Innodb_buffer_pool_pages_flushed:2249
read_ahead:0 ahead_evict:0 ahead_rnd:0

Innodb_buf_pool_pages_total:及Innodb_buffer_pool_pages_data The total size of the InnoDB buffer pool, in pages.(当前值输出单位页)
Innodb_buf_pool_pages_dirty:及Innodb_buffer_pool_pages_dirty The current number of dirty pagesin the InnoDB buffer pool.(当前值输出单位页)
Innodb_buf_pages_data:及Innodb_buffer_pool_pages_data The number of pagesin the InnoDB buffer poolcontaining data. The number includes both dirtyand
clean pages.(当前值输出单位页)
Innodb_buf_pages_free:及Innodb_buffer_pool_pages_free The number of free pagesin the InnoDB buffer pool.(当前值输出单位页)
Innodb_buffer_pool_wait_free:Normally, writes to the InnoDB buffer poolhappen in the background. When InnoDBneeds to read or
create a pageand no clean pages are available, InnoDBflushes some dirty pagesfirst and waits for that
operation to finish. This counter counts instances of these waits.(累计值相减输出单位次数)

Innodb_buffer_pool_pages_misc:The number of pages in the InnoDB buffer pool that are busy because they have
been allocated for administrative overhead, such as row locks or the adaptive hash
index. This value can also be calculated as Innodb_buffer_pool_pages_total ?
Innodb_buffer_pool_pages_free ? Innodb_buffer_pool_pages_data.(当前值输出单位页)

Innodb_dblwr_pages_written:The number of pages that have been written to the doublewrite buffer.(累计值相减输出单位页)
Innodb_dblwr_writes:The number of doublewrite operations that have been performed. (累计值相减输出单位次数)
Innodb_buffer_pool_pages_flushed:The number of requests to flush pages from the InnoDB buffer pool.(累计值相减输出单位次数)

这部分是innodb_buffer_pool的重点包含了脏页,数据页,空闲页等信息,以及刷磁盘的次数

第十部分：
[###Innodb_log info:]
Innodb_log_writes:796 Innodb_log_waits:0
Innodb_os_log_pending_fsyncs:0 Innodb_os_log_pending_writes:0
Innodb_os_log_written:44073472

Innodb_log_writes:The number of physical writes to the InnoDB redo logfile.(累计值相减输出单位次数)
Innodb_log_waits:The number of times that the log buffer was too small and a wait was required for it to be flushed before
continuing.(累计值相减输出单位次数)
Innodb_os_log_pending_fsyncs:The number of pending fsync() operations for the InnoDB redo logfiles.(累计值相减输出单位次数)
Innodb_os_log_pending_writes:The number of pending writes to the InnoDB redo logfiles.(累计值相减输出单位次数)
Innodb_os_log_written:The number of bytes written to the InnoDB redo logfiles.(累计值相减输出单位字节)

这部分说明了 innodb redo 写的信息，如写了多少数据，多少次等待，如果Innodb_os_log_pending_writes和Innodb_os_log_pending_fsyncs
不为0则说明遭遇了I/O等待。而Innodb_log_waits不为0则考虑innodb_log_buffer_size 是否过小。
注意：不管innodb_flush_method设置如何 redo始终使用kernel buffer。

第十一部分：
[###Innodb_data read/writes info:]
Innodb_data_reads:684 Innodb_data_read:11206656
Innodb_data_writes:2067 Innodb_data_written:63603200
Innodb_data_fsyncs:1017 Innodb_data_pending_fsyncs:0
Innodb_buffer_pool_reads(physics reads):423
Innodb_buffer_pool_read_requests(logic reads):2869823

Innodb_data_reads:The total number of data reads.(累计值相减输出单位次数)
Innodb_data_read:The amount of data read since the server was started(累计值相减输出单位字节数)
Innodb_data_writes:The total number of data writes.(累计值相减输出单位次数)
Innodb_data_written:The amount of data written so far, in bytes.(累计值相减输出单位字节数)
Innodb_data_fsyncs:The number of fsync()operations so far. The frequency of fsync()calls is influenced by the setting
of the innodb_flush_method configuration option.(累计值相减输出单位次数)
Innodb_data_pending_fsyncs:The current number of pending fsync()operations. The frequency of fsync()calls is influenced by
the setting of the innodb_flush_method configuration option.(累计值相减输出单位次数)
Innodb_buffer_pool_reads(physics reads):The number of logical reads that InnoDB could not satisfy from the buffer pool, and had to read
directly from disk.(累计值相减输出单位次数)
Innodb_buffer_pool_read_requests(logic reads):The number of logical read requests.(累计值相减输出单位次数)
read_ahead:及Innodb_buffer_pool_read_ahead:The number of pages read into the InnoDB buffer pool by the read-ahead background thread.(累计值相减输出单位页)
ahead_evict:及Innodb_buffer_pool_read_ahead_evicted:The number of pagesread into the InnoDB buffer poolby the read-aheadbackground thread that were
subsequently evictedwithout having been accessed by queries.(累计值相减输出单位页)
ahead_rnd:及Innodb_buffer_pool_read_ahead_rnd:The number of “random”read-aheads initiated by InnoDB. This happens when a query scans a large
portion of a table but in random order.(累计值相减输出单位次数)

这一部分可以看出逻辑读取的命中率，预读效率等，注意Innodb_data_pending_fsyncs和Innodb_data_fsyncs如果开启了O_DIRECT,那么innodb_buffer的内存
数据不会出现在kernel buffer，那么fsyncs pending的情况会少得多。如果这两个值较大注意磁盘I/O

第十二部分：
本部分全部是(累计值相减输出单位行数)
U(rows):675 D(rows):34575 I(rows):194 S(rows):767094

U:Innodb_rows_updated
D:Innodb_rows_deleted
I:Innodb_rows_inserted
S:Innodb_rows_read

这部分真正的反应了DML和SELECT的行数，注意和Com_xxx和Handler_xxx的区别

第十三部分：
Bytes_received:911280 Bytes_sent:8653956

没什么好说的自己查文档

七、内存溢出检测
使用：
valgrind –tool=memcheck –leak-check=full ./mymon5.7c -S/mysqldata/mysql5.7sla/mysqld3307.sock -t 1 -n 10

==5120== LEAK SUMMARY:
==5120== definitely lost: 0 bytes in 0 blocks
==5120== indirectly lost: 0 bytes in 0 blocks
==5120== possibly lost: 232 bytes in 2 blocks
==5120== still reachable: 8,176 bytes in 2 blocks
==5120== suppressed: 0 bytes in 0 blocks
==5120== Reachable blocks (those to which a pointer was found) are not shown.
==5120== To see them, rerun with: –leak-check=full –show-reachable=yes
==5120==
==5120== For counts of detected and suppressed errors, rerun with: -v
==5120== ERROR SUMMARY: 2 errors from 2 contexts (suppressed: 6 from 6)
可以看到没有内存溢出

八、核心源码

点击(此处)折叠或打开 main主调函数：
/*************************************************************************
> File Name: main.c
> Author: gaopeng QQ:22389860 all right reserved
> Mail: gaopp_200217@163.com
> Created Time: Wed 08 Feb 2017 08:01:58 AM CST
************************************************************************/

#include”type.h”

int useage(void)
{
printf(
“Author: gaopeng QQ:22389860 Mail: gaopp_200217@163.com n”
“Info:This tool collect data from ‘show global status’ and show interval (-t) seconds data changen”
“help DBA to diagnose performance problem,when error or (-d) run log is at current dir name n”
“is mymon.log.n”
“WIN32 platform is not supportn”
“Useage:./mymon [-u username] [-p password] -S socketfile/{-h hosname -P port} -t seconds -n times [-l logfile] [-d]n”
“[-?] :help info;n”
“[-u username] :connect to mysqld username,when no username requisite is optional;n”
“[-p password] :connect to mysqld password,when no password requisite is optional;n”
“[-S socketfile]:connect to mysqld socket file;n”
“[-h hostname] :connect to mysqld host;n”
“[-P port] :connect to mysqld port;n”
” socketfile or [hostname port] must;n”
“[-t seconds] :seconds of interval,this is necessary;n”
“[-n times] :how many times you watch,this is necessary;n”
“[-l logfile] :when you want to write information to a logfile provide a logfile namen”
” when a logfile provide no information at foreground,is optional;n”
“[-d] :more running log at logfile,otherwise error log in mymon.log;n”
);
return 0;
}

int wel(void)
{
printf(
“welcome used this mysql monitor tooln”
“Author: gaopeng QQ:22389860 Mail: gaopp_200217@163.com n”
“Info:This tool collect data from ‘show global status’ and show interval (-t) seconds data changen”
“help DBA to diagnose performance problem,when error or (-d) run log is at current dir name n”
“is mymon.log.n”
“WIN32 platform is not supportn”
“Useage:./mymon [-u username] [-p password] -S socketfile/{-h hosname -P port} -t seconds -n times [-l logfile] [-d]n”
“more help info in ./mymon -hn”
);
return 0;
}

int getpar( char (*par)[500],int n)
{
int i;
for(i=0;i
{
printf(“your parameter is %sn”,*(par+i));
}
}

int checkpar(char (*par)[500],int t,int n)
{
if(strlen(*(par+2)) == 0 && (strlen(*(par+4)) == 0 || strlen(*(par+5)) == 0 ))
{

useage();
return -1;
}

if(t==0||n==0)
{
useage();
return -1;
}
return 0;
}

int main(int argc,char* argv[])
{
int ch;
int t = 0; //t how long
int n = 0;//n count
FILE* fd = 0;//log file fs
unsigned int mport = 0;//mysql connect port

debug = 0;
write_file = 0;
PAR par;
setbuf(stdout,NULL);//stdio buffer disable
memset(par,0,sizeof(PAR));
if(argc
{
useage();
return -1;
}
while ((ch = getopt(argc, argv, “u:p:S:t:n:l:h:P:d?”)) != -1)
{
switch (ch)
{
case ‘u’:
if(strlen(optarg)>MAX_PAR_LEN)
{
printf(“MYMON:username max length more than 500 bytesn”);
return -1;
}
strcpy(par[0],optarg);
break;
case ‘h’:
if(strlen(optarg)>MAX_PAR_LEN)
{
printf(“MYMON:hostname max length more than 500 bytesn”);
return -1;
}
strcpy(par[4],optarg);
break;
case ‘P’:
if(strlen(optarg)>MAX_PAR_LEN)
{
printf(“MYMON:port max length more than 500 bytesn”);
return -1;
}
strcpy(par[5],optarg);
sscanf (par[5],”%u”,&mport);
break;
case ‘p’:
if(strlen(optarg)>MAX_PAR_LEN)
{
printf(“MYMON:password max length more than 500 bytesn”);
return -1;
}
strcpy(par[1],optarg);
break;
case ‘S’:
if(strlen(optarg)>MAX_PAR_LEN)
{
printf(“MYMON:socket max length more than 500 bytesn”);
return -1;
}
strcpy(par[2],optarg);
break;
case ‘t’:
sscanf (optarg,”%d”,&t);
break;
case ‘n’:
sscanf (optarg,”%d”,&n);
break;
case ‘l’:
if(strlen(optarg)>MAX_PAR_LEN)
{
printf(“MYMON:logfile name max length more than 500 bytesn”);
return -1;
}
strcpy(par[3],optarg);
if((fd = fopen(par[3],OPENFLAG)) == NULL)
{
perror(“MYMON:”);
return -1;
}
write_file = 1;
setbuf(fd,NULL);//stdio buffer disable
break;
case ‘d’:
debug = 1;
break;
default:
case ‘?’:
useage();
return -1;
}
}

if(checkpar(par,t,n) == -1)
{
printf(“MYMON:must parameter errorn”);
return -1;
}
wel();
if(conn(par,t,n+1,fd,mport) !=0)
{
printf(“MYMON:error pelase check logn”);
return -1;
}
if(write_file == 1)
{
fclose(fd);
}
return 0;
}
点击(此处)折叠或打开 fun.c功能实现接口函数
/*************************************************************************
> File Name: fun.c
> Author: gaopeng QQ:22389860 all right reserved
> Mail: gaopp_200217@163.com
> Created Time: Wed 08 Feb 2017 10:08:56 AM CST
************************************************************************/
/*
This file frist store new and old data to one data struct
**struct->*struct->struct
struct have three pointer
statname
olddata
newdata

Then sub new and old data to one struct array
Last use array to print data
*/

#include”type.h”
#include”fun.h”

/*
* field pointer to status name
* olddata pointer to prev data
* newdata pointer to now data
*/

typedef struct MDATA
{
char* statname;
char* olddata;
char* newdata;
} MDATA;

typedef struct D_STROE
{
char statname[MAXSTATNAME];
int64_t data;
} D_STROE;

/* ret=0 is sucss retint handle_data(MYSQL_RES *result/*in*/,uint64_t row_count,unsigned int row_field,FILE* fd,MDATA* *datap/*in*/,int i);
/* ret=0 is sucss retint memaloc(MDATA** *datap/*out*/,uint64_t row_count);
/* ret=0 is sucss retint sub_store_data(MDATA* *datap,D_STROE* *last_data/*out*/,uint64_t row_count,int* lenth/*in*/);
/* ret=0 is sucss retint print_data(D_STROE* last_data,int lenth,FILE* fd) ;
/* ret=-1 is no data return >=0 and -65535 is data return */
int64_t findva(const char* ins,D_STROE* last_data,int lenth);
/* no retrun value*/
int freedatap(MDATA* *datap,uint64_t row_count);

static freecouti=0;

void test(void)
{
;
}

int mprintf(FILE* outfile,const char *format,…)
{
va_list args;
va_start(args,format);
if( vfprintf(outfile,format,args)
{
GET_LOG(LogLevel[4],ERROR,”ERROR:mprintf() error:%dn”,ERROR);
printf(“MYMON: mprintf() error:%dn”,ERROR);
exit(-1);
}
va_end(args);
return 0;
}

int xfree(void* p)
{
if(p==NULL)
{
freecouti++;
if(debug == 3)
{
GET_LOG(LogLevel[1],DEBUG,”DEBUG:xfree(NULL) debug:%dn”,freecouti);
}
return 0;
}
free(p);
p=NULL;
return 0;
}

int conn(char (*par)[500]/*in*/,int t,int n,FILE* fd,unsigned int myport)
{
MYSQL conn_ptr;
MYSQL_RES *query_res =NULL;
MYSQL_ROW sqlrow_data;
int ret = 0;
uint64_t row_count = 0;
unsigned int row_field = 0;
MDATA* *datap =NULL;
int i=0;//used to check frist time no old data

/*
*MYSQL *mysql_init(MYSQL *mysql)
*An initialized MYSQL*handle. NULLif there was insufficient memory to allocate a new object.
*/
if(mysql_init(&conn_ptr) == NULL)
{
ret = -1;
GET_LOG(LogLevel[4],ERROR,”ERROR:mysql_init() error:%dn”,ERROR);
printf(“MYMON: mysql_init() errorn”);
return ret;
}
/*
*MYSQL *mysql_real_connect(MYSQL *mysql, const char *host, const char *user,
*const char *passwd, const char *db, unsigned int port, const char *unix_socket,
*unsigned long client_flag)
*A MYSQL*connection handle if the connection was successful, NULLif the connection was unsuccessful.
*For a successful connection, the return value is the same as the value of the first parameter
*/
if(mysql_real_connect(&conn_ptr,*(par+4),*par,*(par+1),NULL,myport,*(par+2),0)==NULL)
{
ret = -2;
GET_LOG(LogLevel[4],ERROR,”ERROR:mysql_real_connect() error:%dn”,ERROR);
printf(“MYMON: mysql_real_connect() errorn”);
if(mysql_errno(&conn_ptr))
{
GET_LOG(LogLevel[4],ERROR,”ERROR:mysql_real_connect() error:%sn”,mysql_error(&conn_ptr));
printf(“MYMON: mysql_real_connect() error:%sn”,mysql_error(&conn_ptr));
}
return ret;
}

while(n–)
{
/*
*int mysql_query(MYSQL *mysql, const char *stmt_str)
*Zero for success. Nonzero if an error occurred.
*/
if((mysql_query(&conn_ptr,”show global status”)) != 0)
{
ret = -3;
GET_LOG(LogLevel[4],ERROR,”ERROR:mysql_query() error:%sn”,mysql_error(&conn_ptr));
printf(“MYMON: mysql_query() error:%sn”,mysql_error(&conn_ptr));
return ret;
}
/*
* MYSQL give memory out
* MYSQL_RES *mysql_store_result(MYSQL *mysql)
* A MYSQL_RESresult structure with the results. NULL(0) if an error occurred
*/

if((query_res=mysql_store_result(&conn_ptr)) == NULL)
{
ret = -4;
GET_LOG(LogLevel[4],ERROR,”ERROR:mysql_store_result() error:%sn”,mysql_error(&conn_ptr));
printf(“MYMON: mysql_store_result() error:%sn”,mysql_error(&conn_ptr));
return ret;
}
/*
* my_ulonglong mysql_num_rows(MYSQL_RES *result)
* The number of rows in the result set.
* unsigned int mysql_num_fields(MYSQL_RES *result)
* An unsigned integer representing the number of columns in a result set
*/
row_count = mysql_num_rows(query_res);
row_field = mysql_field_count(&conn_ptr);
if(debug == 1)
{
GET_LOG(LogLevel[2],INFO,”INFO:mysql_num_rows() rows is %llun”,row_count);
GET_LOG(LogLevel[2],INFO,”INFO:mysql_field_count() fileds is %ldn”,row_field);
printf(“MYMON: mysql_num_rows() rows is %llun”,row_count);
printf(“MYMON: mysql_field_count() fileds is %ldn”,row_field);
}
if(i == 0)
{
if((memaloc(&datap,row_count)) !=0)
{
ret = -6;
GET_LOG(LogLevel[4],ERROR,”ERROR:memaloc() error:%sn”,”ERROR”);
printf(“MYMON: memaloc() error:%sn”,”ERROR”);
return ret;
}
}
if(handle_data(query_res,row_count,row_field,fd,datap,i)!=0)
{
ret = -5;
GET_LOG(LogLevel[4],ERROR,”ERROR:handle_data() error:%sn”,”ERROR”);
printf(“MYMON: handle_data() error:%sn”,”ERROR”);
return ret;
}

/*
* void mysql_free_result(MYSQL_RES *result)
* no result
*/
mysql_free_result(query_res);
sleep(t);
i++;
}
/*
* void mysql_close(MYSQL *mysql)
* no result
*/

/*hear free datap*/
{
freedatap(datap,row_count);
}
mysql_close(&conn_ptr);
return ret;
}

int freedatap(MDATA* *datap,uint64_t row_count)
{
int ret = 0;
uint64_t i = 0;
if(datap == NULL)
{
return ret;
}
GET_LOG(LogLevel[3],INFO,”INFO:freedatap() check previous line is error?:%dn”,ret);
for(i=0;i
{
xfree ((*(datap+i))->statname);
xfree((*(datap+i))->newdata);
xfree((*(datap+i))->olddata);
xfree(*(datap+i));
}
xfree(datap);
return ret;
}
int memaloc(MDATA** *datap,uint64_t row_count)
{
int ret=0;
MDATA* *mp=NULL;
uint64_t i=0;

if(datap == NULL)
{
ret = -5;
GET_LOG(LogLevel[4],ERROR,”ERROR:memaloc() datap=NULL?:%dn”,ret);
printf(“MYMON: memaloc() datap=NULL?:%dn”,ret);
return ret;
}
if((mp = (MDATA**)calloc(1,row_count*sizeof(MDATA*))) == NULL)
{
ret = -3;
GET_LOG(LogLevel[4],ERROR,”ERROR:memaloc() memory malloc **p error:%dn”,ret);
printf(“MYMON: memaloc() memory malloc **p error:%dn”,ret);
return ret;
}
for(i=0;i
{
if((*(mp+i)=(MDATA*)calloc(1,sizeof(MDATA))) == NULL)
{
ret=-4;
GET_LOG(LogLevel[4],ERROR,”ERROR:handle_data() memory malloc *p error:%dn”,ret);
printf(“MYMON: MYMON:handle_data() memory malloc *p error:%dn”,ret);
freedatap(mp,row_count);
return ret;
}
}
*datap = mp;
return ret;

}

int handle_data(MYSQL_RES *result,uint64_t row_count,unsigned int row_field,FILE* fd,MDATA* *datap,int seq)
{
uint64_t i = 0;
MYSQL_ROW sqlrow_data;
int ret = 0;
D_STROE* last_data=NULL;//used last data store
int lenth=0;//last data lenth

//tmp
int tmp = 0;
D_STROE* tmp_last_data = NULL;

if(result == NULL||datap == NULL)
{
ret = -2;
GET_LOG(LogLevel[4],ERROR,”ERROR:handle_data() result == NULL datap == NULL :%sn”,”ERROR”);
printf(“MYMON:handle_data() result == NULL datap == NULL :%sn”,”ERROR”);
return ret;
}

for(i=0;i
{
/*
* MYSQL_ROW mysql_fetch_row(MYSQL_RES *result)
* A MYSQL_ROW structure for the next row. NULL if there are no more rows to retrieve or if an error occurred
*/
if((sqlrow_data=mysql_fetch_row(result)) == NULL) //control mysql_fetch_row error
{
ret = -1;
GET_LOG(LogLevel[4],ERROR,”ERROR:mysql_fetch_row() error:%sn”,”ERROR”);
printf(“MYMON: mysql_fetch_row() error:%sn”,”ERROR”);
return ret;
}
{
unsigned long *lengths;
lengths = mysql_fetch_lengths(result);
xfree((*(datap+i))->statname);
if(((*(datap+i))->statname = (char*)calloc(1,lengths[0]+10)) == NULL)
{
ret = -4;
GET_LOG(LogLevel[4],ERROR,”ERROR:handle_data() statname loop mem alloc error:%sn”,”ERROR”);
printf(“MYMON: handle_data() statname loop mem alloc error:%sn”,”ERROR”);
return ret;

}
strcpy((*(datap+i))->statname,sqlrow_data[0] ? sqlrow_data[0] : “NULL”);
xfree((*(datap+i))->olddata);
(*(datap+i))->olddata = (*(datap+i))->newdata;
//no free newdata pointer
if(((*(datap+i))->newdata = (char*)calloc(1,lengths[1]+10)) == NULL)
{
ret = -5;
GET_LOG(LogLevel[4],ERROR,”ERROR:handle_data() newdata loop mem alloc error:%sn”,”ERROR”);
printf(“MYMON: handle_data() newdata loop mem alloc error:%sn”,”ERROR”);
return ret;
}
strcpy((*(datap+i))->newdata,sqlrow_data[1] ? sqlrow_data[1] : “NULL”);
if(debug == 1)
{
if((!strcmp((*(datap+i))->statname,”NULL”)) || (!strcmp((*(datap+i))->newdata,”NULL”)) )
{
GET_LOG(LogLevel[1],DEBUG,”DEBUG:handle_data() statname or data IS NULL:[%s]n”,(*(datap+i))->statname);
GET_LOG(LogLevel[1],DEBUG,”DEBUG:handle_data() statname or data IS NULL:[%s]n”,(*(datap+i))->newdata);
}
}
}

}
if(seq == 0)//olddata have no data
{
if(debug == 1)
{
GET_LOG(LogLevel[1],DEBUG,”DEBUG:handle_data() first time is info %sn”,”no old data”);
printf(“MYMON: handle_data() first time is info %sn”,”no old data”);
}
}
if(seq != 0 ) //newdata and olddata have data
{
if(sub_store_data(datap,&last_data,row_count,&lenth) != 0 ) //store data to array
{
ret = -6;
GET_LOG(LogLevel[4],ERROR,”ERROR:handle_data() sub_store_data() error:%sn”,”ERROR”);
printf(“MYMON: handle_data() sub_store_data() error %sn”,”ERROR”);
return ret;
}
if(debug == 1)
{
tmp_last_data = last_data;
for(tmp =0;tmp
{
GET_LOG(LogLevel[1],DEBUG,”DEBUG:handle_data() fi开发云主机域名nd data is [%s]”,(tmp_last_data)->statname);
GET_LOG(LogLevel[1],DEBUG,”[%lld] n”,(tmp_last_data)->data);
tmp_last_data++;
}
}

if(print_data(last_data,lenth,fd) != 0) //store data from array
{
ret = -7;
GET_LOG(LogLevel[4],ERROR,”ERROR:handle_data() print_data() error:%sn”,”ERROR”);
printf(“MYMON: handle_data() print_data() error %sn”,”ERROR”);
return ret;
}

}
//used end free last data array
xfree(last_data);
return ret;
}

int sub_store_data(MDATA* *datap,D_STROE* *last_data,uint64_t row_count,int* lenth)
{
D_STROE* tmp_p = NULL;
int ret = 0;
uint64_t i = 0;

char* *dyd_d = NULL;
char* *std_d = NULL;
int64_t a = 0;
int64_t b = 0;

if(datap == NULL || last_data == NULL )
{
ret = -1;
GET_LOG(LogLevel[4],ERROR,”ERROR:sub_store_data() datap == NULL || fail_data == NULL %sn”,”ERROR”);
printf(“MYMON: sub_store_data() datap == NULL || fail_data == NULL %sn”,”ERROR”);
return ret;
}

if((tmp_p = (D_STROE*)calloc(1,(sizeof(stdata)+sizeof(dydata))*sizeof(D_STROE))) == NULL)
{
ret = -2;
GET_LOG(LogLevel[4],ERROR,”ERROR:sub_store_data() mem alloc error:%sn”,”ERROR”);
printf(“MYMON: sub_store_data() mem alloc error:%sn”,”ERROR”);
return ret;
}
*last_data = tmp_p;

for(i=0;i
{
dyd_d = dydata; //re pointer to dydata
std_d = stdata; //re pointer to dydata
if(strlen((*(datap+i))->statname)>MAXSTATNAME)
{
ret = -3;
GET_LOG(LogLevel[4],ERROR,”ERROR:sub_store_data() statname>MAXSTATNAME:%sn”,”ERROR”);
printf(“MYMON: sub_store_data() statname>MAXSTATNAME:%sn”,”ERROR”);
goto er;
}
while(*dyd_d)
{
if(debug == 3)
{
printf(“dyd [%s] [%s] [%d] [%d]n”,*dyd_d,(*(datap+i))->statname,strlen(*dyd_d),strlen((*(datap+i))->statname));
}
if(!strcmp(*dyd_d,(*(datap+i))->statname))
{

if(debug == 1)
{
GET_LOG(LogLevel[1],DEBUG,”DEBUG:sub_store_data() find data is %s n”,*dyd_d);
}
strcpy(tmp_p->statname,*dyd_d);
if( !strcmp((*(datap+i))->olddata,”NULL”) || !strcmp((*(datap+i))->newdata,”NULL”)) //check “NULL” DATA ?
{
GET_LOG(LogLevel[2],INFO,”INFO:sub_store_data() data %s is [NULL] n”,*dyd_d);
tmp_p->data = ERRORDATA;
}
if(sscanf((*(datap+i))->olddata,”%lld”,&a) == EOF || sscanf((*(datap+i))->newdata,”%lld”,&b) == EOF)
{
ret = -4;
GET_LOG(LogLevel[4],ERROR,”ERROR:sub_store_data() sscanf() error %sn”,”ERROR”);
printf(“MYMON: sub_store_data() sscanf() error:%sn”,”ERROR”);
goto er;
}
tmp_p->data = b-a; //new – old
tmp_p++;
(*lenth)++;
break;
}
dyd_d++;
}
while(*std_d)
{
if(debug == 3)
{
printf(“std [%s] [%s] [%d] [%d]n”,*std_d,(*(datap+i))->statname,strlen(*std_d),strlen((*(datap+i))->statname));
}
if(!strcmp(*std_d,(*(datap+i))->statname))
{

if(debug == 1)
{
GET_LOG(LogLevel[1],DEBUG,”DEBUG:sub_store_data() find data is %s n”,*std_d);
}
strcpy(tmp_p->statname,*std_d);
if(!strcmp((*(datap+i))->newdata,”NULL”)) //check “NULL” DATA ?
{
GET_LOG(LogLevel[2],INFO,”INFO:sub_store_data() data %s is [NULL] n”,*std_d);
tmp_p->data = ERRORDATA;
}
if(sscanf((*(datap+i))->olddata,”%lld”,&tmp_p->data) == EOF)
{
ret = -4;
GET_LOG(LogLevel[4],ERROR,”ERROR:sub_store_data() sscanf() error %sn”,”ERROR”);
printf(“MYMON: sub_store_data() sscanf() error:%sn”,”ERROR”);
goto er;
}
tmp_p++;
(*lenth)++;
break;
}
std_d++;
}
}
return ret;

er:
xfree(tmp_p);
*last_data= NULL;
return ret;
}

int print_data(D_STROE* last_data,int lenth,FILE* fd)
{
time_t rawtime;
struct tm * timeinfo = NULL;
int ret = 0;
FILE* outfile = NULL;
if(write_file == 1)
{
outfile=fd;
}
else
{
outfile=stdout;
}

if(time (&rawtime) == -1)
{
ret = -2;
GET_LOG(LogLevel[4],ERROR,”ERROR:print_data() time() error %sn”,”ERROR”);
printf(“MYMON: ERROR:print_data() time() error:%sn”,”ERROR”);
return ret;
}
timeinfo = localtime ( &rawtime );
if (timeinfo == NULL )
{
ret = -1;
GET_LOG(LogLevel[4],ERROR,”ERROR:print_data() localtime () error %sn”,”ERROR”);
printf(“MYMON: ERROR:print_data() localtime () error:%sn”,”ERROR”);
return ret;
}
mprintf(outfile,”——————————————MYMON——————————————–n”);
mprintf(outfile,”SysTime: %s”,asctime (timeinfo));
mprintf(outfile,
“%s%-11ld%s%-8ld%s%-8ld%s%-8ldn”,
“Uptime:”,findva(“Uptime”,last_data,lenth),
“Connections:”,findva(“Connections”,last_data,lenth) ,
“Aborted_clients:”,findva(“Aborted_clients”,last_data,lenth),
“Max_used_connections:”,findva(“Max_used_connections”,last_data,lenth));
mprintf(outfile,”————————————–MYSQLINFO——————————————-n”);
mprintf(outfile,”[###Threads info and qc info:]n”);
mprintf(outfile,
“%s%-12ld%s%-14ld%s%-13ldn”,
“Threads_connected:”,findva(“Threads_connected”,last_data,lenth),
“Threads_running:”,findva(“Threads_running”,last_data,lenth),
“Qcache_free_memory:”,findva(“Qcache_free_memory”,last_data,lenth)
);
mprintf(outfile,
“%s%-18ld%s%-12ld%s%-17ldn”,
“Qcache_hits:”,findva(“Qcache_hits”,last_data,lenth),
“Qcache_not_cached:”,findva(“Qcache_not_cached”,last_data,lenth),
“Qcache_inserts:”,findva(“Qcache_inserts”,last_data,lenth)
);
mprintf(outfile,”[###Table read info:]n”);
mprintf(outfile,
“%s%-11ld%s%-13ld%s%-14ldn”,
“Handler_read_first:”,findva(“Handler_read_first”,last_data,lenth),
“Handler_read_key:”,findva(“Handler_read_key”,last_data,lenth),
“Handler_read_next:”,findva(“Handler_read_next”,last_data,lenth)
);

mprintf(outfile,
“%s%-12ld%s%-13ld%s%ldn”,
“Handler_read_last:”,findva(“Handler_read_last”,last_data,lenth),
“Handler_read_rnd:”,findva(“Handler_read_rnd”,last_data,lenth),
“Handler_read_rnd_next:”,findva(“Handler_read_rnd_next”,last_data,lenth)
);
mprintf(outfile,”[###Sorts and temp tables info:]n”);
mprintf(outfile,
“%s%-20ld%s%ldn”,
“Sort_rows:”,findva(“Sort_rows”,last_data,lenth),
“Sort_merge_passes:”,findva(“Sort_merge_passes”,last_data,lenth)
);
mprintf(outfile,
“%s%-11ld%s%ldn”,
“Created_tmp_tables:”,findva(“Created_tmp_tables”,last_data,lenth),
“Created_tmp_disk_tables:”,findva(“Created_tmp_disk_tables”,last_data,lenth)
);
mprintf(outfile,”[###Table join info:]n”);
mprintf(outfile,
“%s%-13ld%s%ldn”,
“Select_full_join:”,findva(“Select_full_join”,last_data,lenth),
“Select_scan:”,findva(“Select_scan”,last_data,lenth)
);

mprintf(outfile,”[###Open table cache and binlog cache info:]n”);
mprintf(outfile,
“%s%-8ld%s%ldn”,
“Table_open_cache_hits:”,findva(“Table_open_cache_hits”,last_data,lenth),
“Table_open_cache_misses:”,findva(“Table_open_cache_misses”,last_data,lenth)
);
mprintf(outfile,
“%s%-13ld%s%ldn”,
“Binlog_cache_use:”,findva(“Binlog_cache_use”,last_data,lenth),
“Binlog_cache_disk_use:”,findva(“Binlog_cache_disk_use”,last_data,lenth)
);
mprintf(outfile,”[###Hanler_xxx and Com_xxx info:]n”);

mprintf(outfile,
“%s%-15ld%s%-15ld%s%ldn”,
“Handler_delete:”,findva(“Handler_delete”,last_data,lenth),
“Handler_update:”,findva(“Handler_update”,last_data,lenth),
“Handler_write:”,findva(“Handler_write”,last_data,lenth)
);
mprintf(outfile,
“%s%-15ld%s%-ldn”,
“Handler_commit:”,findva(“Handler_commit”,last_data,lenth),
“Handler_rollback:”,findva(“Handler_rollback”,last_data,lenth)
);
mprintf(outfile,
“%s%-19ld%s%ldn”,
“Com_commit:”,findva(“Com_commit”,last_data,lenth),
“Com_rollback:”,findva(“Com_rollback”,last_data,lenth)
);
mprintf(outfile,
“%s%-8ld%s%-8ld%s%-8ld%s%-8ldn”,
“U(counts):”,findva(“Com_update”,last_data,lenth),
“D(counts):”,findva(“Com_delete”,last_data,lenth),
“I(counts):”,findva(“Com_insert”,last_data,lenth),
“S(counts):”,findva(“Com_select”,last_data,lenth)
);
mprintf(outfile,
“————————————–INNODBINFO——————————————n”);
mprintf(outfile,”[###Innodb_lock info:]n”);
mprintf(outfile,
“%s%-17ld%s%ldn”,
“Innodb_row_lock_time:”,findva(“Innodb_row_lock_time”,last_data,lenth),
“Innodb_row_lock_waits:”,findva(“Innodb_row_lock_waits”,last_data,lenth)
);
mprintf(outfile,
“%s%ldn”,
“Innodb_row_lock_current_waits:”,findva(“Innodb_row_lock_current_waits”,last_data,lenth)
);
mprintf(outfile,”[###Innodb_buffer_pool info:]n”);

mprintf(outfile,”%s%-10ld%s%ldn”,”Innodb_buf_pool_pages_total:”,findva(“Innodb_buffer_pool_pages_total”,last_data,lenth)
,”Innodb_buf_pool_pages_dirty:”,findva(“Innodb_buffer_pool_pages_dirty”,last_data,lenth));

mprintf(outfile,”%s%-16ld%s%ldn”,”Innodb_buf_pages_data:”,findva(“Innodb_buffer_pool_pages_data”,last_data,lenth)
,”Innodb_buf_pages_free:”,findva(“Innodb_buffer_pool_pages_free”,last_data,lenth));

mprintf(outfile,
“%s%-9ld%s%ldn”,
“Innodb_buffer_pool_wait_free:”,findva(“Innodb_buffer_pool_wait_free”,last_data,lenth),
“Innodb_buffer_pool_pages_misc:”,findva(“Innodb_buffer_pool_pages_misc”,last_data,lenth)
);
mprintf(outfile,
“%s%-11ld%s%ldn”,
“Innodb_dblwr_pages_written:”,findva(“Innodb_dblwr_pages_written”,last_data,lenth),
“Innodb_dblwr_writes:”,findva(“Innodb_dblwr_writes”,last_data,lenth)
);

mprintf(outfile,
“%s%ldn”,
“Innodb_buffer_pool_pages_flushed:”,findva(“Innodb_buffer_pool_pages_flushed”,last_data,lenth)
);
mprintf(outfile,
“%s%-10ld %s%-10ld %s%-10ldn”,
“read_ahead:”,findva(“Innodb_buffer_pool_read_ahead”,last_data,lenth),
“ahead_evict:”,findva(“Innodb_buffer_pool_read_ahead_evicted”,last_data,lenth),
“ahead_rnd:”,findva(“Innodb_buffer_pool_read_ahead_rnd”,last_data,lenth));
mprintf(outfile,”[###Innodb_log info:]n”);
mprintf(outfile,
“%s%-20ld%s%ldn”,
“Innodb_log_writes:”,findva(“Innodb_log_writes”,last_data,lenth),
“Innodb_log_waits:”,findva(“Innodb_log_waits”,last_data,lenth)
);
mprintf(outfile,
“%s%-9ld%s%ldn”,
“Innodb_os_log_pending_fsyncs:”,findva(“Innodb_os_log_pending_fsyncs”,last_data,lenth),
“Innodb_os_log_pending_writes:”,findva(“Innodb_os_log_pending_writes”,last_data,lenth)
);
mprintf(outfile,
“%s%ldn”,
“Innodb_os_log_written:”,findva(“Innodb_os_log_written”,last_data,lenth)
);
mprintf(outfile,”[###Innodb_data read/writes info:]n”);
mprintf(outfile,
“%s%-20ld%s%ldn”,
“Innodb_data_reads:”,findva(“Innodb_data_reads”,last_data,lenth),
“Innodb_data_read:”,findva(“Innodb_data_read”,last_data,lenth)
);
mprintf(outfile,
“%s%-19ld%s%ldn”,
“Innodb_data_writes:”,findva(“Innodb_data_writes”,last_data,lenth),
“Innodb_data_written:”,findva(“Innodb_data_written”,last_data,lenth)
);
mprintf(outfile,
“%s%-19ld%s%ldn”,
“Innodb_data_fsyncs:”,findva(“Innodb_data_fsyncs”,last_data,lenth),
“Innodb_data_pending_fsyncs:”,findva(“Innodb_data_pending_fsyncs”,last_data,lenth)
);
mprintf(outfile,
“%s%ldn”,
“Innodb_buffer_pool_reads(physics reads):”,findva(“Innodb_buffer_pool_reads”,last_data,lenth)
);
mprintf(outfile,
“%s%ldn”,
“Innodb_buffer_pool_read_requests(logic reads):”,findva(“Innodb_buffer_pool_read_requests”,last_data,lenth)
);

mprintf(outfile,”[###Innodb_data rows info:]n”);
mprintf(outfile,
“%s%-8ld%s%-8ld%s%-8ld%s%-8ldn”,
“U(rows):”,findva(“Innodb_rows_updated”,last_data,lenth),
“D(rows):”,findva(“Innodb_rows_deleted”,last_data,lenth),
“I(rows):”,findva(“Innodb_rows_inserted”,last_data,lenth),
“S(rows):”,findva(“Innodb_rows_read”,last_data,lenth)
);

mprintf(outfile,”—————————————TOTALINFO——————————————n”);
mprintf(outfile,
“%s%-15ld%s%ldn”,
“Bytes_received:”,findva(“Bytes_received”,last_data,lenth),
“Bytes_sent:”,findva(“Bytes_sent”,last_data,lenth)
);
return ret;
}

int64_t findva(const char* ins,D_STROE* last_data,int lenth)
{
int i=0;
for(i=0;i
{
if(!strcmp(last_data->statname,ins))
{
return last_data->data;
break;
}
last_data++;
}
return (int64_t)(-1);
}以上是“如何使用自己开发的MYMON工具监控MYSQL运行状态”这篇文章的所有内容，感谢各位的阅读！希望分享的内容对大家有帮助，更多相关知识，欢迎关注开发云行业资讯频道！

相关推荐: mysql查询最近7天数据的方法

这篇文章主要介绍mysql查询最近7天数据的方法，文中介绍的非常详细，具有一定的参考价值，感兴趣的小伙伴们一定要看完！mysql查询最近7天的数据的方法：直接执行【SELECT * FROM 表名 WHERE date_sub(curdate(), inter…