Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to use their own development of MYMON tools to monitor the running status of MYSQL, the article is very detailed, has a certain reference value, interested friends must read it!

1. How this tool is displayed:

The tool constantly prints some important values in show global status to the foreground or to the

In the specified file, it is used to help DBA with performance diagnosis or bottleneck determination.

Second, the operation principle of this tool:

Record the value of show global status according to the number of seconds entered by the user. The previous value is oldvalue, and the new value is newvalue.

In memory, and then determine whether it is a cumulative value or a current value, if it is a cumulative value, newvalue-oldvalue, if it is a current value

Output, which ensures the correctness of the output

Third, the characteristics of tools:

1. Low permissions for monitoring accounts only require useage permissions

2. Remote connection function

3. Write data to the specified file according to the needs of the user

4. Run log records to detect errors and enter logs

5. 5.6 5.7 support, no testing 5.5

6. Open source core source code

7. You can customize the output at any time according to your needs. Of course, you need to contact me.

Note:

WINDOWS does not support it, and only LINUX 64BIT.

And this tool relies on MYSQL to connect to dynamic library files

Help for the use of tools

[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 socketfile

[- 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

Note that the local connection uses-S connection to specify socketfile, remote use of-h-P connection uses IP and port connection, and-l can directly write the output to the specified file.

-d is the debug mode. Generally, this tool will record a log under the name of mymon.log in the current directory. If there are any errors, it will be recorded in the log.

Open-d accounting for more information, but generally do not need to use, mainly when I debug the code. Of course-t and-n are like help talking about the number of seconds between and

The number of times it lasts. If the output has a value of-1 in use, it means that the value has not been collected.

Generally, you only need to put this tool on a server online, and use a remote connection to connect to how to connect to the server, such as:

. / mymon-h 192.168.190.91-P3306-t 5-n 100-ummon2-pgelc123-l / root/mysqlog/20170123mysql.log

Means to connect to the server 192.168.190.91 3306 port to output 100 times at a time in 5 seconds, and keep the output to the file / root/mysqlog/20170123mysql.log

But note that SysTime: Sun Jan 22 16:13:30 2017 output is the current time to run the program system, but Uptime is certainly the time of the MYSQLD server

IV. If obtained and used

Get:

I compiled a total of four versions.

5.7Community version mymon5.7c

5.7 percona version mymon5.7p

5.6Community version mymon5.6c

Version 5.6 perconna mymon5.6p

Baidu cloud disk is as follows:

Http://pan.baidu.com/s/1jIa6OfG

If you want to use an older version, please contact me.

Use:

Download and upload to the LINUX operating system

Mkdir mon

Cp mymon5.7p. / mon/

Chmod 755 mymon5.7p

Then you must find the appropriate library file location, usually in the lib directory of the installation location, such as:

[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

It can run normally, by the way, 5.6 is so.18 5.7for so.20 and the older version is prior to 5.6.

About MYSQL monitoring user rights: what permissions the monitoring department needs, as long as a user is established.

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)

This also minimizes concerns, because this account basically does not have any permissions, and if you do not trust the program, you can put the program to remotely connect to the online database, such as:

. / mymon5.6c-umoni-pgelc123-h292.168.190.93-P3307-n 1-t 1-l / tmp/mysqlmon.log

In this way, the program will have neither permission nor online database server, and can be managed centrally, but there is one note:

SysTime: Sun Jan 22 16:13:30 2017 output is the current time of running the program system, but Uptime is certainly the time of the MYSQLD server

5. The contents of this tool are displayed:

The content displayed is not fixed. You can simply add indicators later according to the requirements. The current output is as follows:

-- 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): 631D (counts): 159I (counts): 47s (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): 675D (rows): 34575 I (rows): 194s (rows): 767094

-- TOTALINFO--

Bytes_received:911280 Bytes_sent:8653956

VI. Description of the monitoring value:

(this part, with the author's own understanding, please point out if there is any mistake)

In the text

Current value output: keep the current value output of show global status

Cumulative value subtractive output: subtracts the next value output from the previous value, with an interval of seconds specified by the user

Part one:

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. (current value output unit second)

Connections:The number of connection attempts (successful or not) to the MySQL server. (cumulative values minus the number of output units)

Aborted_clients:The number of connections that were aborted because the client died without closing the connection

Properly. (cumulative value minus output, number of connections per unit)

Max_used_connections:The maximum number of connections that have been in use simultaneously since the server started.

(current value output, connections per unit)

The second part:

[# 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. (current value output unit connections)

Threads_running:The number of threads that are not sleeping. (current value output unit connections)

Qcache_free_memory:The amount of free memory for the query cache. (current value output unit bytes)

Qcache_hits:The number of query cache hits. (cumulative values minus the number of output units)

Qcache_not_cached:The number of noncached queries. (cumulative values minus the number of output units)

Qcache_inserts:The number of queries added to the query cache. (cumulative values minus the number of output units)

The third part:

This section is all (cumulative values minus the number of output units)

[# 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:

/ * the number of times the first record in the index was read. If high, it indicates that the server is performing a large number of full index scans; for example, SELECT * order by id, assume that the id column has an index.

* 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:

/ * the number of requests to read a row according to the index. If high, the query and table are indexed correctly. This value may be ref or const or range, but it represents the index KEY hit using the index

* 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:

/ * the number of requests to read the next row in index order. This value will be triggered if the index is not unique or if the prefix or range of the unique index is used. To put it simply, only a unique hit does not trigger this value.

* if this value is large, it means that the next key needs to be accessed continuously for a large number of times.

*

* 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:

/ *

* query the number of requests for the last index key of the read index. When using ORDER BY, the server first makes a request to use the first index, and then scans the index back in order. When using ORDER BY DESC

* the server gives priority to a request to use the last index, and then scans the index forward.

* for example: select … Order by id desc limit 10

, /

Handler_read_rnd,Handler_read_rnd_next: if these two values are high, you may encounter full table scan and type=ALL.

This part can get a general idea of the current use of full table scans and indexes in the database.

Part IV:

[# 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. (cumulative values minus rows of output units)

Sort_merge_passes:The number of merge passes that the sort algorithm has had to do. (cumulative values minus the number of output units)

Created_tmp_tables:The number of internal temporary tables created by the server while executing statements. (cumulative values minus the number of output units)

Created_tmp_disk_tables:The number of internal on-disk temporary tables created by the server while executing statements. (cumulative values minus the number of output units)

Obviously, this section illustrates the use of sorting and temporary tables, which can help DBA to judge.

Part V:

[# 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

(cumulative values minus the number of output units)

Select_scan:The number of joins that did a full scan of the first table. (cumulative values minus the number of output units)

These two values indicate whether the index is used in jion, especially Select_full_join, which should be relatively low, otherwise the index is not used in the driven table.

It should be noted that optimizing statements, generally speaking, it is normal to have a high Select_scan, because it is normal for a driver table to use a full table scan if there is no predicate condition.

Part VI:

[# 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. (cumulative values minus the number of output units)

Table_open_cache_misses:The number of misses for open tables cache lookups. (cumulative values minus the number of output units)

Binlog_cache_use: The number of transactions that used the binary log cache. (cumulative values minus the number of output units)

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. (cumulative values minus the number of output units)

Obviously, this part shows whether binlog_cache_size and table_open_cache are suitable or not.

Part VII:

This section is all (cumulative values minus the number of output units)

[# 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): 631D (counts): 159I (counts): 47s (counts): 4955

U (counts): 631D (counts): 159I (counts): 47s (counts): 4955

This line is an abbreviation for Com_update\ Com_delete\ Com_insert\ Com_select

Handler_xxx: this batch of values indicates the number of times the corresponding operation has been processed

Com_xxx: this batch of values indicates the number of times the statement corresponding to the corresponding operation was issued

For example, Handler_commit is different from Com_commit. If it is implied submission and automatic submission, you do not need to initiate commit, but

Commit is processed, so Handler_commit increases but Com_rollback remains the same, such as manually executing a commit

The addition of Handler_commit to Com_commit does not change, because although the commit command is initiated, you do not need to deal with the experiment as follows:

Mysql > show status like'% commit%'

+-+ +

| | Variable_name | Value |

+-+ +

| | Com_commit | 1 | |

| | Com_xa_commit | 0 | |

| | Handler_commit | 4 |

+-+ +

3 rows in set (0.01sec)

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)

Part VIII:

[# 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. (cumulative values minus output units in milliseconds)

Innodb_row_lock_waits:The number of times operations on InnoDB tables had to wait for a row lock. (cumulative values minus the number of output units)

Innodb_row_lock_current_waits:The number of row lockscurrently being waited for by operations on InnoDB tables. (number of output units of current value)

Obviously this part is to monitor the status of the innodb lock.

Part IX:

[# 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: and Innodb_buffer_pool_pages_data The total size of the InnoDB buffer pool, in pages. (current value output unit page)

Innodb_buf_pool_pages_dirty: and Innodb_buffer_pool_pages_dirty The current number of dirty pagesin the InnoDB buffer pool. (current value output unit page)

Innodb_buf_pages_data: and Innodb_buffer_pool_pages_data The number of pagesin the InnoDB buffer poolcontaining data. The number includes both dirtyand

Clean pages. (current value output unit page)

Innodb_buf_pages_free: and Innodb_buffer_pool_pages_free The number of free pagesin the InnoDB buffer pool. (current value output unit page)

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. (cumulative values minus the number of output units)

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. (current value output unit page)

Innodb_dblwr_pages_written:The number of pages that have been written to the doublewrite buffer. (cumulative values minus output units page)

Innodb_dblwr_writes:The number of doublewrite operations that have been performed. (cumulative values minus the number of output units)

Innodb_buffer_pool_pages_flushed:The number of requests to flush pages from the InnoDB buffer pool. (cumulative values minus the number of output units)

This part is the focus of innodb_buffer_pool, including dirty pages, data pages, free pages and other information, as well as the number of times to scan the disk.

Part X:

[# 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. (cumulative values minus the number of output units)

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. (cumulative values minus the number of output units)

Innodb_os_log_pending_fsyncs:The number of pending fsync () operations for the InnoDB redo logfiles. (cumulative values minus the number of output units)

Innodb_os_log_pending_writes:The number of pending writes to the InnoDB redo logfiles. (cumulative values minus the number of output units)

Innodb_os_log_written:The number of bytes written to the InnoDB redo logfiles. (cumulative value minus output unit bytes)

This section describes the information written by innodb redo, such as how much data has been written, how many times to wait, if Innodb_os_log_pending_writes and Innodb_os_log_pending_fsyncs

If it is not 0, it means that you have encountered Igamo waiting. If the Innodb_log_waits is not 0, consider whether the innodb_log_buffer_size is too small.

Note: redo always uses kernel buffer regardless of innodb_flush_method settings.

Part 11:

[# 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. (cumulative values minus the number of output units)

Innodb_data_read:The amount of data read since the server was started (cumulative value minus output unit bytes)

Innodb_data_writes:The total number of data writes. (cumulative values minus the number of output units)

Innodb_data_written:The amount of data written so far, in bytes. (cumulative value minus output unit 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. (cumulative values minus the number of output units)

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. (cumulative values minus the number of output units)

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. (cumulative values minus the number of output units)

Innodb_buffer_pool_read_requests (logic reads): The number of logical read requests. (cumulative values minus the number of output units)

Read_ahead: and Innodb_buffer_pool_read_ahead:The number of pages read into the InnoDB buffer pool by the read-ahead background thread. (cumulative values minus output units page)

Ahead_evict: and 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. (cumulative values minus output units page)

Ahead_rnd: and 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. (cumulative values minus the number of output units)

In this part, you can see the hit rate and pre-read efficiency of logic reads. Note that if O_DIRECT is enabled for Innodb_data_pending_fsyncs and Innodb_data_fsyncs, then the memory of innodb_buffer

The data will not appear in kernel buffer, so the case of fsyncs pending will be much less. If these two values are large, pay attention to disk Igamo.

Part XII:

This section is all (cumulative values minus the number of output units)

U (rows): 675D (rows): 34575 I (rows): 194s (rows): 767094

U:Innodb_rows_updated

D:Innodb_rows_deleted

I:Innodb_rows_inserted

S:Innodb_rows_read

This section really reflects the number of lines of DML and SELECT, noticing the difference between Com_xxx and Handler_xxx

Part XIII:

Bytes_received:911280 Bytes_sent:8653956

There's nothing to say. Check the documents yourself.

7. Memory overflow detection

Use:

Valgrind-- tool=memcheck-- leak-check=full. / mymon5.7c-S/mysqldata/mysql5.7sla/mysqld3307.sock-t 1-n 10

= = 5120mm = LEAK SUMMARY:

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

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

= = 5120 blocks = possibly lost: 232 bytes in 2 blocks

= = 5120 bytes in = still reachable: 8176 bytes in 2 blocks

= = 5120 bytes in = suppressed: 0 bytes in 0 blocks

= = 5120 = Reachable blocks (those to which a pointer was found) are not shown.

= = 5120 hours = To see them, rerun with:-- leak-check=full-- show-reachable=yes

= = 5120 =

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

= = 5120 errors from = ERROR SUMMARY: 2 errors from 2 contexts (suppressed: 6 from 6)

You can see that there is no memory overflow.

VIII. Core source code

Click (here) to collapse or open

Main tone function:

/ *

> 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."

"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 socketfile;\ 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."

"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 (iMAXPARLEN)

{

Printf ("MYMON:hostname max length more than 500 bytes\ n")

Return-1

}

Strcpy (par [4], optarg)

Break

Case'Please:

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'packs:

If (strlen (optarg) > MAX_PAR_LEN)

{

Printf ("MYMON:password max length more than 500 bytes\ n")

Return-1

}

Strcpy (par [1], optarg)

Break

Case'Smits:

If (strlen (optarg) > MAX_PAR_LEN)

{

Printf ("MYMON:socket max length more than 500 bytes\ n")

Return-1

}

Strcpy (par [2], optarg)

Break

Case'tasking:

Sscanf (optarg, "% d", & t)

Break

Case'nails:

Sscanf (optarg, "d", & n)

Break

Case'lust:

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'dcards:

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

}

Click (here) to collapse or open

Fun.c function to realize interface function

/ *

> 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 retolddata)

(* (datap+i))-> olddata = (* (datap+i))-> newdata

/ / no free newdata pointer

If ((* (datap+i)-> newdata = (char*) calloc (1 minutes [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", "ERROR")

Return ret

}

If (debug = = 1)

{

Tmp_last_data = last_data

For (tmp = 0 position tpstatname)

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", "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)

{

Dolostroe * 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 = (DoloStroe *) 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 (iSistatname) > 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", * 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 = bMura; / / 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", * 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 (Dolostroe * 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 iTunes 0

For (iTun0scape istamereins))

{

Return last_data- > data

Break

}

Last_data++

}

Return (int64_t) (- 1)

}

The above is all the contents of the article "how to use your own MYMON tools to monitor the running status of MYSQL". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.