MySQL 8.4 & 9.0: New Features and Migration Guide

MySQL has been the backbone of countless applications for decades. With the release of MySQL 8.4 and the upcoming MySQL 9.0, the database continues to evolve, offering new features that enhance performance, security, and developer experience. Whether you're running a small blog or a large-scale e-commerce platform, staying up-to-date with these versions is crucial for leveraging the latest capabilities and ensuring long-term support.

MySQL 8.4, released in April 2024, is a long-term support (LTS) release, meaning it will receive updates and security patches for several years. It introduces features like instant ADD COLUMN (no more table rebuilds for adding columns), improved JSON functions (JSON_VALUE, JSON_OVERLAPS), and new system variables for better resource management. On the other hand, MySQL 9.0, the first innovation release in the 9.x series, brings vector support for AI/ML workloads, enhanced security with TLS 1.3, and performance schema improvements for deeper insights.

Migrating to these versions requires careful planning. In this tutorial, we'll walk through the key features, provide practical examples, and outline a step-by-step migration strategy. By the end, you'll be equipped to upgrade your MySQL databases confidently, avoiding common pitfalls and taking advantage of the latest innovations.

1. Overview of MySQL 8.4 New Features

MySQL 8.4 is an LTS release that brings several significant enhancements. One of the most anticipated features is instant ADD COLUMN, which allows adding a column to a table without rebuilding the table, reducing downtime for large tables. However, this only works for columns that can be added without modifying existing rows (e.g., nullable columns or columns with a default that can be stored in the data dictionary). Another important feature is improved JSON support, including the JSON_VALUE() function for extracting values from JSON documents and JSON_OVERLAPS() for checking if two JSON documents share any key-value pairs. Additionally, MySQL 8.4 introduces new system variables like 'innodb_dedicated_server' for automatic buffer pool sizing, and 'max_execution_time' for limiting query execution time. Security enhancements include the removal of the deprecated mysql_native_password plugin (now caching_sha2_password is default) and improved TLS configuration.

-- Example: Instant ADD COLUMN (works for nullable columns)
ALTER TABLE employees ADD COLUMN middle_name VARCHAR(50) NULL, ALGORITHM=INSTANT;

-- Example: Non-instant ADD COLUMN (requires rebuild)
ALTER TABLE employees ADD COLUMN salary DECIMAL(10,2) NOT NULL DEFAULT 0, ALGORITHM=INSTANT;
-- This will fail with error 1845 because it requires backfill.

-- Use JSON_VALUE to extract data
SELECT JSON_VALUE('{"name": "John"}', '$.name') AS name;

2. MySQL 9.0: Innovation Release Highlights

MySQL 9.0 is the first innovation release in the 9.x series, focusing on new features for modern workloads. The standout feature is vector support, which enables storage and querying of vector embeddings for AI and machine learning applications. MySQL 9.0 introduces a new VECTOR data type and functions like VECTOR_DISTANCE() for similarity search. This allows developers to build recommendation systems, semantic search, and other AI-powered features directly in MySQL without external tools. Security improvements include TLS 1.3 support for encrypted connections, deprecation of older TLS versions, and enhanced audit logging. Performance schema gains new instruments for monitoring memory usage and thread activity. Additionally, MySQL 9.0 removes several deprecated features, including the mysql_native_password plugin (now fully removed), the GROUP BY ASC/DESC syntax, and the FLUSH QUERY CACHE statement (query cache was removed in 8.0).

-- Create a table with a VECTOR column (MySQL 9.0+)
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    embedding VECTOR(128)
);

-- Insert vector data
INSERT INTO products VALUES (1, 'Laptop', TO_VECTOR('[0.1, 0.2, ...]'));

-- Find similar products using cosine distance
SELECT id, name, VECTOR_DISTANCE(embedding, TO_VECTOR('[0.1, 0.2, ...]')) AS distance
FROM products
ORDER BY distance ASC
LIMIT 10;

3. Preparing for Migration: Pre-Upgrade Checklist

Before upgrading to MySQL 8.4 or 9.0, thorough preparation is essential to avoid downtime and compatibility issues. Start by reviewing the release notes for deprecated and removed features. For MySQL 8.4, note that the mysql_native_password plugin is deprecated; for 9.0, it's removed. Check your application's database drivers and ORM compatibility. Use the MySQL Upgrade Checker tool (mysqlcheck) to identify potential issues. This tool scans your database for incompatible syntax, data types, and configurations. Also, ensure your hardware meets the new system requirements, especially for memory and disk space. Backup your data and test the upgrade on a staging environment that mirrors production. Finally, plan for a maintenance window and have a rollback strategy.

-- Run mysqlcheck to check compatibility
mysqlcheck -u root -p --all-databases --check-upgrade

-- Check for deprecated features
SELECT * FROM information_schema.OPTIMIZER_TRACE WHERE QUERY LIKE '%deprecated%';

-- List users with old authentication plugin
SELECT user, host, plugin FROM mysql.user WHERE plugin NOT IN ('caching_sha2_password', 'sha256_password');

4. Step-by-Step Migration Process

The migration process involves several steps: backup, upgrade, and post-upgrade verification. First, take a full backup using mysqldump or physical backup tools like Percona XtraBackup. Then, upgrade the MySQL binaries on your server. For in-place upgrades, stop the MySQL service, replace the binaries, and restart. Alternatively, you can use a logical upgrade by dumping and restoring data. After restarting, run mysql_upgrade to update system tables and check for compatibility. This step is crucial for MySQL 8.4 and 9.0 as they introduce new system tables and data dictionary changes. Finally, verify the upgrade by checking the version, running test queries, and monitoring logs for errors. For replication setups, upgrade replicas first, then promote them to master if needed.

# Step 1: Backup
mysqldump -u root -p --all-databases --routines --events --triggers > full_backup.sql

# Step 2: Stop MySQL
sudo systemctl stop mysql

# Step 3: Replace binaries (example for Ubuntu)
sudo apt-get update
sudo apt-get install mysql-server-8.4

# Step 4: Start MySQL
sudo systemctl start mysql

# Step 5: Run mysql_upgrade
sudo mysql_upgrade -u root -p

# Step 6: Verify version
mysql -u root -p -e "SELECT VERSION();"

5. Post-Migration Verification and Optimization

After migration, it's critical to verify that everything works correctly. Start by checking the MySQL error log for any warnings or errors. Run ANALYZE TABLE on all tables to update optimizer statistics, as the new version may have different cost models. Review slow query logs to identify any performance regressions. Update your application's database drivers to versions that support the new MySQL version. For MySQL 9.0, ensure your ORM supports the VECTOR data type if you plan to use it. Additionally, consider enabling new features like instant DDL for future schema changes. Finally, monitor replication lag if you have replicas, as the binary log format may have changed.

-- Update optimizer statistics
ANALYZE TABLE employees;

-- Check for errors in the log
SHOW VARIABLES LIKE 'log_error';

-- Enable instant DDL for future alterations
SET GLOBAL innodb_alter_table_default_algorithm=INSTANT;

-- Verify replication status (if applicable)
SHOW SLAVE STATUS\G

6. Handling Deprecated and Removed Features

MySQL 8.4 deprecates several features that are removed in 9.0. The most impactful is the mysql_native_password authentication plugin. If your application uses this plugin, you need to migrate users to caching_sha2_password. Additionally, the GROUP BY ASC/DESC syntax is deprecated; use ORDER BY instead. The FLUSH QUERY CACHE statement is removed (query cache was removed in 8.0). Also, the old temporal format (DATETIME with 2-digit year) is deprecated. To handle these changes, update your application code and database schema. For authentication migration, you can alter user accounts to use the new plugin. For SQL syntax, search for deprecated patterns and replace them.

-- Migrate user to caching_sha2_password
ALTER USER 'old_user'@'%' IDENTIFIED WITH caching_sha2_password BY 'new_password';

-- Replace deprecated GROUP BY ASC/DESC
-- Old: SELECT col1, COUNT(*) FROM t GROUP BY col1 ASC;
-- New: SELECT col1, COUNT(*) FROM t GROUP BY col1 ORDER BY col1 ASC;

-- Check for old temporal format
SELECT * FROM information_schema.COLUMNS WHERE DATA_TYPE IN ('datetime', 'timestamp') AND CHARACTER_MAXIMUM_LENGTH = 2;

7. Performance Improvements and Tuning

Both MySQL 8.4 and 9.0 bring performance improvements. In 8.4, the InnoDB buffer pool can be automatically sized using innodb_dedicated_server. This is especially useful for dedicated database servers. The new max_execution_time variable allows you to limit query execution time globally or per session, preventing runaway queries. In 9.0, the Performance Schema has been enhanced with new instruments for monitoring memory usage and thread activity. Additionally, the optimizer has been improved for better query plans, especially for JSON queries and window functions. To take advantage of these improvements, review your configuration and adjust settings accordingly. For example, enable innodb_dedicated_server if you have a dedicated server, and set appropriate timeouts.

-- Enable dedicated server mode (MySQL 8.4+)
SET GLOBAL innodb_dedicated_server=ON;

-- Set global query execution timeout (in milliseconds)
SET GLOBAL max_execution_time=30000; -- 30 seconds

-- Check Performance Schema memory instruments (MySQL 9.0+)
SELECT * FROM performance_schema.memory_summary_global_by_event_name WHERE EVENT_NAME LIKE '%memory%' LIMIT 5;

8. Security Enhancements and Best Practices

Security is a major focus in both MySQL 8.4 and 9.0. MySQL 8.4 deprecates the mysql_native_password plugin, and 9.0 removes it entirely. All users should use caching_sha2_password, which provides stronger password hashing. TLS 1.3 is supported in 9.0, offering faster and more secure encrypted connections. Additionally, MySQL 8.4 introduces the 'require_secure_transport' system variable to enforce encrypted connections. Audit logging has been improved with new event fields. To enhance security, update your authentication plugins, enable TLS, and configure firewalls. Also, review user privileges and remove unnecessary grants. Use the 'SHOW GRANTS' command to audit permissions.

-- Enforce secure transport (MySQL 8.4+)
SET GLOBAL require_secure_transport=ON;

-- Check TLS version (MySQL 9.0+)
SHOW STATUS LIKE 'Tls_version';

-- Audit user privileges
SHOW GRANTS FOR 'app_user'@'%';

-- Remove deprecated mysql_native_password users
ALTER USER 'old_user'@'%' IDENTIFIED WITH caching_sha2_password BY 'strong_password';