MySQL Server Advanced Plugin Monitoring of MySQL databases on Unix. 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. 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