# MySQL Server Advanced Plugin # Changelog
DateAuthorChangeVersion
12/03/12TomasFirst VersionV1r1
01/07/2013MarioChanges in codev1r2
25/04/2014MarioChanges in codev1r3
3/09/2022AlexChanges in codev1r4
# Introduction This document has as main aim the description of the MySQL database monitoring on Unix. A series of “ base” modules has been chosen on the basis of our experience in the system monitoring and on the requirements of some of our clients. Besides, all the specifications collected in different real production environments have been added, taking real specifications of database administrators. For the information extraction we use: • An external configuration file where all the plugin parameterization is defined. This configuration file could do calls (includes) to other files. • The software already installed in the system is used (MySQL, system commands, MySQL alerts files, ect) for the monitoring done by the plugin without having to install libraries or third party utilities. • An existing log parser is used (the one from Pandora) to process the MySQL alert logs.This parser should be “automatic” and should be based in the report of all the critical error messages with the “ERROR”\* form. •A serie of basic checks “by default” are done, but they could be deleted or customized. •An “open” interface is available to specify SQL free queries, allowing to do all kind of SQL queries that are done with other tools or in a manual way by the administrators. • The system is integrated with the Unix agent and with the capacity of distributing file collections, so the plugin could be distributed by one side, and the file colections by individual way-by agent-and/or by policy. It is important to mention, that same as with the rest of the montoring with PandoraFMS, the MySQL monitoring plugin could be used to collect information kind “text string” (to manage it as events) or kind numeric (to do performance management). # Requirements The requirements for doing that this monitoring works properly are the following: •To install the Pandora FMS agent. •To install Perl •To have one MySQL database installed in the machine where it is going to monitor, with connection to this database. •To specify the name, user, password and host of the MySQL database. •It is necessary that the user whith which the Pandora FMS agent is executed, that is, the user that will execute the plugin, will have access to the following MySQL resources: ◦ MySQL homedir Directory( directory with the MySQL , usually /var/lib/mysql). ◦MySQL log file (usually /var/lib/mysql/mysql.log). ◦Access and writting permissions to a directory for temporary files. **Documentation the Area that requires the monitoring should report.** For the right MySQL monitoring, it should be necessary that the Technical Area sends some specific information that will be included in the configuration files. These information is the following: • User, password, host with access to the MySQL database. This user should have permission to read all the tables from which he want to extract information. This user should have pemissions to read all the tables from which he want to extract information. •Log files and MySQL base directory. •If you want to monitor some item that does not come defined “by default”, then it will be necessary that you provide that SQL code to do this monitoring, and also an exit data example, specifying if it is numeric, kind string, etc). # Compatibility matrix
Systems where tested• Ubuntu 10.04 con MySQL 5.0 • OpenSuse 11.2 con MySQL 5.5
Systems where it should work• All other Unix systems with MySQL 5.0 or higher higher
# Agent modules clasification Note that most of these parameters must be processed as "delta" or using the "generic\_data\_inc" data type since they are counters. using the "generic\_data\_inc" data type since they are counters. Aborted\_connects Binlog\_cache\_disk\_use Binlog\_cache\_use Binlog\_stmt\_cache\_disk\_use Binlog\_stmt\_cache\_use Bytes\_received Bytes\_sent Com\_admin\_commands Com\_assign\_to\_keycache Com\_alter\_db Com\_alter\_db\_upgrade Com\_alter\_event Com\_alter\_function Com\_alter\_procedure Com\_alter\_server Com\_alter\_table Com\_alter\_tablespace Com\_analyze Com\_begin Com\_binlog Com\_call\_procedure Com\_change\_db Com\_change\_master Com\_check Com\_checksum Com\_commit Com\_create\_db Com\_create\_event Com\_create\_function Com\_create\_index Com\_create\_procedure Com\_create\_server Com\_create\_table Com\_create\_trigger Com\_create\_udf Com\_create\_user Com\_create\_view Com\_dealloc\_sql Com\_delete Com\_delete\_multi Com\_do Com\_drop\_db Com\_drop\_event Com\_drop\_function Com\_drop\_index Com\_drop\_procedure Com\_drop\_server Com\_drop\_table Com\_drop\_trigger Com\_drop\_user Com\_drop\_view Com\_empty\_query Com\_execute\_sql Com\_flush Com\_grant Com\_ha\_close Com\_ha\_open Com\_ha\_read Com\_help Com\_insert Com\_insert\_select Com\_install\_plugin Com\_kill Com\_load Com\_lock\_tables Com\_optimize Com\_preload\_keys Com\_prepare\_sql Com\_purge Com\_purge\_before\_date Com\_release\_savepoint Com\_rename\_table Com\_rename\_user Com\_repair Com\_replace Com\_replace\_select Com\_reset Com\_resignal Com\_revoke Com\_revoke\_all Com\_rollback Com\_rollback\_to\_savepoint Com\_savepoint Com\_select Com\_set\_option Com\_signal Com\_show\_authors Com\_show\_binlog\_events Com\_show\_binlogs Com\_show\_charsets Com\_show\_collations Com\_show\_contributors Com\_show\_create\_db Com\_show\_create\_event Com\_show\_create\_func Com\_show\_create\_proc Com\_show\_create\_table Com\_show\_create\_trigger Com\_show\_databases Com\_show\_engine\_logs Com\_show\_engine\_mutex Com\_show\_engine\_status Com\_show\_events Com\_show\_errors Com\_show\_fields Com\_show\_function\_status Com\_show\_grants Com\_show\_keys Com\_show\_master\_status Com\_show\_open\_tables Com\_show\_plugins Com\_show\_privileges Com\_show\_procedure\_status Com\_show\_processlist Com\_show\_profile Com\_show\_profiles Com\_show\_relaylog\_events Com\_show\_slave\_hosts Com\_show\_slave\_status Com\_show\_status Com\_show\_storage\_engines Com\_show\_table\_status Com\_show\_tables Com\_show\_triggers Com\_show\_variables Com\_show\_warnings Com\_slave\_start Com\_slave\_stop Com\_stmt\_close Com\_stmt\_execute Com\_stmt\_fetch Com\_stmt\_prepare Com\_stmt\_reprepare Com\_stmt\_reset Com\_stmt\_send\_long\_data Com\_truncate Com\_uninstall\_plugin Com\_unlock\_tables Com\_update Com\_update\_multi Com\_xa\_commit Com\_xa\_end Com\_xa\_prepare Com\_xa\_recover Com\_xa\_rollback Com\_xa\_start Compression Connections Created\_tmp\_disk\_tables Created\_tmp\_files Created\_tmp\_tables Delayed\_errors Delayed\_insert\_threads Delayed\_writes Flush\_commands Handler\_commit Handler\_delete Handler\_discover Handler\_prepare Handler\_read\_first Handler\_read\_key Handler\_read\_last Handler\_read\_next Handler\_read\_prev Handler\_read\_rnd Handler\_read\_rnd\_next Handler\_rollback Handler\_savepoint Handler\_savepoint\_rollback Handler\_update Handler\_write Innodb\_buffer\_pool\_pages\_data Innodb\_buffer\_pool\_pages\_dirty Innodb\_buffer\_pool\_pages\_flushed Innodb\_buffer\_pool\_pages\_free Innodb\_buffer\_pool\_pages\_misc Innodb\_buffer\_pool\_pages\_total Innodb\_buffer\_pool\_read\_ahead\_rnd Innodb\_buffer\_pool\_read\_ahead Innodb\_buffer\_pool\_read\_ahead\_evicted Innodb\_buffer\_pool\_read\_requests Innodb\_buffer\_pool\_reads Innodb\_buffer\_pool\_wait\_free Innodb\_buffer\_pool\_write\_requests Innodb\_data\_fsyncs Innodb\_data\_pending\_fsyncs Innodb\_data\_pending\_reads Innodb\_data\_pending\_writes Innodb\_data\_read Innodb\_data\_reads Innodb\_data\_writes Innodb\_data\_written Innodb\_dblwr\_pages\_written Innodb\_dblwr\_writes Innodb\_have\_atomic\_builtins Innodb\_log\_waits Innodb\_log\_write\_requests Innodb\_log\_writes Innodb\_os\_log\_fsyncs Innodb\_os\_log\_pending\_fsyncs Innodb\_os\_log\_pending\_writes Innodb\_os\_log\_written Innodb\_page\_size Innodb\_pages\_created Innodb\_pages\_read Innodb\_pages\_written Innodb\_row\_lock\_current\_waits Innodb\_row\_lock\_time Innodb\_row\_lock\_time\_avg Innodb\_row\_lock\_time\_max Innodb\_row\_lock\_waits Innodb\_rows\_deleted Innodb\_rows\_inserted Innodb\_rows\_read Innodb\_rows\_updated Innodb\_truncated\_status\_writes Key\_blocks\_not\_flushed Key\_blocks\_unused Key\_blocks\_used Key\_read\_requests Key\_reads Key\_write\_requests Key\_writes Last\_query\_cost Max\_used\_connections Not\_flushed\_delayed\_rows Open\_files Open\_streams Open\_table\_definitions Open\_tables Opened\_files Opened\_table\_definitions Opened\_tables Performance\_schema\_cond\_classes\_lost Performance\_schema\_cond\_instances\_lost Performance\_schema\_file\_classes\_lost Performance\_schema\_file\_handles\_lost Performance\_schema\_file\_instances\_lost Performance\_schema\_locker\_lost Performance\_schema\_mutex\_classes\_lost Performance\_schema\_mutex\_instances\_lost Performance\_schema\_rwlock\_classes\_lost Performance\_schema\_rwlock\_instances\_lost Performance\_schema\_table\_handles\_lost Performance\_schema\_table\_instances\_lost Performance\_schema\_thread\_classes\_lost Performance\_schema\_thread\_instances\_lost Prepared\_stmt\_count Qcache\_free\_blocks Qcache\_free\_memory Qcache\_hits Qcache\_inserts Qcache\_lowmem\_prunes Qcache\_not\_cached Qcache\_queries\_in\_cache Qcache\_total\_blocks Queries Questions Rpl\_status Select\_full\_join Select\_full\_range\_join Select\_range Select\_range\_check Select\_scan Slave\_heartbeat\_period Slave\_open\_temp\_tables Slave\_received\_heartbeats Slave\_retried\_transactions Slave\_running Slow\_launch\_threads Slow\_queries Sort\_merge\_passes Sort\_range Sort\_rows Sort\_scan Ssl\_accept\_renegotiates Ssl\_accepts Ssl\_callback\_cache\_hits Ssl\_cipher Ssl\_cipher\_list Ssl\_client\_connects Ssl\_connect\_renegotiates Ssl\_ctx\_verify\_depth Ssl\_ctx\_verify\_mode Ssl\_default\_timeout Ssl\_finished\_accepts Ssl\_finished\_connects Ssl\_session\_cache\_hits Ssl\_session\_cache\_misses Ssl\_session\_cache\_mode Ssl\_session\_cache\_overflows Ssl\_session\_cache\_size Ssl\_session\_cache\_timeouts Ssl\_sessions\_reused Ssl\_used\_session\_cache\_entries Ssl\_verify\_depth Ssl\_verify\_mode Ssl\_version Table\_locks\_immediate Table\_locks\_waited Tc\_log\_max\_pages\_used Tc\_log\_page\_size Tc\_log\_page\_waits Threads\_cached Threads\_connected Threads\_created Threads\_running Uptime Uptime\_since\_flush\_status # Instalation Copy the plugin to the agent's plugin directory, or distribute it with file collections. The same with the conf file. The call from the agent will be similar to this one, but using the paths where the plugin and the conf file are installed. ``` module_plugin perl /etc/pandora/plugins/pandora_mysql.pl /etc/pandora/plugins/mysql.conf ``` # Monitoring The plugin monitors “by default” the following things: •Checking of connectivity with the database. •Checks if the MySql process is active. •Checks the server memory (process) . •Number of connections TIME\_WAIT in the system . •Checks the space in the server disk (usually/var/lib/mysql). •Size of the file ibdata1 . •Search of errors in the database error logs (usually /var/lib/mysql/mysql.log). Besides, it also monitors the following performance parameters: •Number of MySQL active connections. •Time of server activity (uptime). •Number of aborted connections because of which the client did not close correctly the connection. •Number of bytes got by the clients. •Information about the server status (SHOW GLOBAL STATUS or SHOW STATUS). •Information about the status of InnoDB (SHOW INNODB STATUS) . •Number of bytes sent by clients. •Number of database entries. •Number of locks on tables on the database when doing a transaction. •Active locks on tables and registers for each active session. •Not answered queries from I/0 •Total data size in GB. All these modules comes parametrized in the “mysql.conf” file that comes with the plugin package. These modules could be deleted or extended by a MySQL administrator. # Plugin parametrization The plugin is used after having previously configured the configuration external file. NOTE: It is extremely important to consider that the configuration files that are though for the plugin in UNIX should be edited and stored with carriage return kind “UNIX” and that carriage return kind “WINDOWS” are used, then the plugin will be not work correctly. There are three functional blocks in the configuration file: **MySQL Access Configuration and General data** In order for the plugin to monitor the database, you have to provide it with the access credentials and some general data that will be used later in the checks: MySQL user, password and host. The access credentials will be completed as follows: ``` conf_mysql_user mysql conf_mysql_pass 1234 conf_mysql_host 127.0.0.1 ``` There is a possibility that we have a blank password for the mysql user. In this case the line ( # conf\_mysql\_pass) should be commented out. General data Here you will specify the MySQL home directory (usually /var/lib/mysql), the log file, a directory for temporary files and the complete path to the log file, a directory for temporary files and the complete path to the log parsing plugin of Pandora log parsing plugin: ``` conf_mysql_homedir /var/lib/mysql conf_mysql_basedir /var/lib/mysql conf_mysql_logfile /var/log/mysql.log conf_temp /tmp conf_logparser /etc/pandora/plugins/grep_log ``` **System parameters** The following describes the system-specific test modules that are configured by default in the attached mysql.conf. configured by default in the attached mysql.conf: System check block A system check block in the configuration file is as shown below: ``` check_begin # Linea de comentario. check_end ``` The following is a description of the plugin checks: Database connectivity check. This check will verify if the database has connectivity with other software elements. software. If this check is not satisfactory, the monitoring will be aborted: ``` check_begin check_mysql_service check_end ``` Check if the mysql process is active This check verifies that the MySQL process is active on the system: ``` check_begin check_mysql_service check_end ``` Server memory check (process) This check checks the memory usage of the MySQL server. ``` check_begin check_mysql_memory check_end ``` Number of TIME\_WAIT connections in the system Displays the number of connections in TIME\_WAIT state in the system: ``` check_begin check_system_timewait check_end ``` Server disk space check Checks the disk space (in KB) on the database side. It will check the size of the MySQL homedir directory specified in the General data section will be checked: ``` check_begin check_system_diskusage check_end ``` ibdata1 file size Checks the size (in KB) of the ibdata1 file located in the MySQL homedir directory: ``` check_begin check_mysql_ibdata1 check_end ``` Search for errors in the database error logs. It will search for the string "ERROR" in the log file indicated in the General Data section. This module will always return the async\_string data type and in case the check returns data the associated command will be executed (explained in the section Executing commands under condition): ``` check_begin check_mysql_logs check_end ``` **Performance parameters** The performance check modules are described below: Performance check block. Similar to the system checks as shown below: ``` check_begin # Linea de comentario. check_end ``` The performance check blocks are shown below: Number of active MySQL connections. Returns the number of active connections to the database: ``` check_begin mysql_status Full processlist check_end ``` * Number of connections aborted because the client did not correctly close the connection.* ``` check_begin mysql_status Aborted_connects check_end ``` Number of bytes received by clients ``` check_begin mysql_status Bytes_received check_end ``` * Number of bytes sent by clients * ``` check_begin mysql_status Bytes_sent check_end ``` Server status information (SHOW GLOBAL STATUS or SHOW STATUS) These queries are based on the MySQL SHOW GLOBAL STATUS command. Within the data returned by this command you can search for a token and its associated value: ``` check_begin mysql_status check_end ``` InnoDB Status Information (SHOW INNODB STATUS) These queries are based on the MySQL SHOW INNODB STATUS command. Within the data returned by this command you can search for a token and its associated value: ``` check_begin mysql_status Com_insert check_end ``` Number of insertions in the database Number of INSERT transactions made in the database: ``` check_begin check_name mysql_innodb check_end ``` *Number of locks on tables in the database when performing a transaction * ``` check_begin mysql_status Com_lock_tables check_end ``` *Active locks on tables and records for each active session* ``` check_begin # Number of locks over DB tables mysql_status Table_locks_waited check_end check_begin # Number of row locks mysql_status Innodb_row_lock_waits check_end ``` *Pending I/0 requests * ``` check_begin mysql_status Pending_io check_end ``` *Total data size in GB* ``` check_begin mysql_status Total_size check_end ``` Monitoring via SQL One of the most powerful features of the plugin is the possibility to specify your own SQL command to get the value. SQL to get the value. Let's see an example: ``` check_begin check_name num_tables check_schema information_schema check_sql SELECT COUNT(*) FROM tables check_end Check_name El nombre de la comprbación que se verá en la interfaz de Pandora. . Check_sql La query que deberá devolver un dato simple (ya sea número o cadena).. Check_schema El esquema de MySQL al que se conectará el plugin para realizar la query ``` Execution of commands under condition In all modules it is possible to specify the execution of a command if a condition is met. Such conditions can be: \- == (equal to a value) \- != (different from a value) \- < (less than a given value) \- > (Greater than a given value). If the condition is satisfied then the configured command will be executed using the post\_execution token. In the case of the check\_mysql\_logs check the command will be executed if any data is returned by this check. An example of this configuration is as follows: ``` check_begin check_mysql_cpu post_condition > 95 post_execution snmptrap -v 1 -c public x.x.x.x 1.2.4.4.65.6.4.3 6 128 check_end ``` Parameterization of the command associated to a check As mentioned above, in each check a command can be configured to be executed if the condition is met. In addition, a token can be used in the command so that if it is executed then it is replaced by the value of the check. For example, by means of the macro \_DATA\_ in this example the content of the check is saved in the file /tmp/mysql\_cpu\_result: ``` check_begin check_mysql_cpu post_condition > 95 post_execution echo _DATA_ >> /tmp/mysql_cpu_result check_end ``` Module status Complementarily, modules will return a status if indicated with the configuration token: post\_status WARNING Or: post\_status CRITICAL Data type By default all monitors will return generic\_data, unless the following configuration token is specified. module\_type generic\_data\_inc module\_type async\_string (or any other valid one) The only exception is the monitor check\_mysql\_service automatically a module of type generic\_proc. \* In modules where we use check\_name, check\_type can be used to choose the data type of the module. Data processing in performance type checks In performance type checks and SQL queries can be configured to return the absolute value as it is returned. the absolute value as returned by MySQL (data\_absolute) or the difference between the current and previous data (data\_delta). In the first execution of the check configured with data\_delta no data will be returned. From the second run the increment will be returned. If the difference between the current and previous data is negative, the value of the check is reset and the check will not return any data. By default the performance type checks will be set to data\_absolute. For example this check will return the increment of active MySQL sessions: ``` check_begin mysql_status Full processlist data_delta check_end ```