MASTER STATUS and SLAVE STATUS are no longer used in MySQL 8.4.2 and higher

[haeiven]

What's wrong?

MASTER STATUS and SLAVE STATUS are no longer working in mysql 8.4.2 and higher, this prevent the mysql exporter slave status working correctly on mysql >= 8.4.2

Steps to reproduce

Install mysql >= 8.4.2 or higher & try to monitor with alloy mysqld exporter

System information

Ubuntu 24.04.1 LTS - Kernel 6.8.0-49-generic

Software version

Grafana Alloy 1.5.0-1

Configuration

// Metrics

prometheus.exporter.mysql "integrations_mysqld_exporter" {
  data_source_name = "monitoring:XXXXXXX(/var/run/mysqld/mysqld.sock)/?tls=false"
}

discovery.relabel "integrations_mysqld_exporter" {
  targets = prometheus.exporter.mysql.integrations_mysqld_exporter.targets

  rule {
    target_label = "job"
    replacement  = "integrations/mysql"
  }

  rule {
    target_label = "instance"
    replacement  = "mysql84-test"
  }
}

prometheus.scrape "integrations_mysqld_exporter" {
  targets         = discovery.relabel.integrations_mysqld_exporter.output
  forward_to      = [prometheus.relabel.integrations_mysqld_exporter.receiver]
  job_name        = "integrations/mysqld_exporter"
  scrape_interval = "30s"
}

prometheus.relabel "integrations_mysqld_exporter" {
  forward_to = [prometheus.remote_write.integrations.receiver]

  rule {
    source_labels = ["__name__"]
    regex         = "instance:mysql_heartbeat_lag_seconds|instance:mysql_slave_lag_seconds|mysql_global_status_aborted_clients|mysql_global_status_aborted_connects|mysql_global_status_buffer_pool_pages|mysql_global_status_bytes_received|mysql_global_status_bytes_sent|mysql_global_status_commands_total|mysql_global_status_created_tmp_disk_tables|mysql_global_status_created_tmp_files|mysql_global_status_created_tmp_tables|mysql_global_status_handlers_total|mysql_global_status_innodb_log_waits|mysql_global_status_innodb_mem_adaptive_hash|mysql_global_status_innodb_mem_dictionary|mysql_global_status_innodb_num_open_files|mysql_global_status_innodb_page_size|mysql_global_status_max_used_connections|mysql_global_status_open_files|mysql_global_status_open_table_definitions|mysql_global_status_open_tables|mysql_global_status_opened_files|mysql_global_status_opened_table_definitions|mysql_global_status_opened_tables|mysql_global_status_qcache_free_memory|mysql_global_status_qcache_hits|mysql_global_status_qcache_inserts|mysql_global_status_qcache_lowmem_prunes|mysql_global_status_qcache_not_cached|mysql_global_status_qcache_queries_in_cache|mysql_global_status_queries|mysql_global_status_questions|mysql_global_status_select_full_join|mysql_global_status_select_full_range_join|mysql_global_status_select_range|mysql_global_status_select_range_check|mysql_global_status_select_scan|mysql_global_status_slow_queries|mysql_global_status_sort_merge_passes|mysql_global_status_sort_range|mysql_global_status_sort_rows|mysql_global_status_sort_scan|mysql_global_status_table_locks_immediate|mysql_global_status_table_locks_waited|mysql_global_status_table_open_cache_hits|mysql_global_status_table_open_cache_misses|mysql_global_status_table_open_cache_overflows|mysql_global_status_threads_cached|mysql_global_status_threads_connected|mysql_global_status_threads_created|mysql_global_status_threads_running|mysql_global_status_uptime|mysql_global_status_wsrep_local_recv_queue|mysql_global_status_wsrep_local_state|mysql_global_status_wsrep_ready|mysql_global_variables_innodb_additional_mem_pool_size|mysql_global_variables_innodb_buffer_pool_size|mysql_global_variables_innodb_log_buffer_size|mysql_global_variables_key_buffer_size|mysql_global_variables_max_connections|mysql_global_variables_open_files_limit|mysql_global_variables_query_cache_size|mysql_global_variables_table_definition_cache|mysql_global_variables_table_open_cache|mysql_global_variables_thread_cache_size|mysql_global_variables_tokudb_cache_size|mysql_global_variables_wsrep_desync|mysql_heartbeat_now_timestamp_seconds|mysql_heartbeat_stored_timestamp_seconds|mysql_info_schema_processlist_threads|mysql_slave_status_seconds_behind_master|mysql_slave_status_slave_io_running|mysql_slave_status_slave_sql_running|mysql_slave_status_sql_delay|mysql_up"
    action        = "keep"
  }
}

Logs

2024-11-27T14:42:58.589096+01:00 mysql84-test alloy[9902]: ts=2024-11-27T13:42:58.588536023Z level=error msg="Error from scraper" component_path=/ component_id=prometheus.exporter.mysql.integrations_mysqld_exporter scraper=slave_status target=/var/run/mysqld/mysqld.sock err="Error 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SLAVE STATUS' at line 1"

[joshuapare]

Looks like this was fixed in the latest release of the underlying exporter and just needs an update: https://github.com/prometheus/mysqld_exporter/releases/tag/v0.16.0