Telegraf+Influxdb+Grafana 监控MySQL

本文介绍 使用 Telegraf+Influxdb+Grafana 架构 来快速 监控MySQL

首先 ,我们先看下效果图

Telegraf+Influxdb+Grafana 监控MySQL

一、添加telegraf 收集MySQL 的配置文件

因为我们在前面已经部署好telegraf 了,也已经采集了 主机侧的 一些 CPU ,内存,磁盘,网络流量 等 信息 , 现在收集Mysql 监控信息,最好还是与之前的telegraf.conf 配置文件分离开

[root@fxkj ~]# vim /etc/telegraf/telegraf.d/telegraf_mysql.conf

[[outputs.influxdb]]
  database = "mysql_metrics"
  urls = ["http://127.0.0.1:8086"]
  namepass = ["*_mysql"]
  username = "fxkj"
  password = "123456"


[[inputs.mysql]]
  servers = ["root:123456@tcp(localhost:3306)/?tls=false"]
  name_suffix = "_mysql"

#database 表示数据库名称,采集的数据都放在此库中

#urls 表示 influxdb 数据库地址

#servers 里面包含 了MySQL 授权用户的 用户名,密码 以及连接mysql 的地址

#name_suffix 表示名称后缀

二、重启 telegrag 采集器,查看日志刷新

[root@fxkj ~]#  systemctl restart telegraf.service

[root@fxkj ~]# tail -n 10 /tmp/telegraf.log

2020-08-11T01:37:20Z E! [outputs.influxdb] when writing to [http://localhost:8086]: Post

2020-08-11T01:37:42Z I! Loaded processors:

2020-08-11T01:37:42Z I! Loaded outputs: influxdb influxdb

2020-08-11T01:37:42Z I! Tags enabled: host=fxkjnj.com

2020-08-11T01:37:42Z I! [agent] Config: Interval:10s, Quiet:false, Hostname:"fxkj", Flush Interval:10s

 

三、登录到Influxdb 中查看Mysql监控数据

[root@aly mysql]# influx -username fxkj -password ‘123456’

Connected to http://localhost:8086 version 1.8.1

InfluxDB shell version: 1.8.1

> show databases;

name: databases

name

----

_internal

telegraf

mysql_metrics

> use mysql_metrics;

Using database mysql_metrics

> show measurements;

name: measurements

name

----

mysql_mysql

 

可以看到有一个 mysql_metrics 的库 以及一个 叫 mysql_mysql 的表

查看表中有哪些字段:

> show field keys from mysql_mysql;

name: mysql_mysql

fieldKey fieldType

-------- ---------

aborted_clients integer

aborted_connects integer

access_denied_errors integer

busy_time integer

bytes_received integer

bytes_sent integer

commands_admin_commands integer

commands_alter_db integer

commands_alter_db_upgrade integer

commands_alter_event integer

commands_alter_function integer

commands_alter_procedure integer

commands_alter_server integer

commands_alter_table integer

commands_alter_tablespace integer

commands_analyze integer

commands_assign_to_keycache integer

commands_begin integer

commands_binlog integer

commands_call_procedure integer

commands_change_db integer

commands_change_master integer

commands_check integer

commands_checksum integer

commands_commit integer

commands_create_db integer

commands_create_event integer

commands_create_function integer

commands_create_index integer

commands_create_procedure integer

commands_create_server integer

commands_create_table integer

commands_create_trigger integer

commands_create_udf integer

commands_create_user integer

commands_create_view integer

commands_dealloc_sql integer

commands_delete integer

commands_delete_multi integer

commands_do integer

commands_drop_db integer

commands_drop_event integer

commands_drop_function integer

commands_drop_index integer

commands_drop_procedure integer

commands_drop_server integer

commands_drop_table integer

commands_drop_trigger integer

commands_drop_user integer

commands_drop_view integer

commands_empty_query integer

commands_execute_sql integer

commands_flush integer

commands_grant integer

commands_ha_close integer

commands_ha_open integer

commands_ha_read integer

commands_help integer

commands_insert integer

commands_insert_select integer

commands_install_plugin integer

commands_kill integer

commands_load integer

commands_lock_tables integer

commands_optimize integer

commands_preload_keys integer

commands_prepare_sql integer

commands_purge integer

commands_purge_before_date integer

commands_release_savepoint integer

commands_rename_table integer

commands_rename_user integer

commands_repair integer

commands_replace integer

commands_replace_select integer

commands_reset integer

commands_resignal integer

commands_revoke integer

commands_revoke_all integer

commands_rollback integer

commands_rollback_to_savepoint integer

commands_savepoint integer

commands_select integer

commands_set_option integer

commands_show_authors integer

commands_show_binlog_events integer

commands_show_binlogs integer

commands_show_charsets integer

commands_show_client_statistics integer

commands_show_collations integer

commands_show_contributors integer

commands_show_create_db integer

commands_show_create_event integer

commands_show_create_func integer

commands_show_create_proc integer

commands_show_create_table integer

commands_show_create_trigger integer

commands_show_databases integer

commands_show_engine_logs integer

commands_show_engine_mutex integer

commands_show_engine_status integer

commands_show_errors integer

commands_show_events integer

commands_show_fields integer

commands_show_function_status integer

commands_show_grants integer

commands_show_index_statistics integer

commands_show_keys integer

commands_show_master_status integer

commands_show_open_tables integer

commands_show_plugins integer

commands_show_privileges integer

commands_show_procedure_status integer

commands_show_processlist integer

commands_show_profile integer

commands_show_profiles integer

commands_show_relaylog_events integer

commands_show_slave_hosts integer

commands_show_slave_status integer

commands_show_status integer

commands_show_storage_engines integer

commands_show_table_statistics integer

commands_show_table_status integer

commands_show_tables integer

commands_show_triggers integer

commands_show_user_statistics integer

commands_show_variables integer

commands_show_warnings integer

commands_signal integer

commands_slave_start integer

commands_slave_stop integer

commands_stmt_close integer

commands_stmt_execute integer

commands_stmt_fetch integer

commands_stmt_prepare integer

commands_stmt_reprepare integer

commands_stmt_reset integer

commands_stmt_send_long_data integer

commands_truncate integer

commands_uninstall_plugin integer

commands_unlock_tables integer

commands_update integer

commands_update_multi integer

commands_xa_commit integer

commands_xa_end integer

commands_xa_prepare integer

commands_xa_recover integer

commands_xa_rollback integer

commands_xa_start integer

connections integer

created_tmp_disk_tables integer

created_tmp_files integer

created_tmp_tables integer

delayed_errors integer

delayed_insert_threads integer

delayed_writes integer

empty_queries integer

executed_events integer

executed_triggers integer

feature_dynamic_columns integer

feature_fulltext integer

feature_gis integer

feature_locale integer

feature_subquery integer

feature_timezone integer

feature_trigger integer

feature_xml integer

flush_commands integer

handler_commit integer

handler_delete integer

handler_discover integer

handler_icp_attempts integer

handler_icp_match integer

handler_mrr_init integer

handler_mrr_key_refills integer

handler_mrr_rowid_refills integer

handler_prepare integer

handler_read_first integer

handler_read_key integer

handler_read_last integer

handler_read_next integer

handler_read_prev integer

handler_read_rnd integer

handler_read_rnd_deleted integer

handler_read_rnd_next integer

handler_rollback integer

handler_savepoint integer

handler_savepoint_rollback integer

handler_tmp_update integer

handler_tmp_write integer

handler_update integer

handler_write integer

innodb_adaptive_hash_cells integer

innodb_adaptive_hash_hash_searches integer

innodb_adaptive_hash_heap_buffers integer

innodb_adaptive_hash_non_hash_searches integer

innodb_background_log_sync integer

innodb_buffer_pool_bytes_data integer

innodb_buffer_pool_bytes_dirty integer

innodb_buffer_pool_pages_LRU_flushed integer

innodb_buffer_pool_pages_data integer

innodb_buffer_pool_pages_dirty integer

innodb_buffer_pool_pages_flushed integer

innodb_buffer_pool_pages_free integer

innodb_buffer_pool_pages_made_not_young integer

innodb_buffer_pool_pages_made_young integer

innodb_buffer_pool_pages_misc integer

innodb_buffer_pool_pages_old integer

innodb_buffer_pool_pages_total integer

innodb_buffer_pool_read_ahead integer

innodb_buffer_pool_read_ahead_evicted integer

innodb_buffer_pool_read_ahead_rnd integer

innodb_buffer_pool_read_requests integer

innodb_buffer_pool_reads integer

innodb_buffer_pool_wait_free integer

innodb_buffer_pool_write_requests integer

innodb_checkpoint_age integer

innodb_checkpoint_max_age integer

innodb_checkpoint_target_age integer

innodb_current_row_locks integer

innodb_data_fsyncs integer

innodb_data_pending_fsyncs integer

innodb_data_pending_reads integer

innodb_data_pending_writes integer

innodb_data_read integer

innodb_data_reads integer

innodb_data_writes integer

innodb_data_written integer

innodb_dblwr_pages_written integer

innodb_dblwr_writes integer

innodb_deadlocks integer

innodb_descriptors_memory integer

innodb_dict_tables integer

innodb_have_atomic_builtins integer

innodb_history_list_length integer

innodb_ibuf_discarded_delete_marks integer

innodb_ibuf_discarded_deletes integer

innodb_ibuf_discarded_inserts integer

innodb_ibuf_free_list integer

innodb_ibuf_merged_delete_marks integer

innodb_ibuf_merged_deletes integer

innodb_ibuf_merged_inserts integer

innodb_ibuf_merges integer

innodb_ibuf_segment_size integer

innodb_ibuf_size integer

innodb_log_waits integer

innodb_log_write_requests integer

innodb_log_writes integer

innodb_lsn_current integer

innodb_lsn_flushed integer

innodb_lsn_last_checkpoint integer

innodb_master_thread_10_second_loops integer

innodb_master_thread_1_second_loops integer

innodb_master_thread_background_loops integer

innodb_master_thread_main_flush_loops integer

innodb_master_thread_sleeps integer

innodb_max_trx_id integer

innodb_mem_adaptive_hash integer

innodb_mem_dictionary integer

innodb_mem_total integer

innodb_mutex_os_waits integer

innodb_mutex_spin_rounds integer

innodb_mutex_spin_waits integer

innodb_oldest_view_low_limit_trx_id integer

innodb_os_log_fsyncs integer

innodb_os_log_pending_fsyncs integer

innodb_os_log_pending_writes integer

innodb_os_log_written integer

innodb_page_size integer

innodb_pages_created integer

innodb_pages_read integer

innodb_pages_written integer

innodb_purge_trx_id integer

innodb_purge_undo_no integer

innodb_read_views_memory integer

innodb_row_lock_current_waits integer

innodb_row_lock_time integer

innodb_row_lock_time_avg integer

innodb_row_lock_time_max integer

innodb_row_lock_waits integer

innodb_rows_deleted integer

innodb_rows_inserted integer

innodb_rows_read integer

innodb_rows_updated integer

innodb_s_lock_os_waits integer

innodb_s_lock_spin_rounds integer

innodb_s_lock_spin_waits integer

innodb_truncated_status_writes integer

innodb_x_lock_os_waits integer

innodb_x_lock_spin_rounds integer

innodb_x_lock_spin_waits integer

key_blocks_not_flushed integer

key_blocks_unused integer

key_blocks_used integer

key_blocks_warm integer

key_read_requests integer

key_reads integer

key_write_requests integer

key_writes integer

last_query_cost integer

max_used_connections integer

not_flushed_delayed_rows integer

open_files integer

open_streams integer

open_table_definitions integer

open_tables integer

opened_files integer

opened_table_definitions integer

opened_tables integer

opened_views integer

performance_schema_cond_classes_lost integer

performance_schema_cond_instances_lost integer

performance_schema_file_classes_lost integer

performance_schema_file_handles_lost integer

performance_schema_file_instances_lost integer

performance_schema_locker_lost integer

performance_schema_mutex_classes_lost integer

performance_schema_mutex_instances_lost integer

performance_schema_rwlock_classes_lost integer

performance_schema_rwlock_instances_lost integer

performance_schema_table_handles_lost integer

performance_schema_table_instances_lost integer

performance_schema_thread_classes_lost integer

performance_schema_thread_instances_lost integer

prepared_stmt_count integer

qcache_free_blocks integer

qcache_free_memory integer

qcache_hits integer

qcache_inserts integer

qcache_lowmem_prunes integer

qcache_not_cached integer

qcache_queries_in_cache integer

qcache_total_blocks integer

queries integer

questions integer

rows_read integer

rows_sent integer

rows_tmp_read integer

rpl_status integer

select_full_join integer

select_full_range_join integer

select_range integer

select_range_check integer

select_scan integer

slave_heartbeat_period integer

slave_open_temp_tables integer

slave_received_heartbeats integer

slave_retried_transactions integer

slave_running integer

slow_launch_threads integer

slow_queries integer

sort_merge_passes integer

sort_range integer

sort_rows integer

sort_scan integer

subquery_cache_hit integer

subquery_cache_miss integer

syncs integer

table_locks_immediate integer

table_locks_waited integer

tc_log_max_pages_used integer

tc_log_page_size integer

tc_log_page_waits integer

threadpool_idle_threads integer

threadpool_threads integer

threads_cached integer

threads_connected integer

threads_created integer

threads_running integer

uptime integer

uptime_since_flush_status integer

 

四、登录Grafana 添加数据源 ,import 导入监控模板

(更多的监控模板,大家可以到https://grafana.com/grafana/dashboards 中 下载 ),评论+ 私信 回复小编 0811 即可获得 本文的监控模板

因为之前做了个主机侧的监控,所以为了方便区分,我们这次再添加一个influxdb 数据源

点击Configuration ,选择 Data Sources ,点击 Add data source

Telegraf+Influxdb+Grafana 监控MySQL

数据源名称: MySQL (我监控模板里的数据源是这个名称,最好和我一致,不然模板导入会报错)

URL : Influxdb 地址

Database :mysql 监控信息存放在 influxdb 数据库中的名称

Telegraf+Influxdb+Grafana 监控MySQL

点击 Save & Test 验证添加是否有问题

Telegraf+Influxdb+Grafana 监控MySQL

数据源准备好了 就可以导入 mysql 监控模板了

点击 左侧 + 号 ,选择 import ,—— > 点击 Upload.json file , 上传模板文件

Telegraf+Influxdb+Grafana 监控MySQL

Telegraf+Influxdb+Grafana 监控MySQL

到此模板就导入成功了

五、图标展示