使用Zabbix 监控MySQL （Python3脚本简单监控）

环境：

    操作系统版本：CentOS7.6

    MysqlServe版本：8.0

    Zabbix.Server版本：6.0LTS

    Zabbix.agent版本：Agent2

功能作用：

监控数据库信息

采集原理：

根据Mysql命令 show global status; 进行mysql的简单监控。
通过python脚本获取命令结果，转换为json格式，通过zabbix的相关项目和预处理进行指标采集。
脚本通过Mysql账户登录查询数据采集，首先要创建一个mysql的监控用户，创建用户过程略。

编写python3脚本：

定义sql语句文件

vim /etc/zabbix/mysql_status_all.sql
show global status;

python脚本内容

#!/usr/bin/python3
import subprocess
import json

MYSQL_USER = 'dbmonitor'
MYSQL_PWD = 'dbmonitor'
MYSQL_CMD = '/usr/local/mysql-8.0/bin/mysql'
SQL_FILE = '/etc/zabbix/mysql_status_all.sql'
mysql_port = 3306
mysql_host='127.0.0.1'
class MysqlCheck:
    def __init__(self,port=3306,host='127.0.0.1'):
        self.port = port
        self.host = host

    def GlobalMysql(self):
        cmd =f"cat {SQL_FILE} | {MYSQL_CMD} -u{MYSQL_USER} -p{MYSQL_PWD} -h{self.host} -P{self.port} | tail -n +2"
        process = subprocess.Popen(cmd, shell=True, stdout=subprocess.PIPE, stderr=subprocess.PIPE,universal_newlines=True)
        stdout, stderr = process.communicate()
        return stdout

    def JsonData(self):`在这里插入代码片`
        data = self.GlobalMysql()
        data_dict = {}
        lines = data.strip().split('\n')

        max_value_length = 50
        for line in lines:
            parts = line.split('\t')
            if len(parts) == 2:
                key, value = parts
                if len(value) < max_value_length:
                    data_dict[key] = value
        result = json.dumps(data_dict)
        return result

if __name__ == '__main__':
    mysql_check = MysqlCheck(port=mysql_port,host=mysql_host)
    result = mysql_check.JsonData()
    print(result)

脚本执行结果

[root@k8s-master03 zabbix]# python3 check_mysql.py 
{"Aborted_clients": "0", "Aborted_connects": "2", "Acl_cache_items_count": "0", "Binlog_cache_disk_use": "0", "Binlog_cache_use": "5", "Binlog_stmt_cache_disk_use": "0", "Binlog_stmt_cache_use": "2", "Bytes_received": "38980", "Bytes_sent": "1742055", "Com_admin_commands": "0", "Com_assign_to_keycache": "0", "Com_alter_db": "0", "Com_alter_event": "0", "Com_alter_function": "0", "Com_alter_instance": "0", "Com_alter_procedure": "0", "Com_alter_resource_group": "0", "Com_alter_server": "0", "Com_alter_table": "0", "Com_alter_tablespace": "0", "Com_alter_user": "2", "Com_alter_user_default_role": "0", "Com_analyze": "0", "Com_begin": "0", "Com_binlog": "0", "Com_call_procedure": "0", "Com_change_db": "1", "Com_change_master": "0", "Com_change_repl_filter": "0", "Com_change_replication_source": "0", "Com_check": "0", "Com_checksum": "0", "Com_clone": "0", "Com_commit": "0", "Com_create_db": "2", "Com_create_event": "0", "Com_create_function": "0", "Com_create_index": "0", "Com_create_procedure": "0", "Com_create_role": "0", "Com_create_server": "0", "Com_create_table": "35", "Com_create_resource_group": "0", "Com_create_trigger": "0", "Com_create_udf": "0", "Com_create_user": "1", "Com_create_view": "0", "Com_create_spatial_reference_system": "0", "Com_dealloc_sql": "0", "Com_delete": "0", "Com_delete_multi": "0", "Com_do": "0", "Com_drop_db": "0", "Com_drop_event": "0", "Com_drop_function": "0", "Com_drop_index": "0", "Com_drop_procedure": "0", "Com_drop_resource_group": "0", "Com_drop_role": "0", "Com_drop_server": "0", "Com_drop_spatial_reference_system": "0", "Com_drop_table": "0", "Com_drop_trigger": "0", "Com_drop_user": "0", "Com_drop_view": "0", "Com_empty_query": "0", "Com_execute_sql": "0", "Com_explain_other": "0", "Com_flush": "3", "Com_get_diagnostics": "0", "Com_grant": "1", "Com_grant_roles": "0", "Com_ha_close": "0", "Com_ha_open": "0", "Com_ha_read": "0", "Com_help": "0", "Com_import": "0", "Com_insert": "0", "Com_insert_select": "0", "Com_install_component": "0", "Com_install_plugin": "0", "Com_kill": "0", "Com_load": "0", "Com_lock_instance": "0", "Com_lock_tables": "0", "Com_optimize": "0", "Com_preload_keys": "0", "Com_prepare_sql": "0", "Com_purge": "0", "Com_purge_before_date": "0", "Com_release_savepoint": "0", "Com_rename_table": "0", "Com_rename_user": "0", "Com_repair": "0", "Com_replace": "0", "Com_replace_select": "0", "Com_reset": "0", "Com_resignal": "0", "Com_restart": "0", "Com_revoke": "0", "Com_revoke_all": "0", "Com_revoke_roles": "0", "Com_rollback": "0", "Com_rollback_to_savepoint": "0", "Com_savepoint": "0", "Com_select": "125", "Com_set_option": "4", "Com_set_password": "0", "Com_set_resource_group": "0", "Com_set_role": "0", "Com_signal": "0", "Com_show_binlog_events": "0", "Com_show_binlogs": "0", "Com_show_charsets": "0", "Com_show_collations": "0", "Com_show_create_db": "0", "Com_show_create_event": "0", "Com_show_create_func": "0", "Com_show_create_proc": "0", "Com_show_create_table": "0", "Com_show_create_trigger": "0", "Com_show_databases": "0", "Com_show_engine_logs": "0", "Com_show_engine_mutex": "0", "Com_show_engine_status": "0", "Com_show_events": "0", "Com_show_errors": "0", "Com_show_fields": "0", "Com_show_function_code": "0", "Com_show_function_status": "0", "Com_show_grants": "2", "Com_show_keys": "0", "Com_show_master_status": "0", "Com_show_open_tables": "0", "Com_show_plugins": "0", "Com_show_privileges": "0", "Com_show_procedure_code": "0", "Com_show_procedure_status": "0", "Com_show_processlist": "0", "Com_show_profile": "0", "Com_show_profiles": "0", "Com_show_relaylog_events": "0", "Com_show_replicas": "0", "Com_show_slave_hosts": "0", "Com_show_replica_status": "0", "Com_show_slave_status": "0", "Com_show_status": "108", "Com_show_storage_engines": "0", "Com_show_table_status": "0", "Com_show_tables": "0", "Com_show_triggers": "0", "Com_show_variables": "0", "Com_show_warnings": "0", "Com_show_create_user": "0", "Com_shutdown": "0", "Com_replica_start": "0", "Com_slave_start": "0", "Com_replica_stop": "0", "Com_slave_stop": "0", "Com_group_replication_start": "0", "Com_group_replication_stop": "0", "Com_stmt_execute": "0", "Com_stmt_close": "0", "Com_stmt_fetch": "0", "Com_stmt_prepare": "0", "Com_stmt_reset": "0", "Com_stmt_send_long_data": "0", "Com_truncate": "0", "Com_uninstall_component": "0", "Com_uninstall_plugin": "0", "Com_unlock_instance": "0", "Com_unlock_tables": "0", "Com_update": "0", "Com_update_multi": "0", "Com_xa_commit": "0", "Com_xa_end": "0", "Com_xa_prepare": "0", "Com_xa_recover": "0", "Com_xa_rollback": "0", "Com_xa_start": "0", "Com_stmt_reprepare": "0", "Connection_errors_accept": "0", "Connection_errors_internal": "0", "Connection_errors_max_connections": "0", "Connection_errors_peer_address": "0", "Connection_errors_select": "0", "Connection_errors_tcpwrap": "0", "Connections": "135", "Created_tmp_disk_tables": "12", "Created_tmp_files": "5", "Created_tmp_tables": "120", "Current_tls_ca": "ca.pem", "Current_tls_capath": "", "Current_tls_cert": "server-cert.pem", "Current_tls_cipher": "", "Current_tls_ciphersuites": "", "Current_tls_crl": "", "Current_tls_crlpath": "", "Current_tls_key": "server-key.pem", "Current_tls_version": "TLSv1.2,TLSv1.3", "Delayed_errors": "0", "Delayed_insert_threads": "0", "Delayed_writes": "0", "Error_log_buffered_bytes": "1720", "Error_log_buffered_events": "10", "Error_log_expired_events": "0", "Error_log_latest_write": "1694237694215575", "Flush_commands": "3", "Global_connection_memory": "0", "Handler_commit": "780", "Handler_delete": "168", "Handler_discover": "0", "Handler_external_lock": "7579", "Handler_mrr_init": "0", "Handler_prepare": "20", "Handler_read_first": "59", "Handler_read_key": "2304", "Handler_read_last": "0", "Handler_read_next": "4789", "Handler_read_prev": "0", "Handler_read_rnd": "0", "Handler_read_rnd_next": "105573", "Handler_rollback": "0", "Handler_savepoint": "0", "Handler_savepoint_rollback": "0", "Handler_update": "340", "Handler_write": "52983", "Innodb_buffer_pool_dump_status": "Dumping of buffer pool not started", "Innodb_buffer_pool_load_status": "Buffer pool(s) load completed at 230909 13:34:54", "Innodb_buffer_pool_resize_status": "", "Innodb_buffer_pool_resize_status_code": "0", "Innodb_buffer_pool_resize_status_progress": "0", "Innodb_buffer_pool_pages_data": "999", "Innodb_buffer_pool_bytes_data": "16367616", "Innodb_buffer_pool_pages_dirty": "0", "Innodb_buffer_pool_bytes_dirty": "0", "Innodb_buffer_pool_pages_flushed": "264", "Innodb_buffer_pool_pages_free": "7183", "Innodb_buffer_pool_pages_misc": "10", "Innodb_buffer_pool_pages_total": "8192", "Innodb_buffer_pool_read_ahead_rnd": "0", "Innodb_buffer_pool_read_ahead": "0", "Innodb_buffer_pool_read_ahead_evicted": "0", "Innodb_buffer_pool_read_requests": "29969", "Innodb_buffer_pool_reads": "843", "Innodb_buffer_pool_wait_free": "0", "Innodb_buffer_pool_write_requests": "7024", "Innodb_data_fsyncs": "227", "Innodb_data_pending_fsyncs": "0", "Innodb_data_pending_reads": "0", "Innodb_data_pending_writes": "0", "Innodb_data_read": "13880320", "Innodb_data_reads": "864", "Innodb_data_writes": "604", "Innodb_data_written": "4901888", "Innodb_dblwr_pages_written": "123", "Innodb_dblwr_writes": "36", "Innodb_redo_log_read_only": "OFF", "Innodb_redo_log_uuid": "1075899837", "Innodb_redo_log_checkpoint_lsn": "19434259", "Innodb_redo_log_current_lsn": "19434259", "Innodb_redo_log_flushed_to_disk_lsn": "19434259", "Innodb_redo_log_logical_size": "512", "Innodb_redo_log_physical_size": "3276800", "Innodb_redo_log_capacity_resized": "104857600", "Innodb_redo_log_resize_status": "OK", "Innodb_log_waits": "0", "Innodb_log_write_requests": "5126", "Innodb_log_writes": "146", "Innodb_os_log_fsyncs": "93", "Innodb_os_log_pending_fsyncs": "0", "Innodb_os_log_pending_writes": "0", "Innodb_os_log_written": "449024", "Innodb_page_size": "16384", "Innodb_pages_created": "161", "Innodb_pages_read": "842", "Innodb_pages_written": "264", "Innodb_redo_log_enabled": "ON", "Innodb_row_lock_current_waits": "0", "Innodb_row_lock_time": "0", "Innodb_row_lock_time_avg": "0", "Innodb_row_lock_time_max": "0", "Innodb_row_lock_waits": "0", "Innodb_rows_deleted": "0", "Innodb_rows_inserted": "0", "Innodb_rows_read": "0", "Innodb_rows_updated": "0", "Innodb_system_rows_deleted": "168", "Innodb_system_rows_inserted": "446", "Innodb_system_rows_read": "5862", "Innodb_system_rows_updated": "340", "Innodb_sampled_pages_read": "0", "Innodb_sampled_pages_skipped": "0", "Innodb_num_open_files": "15", "Innodb_truncated_status_writes": "0", "Innodb_undo_tablespaces_total": "2", "Innodb_undo_tablespaces_implicit": "2", "Innodb_undo_tablespaces_explicit": "0", "Innodb_undo_tablespaces_active": "2", "Key_blocks_not_flushed": "0", "Key_blocks_unused": "6698", "Key_blocks_used": "0", "Key_read_requests": "0", "Key_reads": "0", "Key_write_requests": "0", "Key_writes": "0", "Locked_connects": "0", "Max_execution_time_exceeded": "0", "Max_execution_time_set": "0", "Max_execution_time_set_failed": "0", "Max_used_connections": "1", "Max_used_connections_time": "2023-09-09 13:36:09", "Mysqlx_aborted_clients": "0", "Mysqlx_address": "::", "Mysqlx_bytes_received": "0", "Mysqlx_bytes_received_compressed_payload": "0", "Mysqlx_bytes_received_uncompressed_frame": "0", "Mysqlx_bytes_sent": "0", "Mysqlx_bytes_sent_compressed_payload": "0", "Mysqlx_bytes_sent_uncompressed_frame": "0", "Mysqlx_compression_algorithm": "", "Mysqlx_compression_level": "", "Mysqlx_connection_accept_errors": "0", "Mysqlx_connection_errors": "0", "Mysqlx_connections_accepted": "0", "Mysqlx_connections_closed": "0", "Mysqlx_connections_rejected": "0", "Mysqlx_crud_create_view": "0", "Mysqlx_crud_delete": "0", "Mysqlx_crud_drop_view": "0", "Mysqlx_crud_find": "0", "Mysqlx_crud_insert": "0", "Mysqlx_crud_modify_view": "0", "Mysqlx_crud_update": "0", "Mysqlx_cursor_close": "0", "Mysqlx_cursor_fetch": "0", "Mysqlx_cursor_open": "0", "Mysqlx_errors_sent": "0", "Mysqlx_errors_unknown_message_type": "0", "Mysqlx_expect_close": "0", "Mysqlx_expect_open": "0", "Mysqlx_init_error": "0", "Mysqlx_messages_sent": "0", "Mysqlx_notice_global_sent": "0", "Mysqlx_notice_other_sent": "0", "Mysqlx_notice_warning_sent": "0", "Mysqlx_notified_by_group_replication": "0", "Mysqlx_port": "33060", "Mysqlx_prep_deallocate": "0", "Mysqlx_prep_execute": "0", "Mysqlx_prep_prepare": "0", "Mysqlx_rows_sent": "0", "Mysqlx_sessions": "0", "Mysqlx_sessions_accepted": "0", "Mysqlx_sessions_closed": "0", "Mysqlx_sessions_fatal_error": "0", "Mysqlx_sessions_killed": "0", "Mysqlx_sessions_rejected": "0", "Mysqlx_socket": "/tmp/mysqlx.sock", "Mysqlx_ssl_accepts": "0", "Mysqlx_ssl_active": "", "Mysqlx_ssl_cipher": "", "Mysqlx_ssl_cipher_list": "", "Mysqlx_ssl_ctx_verify_depth": "18446744073709551615", "Mysqlx_ssl_ctx_verify_mode": "5", "Mysqlx_ssl_finished_accepts": "0", "Mysqlx_ssl_server_not_after": "Sep  6 05:33:34 2033 GMT", "Mysqlx_ssl_server_not_before": "Sep  9 05:33:34 2023 GMT", "Mysqlx_ssl_verify_depth": "", "Mysqlx_ssl_verify_mode": "", "Mysqlx_ssl_version": "", "Mysqlx_stmt_create_collection": "0", "Mysqlx_stmt_create_collection_index": "0", "Mysqlx_stmt_disable_notices": "0", "Mysqlx_stmt_drop_collection": "0", "Mysqlx_stmt_drop_collection_index": "0", "Mysqlx_stmt_enable_notices": "0", "Mysqlx_stmt_ensure_collection": "0", "Mysqlx_stmt_execute_mysqlx": "0", "Mysqlx_stmt_execute_sql": "0", "Mysqlx_stmt_execute_xplugin": "0", "Mysqlx_stmt_get_collection_options": "0", "Mysqlx_stmt_kill_client": "0", "Mysqlx_stmt_list_clients": "0", "Mysqlx_stmt_list_notices": "0", "Mysqlx_stmt_list_objects": "0", "Mysqlx_stmt_modify_collection_options": "0", "Mysqlx_stmt_ping": "0", "Mysqlx_worker_threads": "2", "Mysqlx_worker_threads_active": "0", "Not_flushed_delayed_rows": "0", "Ongoing_anonymous_transaction_count": "0", "Open_files": "2", "Open_streams": "0", "Open_table_definitions": "45", "Open_tables": "98", "Opened_files": "2", "Opened_table_definitions": "97", "Opened_tables": "182", "Performance_schema_accounts_lost": "0", "Performance_schema_cond_classes_lost": "0", "Performance_schema_cond_instances_lost": "0", "Performance_schema_digest_lost": "0", "Performance_schema_file_classes_lost": "0", "Performance_schema_file_handles_lost": "0", "Performance_schema_file_instances_lost": "0", "Performance_schema_hosts_lost": "0", "Performance_schema_index_stat_lost": "0", "Performance_schema_locker_lost": "0", "Performance_schema_memory_classes_lost": "0", "Performance_schema_metadata_lock_lost": "0", "Performance_schema_mutex_classes_lost": "0", "Performance_schema_mutex_instances_lost": "0", "Performance_schema_nested_statement_lost": "0", "Performance_schema_prepared_statements_lost": "0", "Performance_schema_program_lost": "0", "Performance_schema_rwlock_classes_lost": "0", "Performance_schema_rwlock_instances_lost": "0", "Performance_schema_session_connect_attrs_longest_seen": "134", "Performance_schema_session_connect_attrs_lost": "0", "Performance_schema_socket_classes_lost": "0", "Performance_schema_socket_instances_lost": "0", "Performance_schema_stage_classes_lost": "0", "Performance_schema_statement_classes_lost": "0", "Performance_schema_table_handles_lost": "0", "Performance_schema_table_instances_lost": "0", "Performance_schema_table_lock_stat_lost": "0", "Performance_schema_thread_classes_lost": "0", "Performance_schema_thread_instances_lost": "0", "Performance_schema_users_lost": "0", "Prepared_stmt_count": "0", "Queries": "371", "Questions": "370", "Replica_open_temp_tables": "0", "Resource_group_supported": "ON", "Secondary_engine_execution_count": "0", "Select_full_join": "0", "Select_full_range_join": "0", "Select_range": "0", "Select_range_check": "0", "Select_scan": "216", "Slave_open_temp_tables": "0", "Slow_launch_threads": "0", "Slow_queries": "0", "Sort_merge_passes": "0", "Sort_range": "0", "Sort_rows": "0", "Sort_scan": "0", "Ssl_accept_renegotiates": "0", "Ssl_accepts": "102", "Ssl_callback_cache_hits": "0", "Ssl_cipher": "TLS_AES_256_GCM_SHA384", "Ssl_client_connects": "0", "Ssl_connect_renegotiates": "0", "Ssl_ctx_verify_depth": "18446744073709551615", "Ssl_ctx_verify_mode": "5", "Ssl_default_timeout": "7200", "Ssl_finished_accepts": "102", "Ssl_finished_connects": "0", "Ssl_server_not_after": "Sep  6 05:33:34 2033 GMT", "Ssl_server_not_before": "Sep  9 05:33:34 2023 GMT", "Ssl_session_cache_hits": "0", "Ssl_session_cache_misses": "0", "Ssl_session_cache_mode": "SERVER", "Ssl_session_cache_overflows": "0", "Ssl_session_cache_size": "128", "Ssl_session_cache_timeout": "300", "Ssl_session_cache_timeouts": "0", "Ssl_sessions_reused": "0", "Ssl_used_session_cache_entries": "0", "Ssl_verify_depth": "18446744073709551615", "Ssl_verify_mode": "5", "Ssl_version": "TLSv1.3", "Table_locks_immediate": "108", "Table_locks_waited": "0", "Table_open_cache_hits": "3608", "Table_open_cache_misses": "200", "Table_open_cache_overflows": "0", "Tc_log_max_pages_used": "0", "Tc_log_page_size": "0", "Tc_log_page_waits": "0", "Threads_cached": "0", "Threads_connected": "1", "Threads_created": "1", "Threads_running": "2", "Tls_library_version": "OpenSSL 1.1.1s  1 Nov 2022", "Uptime": "14246", "Uptime_since_flush_status": "14246"}

Zabbix自定义监控项

定义键值

[root@k8s-master03 zabbix_agent2.d]# cat checkmysql.conf 
UserParameter=mysql.instance.check,python3 /etc/zabbix/check_mysql.py

重启zabbix-agent2

[root@k8s-master03 ~]# systemctl restart zabbix-agent2

Zabbix web界面添加主监控项

主监控项： 不同类型的监控项，用于采集原始的监控数据。
这里新建一个名为Mysql_linux_check_python3的模板，方便后续使用。

添加依赖监控项

依赖监控项： 根据一定的规则匹配(预处理)主监控项的原始数据，以提取需要的数据。

可根据想要采集的数据添加依赖监控项，操作模式一样。
可自行百度show global status;的详细参数解释，根据此来取数据。

监控结果

随意添加了几个监控项，在虚拟环境搭建的mysql8.0，因此很多指标都是默认的。

这只是一个简单的mysql数据监控，脚本还待优化。多实例mysql可以采用zabbix的自动发现进行数据采集。