What's new in MySQL 9.0

MySQL 9, the latest major release, introduces new features and performance enhancements while phasing out older functionalities. In this review, we’ll explore what’s new, what’s going away, and guide those impacted by deprecated functions like mysql_native_password on how to handle the transition.

What’s New in MySQL 9

1. Enhanced JSON Support

MySQL 9 builds on its JSON capabilities with:

• Schema Validation: Ensure JSON data adheres to defined schemas, adding an extra layer of data integrity.
• JSON Indexing: Index JSON columns for faster querying, crucial for applications that frequently retrieve JSON data.
• Additional JSON Functions: New functions enable advanced JSON manipulation for more efficient data handling.

2. JavaScript Stored Procedures

One of the most exciting additions in MySQL 9 is the support for JavaScript stored programs, enabling developers to write procedures and functions in JavaScript. This feature, available in the Enterprise Edition, aligns MySQL with modern application environments where JavaScript is already widely used.

3. VECTOR Data Type for Machine Learning Applications

A new VECTOR data type has been introduced, allowing the storage of fixed-length arrays of single-precision floating-point numbers. This makes MySQL more machine-learning friendly, allowing for efficient storage and processing of vectorized data.

4. Security Enhancements

Security is a focal point in MySQL 9, with the following upgrades:

• Modern Encryption Options: Expanded support for algorithms like SHA-3, catering to enterprise security needs.
• OpenID Connect Integration: Direct support for OpenID Connect simplifies single sign-on integrations with OAuth2 systems.

5. Backup and Recovery Improvements

MySQL 9 optimizes backup operations with:

• Incremental Backups: Only changes since the last backup are saved, reducing backup times and storage costs.
• Point-in-Time Recovery: Enhanced to allow precise restoration to specific moments, invaluable for high-availability systems.

What’s Going Away: Deprecated Features

To streamline MySQL, some older features are being deprecated or removed. Here are key changes:

• Removal of mysql_native_password: This authentication plugin is no longer available in MySQL 9. Applications relying on it must migrate to more secure alternatives like caching_sha2_password.

Updating Deprecated Authentication Methods (mysql_native_password)

For users who relied on mysql_native_password for authentication, here’s how to transition to a supported method in MySQL 9. Follow these steps to ensure a smooth transition without compromising security:

Steps to Update Authentication Method

1. Disable Grant Tables Temporarily

   Edit your MySQL configuration file (/etc/my.cnf or /etc/mysql/my.cnf) and add the following line under the [mysqld] section to skip grant tables temporarily:

   [mysqld]
   skip-grant-tables
   

2. Restart MySQL Server

   Apply the changes by restarting the MySQL server:

   sudo systemctl restart mysql
   

3. Log in as Root

   Connect to the MySQL server as the root user:

   mysql -u root
   

4. Update Authentication Method

   Identify users relying on mysql_native_password and update their authentication method:

   SELECT User, Host, plugin FROM mysql.user WHERE plugin = 'mysql_native_password';
   

   For each user listed, run:

   ALTER USER 'username'@'host' IDENTIFIED WITH caching_sha2_password BY 'user_password';
   

   Replace 'username', 'host', and 'user_password' with the relevant details.

5. Re-enable Grant Tables

   Remove or comment out the skip-grant-tables line from your configuration file, then restart MySQL to apply the changes:

   sudo systemctl restart mysql
   

For further information, refer to this GitHub discussion on the mysql_native_password removal and how users have adapted.

What’s Next for MySQL

Oracle has committed to a release cadence of major updates every two years, with MySQL 9 laying a strong foundation for future improvements. Expect to see more frequent incremental updates and security patches, ensuring MySQL remains at the forefront of database technology.