MySQL Versions, Upgrades, and Deprecations

Concept

MySQL release tracks:

LTS (Long-Term Support) 8.4 LTS (2024) Long support with bug and security fixes; recommended for production

New features every few months; short support per release 8.0 (end of life announced for April 2026), 5.7 (end of life October 2023) Upgrade planning required (Always check Oracle's current lifecycle page for exact dates before planning.) Notable feature history:

InnoDB default engine Online DDL, InnoDB full-text, GTIDs JSON type, generated columns, sys schema, Group Replication (5.7.17)

Transactional data dictionary, atomic DDL, CTEs, window functions, roles, utf8mb4 default, caching_sha2_password default, invisible/descending/functional indexes, histograms, EXPLAIN ANALYZE, hash join, CHECK constraints (8.0.16), INSTANT ADD COLUMN, clone plugin, LATERAL, JSON_TABLE, INTERSECT/EXCEPT (8.0.31); query cache removed

Removal of deprecated replication MASTER/SLAVE syntax, mysql_native_password not loaded by default, mysqlpump removed, new default values for several InnoDB settings

New features such as JavaScript stored programs (Enterprise), VECTOR data type (9.0), further removals (e.g., mysql_native_password plugin removed in 9.0)

Why It Matters

Upgrades bring security fixes and features, but removed/changed behavior (authentication, reserved words, SQL modes, deprecated syntax) can break applications if not tested.

Syntax

MySQL  JS > util.checkForServerUpgrade('root@localhost:3306', {"targetVersion": "8.4.0"})

Example

Upgrade plan (8.0 → 8.4 LTS):

Deprecated / removed features to replace:

CHANGE MASTER TO, START SLAVE, SHOW SLAVE STATUS CHANGE REPLICATION SOURCE TO, START REPLICA, SHOW REPLICA STATUS --master-data (mysqldump)

VALUES(col) in ON DUPLICATE KEY UPDATE Row alias: ... AS new ON DUPLICATE KEY UPDATE c = new.c mysqldump, MySQL Shell dump utilities INT(11) display width, ZEROFILL INT, LPAD() for display SQL_CALC_FOUND_ROWS / FOUND_ROWS() Separate COUNT(*) query

User variables assigned inside expressions utf8 / utf8mb3

Application/proxy caching, better indexes SUPER privilege Dynamic privileges (SYSTEM_VARIABLES_ADMIN, CONNECTION_ADMIN,...)

1. Read release notes for every version in between (removed features, changed defaults).
2. Run util.checkForServerUpgrade() and fix reported issues:
   - reserved-word conflicts in identifiers
   - deprecated syntax (START SLAVE, CHANGE MASTER TO, VALUES() in ON DUPLICATE KEY UPDATE)
   - accounts using mysql_native_password
   - removed system variables in option files
3. Take a full backup and verify it restores.
4. Upgrade a staging copy; run application test suites and performance tests.
5. Upgrade replicas first, then switch over (or perform a rolling upgrade in InnoDB Cluster).
6. Upgrade the old primary; monitor error logs, slow logs, replication.
7. Keep a rollback plan (downgrade is generally not supported in-place — restore from backup or fail back to an old-version replica kept aside).

Expected Result

A tested, documented upgrade with no application errors and a rollback plan.

Common Mistakes

Upgrading production first.

Skipping the upgrade checker.

Assuming a downgrade is possible after upgrade.

Leaving applications on old connectors that cannot authenticate.

Best Practices

Stay on a supported LTS; schedule upgrades yearly.

Test with production-like data and traffic; upgrade replicas first.

Interview Relevance

Low-medium: "What changed in MySQL 8?", "What is an LTS vs Innovation release?", "How do you upgrade MySQL safely?".

Practice Questions

Run the upgrade checker against your server and summarize the findings.

Rewrite five deprecated statements from the table above into their modern forms.