Lesson 22 intermediate

What’s New in MySQL 8.4 and 9.7 LTS

Everything that changed across the two current LTS releases in one place: MySQL 8.4's removals and new defaults (mysql_native_password off, MASTER/SLAVE syntax gone, modern InnoDB defaults, mysqlpump removed, stricter foreign keys), then MySQL 9.7's additions (the VECTOR type, JSON duality views, the hypergraph optimizer in Community, cluster and replication improvements, the four-part version number) and the move to calendar versioning.

The earlier lessons mention version differences where they matter. This last lesson collects them, so you can review a server you are about to upgrade, or catch up if you last used MySQL 8.0. It ends with the features that are brand new in 9.7.

The release timeline

ReleaseDateTrack
8.0April 2018Old model; end of life April 2026
8.1 to 8.32023 to early 2024Innovation
8.4April 2024LTS
9.0 to 9.6July 2024 to early 2026Innovation
9.7April 2026LTS, the last sequentially numbered release
26.7 onwardJuly 2026 onwardCalendar versioning, YY.M

Features usually appear first in an Innovation release and reach most production servers with the next LTS. Everything below is therefore new to anyone moving from 8.0 to 8.4, or from 8.4 to 9.7.

MySQL 8.4 LTS: what changed from 8.0

Authentication

  • mysql_native_password is disabled by default. Accounts that use it cannot log in unless the server starts with mysql-native-password=ON. Move them to caching_sha2_password (lesson 18).
  • default_authentication_plugin is gone; authentication_policy replaces it.
  • The FIDO authentication plugins were replaced by authentication_webauthn (the server side is Enterprise only).

Replication terminology

The MASTER and SLAVE statements, options and variables are removed and now raise syntax errors:

RemovedUse instead
CHANGE MASTER TO MASTER_HOST = ...CHANGE REPLICATION SOURCE TO SOURCE_HOST = ...
START SLAVE / STOP SLAVESTART REPLICA / STOP REPLICA
SHOW SLAVE STATUSSHOW REPLICA STATUS
SHOW MASTER STATUSSHOW BINARY LOG STATUS
RESET MASTERRESET BINARY LOGS AND GTIDS
SHOW SLAVE HOSTSSHOW REPLICAS
RESET SLAVERESET REPLICA

The privilege REPLICATION SLAVE keeps its name.

New InnoDB defaults

8.4 tuned InnoDB for SSDs and multi-core machines. The most visible changes:

Variable8.0 default8.4 default
innodb_io_capacity20010000
innodb_io_capacity_maxat least 20002 × innodb_io_capacity
innodb_adaptive_hash_indexONOFF
innodb_change_bufferingallnone
innodb_flush_method (Linux)fsyncO_DIRECT where supported
innodb_log_buffer_size16 MB64 MB
innodb_buffer_pool_instances8 (1 below a 1 GB pool)1 up to a 1 GB pool, then scaled to the pool size and CPUs
innodb_doublewrite_pages4128
innodb_numa_interleaveOFFON
innodb_use_fdatasyncOFFON
innodb_purge_threads41 with 16 or fewer CPUs, else 4
innodb_read_io_threads4CPUs / 2, at least 4
temptable_max_ram1 GB3% of memory, between 1 and 4 GB
temptable_max_mmap1 GB0 (off)

If your my.cnf sets any of these explicitly because an old tuning guide said so, test whether you still need the override.

Removed tools and options

  • mysqlpump is removed; use mysqldump or MySQL Shell’s dump utilities (lesson 19).
  • mysql_upgrade and mysql_ssl_rsa_setup are removed; the server upgrades itself and creates its own certificates.
  • expire_logs_days is removed; use binlog_expire_logs_seconds.
  • --ssl, --admin-ssl, have_ssl and have_openssl are removed; use --tls-version and the ssl_* options.
  • --skip-host-cache is replaced by host_cache_size = 0, and FLUSH HOSTS by TRUNCATE TABLE performance_schema.host_cache.
  • keyring_file and similar plugins are replaced by keyring components such as component_keyring_file.

Stricter rules and deprecations

  • Foreign keys must reference a primary key or unique key: restrict_fk_on_non_standard_key is ON by default (lesson 12).
  • Wildcards (%, _) in database names in GRANT statements are deprecated.

New in 8.4

  • Automatic histogram updates: ANALYZE TABLE notes UPDATE HISTOGRAM ON pinned AUTO UPDATE; keeps a histogram fresh whenever InnoDB recalculates the table’s statistics.
  • Tagged GTIDs: SET gtid_next = 'AUTOMATIC:batch_import'; labels transactions so tools can tell groups of changes apart (requires the TRANSACTION_GTID_TAG privilege).
  • Clone across patch versions: the clone plugin only needs the same major and minor version, so an 8.4.2 donor can provision an 8.4.6 replica.
  • SOURCE_RETRY_COUNT now defaults to 10 reconnection attempts.
  • Faster INTERSECT and EXCEPT with hash tables (hash_set_operations optimizer switch, from 8.2).
  • New mysql client options to switch off risky client commands: --system-command (8.4.3+) for the \! shell escape and --commands (8.4.6+).

MySQL 9.7 LTS: what is new since 8.4

The VECTOR type

AI applications store embeddings, lists of numbers that describe the meaning of a text or image. MySQL 9.x has a VECTOR(n) type holding up to 16,383 single-precision floats (2048 when you write just VECTOR):

CREATE TABLE note_embeddings (
  note_id   INT UNSIGNED PRIMARY KEY,
  embedding VECTOR(3) NOT NULL,
  FOREIGN KEY (note_id) REFERENCES notes (id) ON DELETE CASCADE
);

INSERT INTO note_embeddings VALUES (4, STRING_TO_VECTOR('[1.05, -17.8, 32]'));

SELECT note_id, VECTOR_DIM(embedding) AS dims, VECTOR_TO_STRING(embedding) AS v
FROM note_embeddings;
+---------+------+----------------------------------------+
| note_id | dims | v                                      |
+---------+------+----------------------------------------+
|       4 |    3 | [1.05000e+00,-1.78000e+01,3.20000e+01] |
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

Real embeddings have hundreds or thousands of dimensions; three keep the example readable.

FunctionPurpose
STRING_TO_VECTOR(s) / TO_VECTOR(s)Text like '[1, 2, 3]' to a vector
VECTOR_TO_STRING(v) / FROM_VECTOR(v)Vector back to text
VECTOR_DIM(v)Number of dimensions
DISTANCE(a, b, 'COSINE') / VECTOR_DISTANCESimilarity search; only in MySQL HeatWave on OCI and MySQL AI, not in Community or Commercial downloads

Vectors cannot be keys or be compared except for equality, and most functions reject them. On a Community server you can store and transport embeddings, then compute similarity in the application or a vector-capable service.

DROP TABLE note_embeddings;

JSON duality views

A JSON duality view shows relational tables as JSON documents, and lets applications read (and in 9.7 Community, also insert, update and delete) whole documents while the data stays normalised in tables:

CREATE JSON DUALITY VIEW user_notes_dv AS
SELECT JSON_DUALITY_OBJECT(
  '_id': id,
  'name': name,
  'notes': (
    SELECT JSON_ARRAYAGG(JSON_DUALITY_OBJECT('id': id, 'title': title))
    FROM notes
    WHERE notes.user_id = users.id
  )
)
FROM users;

SELECT JSON_PRETTY(data) FROM user_notes_dv WHERE data->'$._id' = 2;

The view has one column, data, and this query returns Ben as one document with his two notes nested inside. Rules to know: the root object needs an _id key mapped to the root table’s primary key, every table involved needs a primary key, and the top-level query cannot use WHERE, JOIN, GROUP BY, ORDER BY or LIMIT. JSON, VECTOR, GEOMETRY and generated columns cannot be projected. Annotations inside JSON_DUALITY_OBJECT control which operations a document allows on each table.

The hypergraph optimizer in Community

MySQL’s newer hypergraph optimizer considers many more join orders and plan shapes than the classic optimizer, which helps complex analytical queries with many joins. In 9.7 it is available in the Community edition. It is switched by an optimizer_switch flag, off by default:

SET SESSION optimizer_switch = 'hypergraph_optimizer=on';
EXPLAIN FORMAT=TREE SELECT ...;

Try it per session on your heaviest reports and compare plans and timings before enabling it more widely.

Replication and cluster operations

9.7 brings operational improvements for Group Replication and InnoDB Cluster:

  • Monitoring of flow control, so you can see when the cluster throttles writers.
  • More detailed applier statistics for multi-threaded replicas, including lag and throughput per worker.
  • Automatic eviction and rejoin of unhealthy members.
  • Failover that prefers the most up-to-date eligible member when electing a new primary.

The variables replica_parallel_type and group_replication_allow_local_lower_version_join were removed; delete them from configuration files before upgrading.

Security

  • mysql_native_password, already disabled in 8.4, is removed from the server in the 9.x series.
  • New authentication options were added, including OpenID Connect based login; check the edition notes for the one you need.
  • Dynamic data masking, applying masking policies to query results without application changes, arrived for the Enterprise edition.
  • For LDAP SASL authentication, SCRAM-SHA-1 is deprecated and SCRAM-SHA-256 is the default.

Enterprise-only additions

JavaScript stored programs let you write procedures and functions in JavaScript (CREATE FUNCTION ... LANGUAGE JAVASCRIPT AS $$ ... $$). They run in the Multilingual Engine (MLE) component, which is part of MySQL Enterprise Edition.

A four-part version number

Starting with 9.7.4 (and 8.4.13), each release also has an update number. VERSION() and mysqld --version still return three parts, while SELECT VERSION_WITH_UPDATE();, the version_with_update variable and mysqld --version-with-update return MAJOR.MINOR.PATCH.UPDATE. Upgrade history is recorded in the mysql_upgrade_history file in the data directory.

After 9.7: calendar versions

MySQL 9.7 is the last release numbered the old way. From July 2026, versions are named by year and month: 26.7 is the July 2026 release, 27.7 the July 2027 one. An LTS series keeps its YY.M name throughout its support life, receiving patch updates. The upgrade rule stays the same: go from one LTS series to the next, without skipping one.

Your upgrade checklist

  1. Read the release notes of every version between yours and the target.
  2. Run util.checkForServerUpgrade() from MySQL Shell (lesson 21) and fix every error.
  3. Move all accounts off mysql_native_password.
  4. Replace MASTER/SLAVE statements in scripts, monitoring and tools.
  5. Remove deleted options from my.cnf; review InnoDB overrides against the new defaults.
  6. Replace mysqlpump in backup jobs.
  7. Update client libraries and connectors.
  8. Upgrade a replica first, test, then switch over.
  9. Pin the repository to the LTS track you want.

Exercise

Which statement shows the binary log position on a MySQL 8.4 source?

  1. SHOW BINARY LOG STATUS
  2. SHOW MASTER STATUS
  3. SHOW REPLICA STATUS
  4. SHOW SOURCE STATUS
Show answer

Answer: A. SHOW MASTER STATUS was removed in 8.4. SHOW BINARY LOG STATUS returns the same file, position and GTID information.

Exercise

A Community edition server runs MySQL 9.7. Which vector operation is NOT available on it?

  1. Storing embeddings in a VECTOR(768) column
  2. Similarity search with DISTANCE(a, b, ‘COSINE’)
  3. Converting text with STRING_TO_VECTOR
  4. Reading the dimension count with VECTOR_DIM
Show answer

Answer: B. The VECTOR type and its conversion functions exist on 9.x servers, but DISTANCE (VECTOR_DISTANCE) is only provided in MySQL HeatWave on OCI and MySQL AI.

Exercise

What is the version after MySQL 9.7 called?

  1. 9.8
  2. 10.0
  3. 9.7.5 LTS-2
  4. A calendar version such as 26.7, the July 2026 release
Show answer

Answer: D. MySQL 9.7 is the last sequentially numbered release. Later releases use the YY.M format.

Key takeaways

  • 8.4 disabled mysql_native_password, removed MASTER/SLAVE syntax and mysqlpump, and modernised InnoDB defaults.
  • 8.4 rejects foreign keys to non-unique keys by default and adds automatic histogram updates and tagged GTIDs.
  • 9.7 adds the VECTOR type, writable JSON duality views and the hypergraph optimizer in Community, plus cluster improvements.
  • Vector similarity (DISTANCE) and JavaScript stored programs are not in the Community edition.
  • After 9.7, versions are calendar based (26.7, 27.7); upgrade LTS to LTS.

FAQ

Should new projects start on 8.4 or 9.7?

Start on 9.7 LTS unless your hosting provider, framework or tooling only supports 8.4. Both are supported for years, and 9.7’s support window runs longer.

Do I have to use the new features?

No. The VECTOR type, duality views and the hypergraph optimizer are optional. Everything in lessons 1 to 21 works the same on 8.4 and 9.7, which is why both lines are safe choices.

Where can I read the full list of changes?

The MySQL Reference Manual has a “What Is New” section for each release line, and the release notes list every change per patch release. Check them for the exact versions you run.

What should I learn next?

Practise on a copy of real data: write the queries your application needs, read their plans, break and restore a test server, and set up a replica. Keep the MySQL Cheatsheet at hand, and if you also run PostgreSQL, compare approaches with the Postgres Tutorial.

↑ ↓ navigate Enter open Esc close