MySQL Input Plugin

This plugin gathers statistics from MySQL server instances.

[!NOTE] To gather metrics from the performance schema, it must first be enabled in MySQL. See the performance schema quick start for details.

⭐ Telegraf v0.1.1 🏷️ datastore 💻 all

Global configuration options

Plugins support additional global and plugin configuration settings for tasks such as modifying metrics, tags, and fields, creating aliases, and configuring plugin ordering. See CONFIGURATION.md for more details.

Configuration

toml

# Read metrics from one or many mysql servers
[[inputs.mysql]]
  ## specify servers via a url matching:
  ##  [username[:password]@][protocol[(address)]]/[?tls=[true|false|skip-verify|custom]]
  ##  see https://github.com/go-sql-driver/mysql#dsn-data-source-name
  ##  e.g.
  ##    servers = ["user:passwd@tcp(127.0.0.1:3306)/?tls=false"]
  ##    servers = ["user@tcp(127.0.0.1:3306)/?tls=false"]
  #
  ## If no servers are specified, then localhost is used as the host.
  servers = ["tcp(127.0.0.1:3306)/"]

  ## Selects the metric output format.
  ##
  ## This option exists to maintain backwards compatibility, if you have
  ## existing metrics do not set or change this value until you are ready to
  ## migrate to the new format.
  ##
  ## If you do not have existing metrics from this plugin set to the latest
  ## version.
  ##
  ## Telegraf >=1.6: metric_version = 2
  ##           <1.6: metric_version = 1 (or unset)
  metric_version = 2

  ## if the list is empty, then metrics are gathered from all database tables
  # table_schema_databases = []

  ## gather metrics from INFORMATION_SCHEMA.TABLES for databases provided
  ## in the list above
  # gather_table_schema = false

  ## gather thread state counts from INFORMATION_SCHEMA.PROCESSLIST
  # gather_process_list = false

  ## gather user statistics from INFORMATION_SCHEMA.USER_STATISTICS
  # gather_user_statistics = false

  ## gather auto_increment columns and max values from information schema
  # gather_info_schema_auto_inc = false

  ## gather metrics from INFORMATION_SCHEMA.INNODB_METRICS
  # gather_innodb_metrics = false

  ## gather metrics from all channels from SHOW SLAVE STATUS command output
  # gather_all_slave_channels = false

  ## gather metrics from SHOW SLAVE STATUS command output
  # gather_slave_status = false

  ## gather metrics from SHOW REPLICA STATUS command output
  # gather_replica_status = false

  ## use SHOW ALL SLAVES STATUS command output for MariaDB
  ## use SHOW ALL REPLICAS STATUS command if enable gather replica status
  # mariadb_dialect = false

  ## gather metrics from SHOW BINARY LOGS command output
  # gather_binary_logs = false

  ## gather metrics from SHOW GLOBAL VARIABLES command output
  # gather_global_variables = true

  ## gather metrics from PERFORMANCE_SCHEMA.TABLE_IO_WAITS_SUMMARY_BY_TABLE
  # gather_table_io_waits = false

  ## gather metrics from PERFORMANCE_SCHEMA.TABLE_LOCK_WAITS
  # gather_table_lock_waits = false

  ## gather metrics from PERFORMANCE_SCHEMA.TABLE_IO_WAITS_SUMMARY_BY_INDEX_USAGE
  # gather_index_io_waits = false

  ## gather metrics from PERFORMANCE_SCHEMA.EVENT_WAITS
  # gather_event_waits = false

  ## gather metrics from PERFORMANCE_SCHEMA.FILE_SUMMARY_BY_EVENT_NAME
  # gather_file_events_stats = false

  ## gather metrics from PERFORMANCE_SCHEMA.EVENTS_STATEMENTS_SUMMARY_BY_DIGEST
  # gather_perf_events_statements             = false
  #
  ## gather metrics from PERFORMANCE_SCHEMA.EVENTS_STATEMENTS_SUMMARY_BY_ACCOUNT_BY_EVENT_NAME
  # gather_perf_sum_per_acc_per_event         = false
  #
  ## list of events to be gathered for gather_perf_sum_per_acc_per_event
  ## in case of empty list all events will be gathered
  # perf_summary_events                       = []

  ## the limits for metrics form perf_events_statements
  # perf_events_statements_digest_text_limit = 120
  # perf_events_statements_limit = 250
  # perf_events_statements_time_limit = 86400

  ## Some queries we may want to run less often (such as SHOW GLOBAL VARIABLES)
  ##   example: interval_slow = "30m"
  # interval_slow = ""

  ## Optional TLS Config (used if tls=custom parameter specified in server uri)
  # tls_ca = "/etc/telegraf/ca.pem"
  # tls_cert = "/etc/telegraf/cert.pem"
  # tls_key = "/etc/telegraf/key.pem"
  ## Use TLS but skip chain & host verification
  # insecure_skip_verify = false

String Data

Some fields may return string data. This is unhelpful for some outputs where numeric data is required (e.g. Prometheus). In these cases, users can make use of the enum processor to convert string values to numeric values. Below is an example using the slave_slave_io_running field, which can have a variety of string values:

toml

[[processors.enum]]
  namepass = "mysql"
  [[processors.enum.mapping]]
    field = "slave_slave_io_running"
    dest = "slave_slave_io_running_int"
    default = 4
    [processors.enum.mapping.value_mappings]
      Yes = 0
      No = 1
      Preparing = 2
      Connecting = 3

Metric Version

When metric_version = 2, a variety of field type issues are corrected as well as naming inconsistencies. If you have existing data on the original version enabling this feature will cause a field type error when inserted into InfluxDB due to the change of types. For this reason, you should keep the metric_version unset until you are ready to migrate to the new format.

If preserving your old data is not required you may wish to drop conflicting measurements:

sql

DROP SERIES from mysql
DROP SERIES from mysql_variables
DROP SERIES from mysql_innodb

Otherwise, migration can be performed using the following steps:

Duplicate your mysql plugin configuration and add a name_suffix and metric_version = 2, this will result in collection using both the old and new style concurrently:

toml

[[inputs.mysql]]
  servers = ["tcp(127.0.0.1:3306)/"]

[[inputs.mysql]]
  name_suffix = "_v2"
  metric_version = 2

  servers = ["tcp(127.0.0.1:3306)/"]

Upgrade all affected Telegraf clients to version >=1.6.

New measurements will be created with the name_suffix, for example::
- mysql_v2
- mysql_variables_v2
Update charts, alerts, and other supporting code to the new format.
You can now remove the old mysql plugin configuration and remove old measurements.

If you wish to remove the name_suffix you may use Kapacitor to copy the historical data to the default name. Do this only after retiring the old measurement name.

Use the technique described above to write to multiple locations:

toml

[[inputs.mysql]]
  servers = ["tcp(127.0.0.1:3306)/"]
  metric_version = 2

[[inputs.mysql]]
  name_suffix = "_v2"
  metric_version = 2

  servers = ["tcp(127.0.0.1:3306)/"]

Create a TICKScript to copy the historical data:

sql

dbrp "telegraf"."autogen"

batch
    |query('''
        SELECT * FROM "telegraf"."autogen"."mysql_v2"
    ''')
        .period(5m)
        .every(5m)
        |influxDBOut()
                .database('telegraf')
                .retentionPolicy('autogen')
                .measurement('mysql')

Define a task for your script:

kapacitor define copy-measurement -tick copy-measurement.task

Run the task over the data you would like to migrate:

kapacitor replay-live batch -start 2018-03-30T20:00:00Z -stop 2018-04-01T12:00:00Z -rec-time -task copy-measurement

Verify copied data and repeat for other measurements.

Metrics

Global statuses - all numeric and boolean values of SHOW GLOBAL STATUSES
- wsrep_evs_repl_latency - a complex field containing multiple values is split into separate fields
  - wsrep_evs_repl_latency_min(float, seconds)
  - wsrep_evs_repl_latency_avg(float, seconds)
  - wsrep_evs_repl_latency_max(float, seconds)
  - wsrep_evs_repl_latency_stdev(float, seconds)
  - wsrep_evs_repl_latency_sample_size(float, number)
Global variables - all numeric and boolean values of SHOW GLOBAL VARIABLES
- wsrep_provider_options - a complex field containing multiple values is split into separate fields
  - gcache_size(int, bytes)
Slave status - metrics from SHOW SLAVE STATUS the metrics are gathered when the single-source replication is on. If the multi-source replication is set, then everything works differently, this metric does not work with multi-source replication, unless you set gather_all_slave_channels = true. For MariaDB, mariadb_dialect = true should be set to address the field names and commands differences. If enable gather_replica_status metrics gather from command SHOW REPLICA STATUS, for MariaDB will be SHOW ALL REPLICAS STATUS
- slave_[column name]
Binary logs - all metrics including size and count of all binary files. Requires to be turned on in configuration.
- binary_size_bytes(int, number)
- binary_files_count(int, number)
Process list - connection metrics from processlist for each user. It has the following tags
- connections(int, number)
User Statistics - connection metrics from user statistics for each user. It has the following fields
- access_denied
- binlog_bytes_written
- busy_time
- bytes_received
- bytes_sent
- commit_transactions
- concurrent_connections
- connected_time
- cpu_time
- denied_connections
- empty_queries
- hostlost_connections
- other_commands
- rollback_transactions
- rows_fetched
- rows_updated
- select_commands
- server
- table_rows_read
- total_connections
- total_ssl_connections
- update_commands
- user
Perf Table IO waits - total count and time of I/O waits event for each table and process. It has following fields:
- table_io_waits_total_fetch(float, number)
- table_io_waits_total_insert(float, number)
- table_io_waits_total_update(float, number)
- table_io_waits_total_delete(float, number)
- table_io_waits_seconds_total_fetch(float, milliseconds)
- table_io_waits_seconds_total_insert(float, milliseconds)
- table_io_waits_seconds_total_update(float, milliseconds)
- table_io_waits_seconds_total_delete(float, milliseconds)
Perf index IO waits - total count and time of I/O waits event for each index and process. It has following fields:
- index_io_waits_total_fetch(float, number)
- index_io_waits_seconds_total_fetch(float, milliseconds)
- index_io_waits_total_insert(float, number)
- index_io_waits_total_update(float, number)
- index_io_waits_total_delete(float, number)
- index_io_waits_seconds_total_insert(float, milliseconds)
- index_io_waits_seconds_total_update(float, milliseconds)
- index_io_waits_seconds_total_delete(float, milliseconds)
Info schema autoincrement statuses - autoincrement fields and max values for them. It has following fields:
- auto_increment_column(int, number)
- auto_increment_column_max(int, number)
InnoDB metrics - all metrics of information_schema.INNODB_METRICS with a status "enabled". For MariaDB, mariadb_dialect = true to use ENABLED=1.
Perf table lock waits - gathers total number and time for SQL and external lock waits events for each table and operation. It has following fields. The unit of fields varies by the tags.
- read_normal(float, number/milliseconds)
- read_with_shared_locks(float, number/milliseconds)
- read_high_priority(float, number/milliseconds)
- read_no_insert(float, number/milliseconds)
- write_normal(float, number/milliseconds)
- write_allow_write(float, number/milliseconds)
- write_concurrent_insert(float, number/milliseconds)
- write_low_priority(float, number/milliseconds)
- read(float, number/milliseconds)
- write(float, number/milliseconds)
Perf events waits - gathers total time and number of event waits
- events_waits_total(float, number)
- events_waits_seconds_total(float, milliseconds)
Perf file events statuses - gathers file events statuses
- file_events_total(float,number)
- file_events_seconds_total(float, milliseconds)
- file_events_bytes_total(float, bytes)
Perf events statements - gathers attributes of each event
- events_statements_total(float, number)
- events_statements_seconds_total(float, millieconds)
- events_statements_errors_total(float, number)
- events_statements_warnings_total(float, number)
- events_statements_rows_affected_total(float, number)
- events_statements_rows_sent_total(float, number)
- events_statements_rows_examined_total(float, number)
- events_statements_tmp_tables_total(float, number)
- events_statements_tmp_disk_tables_total(float, number)
- events_statements_sort_merge_passes_totals(float, number)
- events_statements_sort_rows_total(float, number)
- events_statements_no_index_used_total(float, number)
Table schema - gathers statistics per schema. It has following measurements
- info_schema_table_rows(float, number)
- info_schema_table_size_data_length(float, number)
- info_schema_table_size_index_length(float, number)
- info_schema_table_size_data_free(float, number)
- info_schema_table_version(float, number)

MySQL Input Plugin

MySQL Input Plugin

Global configuration options

Configuration

String Data

Metric Version

Metrics

Tags

Example Output