AWS Database Blog
Amazon Aurora MySQL 3 with MySQL 8.0 compatibility is now generally available
Amazon Aurora is a MySQL and PostgreSQL-compatible relational database built for the cloud. Aurora combines the performance and availability of traditional enterprise databases with the simplicity and cost-effectiveness of open-source databases. Amazon Aurora MySQL is compatible with MySQL 5.6 and MySQL 5.7, and is now also compatible with MySQL 8.0. Today, we made Aurora MySQL 3 with MySQL 8.0 compatibility generally available.
Aurora MySQL 3 introduces several new features, including support for common table expressions (CTEs), role-based authentication, replication enhancements, window functions, instant DDL, and more. At launch, Aurora MySQL 3 provides compatibility with the MySQL 8.0.23 Community Edition, and is available in all AWS Regions where Aurora is supported.
In this post, we explore various aspects of this new launch, including version alignment between Aurora MySQL 3 and MySQL Community Edition 8.0, key new features and functionality this version brings, breaking changes, and what you need to know to adopt this new version for your workloads.
Closer compatibility with MySQL Community Edition minor versions
Before we dive into the features and functionality, let’s touch upon how we’re thinking differently about maintaining version currency with new MySQL Community Edition releases. From a minor version compatibility perspective, all Aurora MySQL 1 engine versions are wire-compatible with MySQL 5.6.10a Community Edition, and all Aurora MySQL 2 engine versions are wire-compatible with MySQL 5.7.12 Community Edition. We maintain separate Aurora versions as we release new features and capabilities with Aurora MySQL compatibility, and bring features and other fixes from the MySQL Community Edition to Aurora minor versions.
Starting with Aurora MySQL 3, we will change this release strategy to follow MySQL Community Edition releases more closely. Each Aurora MySQL 3 release will be mapped to a corresponding MySQL 8.0 Community Edition release. For example, Aurora MySQL 3.01 maps to MySQL 8.0.23 and is wire-compatible with that specific Community Edition minor version. This means we will include all fixes and code changes that were added to that specific minor version.
Aurora MySQL 3 minor versions will not only continue to add Aurora-specific fixes and features, but will also maintain currency with MySQL 8.0 Community Edition to ensure all community bug fixes and features are continuously incorporated.
Supported upgrade path from Aurora MySQL 2 to Aurora MySQL 3
Before you consider upgrading your Aurora MySQL DB cluster to version 3, run an upgrade assessment and address all flagged items. You can run an assessment using the mysqlcheck --check-upgrade command. You should also test your application with the upgraded version of Aurora MySQL 3 before considering upgrading production Aurora database clusters. Refer to the behavioral changes section of this post for more details. For more information review the User Guide for Amazon Aurora.
At launch, you can upgrade to Aurora MySQL 3 from Aurora MySQL 2 using snapshot restore. To use this upgrade path, you have to take a snapshot of the current Aurora MySQL 2 cluster and during restore, opt for an Aurora MySQL 3 version. Aurora restores the Aurora MySQL 2 snapshot and performs the required upgrade procedures automatically. Your newly restored DB cluster is then deployed using Aurora MySQL 3 DB instances. If you’re using custom cluster or instance-level parameter groups, make sure you create new custom parameter groups for Aurora MySQL 3 and select them in the Database options under Additional configuration on the Aurora console on the Restore DB cluster configuration page. Alternatively, if you want to restore using the AWS Command Line Interface (AWS CLI) or SDKs, provide the new custom parameter groups to the appropriate API calls.
For production workloads, sometimes it’s desirable to keep the downtime to a minimum during the upgrade process due to the “always active” nature of the workload utilizing the database. To perform a minimal downtime upgrade, first enable binary log replication on the old Aurora MySQL 2 DB cluster, before taking a snapshot. Restore the snapshot as an Aurora MySQL 3 cluster, which now contains all the data from your Aurora MySQL 2 cluster. Configure binary log replication from the Aurora MySQL 2 source to the Aurora MySQL 3 target. After the replication catches up with all changes (make sure the replication is running, and the replica lag is zero), you can stop replication and start using the Aurora MySQL 3 cluster as your primary database.
Key new features introduced in Aurora MySQL 3
Aurora MySQL 3 makes many new MySQL 8.0 features available. You can find a complete list in the user guide. In this section, we highlight the features that many Aurora customers have been asking for:
Binary log improvements
Binary log replication has been supported by Aurora MySQL since launch and continues to be supported in Aurora MySQL 3. Aurora MySQL 3 brings several improvements compared to previous versions:
- Multi-threaded replication – Aurora MySQL 3 supports multi-threaded replication (MTR), which you can enable by setting a greater than zero value for replica_parallel_workers on the replica DB cluster. MTR can improve performance of binary log replication in some scenarios, such as workloads that generate a high rate of writes on the primary DB instance. All changes made at the primary DB instance have to be replayed at the replica to stay in sync. Compared to single threaded replication, multi-threaded replication can apply changes on replica in parallel and as a result may reduce replication lag.
- Replication filtering – Aurora MySQL 3 also introduces support for binary log replication filtering. You can use replication filters to specify which databases and tables are replicated. Replication filters can include databases and tables in replication or exclude them from replication. You can implement replication by using
replicate-do-*andreplicate-ignore-*filtering parameters. - Binary log transaction compression – You can enable and use binary log transaction compression with Aurora MySQL 3. When enabled, the zstd algorithm is used to compress transaction payloads. The compressed transactions are then written in the binary logs. They stay compressed in transit and on the binary log replica. This helps you save disk space on both the primary and the replica Aurora MySQL clusters. It also makes binary logs consume less network bandwidth and improves transit performance.
Instant DDL
Aurora MySQL 3 supports instant DDL. You can take advantage of the instant DDL feature by using the ALGORITHM=INSTANT clause of the ALTER TABLE statement. This feature significantly speeds up supported schema changes like adding a column, setting or dropping column default values, renaming a table, and more. These supported DDL operations not only run significantly faster than using the online (ALGORITHM=INPLACE) or offline (ALGORITHM=COPY) alternative DDL methods, but they also don’t exclusively lock the tables being changed. Because the instant DDL operations only modify metadata in the data dictionary, without metadata locks on the table, the operations are nearly instantaneous.
Common table expressions
CTEs give you a way to use a temporary result set with a name that can be called within the scope of a statement. CTEs allow you to write cleaner and more readable SQL queries when compared to writing multiple subqueries, or when writing recursive queries. CTEs can also provide performance improvements by avoiding having to write a subquery multiple times, and have it evaluated multiple times. CTEs are implemented via a WITH clause.
Window functions
You can improve analytical queries by using window functions. Window functions are aggregate-like operations, although they don’t group results into a single row – they aggregate over a rolling window and output results per row. Aurora MySQL 3 now supports window functions like RANK(), DENSE_RANK(), NTILE(), ROW_NUMBER(), and more.
Improved parallel query support
Parallel query is an Aurora MySQL specific feature that can improve performance of certain types of queries by distributing and parallelizing the query processing into the purpose-built distributed storage layer of Aurora. An example of a query that can benefit parallel query is a simple count operation that uses a WHERE clause, as shown in the following code:
In Aurora MySQL 3, we added support for partitioned tables, aggregations, the HAVING clause, and BLOBS to Aurora parallel query. This support lets you take advantage of the parallel query performance optimization for queries that use aggregation, partition tables, or tables with BLOB data types such as TEXT, JSON, CHAR, and VARCHAR longer than 769 bytes.
New index types
Aurora MySQL 3 now supports descending and invisible index types. Descending indexes improve the performance of queries that need to sort and retrieve from indexes in descending order. Invisible indexes allow you to test the performance impact of removing an index without actually dropping the index. You can use this feature to identify unused indexes, which lets you optimize your schema by removing them.
Roles
You can now create roles that act as a named collection of privileges. You can create and drop roles, and grant and revoke privileges to the roles. You can grant and revoke roles from user accounts. In previous versions on Aurora MySQL, you could only grant privileges to individual user accounts directly. This streamlines granting and revoking of privileges to groups of users and simplifies user management overall. For more information, refer to Using Roles.
Supported migration paths from Amazon RDS for MySQL 5.7/8.0 to Aurora MySQL 3
The following migration paths are available if you want to migrate from Amazon Relational Database Service (Amazon RDS) for MySQL to Aurora MySQL 3:
- Snapshot restore – You can restore an RDS from MySQL 8.0 (MySQL version 8.0.23 and below) snapshot to Aurora MySQL 3. The process is the same as described in the Aurora upgrade section.
- Read replica migration – You can create an Aurora MySQL 3 read replica DB cluster of an Amazon RDS for MySQL 8.0 primary DB instance (MySQL version 8.0.23 and below). This process automatically creates an Aurora MySQL 3 DB cluster, containing all the data from your RDS for MySQL DB instance, and configures binary log replication from the Amazon RDS for MySQL source to the Aurora MySQL target. After the read replica DB cluster is created, and it has caught up with all changes (make sure the replication is running, and the replica lag is zero), you can promote the Aurora read replica DB cluster to a standalone primary DB cluster to accept reads and writes.
These methods are only applicable to migrate from Amazon RDS for MySQL 8.0. You can’t use this method to migrate from Amazon RDS for MySQL 5.7 to Aurora MySQL 3.
Migrating from Amazon RDS for MySQL 5.7 to Aurora MySQL 3 is a two-step process. You need to upgrade your RDS for MySQL 5.7 DB instance first to Amazon RDS for MySQL 8.0, and then use one of the two methods we described for migration.
Supported migration paths from other self-managed MySQL-compatible databases
At this time, you can migrate from a self-managed MySQL-compatible database by performing a logical data export and importing it into a new Aurora MySQL 3 DB cluster. You can use binary log replication to perform a minimal downtime migration similarly as described in the earlier sections from other upgrade or migration sources. The binary log replication configuration is described in detail Amazon Aurora User Guide. You can perform this migration using native MySQL tools provided by the community or third parties, such as mysqldump, mydumper/myloader, or by using AWS Database Migration Service (AWS DMS).
Behavior changes from Aurora MySQL 1 and 2 to Aurora MySQL 3
Along with new capabilities, Aurora MySQL 3 also introduces some changes that may alter the way the database behaves in response to queries, and operates or manages internal structures. We strongly recommend running the pre-upgrade assessment before you consider upgrading the database to make sure you address the flagged items before the upgrade.
The following changes in behavior are notable. For a complete list of changes, see Changes in MySQL 8.0.
TempTable storage engine
MySQL 8.0 introduced the new TempTable storage engine as default, designed to be used by the database engine for creating temporary tables internally when dealing with complex queries. Aurora MySQL 3 also introduces the TempTable storage engine as default. In addition to allocating a certain amount of memory to store internal temporary tables, TempTable can spill larger datasets that don’t fit in memory to either memory mapped files, the InnoDB storage engine, or cascading to both. Because Aurora MySQL uses a shared storage architecture, where InnoDB tables are accessible only in read-only mode on reader DB instances of a cluster, TempTable can’t spill larger internal temporary datasets to InnoDB on reader DB instances. As a result, reader DB instances can only use memory mapped files for internal temporary tables, limited to the amount of temporary storage space allocated corresponding to the DB instance class. You can configure writer DB instances in Aurora MySQL 3 DB clusters to use either or both memory mapped files and InnoDB storage.
The TempTable engine is more efficient at storing VARCHAR, VARBINARY, and other BLOB data types. Compared to the MEMORY storage engine, which used fixed-length row format, the TempTable storage engine stores VARCHAR, VARBINARY, and other BLOB columns using an array of cells, which can be variable length.
Data dictionary changes
Aurora MySQL 3 changes the way the data dictionary (metadata) is maintained. In previous versions, the data dictionary was maintained using non-relational metadata files (such as FRM, TRN, TRG, and OPT files). The data dictionary is now stored in a transactional schema. This new implementation has several benefits, such as a crash safe data directory schema, simplicity due to centralized metadata, uniform caching of dictionary objects, atomic DDL, and more. For a full list of benefits of the new implementation, see MySQL Data Dictionary.
Error codes removed
Several error codes have been removed. If your application uses specific MySQL error codes for error handling, make sure you make appropriate changes to the application. For details, see Features Removed in MySQL 8.0.
ASC/DESC clause for GROUP BY
If your query uses ASC or DESC qualifiers in GROUP BY clauses, this will no longer work. ASC or DESC qualifiers for GROUP BY clauses are removed in this version of Aurora MySQL. If you need to sort the results of the query that uses GROUP BY with the ASC or DESC qualifier, you need to modify the query to use the ORDER BY clause with the ASC or DESC qualifier instead.
How to choose between the major Aurora MySQL versions
The choice of major Aurora MySQL version depends largely on your specific application compatibility requirements, bandwidth to test new major versions, and features you need or desire access to.
You should consider Aurora MySQL 3 (MySQL 8.0 compatible) if you prioritize future-proofing your workloads and access to the latest features. Similarly, running on version 3 makes sure that you have access to the latest hardware in the form of Aurora DB instance classes. You should also consider using the latest version of Aurora MySQL if you’re creating a new workload and don’t have any legacy code. The Aurora MySQL team will focus on developing and releasing new capabilities on the version 3 platform first.
A long-term supported (LTS) version of Aurora MySQL 3 is coming. In the meantime, you should consider Aurora MySQL 2 (MySQL 5.7 compatible) if you need an LTS version of the database engine, where you can be assured that software updates are limited to security and bug fixes, without the risk of changes in workload behavior due to new features. Additionally, if you depend on applications that aren’t yet compatible with MySQL 8.0 (legacy code, or using features that change behavior in new Aurora MySQL 3), Aurora 2 is also the right choice for you. As Aurora MySQL 3 matures, we will add an LTS minor version. This is in line with our version policy (one LTS version per major Aurora MySQL version).
We don’t recommend Aurora MySQL 1 for new workloads. If you’re currently using Aurora MySQL 1 (MySQL 5.6 compatible), you should consider upgrading your existing databases to a more current version of Aurora MySQL.
Conclusion
In this post, we discussed the general availability of the new Aurora MySQL 3 with MySQL 8.0 engine compatibility. We discussed new and exciting feature support, behavioral changes, upgrade and migration options, and how we’re thinking differently in terms of minor version currency.
We can’t wait for you to take advantage of all the features and benefits of the new platform. Start exploring now, by launching a new Aurora MySQL 3 database cluster with MySQL 8.0 compatibility.
About the Authors
Aditya Samant is a Solutions architect specializing in databases at AWS. His day job allows him to help AWS customers design scalable, secure and robust architectures utilizing the most cutting-edge cloud technologies. When not working Aditya enjoys retro PC tech, playing computer games and spending time with his family and friends.
Vlad Vlasceanu is a specialist solutions architect at Amazon Web Services. He works with our customers to provide guidance and technical assistance on database projects, helping them improving the value of their solutions when using AWS.