溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這篇文章主要介紹如何使用自己開發的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_update\Com_delete\Com_insert\Com_select的簡寫
Handler_xxx:這一批值說明的是相應操作處理的次數
Com_xxx:這一批值說明是相應操作對應語句發出的次數
比如Handler_commit 和 Com_commit這不同，如果是隱含提交和自動提交不用發起commit，但是
commit是處理了的，那么Handler_commit增加而Com_rollback不變，又比如手動執行一個commit
Com_commit增加Handler_commit不變，因為雖然發起了commit命令并不需要處理實驗如下：
mysql> show status like '%commit%';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| Com_commit | 1 |
| Com_xa_commit | 0 |
| Handler_commit | 4 |
+----------------+-------+
3 rows in set (0.01 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> show status like '%commit%';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| Com_commit | 2 |
| Com_xa_commit | 0 |
| Handler_commit | 4 |
+----------------+-------+
3 rows in set (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 change\n"
"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 support\n"
"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 name\n"
" 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 tool\n"
"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 change\n"
"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 support\n"
"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 -h\n"
);
return 0;
}
int getpar( char (*par)[500],int n)
{
int i;
for(i=0;i<4;i++)
{
printf("your parameter is %s\n",*(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<2)
{
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 bytes\n");
return -1;
}
strcpy(par[0],optarg);
break;
case 'h':
if(strlen(optarg)>MAX_PAR_LEN)
{
printf("MYMON:hostname max length more than 500 bytes\n");
return -1;
}
strcpy(par[4],optarg);
break;
case 'P':
if(strlen(optarg)>MAX_PAR_LEN)
{
printf("MYMON:port max length more than 500 bytes\n");
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 bytes\n");
return -1;
}
strcpy(par[1],optarg);
break;
case 'S':
if(strlen(optarg)>MAX_PAR_LEN)
{
printf("MYMON:socket max length more than 500 bytes\n");
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 bytes\n");
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 error\n");
return -1;
}
wel();
if(conn(par,t,n+1,fd,mport) !=0)
{
printf("MYMON:error pelase check log\n");
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 ret<0 is false*/
int 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 ret<0 is false*/
int memaloc(MDATA** *datap/*out*/,uint64_t row_count);
/* ret=0 is sucss ret<0 is false*/
int sub_store_data(MDATA* *datap,D_STROE* *last_data/*out*/,uint64_t row_count,int* lenth/*in*/);
/* ret=0 is sucss ret<0 is false*/
int 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) <0 )
{
GET_LOG(LogLevel[4],ERROR,"ERROR:mprintf() error:%d\n",ERROR);
printf("MYMON: mprintf() error:%d\n",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:%d\n",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:%d\n",ERROR);
printf("MYMON: mysql_init() error\n");
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:%d\n",ERROR);
printf("MYMON: mysql_real_connect() error\n");
if(mysql_errno(&conn_ptr))
{
GET_LOG(LogLevel[4],ERROR,"ERROR:mysql_real_connect() error:%s\n",mysql_error(&conn_ptr));
printf("MYMON: mysql_real_connect() error:%s\n",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:%s\n",mysql_error(&conn_ptr));
printf("MYMON: mysql_query() error:%s\n",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:%s\n",mysql_error(&conn_ptr));
printf("MYMON: mysql_store_result() error:%s\n",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 %llu\n",row_count);
GET_LOG(LogLevel[2],INFO,"INFO:mysql_field_count() fileds is %ld\n",row_field);
printf("MYMON: mysql_num_rows() rows is %llu\n",row_count);
printf("MYMON: mysql_field_count() fileds is %ld\n",row_field);
}
if(i == 0)
{
if((memaloc(&datap,row_count)) !=0)
{
ret = -6;
GET_LOG(LogLevel[4],ERROR,"ERROR:memaloc() error:%s\n","ERROR");
printf("MYMON: memaloc() error:%s\n","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:%s\n","ERROR");
printf("MYMON: handle_data() error:%s\n","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?:%d\n",ret);
for(i=0;i<row_count;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?:%d\n",ret);
printf("MYMON: memaloc() datap=NULL?:%d\n",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:%d\n",ret);
printf("MYMON: memaloc() memory malloc **p error:%d\n",ret);
return ret;
}
for(i=0;i<row_count;i++)
{
if((*(mp+i)=(MDATA*)calloc(1,sizeof(MDATA))) == NULL)
{
ret=-4;
GET_LOG(LogLevel[4],ERROR,"ERROR:handle_data() memory malloc *p error:%d\n",ret);
printf("MYMON: MYMON:handle_data() memory malloc *p error:%d\n",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 :%s\n","ERROR");
printf("MYMON:handle_data() result == NULL datap == NULL :%s\n","ERROR");
return ret;
}
for(i=0;i<row_count;i++)//control true result return
{
/*
* 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:%s\n","ERROR");
printf("MYMON: mysql_fetch_row() error:%s\n","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:%s\n","ERROR");
printf("MYMON: handle_data() statname loop mem alloc error:%s\n","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:%s\n","ERROR");
printf("MYMON: handle_data() newdata loop mem alloc error:%s\n","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 %s\n","no old data");
printf("MYMON: handle_data() first time is info %s\n","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:%s\n","ERROR");
printf("MYMON: handle_data() sub_store_data() error %s\n","ERROR");
return ret;
}
if(debug == 1)
{
tmp_last_data = last_data;
for(tmp =0;tmp<lenth;tmp++)
{
GET_LOG(LogLevel[1],DEBUG,"DEBUG:handle_data() find 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:%s\n","ERROR");
printf("MYMON: handle_data() print_data() error %s\n","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 %s\n","ERROR");
printf("MYMON: sub_store_data() datap == NULL || fail_data == NULL %s\n","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:%s\n","ERROR");
printf("MYMON: sub_store_data() mem alloc error:%s\n","ERROR");
return ret;
}
*last_data = tmp_p;
for(i=0;i<row_count;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:%s\n","ERROR");
printf("MYMON: sub_store_data() statname>MAXSTATNAME:%s\n","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 %s\n","ERROR");
printf("MYMON: sub_store_data() sscanf() error:%s\n","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 %s\n","ERROR");
printf("MYMON: sub_store_data() sscanf() error:%s\n","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 %s\n","ERROR");
printf("MYMON: ERROR:print_data() time() error:%s\n","ERROR");
return ret;
}
timeinfo = localtime ( &rawtime );
if (timeinfo == NULL )
{
ret = -1;
GET_LOG(LogLevel[4],ERROR,"ERROR:print_data() localtime () error %s\n","ERROR");
printf("MYMON: ERROR:print_data() localtime () error:%s\n","ERROR");
return ret;
}
mprintf(outfile,"------------------------------------------MYMON--------------------------------------------\n");
mprintf(outfile,"SysTime: %s",asctime (timeinfo));
mprintf(outfile,
"%s%-11ld%s%-8ld%s%-8ld%s%-8ld\n",
"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%-13ld\n",
"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%-17ld\n",
"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%-14ld\n",
"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%ld\n",
"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%ld\n",
"Sort_rows:",findva("Sort_rows",last_data,lenth),
"Sort_merge_passes:",findva("Sort_merge_passes",last_data,lenth)
);
mprintf(outfile,
"%s%-11ld%s%ld\n",
"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%ld\n",
"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%ld\n",
"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%ld\n",
"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%ld\n",
"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%-ld\n",
"Handler_commit:",findva("Handler_commit",last_data,lenth),
"Handler_rollback:",findva("Handler_rollback",last_data,lenth)
);
mprintf(outfile,
"%s%-19ld%s%ld\n",
"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%-8ld\n",
"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%ld\n",
"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%ld\n",
"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%ld\n","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%ld\n","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%ld\n",
"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%ld\n",
"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%ld\n",
"Innodb_buffer_pool_pages_flushed:",findva("Innodb_buffer_pool_pages_flushed",last_data,lenth)
);
mprintf(outfile,
"%s%-10ld %s%-10ld %s%-10ld\n",
"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%ld\n",
"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%ld\n",
"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%ld\n",
"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%ld\n",
"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%ld\n",
"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%ld\n",
"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%ld\n",
"Innodb_buffer_pool_reads(physics reads):",findva("Innodb_buffer_pool_reads",last_data,lenth)
);
mprintf(outfile,
"%s%ld\n",
"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%-8ld\n",
"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%ld\n",
"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<lenth;i++)
{
if(!strcmp(last_data->statname,ins))
{
return last_data->data;
break;
}
last_data++;
}
return (int64_t)(-1);
}
以上是“如何使用自己開發的MYMON工具監控MYSQL運行狀態”這篇文章的所有內容，感謝各位的閱讀！希望分享的內容對大家有幫助，更多相關知識，歡迎關注億速云行業資訊頻道！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
