README
¶
mysql
mysql 监控采集插件，核心原理就是连到 mysql 实例，执行一些 sql，解析输出内容，整理为监控数据上报。
Configuration
# # collect interval
# interval = 15
# 要监控 MySQL，首先要给出要监控的MySQL的连接地址、用户名、密码
[[instances]]
address = "127.0.0.1:3306"
username = "root"
password = "1234"
# # set tls=custom to enable tls
# parameters = "tls=false"
# 通过 show global status监控mysql，默认抓取一些基础指标，
# 如果想抓取更多global status的指标，把下面的配置设置为true
extra_status_metrics = true
# 通过show global variables监控mysql的全局变量，默认抓取一些常规的
# 常规的基本够用了，扩展的部分，默认不采集，下面的配置设置为false
extra_innodb_metrics = false
# 监控processlist，关注较少，默认不采集
gather_processlist_processes_by_state = false
gather_processlist_processes_by_user = false
# 监控各个数据库的磁盘占用大小
gather_schema_size = false
# 监控所有的table的磁盘占用大小
gather_table_size = false
# 是否采集系统表的大小，通过不用，所以默认设置为false
gather_system_table_size = false
# 通过 show slave status监控slave的情况，比较关键，所以默认采集
gather_slave_status = true
# # timeout
# timeout_seconds = 3
# # interval = global.interval * interval_times
# interval_times = 1
# 为mysql实例附一个instance的标签，因为通过address=127.0.0.1:3306不好区分
# important! use global unique string to specify instance
# labels = { instance="n9e-10.2.3.4:3306" }
## Optional TLS Config
# use_tls = false
# tls_min_version = "1.2"
# tls_ca = "/etc/categraf/ca.pem"
# tls_cert = "/etc/categraf/cert.pem"
# tls_key = "/etc/categraf/key.pem"
## Use TLS but skip chain & host verification
# insecure_skip_verify = true
# 自定义SQL，指定SQL、返回的各个列那些是作为metric，哪些是作为label
# [[instances.queries]]
# mesurement = "users"
# metric_fields = [ "total" ]
# label_fields = [ "service" ]
# # field_to_append = ""
# timeout = "3s"
# request = '''
# select 'n9e' as service, count(*) as total from n9e_v5.users
# '''
监控多个实例
当主机填写为localhost时mysql会采用 unix domain socket连接
当主机填写为127.0.0.1时mysql会采用tcp方式连接
大家最常问的问题是如何监控多个mysql实例，实际大家对toml配置学习一下就了解了，[[instances]] 部分表示数组，是可以出现多个的，address参数支持通过unix路径连接 所以，举例：
[[instances]]
address = "10.2.3.6:3306"
username = "root"
password = "1234"
labels = { instance="n9e-10.2.3.6:3306" }
[[instances]]
address = "10.2.6.9:3306"
username = "root"
password = "1234"
labels = { instance="zbx-10.2.6.9:3306" }
[[instances]]
address = "/tmp/mysql.sock"
username = "root"
password = "1234"
labels = { instance="zbx-localhost:3306" }
监控大盘和告警规则
本 README 的同级目录，大家可以看到alerts.json 是告警规则，导入夜莺就可以使用， dashboard-by-instance.json 就是监控大盘（注意！监控大盘使用instance大盘变量，所以，上面的配置文件中要配置一个instance的标签，就是 labels = { instance="n9e-10.2.3.4:3306" } 部分），也是导入夜莺就可以使用。dashboard-by-ident是使用ident作为大盘变量，适用于先找到宿主机器，再找机器上面的mysql实例的场景
Documentation
¶
Index ¶
Constants ¶
View Source
const ( SQL_GLOBAL_STATUS = `SHOW /*!50002 GLOBAL */ STATUS` SQL_GLOBAL_VARIABLES = `SHOW GLOBAL VARIABLES` SQL_ENGINE_INNODB_STATUS = `SHOW /*!50000 ENGINE*/ INNODB STATUS` SQL_INFO_SCHEMA_PROCESSLIST = `` /* 197-byte string literal not displayed */ SQL_INFO_SCHEMA_PROCESSLIST_BY_USER = `SELECT user, sum(1) AS connections FROM INFORMATION_SCHEMA.PROCESSLIST GROUP BY user` SQL_95TH_PERCENTILE = `` /* 335-byte string literal not displayed */ SQL_QUERY_SCHEMA_SIZE = `` /* 135-byte string literal not displayed */ SQL_QUERY_TABLE_SIZE = `` /* 263-byte string literal not displayed */ SQL_QUERY_SYSTEM_TABLE_SIZE = `` /* 259-byte string literal not displayed */ SQL_AVG_QUERY_RUN_TIME = `` /* 198-byte string literal not displayed */ SQL_WORKER_THREADS = "SELECT THREAD_ID, NAME FROM performance_schema.threads WHERE NAME LIKE '%worker'" SQL_PROCESS_LIST = "SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND LIKE '%Binlog dump%'" SQL_INNODB_ENGINES = ` SELECT engine FROM information_schema.ENGINES WHERE engine='InnoDB' and support != 'no' and support != 'disabled'` SQL_SERVER_ID_AWS_AURORA = ` SHOW VARIABLES LIKE 'aurora_server_id'` SQL_REPLICATION_ROLE_AWS_AURORA = `` /* 164-byte string literal not displayed */ SQL_GROUP_REPLICATION_MEMBER = `` /* 129-byte string literal not displayed */ SQL_GROUP_REPLICATION_METRICS = `` /* 449-byte string literal not displayed */ SQL_GROUP_REPLICATION_PLUGIN_STATUS = ` SELECT plugin_status FROM information_schema.plugins WHERE plugin_name='group_replication'` )
Variables ¶
View Source
var BINLOG_VARS = map[string]struct{}{
"binlog_space_usage_bytes": {},
}
View Source
var GALERA_VARS = map[string]struct{}{
"wsrep_cluster_size": {},
"wsrep_local_recv_queue_avg": {},
"wsrep_flow_control_paused": {},
"wsrep_flow_control_paused_ns": {},
"wsrep_flow_control_recv": {},
"wsrep_flow_control_sent": {},
"wsrep_cert_deps_distance": {},
"wsrep_local_send_queue_avg": {},
"wsrep_replicated_bytes": {},
"wsrep_received_bytes": {},
"wsrep_received": {},
"wsrep_local_state": {},
"wsrep_local_cert_failures": {},
}
View Source
var GROUP_REPLICATION_VARS = map[string]struct{}{
"transactions_count": {},
"transactions_check": {},
"conflict_detected": {},
"transactions_row_validating": {},
"transactions_remote_applier_queue": {},
"transactions_remote_applied": {},
"transactions_local_proposed": {},
"transactions_local_rollback": {},
}
View Source
var INNODB_VARS = map[string]struct{}{
"innodb_buffer_pool_size": {},
"open_files_limit": {},
"innodb_log_waits": {},
"innodb_data_reads": {},
"innodb_data_writes": {},
"innodb_os_log_fsyncs": {},
"innodb_mutex_spin_waits": {},
"innodb_mutex_spin_rounds": {},
"innodb_mutex_os_waits": {},
"innodb_row_lock_waits": {},
"innodb_row_lock_time": {},
"innodb_row_lock_current_waits": {},
"innodb_current_row_locks": {},
"innodb_buffer_pool_read_requests": {},
"innodb_buffer_pool_reads": {},
}
auto compute "innodb_buffer_pool_bytes_dirty": {}, "innodb_buffer_pool_bytes_free": {}, "innodb_buffer_pool_bytes_used": {}, "innodb_buffer_pool_bytes_total": {}, "innodb_buffer_pool_pages_utilization": {},
View Source
var OPTIONAL_INNODB_VARS = map[string]struct{}{
"innodb_active_transactions": {},
"innodb_buffer_pool_bytes_data": {},
"innodb_buffer_pool_pages_data": {},
"innodb_buffer_pool_pages_dirty": {},
"innodb_buffer_pool_pages_flushed": {},
"innodb_buffer_pool_pages_free": {},
"innodb_buffer_pool_pages_total": {},
"innodb_buffer_pool_read_ahead": {},
"innodb_buffer_pool_read_ahead_evicted": {},
"innodb_buffer_pool_read_ahead_rnd": {},
"innodb_buffer_pool_wait_free": {},
"innodb_buffer_pool_write_requests": {},
"innodb_checkpoint_age": {},
"innodb_current_transactions": {},
"innodb_data_fsyncs": {},
"innodb_data_pending_fsyncs": {},
"innodb_data_pending_reads": {},
"innodb_data_pending_writes": {},
"innodb_data_read": {},
"innodb_data_written": {},
"innodb_dblwr_pages_written": {},
"innodb_dblwr_writes": {},
"innodb_hash_index_cells_total": {},
"innodb_hash_index_cells_used": {},
"innodb_history_list_length": {},
"innodb_ibuf_free_list": {},
"innodb_ibuf_merged": {},
"innodb_ibuf_merged_delete_marks": {},
"innodb_ibuf_merged_deletes": {},
"innodb_ibuf_merged_inserts": {},
"innodb_ibuf_merges": {},
"innodb_ibuf_segment_size": {},
"innodb_ibuf_size": {},
"innodb_lock_structs": {},
"innodb_locked_tables": {},
"innodb_locked_transactions": {},
"innodb_log_write_requests": {},
"innodb_log_writes": {},
"innodb_lsn_current": {},
"innodb_lsn_flushed": {},
"innodb_lsn_last_checkpoint": {},
"innodb_mem_adaptive_hash": {},
"innodb_mem_additional_pool": {},
"innodb_mem_dictionary": {},
"innodb_mem_file_system": {},
"innodb_mem_lock_system": {},
"innodb_mem_page_hash": {},
"innodb_mem_recovery_system": {},
"innodb_mem_thread_hash": {},
"innodb_mem_total": {},
"innodb_os_file_fsyncs": {},
"innodb_os_file_reads": {},
"innodb_os_file_writes": {},
"innodb_os_log_pending_fsyncs": {},
"innodb_os_log_pending_writes": {},
"innodb_os_log_written": {},
"innodb_pages_created": {},
"innodb_pages_read": {},
"innodb_pages_written": {},
"innodb_pending_aio_log_ios": {},
"innodb_pending_aio_sync_ios": {},
"innodb_pending_buffer_pool_flushes": {},
"innodb_pending_checkpoint_writes": {},
"innodb_pending_ibuf_aio_reads": {},
"innodb_pending_log_flushes": {},
"innodb_pending_log_writes": {},
"innodb_pending_normal_aio_reads": {},
"innodb_pending_normal_aio_writes": {},
"innodb_queries_inside": {},
"innodb_queries_queued": {},
"innodb_read_views": {},
"innodb_rows_deleted": {},
"innodb_rows_inserted": {},
"innodb_rows_read": {},
"innodb_rows_updated": {},
"innodb_s_lock_os_waits": {},
"innodb_s_lock_spin_rounds": {},
"innodb_s_lock_spin_waits": {},
"innodb_semaphore_wait_time": {},
"innodb_semaphore_waits": {},
"innodb_tables_in_use": {},
"innodb_x_lock_os_waits": {},
"innodb_x_lock_spin_rounds": {},
"innodb_x_lock_spin_waits": {},
}
View Source
var OPTIONAL_STATUS_VARS = map[string]struct{}{
"binlog_cache_disk_use": {},
"binlog_cache_use": {},
"handler_commit": {},
"handler_delete": {},
"handler_prepare": {},
"handler_read_first": {},
"handler_read_key": {},
"handler_read_next": {},
"handler_read_prev": {},
"handler_read_rnd": {},
"handler_read_rnd_next": {},
"handler_rollback": {},
"handler_update": {},
"handler_write": {},
"opened_tables": {},
"qcache_total_blocks": {},
"qcache_free_blocks": {},
"qcache_free_memory": {},
"qcache_not_cached": {},
"qcache_queries_in_cache": {},
"select_full_join": {},
"select_full_range_join": {},
"select_range": {},
"select_range_check": {},
"select_scan": {},
"sort_merge_passes": {},
"sort_range": {},
"sort_rows": {},
"sort_scan": {},
"table_locks_immediate": {},
"table_locks_immediate_rate": {},
"threads_cached": {},
"threads_created": {},
"table_open_cache_hits": {},
"table_open_cache_misses": {},
}
View Source
var PERFORMANCE_VARS = map[string]struct{}{
"query_run_time_avg": {},
"perf_digest_95th_percentile_avg_us": {},
}
View Source
var REPLICA_VARS = map[string]struct{}{
"seconds_behind_source": {},
"seconds_behind_master": {},
"slave_io_running": {},
"slave_sql_running": {},
"master_server_id": {},
"source_server_id": {},
"sql_delay": {},
"exec_master_log_pos": {},
"read_master_log_pos": {},
}
"replicas_connected": {},
View Source
var SCHEMA_VARS = map[string]struct{}{
"information_schema_size": {},
}
View Source
var STATUS_VARS = map[string]struct{}{
"uptime": {},
"prepared_stmt_count": {},
"slow_queries": {},
"questions": {},
"queries": {},
"com_select": {},
"com_insert": {},
"com_update": {},
"com_delete": {},
"com_replace": {},
"com_commit": {},
"com_rollback": {},
"com_load": {},
"com_insert_select": {},
"com_update_multi": {},
"com_delete_multi": {},
"com_replace_select": {},
"connections": {},
"max_used_connections": {},
"aborted_clients": {},
"aborted_connects": {},
"open_files": {},
"open_tables": {},
"bytes_sent": {},
"bytes_received": {},
"qcache_hits": {},
"qcache_inserts": {},
"qcache_lowmem_prunes": {},
"table_locks_waited": {},
"table_locks_waited_rate": {},
"created_tmp_tables": {},
"created_tmp_disk_tables": {},
"created_tmp_files": {},
"threads_connected": {},
"threads_running": {},
"key_blocks_used": {},
"key_blocks_unused": {},
"key_blocks_not_flushed": {},
"key_read_requests": {},
"key_reads": {},
"key_write_requests": {},
"key_writes": {},
}
View Source
var SYNTHETIC_VARS = map[string]struct{}{
"qcache_utilization": {},
"qcache_instant_utilization": {},
}
View Source
var TABLE_VARS = map[string]struct{}{
"information_table_index_size": {},
"information_table_data_size": {},
}
View Source
var VARIABLES_VARS = map[string]struct{}{
"key_buffer_size": {},
"key_cache_block_size": {},
"max_connections": {},
"max_prepared_stmt_count": {},
"query_cache_size": {},
"table_open_cache": {},
"thread_cache_size": {},
"long_query_time": {},
"max_user_connections": {},
"read_only": {},
}
Functions ¶
This section is empty.
Types ¶
type Instance ¶
type Instance struct {
config.InstanceConfig
Address string `toml:"address"`
Username string `toml:"username"`
Password string `toml:"password"`
Parameters string `toml:"parameters"`
TimeoutSeconds int64 `toml:"timeout_seconds"`
Queries []QueryConfig `toml:"queries"`
GlobalQueries []QueryConfig `toml:"-"`
ExtraStatusMetrics bool `toml:"extra_status_metrics"`
ExtraInnodbMetrics bool `toml:"extra_innodb_metrics"`
GatherProcessListProcessByState bool `toml:"gather_processlist_processes_by_state"`
GatherProcessListProcessByUser bool `toml:"gather_processlist_processes_by_user"`
GatherSchemaSize bool `toml:"gather_schema_size"`
GatherTableSize bool `toml:"gather_table_size"`
GatherSystemTableSize bool `toml:"gather_system_table_size"`
GatherSlaveStatus bool `toml:"gather_slave_status"`
DisableGlobalStatus bool `toml:"disable_global_status"`
DisableGlobalVariables bool `toml:"disable_global_variables"`
DisableInnodbStatus bool `toml:"disable_innodb_status"`
DisableExtraInnodbStatus bool `toml:"disable_extra_innodb_status"`
DisablebinLogs bool `toml:"disable_binlogs"`
tls.ClientConfig
// contains filtered or unexported fields
}
func (*Instance) Gather ¶
func (ins *Instance) Gather(slist *types.SampleList)
func (*Instance) InitValidMetrics ¶
func (ins *Instance) InitValidMetrics()
type MySQL ¶
type MySQL struct {
config.PluginConfig
Instances []*Instance `toml:"instances"`
Queries []QueryConfig `toml:"queries"`
}
func (*MySQL) GetInstances ¶
Source Files
Click to show internal directories.
Click to hide internal directories.