MySQL Server Advanced Plugin

Monitoring of MySQL databases on Unix.

Changelog
Introduction
Requirements
Compatibility matrix
Agent modules clasification
Instalation
Monitoring
Plugin parametrization

Changelog

Date	Author	Change	Version
12/03/12	Tomas	First Version	V1r1
01/07/2013	Mario	Changes in code	v1r2
25/04/2014	Mario	Changes in code	v1r3
3/09/2022	Alex	Changes in code	v1r4

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.
<token de chequeo de sistema>
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.
<token de chequeo de rendimiento>
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 <token to search>
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 <check_name>
mysql_innodb <token to search in innodb status>
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