Body
MySQL HA Lab MySQL HA Lab · MySQL · Prometheus

MySQL HA Lab, Lesson 4: Monitoring InnoDB Cluster with Prometheus and Grafana

Monitor a MySQL InnoDB Cluster with mysqld-exporter, Prometheus and provisioned Grafana dashboards, and watch a failover live: members, primary, replication backlog and routers.

MySQL HA Lab Lesson 4: Monitoring InnoDB Cluster with Prometheus and Grafana
📘
This post is part of the MySQL HA Lab series: a hands-on MySQL 8.4 InnoDB Cluster on Docker. Code for this lesson: branch 02-monitoring on GitHub. Previous: Lesson 3: Failover, quorum and recovery.

Watch the cluster live: which node is primary, members leaving and returning, writes moving to a new primary, replication backlog, and (from Lesson 5) router health.

flowchart LR
    subgraph Cluster
        m1[("mysql1")]
        m2[("mysql2")]
        m3[("mysql3")]
    end
    e1["exporter-mysql1"] --> m1
    e2["exporter-mysql2"] --> m2
    e3["exporter-mysql3"] --> m3
    init["monitoring-init<br/>creates the exporter user"] -. "once, via router :6446" .-> Cluster
    prom["Prometheus :9090<br/>scrape every 5 s"] --> e1 & e2 & e3
    prom --> hap["HAProxy :8404/metrics"]
    graf["Grafana :3000<br/>provisioned dashboards"] --> prom
The monitoring pipeline: one exporter per node, Prometheus scrapes every 5 seconds, Grafana queries Prometheus.
ComponentRole
monitoring-initOne-shot container that creates the read-only exporter user
mysqld-exporter ×3One per node. Reads that node's status and Group Replication tables and serves them as Prometheus metrics on port 9104.
PrometheusScrapes (pulls) all metrics every 5s and stores them as time series
GrafanaDashboards on top of Prometheus

Each exporter connects directly to its node, not through the router. Through the router you'd only ever see the primary.


Files in this lesson

docker-compose.yml                         # x-exporter, monitoring-init, exporters, prometheus, grafana
monitoring/init-exporter-user.sh           # creates the exporter user
monitoring/prometheus.yml                  # what Prometheus scrapes
monitoring/grafana/provisioning/
├── datasources/prometheus.yml             # Grafana → Prometheus connection
└── dashboards/dashboards.yml              # where Grafana loads dashboards from
monitoring/grafana/dashboards/
└── 1-overview.json … 6-connection-pools.json   # the dashboards (see "Dashboards" below)

Compose profile

All monitoring services have profiles: [monitoring]. They only start when the profile is enabled:

docker compose --profile monitoring up -d      # cluster + monitoring
docker compose up -d                           # cluster only

Commands without --profile monitoring ignore these services (e.g. docker compose down leaves them running). See Operations.


monitoring-init: the exporter user

  monitoring-init:
    image: mysql:8.4
    container_name: monitoring-init
    profiles: [monitoring]
    restart: on-failure
    depends_on: [router-lb]
    entrypoint: ['bash', '/monitoring/init-exporter-user.sh']
    environment:
      MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD}
      EXPORTER_USER: ${EXPORTER_USER}
      EXPORTER_PASSWORD: ${EXPORTER_PASSWORD}
    volumes:
      - ./monitoring:/monitoring:ro
    networks: [cluster]
KeyMeaning
restart: on-failureIf the script exits non-zero (e.g. an SQL error), Docker runs it again. A successful run stays exited.
depends_on: [router-lb]Starts after the load balancer container starts. Readiness is handled by the script's wait loop. (On branch 02-monitoring: [router].)
entrypointRuns the script instead of a MySQL server
environmentRoot password to log in, exporter name and password to create

monitoring/init-exporter-user.sh

until timeout 10 mysql --connect-timeout=5 -h mysql-router -P 6446 -uroot -p"$MYSQL_ROOT_PASSWORD" -e "SELECT 1" >/dev/null 2>&1; do
  echo "Waiting for router..."; sleep 3
done
mysql -h mysql-router -P 6446 -uroot -p"$MYSQL_ROOT_PASSWORD" <<SQL
CREATE USER IF NOT EXISTS '${EXPORTER_USER}'@'%'
  IDENTIFIED WITH caching_sha2_password BY '${EXPORTER_PASSWORD}'
  WITH MAX_USER_CONNECTIONS 5;
ALTER USER '${EXPORTER_USER}'@'%' IDENTIFIED WITH caching_sha2_password BY '${EXPORTER_PASSWORD}';
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO '${EXPORTER_USER}'@'%';
SQL
PartWhy
timeout 10 ... --connect-timeout=5Each attempt ends after at most 10s. Without it, an attempt made while the cluster was still starting can hang forever on a dead connection: the loop never retries and the exporters never start, even after the cluster is healthy.
Wait loop on mysql-router:6446Waits until there is a primary reachable through the router. Users must be created on the primary; Group Replication copies them to all nodes. The loop hides errors, see stuck on "Waiting for router...".
CREATE USER IF NOT EXISTSSafe to rerun
caching_sha2_passwordMySQL 8.4's default authentication plugin
MAX_USER_CONNECTIONS 5Caps the exporter's connections. v0.20 opens up to 2 per scrape.
ALTER USERResyncs the password if .env changed
PROCESSRead the process list and InnoDB status
REPLICATION CLIENTRead replication status
SELECT ON *.*Read performance_schema (Group Replication tables) and information_schema

Exporters

x-exporter: &exporter
  image: prom/mysqld-exporter:v0.20.0
  profiles: [monitoring]
  restart: unless-stopped
  depends_on:
    monitoring-init:
      condition: service_completed_successfully
  environment:
    MYSQLD_EXPORTER_PASSWORD: ${EXPORTER_PASSWORD}
  networks: [cluster]

  exporter-mysql1:
    <<: *exporter
    container_name: exporter-mysql1
    command:
      - --mysqld.address=mysql1:3306
      - --mysqld.username=${EXPORTER_USER}
      - --collect.perf_schema.replication_group_members
      - --collect.perf_schema.replication_group_member_stats
KeyMeaning
condition: service_completed_successfullyExporters start only after monitoring-init exited with code 0, i.e. the user exists
MYSQLD_EXPORTER_PASSWORDThe exporter reads its password from this variable (note the D: MYSQLD_). A wrong name means no password, and mysql_up 0.
command: as a YAML listEach flag is a separate argument. The image runs the binary directly with no shell, so $VARIABLES inside the container are not expanded. ${EXPORTER_USER} works because Compose substitutes it before starting.
--mysqld.address=mysqlN:3306The node this exporter watches. Each exporter must point at a different node.
--mysqld.usernameLogin name; password comes from the variable above
--collect.perf_schema.replication_group_membersMembership: host, role (PRIMARY/SECONDARY), state (ONLINE/RECOVERING/...)
--collect.perf_schema.replication_group_member_statsQueues, certified/applied transactions, conflicts, rollbacks

Default collectors (global status, variables, InnoDB, ...) stay on. No ports are published; Prometheus reaches the exporters inside the Docker network on port 9104.


Prometheus

  prometheus:
    image: prom/prometheus:v3.13.4
    profiles: [monitoring]
    volumes:
      - ./monitoring/prometheus.yml:/etc/prometheus/prometheus.yml:ro
      - prometheus_data:/prometheus
    ports:
      - "9090:9090"
KeyMeaning
prometheus.yml mountScrape configuration
prometheus_data:/prometheusTime-series database on a named volume (default retention 15 days)
9090:9090Web UI on http://localhost:9090

monitoring/prometheus.yml

global:
  scrape_interval: 5s
  evaluation_interval: 5s

scrape_configs:
  - job_name: mysql
    static_configs:
      - targets: ["exporter-mysql1:9104"]
        labels: { node: mysql1 }
      - targets: ["exporter-mysql2:9104"]
        labels: { node: mysql2 }
      - targets: ["exporter-mysql3:9104"]
        labels: { node: mysql3 }

  - job_name: prometheus
    static_configs:
      - targets: ["localhost:9090"]

  - job_name: haproxy
    static_configs:
      - targets: ["mysql-router:8404"]
SettingMeaning
scrape_interval: 5sHow often every target is polled. Short, so a failover is visible within seconds (default is 1m).
evaluation_interval: 5sHow often recording/alerting rules run (none defined yet)
job_nameAdded to every series as label job
targetshost:port to scrape; path defaults to /metrics
labels: { node: ... }Extra label, so legends say mysql1 instead of exporter-mysql1:9104
job_name: haproxyHAProxy's built-in exporter (Lesson 5)

Grafana

  grafana:
    image: grafana/grafana-oss:13.0.2
    profiles: [monitoring]
    depends_on: [prometheus]
    environment:
      GF_SECURITY_ADMIN_USER: admin
      GF_SECURITY_ADMIN_PASSWORD: ${GRAFANA_ADMIN_PASSWORD:-admin}
      GF_DASHBOARDS_DEFAULT_HOME_DASHBOARD_PATH: /var/lib/grafana/dashboards/1-overview.json
    volumes:
      - ./monitoring/grafana/provisioning:/etc/grafana/provisioning:ro
      - ./monitoring/grafana/dashboards:/var/lib/grafana/dashboards:ro
      - grafana_data:/var/lib/grafana
    ports:
      - "3000:3000"
KeyMeaning
GF_* variablesAny Grafana setting can be set as GF_<SECTION>_<KEY>
GF_SECURITY_ADMIN_PASSWORDAdmin password; :-admin is the fallback if .env doesn't set it
GF_DASHBOARDS_DEFAULT_HOME_DASHBOARD_PATHOpens this dashboard on the home page
provisioning mountFiles Grafana reads at startup (datasource + dashboard provider)
dashboards mountThe dashboard JSON files
grafana_dataGrafana's own database (users, preferences)

provisioning/datasources/prometheus.yml

apiVersion: 1
datasources:
  - name: Prometheus
    uid: prometheus
    type: prometheus
    access: proxy
    url: http://prometheus:9090
    isDefault: true
    jsonData:
      timeInterval: 5s
SettingMeaning
uid: prometheusFixed ID; the dashboard JSON refers to the datasource by this uid
access: proxyGrafana's server queries Prometheus (inside the Docker network), not your browser
urlPrometheus by its service name
isDefaultUsed by new panels and Explore
timeInterval: 5sMatches the scrape interval, so $__rate_interval picks sensible windows

provisioning/dashboards/dashboards.yml

apiVersion: 1
providers:
  - name: mysql-ha
    folder: MySQL HA
    type: file
    allowUiUpdates: true
    options:
      path: /var/lib/grafana/dashboards
SettingMeaning
type: fileLoad dashboards from JSON files
folder: MySQL HAGrafana folder they appear in
allowUiUpdates: trueYou may edit in the UI; to keep changes, export the JSON back to the file
options.pathDirectory inside the container, the mounted monitoring/grafana/dashboards/

Don't confuse the two folders: provisioning/dashboards/ holds the provider config; dashboards/ holds the dashboard JSON. A JSON file in the provisioning folder results in an empty "MySQL HA" folder.


Start and verify

sequenceDiagram
    participant I as monitoring-init
    participant H as mysql-router:6446
    participant P as Primary
    participant E as Exporters
    participant Pr as Prometheus
    participant G as Grafana
    loop every 3 s, 10 s timeout per try
        I->>H: SELECT 1 as root
    end
    H->>P: route to the primary
    I->>P: CREATE / ALTER USER exporter, GRANT
    I-->>E: exit 0 (service_completed_successfully)
    E->>E: start, each connects directly to its node
    loop every 5 s
        Pr->>E: GET /metrics
        Pr->>H: GET :8404/metrics
    end
    G->>Pr: PromQL queries for the dashboards
Startup order of the monitoring stack: the exporters wait until monitoring-init has created their user on the primary.
chmod -R a+rX monitoring
docker compose --profile monitoring up -d
docker logs --tail 100 -f monitoring-init     # expect: "Exporter user 'exporter' is ready"
URLWhat
http://localhost:3000Grafana → Dashboards → MySQL HA → 1 · Overview (login admin / GRAFANA_ADMIN_PASSWORD)
http://localhost:9090Prometheus → Status → Target health: 3 exporters, HAProxy, Prometheus, all UP
# each exporter can log in (expect: mysql_up 1, three times)
for n in 1 2 3; do echo -n "exporter-mysql$n: "; docker exec exporter-mysql$n wget -qO- localhost:9104/metrics | grep '^mysql_up'; done

# each exporter watches the right node (expect server_id 1, 2, 3)
for n in 1 2 3; do docker exec exporter-mysql$n wget -qO- localhost:9104/metrics | grep '^mysql_global_variables_server_id'; done

# Group Replication metrics are available
docker exec exporter-mysql1 wget -qO- localhost:9104/metrics | grep -o '^mysql_perf_schema_[a-z_]*' | sort -u

If monitoring-init is stuck on "Waiting for router..."

monitoring-init retries every 3 seconds until it can log in as root through mysql-router:6446, and the exporters wait for it. The loop hides the real error, so it can wait forever without saying why. Most common after Docker or the Mac restarted.

1. Find the broken link in the chain HAProxy → routers → primary:

docker compose ps -a mysql1 mysql2 mysql3 cluster-init router1 router2 router-lb

2. See the real error by running the same login by hand:

mroot -e "SELECT @@hostname"        # root through mysql-router:6446, like monitoring-init
ErrorCauseFix
Unknown MySQL server host 'mysql-router'HAProxy isn't runningdocker logs --tail 100 mysql-router. Usually haproxy.cfg lacks its final newline or is unreadable (Lesson 5). Then docker compose up -d router-lb.
Lost connection ... reading initial communication packetHAProxy is up, but no router is healthy or there's no primaryhttp://localhost:8404/stats, then step 3
Access denied for user 'root'MYSQL_ROOT_PASSWORD changed after the volumes were createdUse the original password, or down -v to rebuild

3. Does the cluster have a primary?

members mysql1      # or mysql2 / mysql3 if mysql1 is down

All OFFLINE (or a single row) means the group never re-formed after a full restart. cluster-init only runs on docker compose up, not when Docker restarts containers itself:

docker compose up -d cluster-init
docker logs --tail 100 -f cluster-init       # → "The Cluster was successfully rebooted."
docker restart mysql-router1 mysql-router2

4. monitoring-init continues on its next retry. If it already gave up: docker compose --profile monitoring up -d.


The dashboard

SectionPanels
Cluster overviewNodes reachable · Members ONLINE · Current primary · Statements/sec
MySQL Routers (via HAProxy)Healthy routers R/W and R/O · Client sessions via load balancer · Health-check failures (15m) · Router up/down · Open sessions per router · New connections/sec per router · Connection errors & check failures (Lesson 5)
MySQL clusterNode up/down · Statements by node · CRUD by type · Client writes by node · InnoDB row operations · GR backlog · Proposed vs applied transactions · Conflicts & rollbacks · Connections · Network · Group members table

Hover the ⓘ next to a panel title for an explanation.

PanelMetric
Nodes reachablemysql_up
Current primary, Members ONLINE, Group membersmysql_perf_schema_replication_group_member_info
GR backlogmysql_perf_schema_transactions_in_queue, mysql_perf_schema_transactions_remote_in_applier_queue
Proposed vs appliedmysql_perf_schema_transactions_local_proposed_total, ..._remote_applied_total
Conflicts & rollbacksmysql_perf_schema_conflicts_detected_total, ..._transactions_local_rollback_total
Statements, CRUD, writes per nodemysql_global_status_commands_total
InnoDB rowsmysql_global_status_innodb_row_ops_total
Router panelshaproxy_*, see Lesson 5

To change the dashboard: edit it in Grafana, then Export → Export as JSON and overwrite the matching file in monitoring/grafana/dashboards/. UI-only changes are lost on down -v.

Useful PromQL

mysql_up                                                     # 1 = node reachable
max by (member_host) (mysql_perf_schema_replication_group_member_info{member_role="PRIMARY"})
sum by (node) (rate(mysql_global_status_commands_total{command=~"insert|update|delete"}[1m]))
max by (node) (mysql_perf_schema_transactions_remote_in_applier_queue)
  • ..._member_info returns 9 series, not 3: each exporter reports every member. max by (member_host) removes the duplicates.
  • Prometheus 3 may note "metric might not be a counter" for mysql_global_status_*: MySQL's counters don't end in _total. Harmless.

Watch a failover live

  1. Open the dashboard (refreshes every 5s).
  2. Start the transaction loop: txloop.
  3. In another terminal, kill the primary (Lesson 3).
TimeWhat you see
immediatelyNodes reachable → 2
~10sMembers ONLINE → 2, Current primary changes
~15–20sClient writes by node continues on the new primary; the loop prints OK with the new node=
after docker startMembers back to 3; GR backlog briefly rises while the node catches up

Dashboards

Six dashboards, one question each. All are in the folder MySQL HA and linked to each other (menu MySQL HA dashboards, top right).

#DashboardFileAnswersLesson
1Overview (home)1-overview.jsonIs everything OK right now? One row per layer: cluster, routing, applications4, 5, 8
2MySQL Cluster2-mysql-cluster.jsonMembers and roles, workload per node, replication backlog, conflicts4
3Routers & HAProxy3-routers-haproxy.jsonRouter health checks, sessions and errors per router5
4Applications4-applications.jsonSuccess %, failed vs retried operations, errors absorbed by retry, latency, stale reads8
5Failover & Outages5-failover-outages.jsonIncident view: node → primary → router → app timelines, outage start / end / duration7, 8
6Connection Pools6-connection-pools.jsonWriter / reader pools: active, idle, pending, timeouts8

Each dashboard starts with a short text explaining how to read it, and every panel has a description (hover the i icon). Two annotations appear on the graphs: purple = a new primary was elected, red = an app's writer path went down or up.


The MySQL HA Lab series

  1. Series introduction
  2. Lesson 1: Environment and SQL helpers
  3. Lesson 2: A self-bootstrapping InnoDB Cluster
  4. Lesson 3: Failover, quorum and recovery
  5. Lesson 4: Monitoring with Prometheus and Grafana (you are here)
  6. Lesson 5: Highly available routing with HAProxy
  7. Lesson 6: Stale reads and read/write splitting
  8. Lesson 7: Failover tuning, switchover and partitions
  9. Lesson 8: A Spring Boot app that survives failover
  10. Lesson 9: The same app in Go
  11. Bonus: Operations and troubleshooting

All code is on GitHub: mytechbytes/mysql-ha-lab. Questions or a different result in your lab? Leave a comment below.

← Previous MySQL HA Lab, Lesson 3: Failover, Quorum and Recovery in InnoDB Cluster Next → MySQL HA Lab, Lesson 5: Highly Available MySQL Router with HAProxy
Part 5 of 11 · Collection MySQL HA Lab Back to collection →
Membership

Go deeper.

Members get the full technical guides, production checklists and the source repos behind every article.

You may also like