Skip to content
Tutorials Center
My learning

Topics

Lesson 64 of 75 ยท Security and Administration

Upgrading and Monitoring MySQL

Plan MySQL upgrades with LTS and innovation releases and the upgrade checker, then monitor key metrics with mysqld_exporter and Prometheus and plan capacity.

A database that is never upgraded becomes a security risk, and a database that is never watched fails at the worst moment. This lesson covers both halves of keeping MySQL healthy: choosing a release track and upgrading safely, and measuring the server so that you notice trouble before your users do.

The commands here are read-only, because a browser cannot run a server or a Prometheus instance. The live examples turn the ideas into small calculations: a buffer pool hit ratio and a disk-capacity forecast.

Release tracks

Since 8.1, MySQL ships two kinds of releases:

  • LTS (long-term support): 8.4 is the current LTS series at the time of writing. It gets bug and security fixes for years and keeps its behavior stable. New LTS series appear roughly every two years.
  • Innovation releases (9.0, 9.1, ...): shipped about every quarter, with new features. Each one is supported only until the next one arrives.

For production, use an LTS release unless you need a specific new feature. MySQL 8.0 reaches end of life in April 2026, so any server still on 8.0 needs an upgrade plan. Check Oracle's lifecycle page for exact dates, because they are the part of this lesson most likely to change.

Upgrade safely

Check Run the upgrade checker on the old server.
Back up A tested backup, plus the binary log.
Rehearse Upgrade a copy and run your test suite.
Roll out Replicas first, then the primary.

There is no supported way to downgrade after the data files are upgraded.

1 / 2

The upgrade checker

MySQL Shell includes a checker that reads the old server and reports what will break in the target version:

JavaScript
// in MySQL Shell
util.checkForServerUpgrade("[email protected]:3306", { targetVersion: "8.4.0" })

It looks for removed or changed system variables, deprecated syntax in stored programs and views, reserved words used as identifiers, old authentication plugins, invalid dates in tables and partitioning problems. Fix every error it prints, and read every warning. The checker is the cheapest insurance in this chapter.

Two changes that commonly surprise people on the way to 8.4: the mysql_native_password plugin is disabled by default (accounts should use caching_sha2_password), and the old master/slave replication statements are gone. Old clients and libraries that cannot talk caching_sha2_password are a classic source of "access denied" errors after an upgrade.

In-place or logical upgrade

There are two ways to move to a new version.

In-place upgrade. Stop the old server, start the new binaries on the same data directory, and let the server upgrade the data dictionary and system tables by itself. Since 8.0.16, mysqld does this at startup (the old mysql_upgrade tool is no longer needed). It is fast, because user data files are not rewritten. Supported paths go from one LTS to the next (for example 8.0.x to 8.4), starting from the latest patch release of the old series. You cannot skip over series, and you cannot go back.

Logical upgrade. Dump the data from the old server (see Backup and Restore) and load it into a freshly installed new server. It is slower and needs more disk space, but it is the safest, because the new server starts clean and the old one is untouched. It is also the right choice when changing character sets or moving between very different versions.

For replicated setups, upgrade the replicas first (a replica running a newer version can read the older source's binary log), check them, then fail over to an upgraded replica and upgrade the old source last. A managed service has a one-click upgrade, but the same rules apply: test on a clone first.

After any upgrade, read the server's error log, run SHOW VARIABLES LIKE 'version%', run your application tests, and watch the slow query log, because the optimizer can pick different plans in a new version. EXPLAIN and EXPLAIN ANALYZE shows how to compare them.

What to measure

Server counters live in SHOW GLOBAL STATUS, and settings in SHOW GLOBAL VARIABLES. These are the first ones to track:

Metric Meaning Worry when
Threads_connected / max_connections Open connections against the limit Above 80% of the limit
Threads_running Sessions actively executing Consistently above the CPU core count
Slow_queries Statements slower than long_query_time The rate jumps
Innodb_buffer_pool_reads vs Innodb_buffer_pool_read_requests Reads that went to disk vs total reads Disk reads are a growing share
Aborted_connects Failed connection attempts A sudden rise (bad password, attack)
Seconds_Behind_Source Replica lag Climbing over minutes
Disk free Space for data, logs and temp files Below 20%
Innodb_row_lock_waits, deadlocks Lock contention A steady rise

The buffer pool hit ratio is 1 - disk_reads / read_requests. Healthy OLTP servers are typically above 99%. This live example computes it from sampled counters:

SQL Stand-in: buffer pool hit ratio from counters

Press Run (or Ctrl + Enter) to see the result here.

Status counters only ever grow, so you must compare two samples, as the LAG window function does. The first two hours stay at 99.8% and 99.6%, and the third drops to about 90%, a clear sign that the working set no longer fits in memory. A monitoring tool does this subtraction for you, which is why you want one. See Window Functions for LAG.

Prometheus and mysqld_exporter

Prometheus collects numbers over time, and Grafana draws them. The bridge is mysqld_exporter, a small program that connects to MySQL and publishes its status as a web page on port 9104. First, give it a limited account:

SQL
CREATE USER 'exporter'@'localhost' IDENTIFIED BY 'a-long-random-password'
  WITH MAX_USER_CONNECTIONS 3;
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'localhost';

Put the credentials in a file that only the exporter can read (for example /etc/.mysqld_exporter.cnf, with a [client] section), start the exporter with --config.my-cnf pointing at it, and add a scrape job to Prometheus:

YAML
scrape_configs:
  - job_name: mysql
    static_configs:
      - targets: ["db1.internal:9104"]

Then write alert rules for conditions a person must act on:

YAML
groups:
  - name: mysql
    rules:
      - alert: MySQLDown
        expr: mysql_up == 0
        for: 1m
      - alert: MySQLTooManyConnections
        expr: mysql_global_status_threads_connected / mysql_global_variables_max_connections > 0.8
        for: 5m
      - alert: MySQLReplicaLagging
        expr: mysql_slave_status_seconds_behind_master > 60
        for: 5m

The for: clause stops a one-second spike from waking anyone. The exporter's replication metrics still carry old slave/master names in some versions, so check your exporter's output. Good alerts are few and actionable: if an alert fires and nobody needs to do anything, delete or change it. For queries rather than counters, see Finding Slow Queries.

Capacity planning

Databases fail from running out of disk, memory or connections, usually slowly and predictably. Record the size of each important table weekly (from information_schema.TABLES, see The Data Dictionary, information_schema and sys Schema) and extrapolate. The simplest forecast is linear growth, and it is already useful:

SQL Stand-in: when will the disk be full?

Press Run (or Ctrl + Enter) to see the result here.

The disk grows 11.5 GB a week, and 544 GB remain, so the disk fills in about 47 weeks. Treat that as the latest date to act: ordering disks, archiving old rows or moving to larger machines takes time. Do the same for memory (does the hot data fit in the buffer pool?), connection counts and write volume, and add the planned launches and campaigns that will change the trend.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which release series should most production systems use?

    LTS series keep a stable behavior and get fixes for years. Innovation releases are supported only until the next one.
  2. 2. What does util.checkForServerUpgrade() do?

    It is a read-only check that reports removed variables, deprecated syntax, invalid data and similar problems before you upgrade.
  3. 3. Why can't Innodb_buffer_pool_reads be used on its own as a rate?

    Counters are cumulative since startup. Monitoring tools compute the difference between samples to get a rate.
  4. 4. What is a good first rule for upgrading a replicated setup?

    A newer replica can read the older source's log, so replicas go first and you can test before touching the primary.

Summary

  • Use an LTS release (8.4) for production; innovation releases are short-lived, and 8.0 is reaching end of life.
  • Run util.checkForServerUpgrade() first, take a tested backup, and rehearse on a copy.
  • In-place upgrades are quick and go from LTS to LTS with no downgrade; logical upgrades are slower but safer.
  • Watch connections, running threads, slow queries, buffer pool misses, replication lag and free disk.
  • mysqld_exporter with Prometheus and Grafana gives history and alerts; keep alerts few and actionable.
  • Forecast growth from weekly measurements, and act well before the limit.

Next: MySQL from PHP with PDO.