Aurora on Amazon RDS
Amazon Aurora (Aurora) is a fully managed, MySQL-compatible, relational database engine that combines the speed and reliability of high-end commercial databases with the simplicity and cost-effectiveness of open-source databases. It delivers up to five times the performance of MySQL without requiring changes to most of your existing applications.
Amazon Aurora makes it simple and cost-effective to set up, operate, and scale your new and existing MySQL deployments, thus freeing you to focus on your business and applications. Amazon Relational Database Service (Amazon RDS) provides administration for Amazon Aurora by handling routine database tasks such as provisioning, patching, backup, recovery, failure detection, and repair. Amazon RDS also provides push-button migration tools to convert your existing Amazon RDS for MySQL applications to Amazon Aurora.
Amazon Aurora is a drop-in replacement for MySQL. The code, tools and applications you use today with your existing MySQL databases can be used with Amazon Aurora.
When you create an Amazon Aurora instance, you create a DB cluster. A DB cluster consists of one or more instances, and a cluster volume that manages the data for those instances. An Aurora cluster volume is a virtual database storage volume that spans multiple Availability Zones, with each Availability Zone having a copy of the cluster data. Two types of instances make up an Aurora DB cluster:
Primary instance – Supports read-write workloads, and performs all of the data modifications to the cluster volume. Each Aurora DB cluster has one primary instance.
Aurora Replica – Supports only read operations. Each DB cluster can have up to 15 Aurora Replicas in addition to the primary instance, which supports both read and write workloads. Multiple Aurora Replicas distribute the read workload, and by locating Aurora Replicas in separate Availability Zones you can also increase database availability.
The following diagram illustrates the relationship between the Amazon Aurora cluster volume and the primary and Aurora Replicas in the Aurora DB cluster.
The following table shows the regions where Amazon Aurora is currently available.
|US East (N. Virginia)||https://console.aws.amazon.com/rds/home?region=us-east-1|
|US East (Ohio)||https://console.aws.amazon.com/rds/home?region=us-east-2|
|US West (Oregon)||https://console.aws.amazon.com/rds/home?region=us-west-2|
|Asia Pacific (Tokyo)||https://console.aws.amazon.com/rds/home?region=ap-northeast-1|
|Asia Pacific (Mumbai)||https://console.aws.amazon.com/rds/home?region=ap-south-1|
|Asia Pacific (Sydney)||https://console.aws.amazon.com/rds/home?region=ap-southeast-2|
|Asia Pacific (Seoul)||https://console.aws.amazon.com/rds/home?region=ap-northeast-2|
You can connect to the instances in an Aurora DB cluster by using one of several endpoints for a DB cluster.
An endpoint is made up of a domain name and a port separated by a colon, for example:
Choose the best endpoint for your scenario from the following.
- Cluster endpoint
Each Aurora DB cluster has a cluster endpoint that you can connect to. The cluster endpoint connects you to the primary instance for the DB cluster. You can perform both read and write operations using the cluster endpoint.
The primary instance also has its own unique endpoint. The difference between these two endpoints is that the cluster endpoint will always point to the current primary instance. In other words, if the primary instance fails, then the cluster endpoint will point to the new primary instance. If the primary instance fails, you will need to re-establish your connection. For more information on failovers, see Fault Tolerance for an Aurora DB Cluster.
For high-availability scenarios, we recommend that you connect to the cluster endpoint. If you do, your application will reconnect to the new primary instance once failover is complete. During a failover, Aurora continues to serve requests to the cluster endpoint from the newly promoted primary instance as that replaces the failed instance. Aurora serves the cluster endpoint with minimal interruption of service.
As an example of how you use cluster endpoints, consider an Aurora DB cluster that has two Aurora Replicas in different Availability Zones from its primary instance. By connecting to the cluster endpoint, you can send both read and write traffic to the primary instance. You can also connect to the endpoint for each Aurora Replica and send queries directly to those DB instances. In the unlikely event that the primary instance or the Availability Zone that contains the primary instance fails, then RDS will promote one of the Aurora Replicas to be the new primary instance and update the Domain Name Service (DNS) record for the cluster endpoint to point to the new primary instance. Your application will continue to send read and write traffic to your Aurora DB cluster by using the cluster endpoint with minimal interruption in service.
- Reader endpoint
Each Aurora DB cluster has a reader endpoint that you can use to connect to one of the Aurora Replicas in your DB cluster. The reader endpoint for a DB cluster load-balances connections across the Aurora Replicas that are available in a DB cluster. As clients request new connections to the reader endpoint, Aurora distributes the connection requests among the Aurora Replicas in the DB cluster. This functionality can help balance your read workload across multiple Aurora Replicas in your DB cluster.
If a failover occurs and the Aurora Replica that you are connected to is promoted to the primary instance, your connection will be dropped. To continue sending your read workload to other Aurora Replicas in the cluster, reconnect to the reader endpoint. For more information on failovers, see Fault Tolerance for an Aurora DB Cluster.
You can use the reader endpoint to provide high availability for your read-only queries from your DB cluster. To take this approach, you place multiple Aurora Replicas in different Availability Zones and then connect to the reader endpoint for your read workload. In the unlikely event that an Availability Zone fails, then your application will continue to send read traffic to the Aurora Replicas in your Aurora DB cluster by using the reader endpoint with minimal interruption in service.
The reader endpoint only load-balances connections to the Aurora Replicas in a DB cluster. If you want to load-balance queries to distribute the read workload for a cluster, you will need to manage that in your application.
During a failover, the reader endpoint might direct connections to the primary instance for the DB cluster for a short time when an Aurora Replica is promoted to the primary instance.
If a client caches DNS information, you might see some discrepancy in the load-balancing of connections when that client connects to the same Aurora Replica using cached connection settings.
- Instance endpoint
The primary instance and Aurora Replicas in a DB cluster all have an instance endpoint, which is a unique endpoint that you can use to connect directly to the specific instance. Instance endpoints don't include cluster- in the identifier. For example, the cluster endpoint might be
mydbcluster.cluster-123456789012.us-east-1.rds.amazonaws.com:3306, and the instance endpoint for the primary instance might be
You can configure multiple clients to connect to different Aurora Replicas in an Aurora DB cluster to distribute the read workload for your application. For high-availability scenarios, you can also place Aurora Replicas in separate Availability Zones. Placing Aurora Replicas in separate Availability Zones ensures that your application can still read data from your Aurora DB cluster if an Availability Zone fails.
Before connecting to an instance using the instance endpoint, consider using the cluster endpoint or the reader endpoint for the DB cluster. If a failover occurs for the DB cluster, the cluster endpoint can be used to connect to the newly promoted primary instance and the reader endpoint can be used to connect to any available Aurora Replicas in the DB cluster.
Amazon Aurora Storage
Aurora data is stored in the cluster volume, which is a single, virtual volume that utilizes solid state disk (SSD) drives. A cluster volume consists of copies of the data across multiple Availability Zones in a single region. Because the data is automatically replicated across Availability Zones, your data is highly durable with less possibility of data loss. This replication also ensures that your database is more available during a failover because the data copies already exist in the other Availability Zones and continue to serve data requests to the instances in your DB cluster.
Aurora cluster volumes automatically grow as the amount of data in your database increases. An Aurora cluster volume can grow to a maximum size of 64 terabytes (TB). Table size is limited to the size of the cluster volume. That is, the maximum table size for a table in an Aurora DB cluster is 64 TB. Even though an Aurora cluster volume can grow to up to 64 TB, you are only charged for the space that you use in an Aurora cluster volume. For pricing information, see Amazon RDS for Aurora Pricing.
Amazon Aurora Replication
Aurora Replicas are independent endpoints in an Aurora DB cluster. They provide read-only access to the data in the DB cluster volume and enable you to scale the read workload for your data over multiple replicated instances to both improve the performance of data reads as well as increase the availability of the data in your Aurora DB cluster. Aurora Replicas are also failover targets and are quickly promoted if the primary instance for your Aurora DB cluster fails.
For more information on Aurora Replicas and other options for replicating data in an Aurora DB cluster, see Replication with Amazon Aurora.
Amazon Aurora Reliability
Aurora is designed to be reliable, durable, and fault tolerant. You can architect your Aurora DB cluster to improve availability by doing things such as adding Aurora Replicas and placing them in different Availability Zones, and also Aurora includes several automatic features that make it a reliable database solution.
Because Aurora maintains multiple copies of your data in three Availability Zones, the chance of losing data as a result of a disk failure is greatly minimized. Aurora automatically detects failures in the disk volumes that make up the cluster volume. When a segment of a disk volume fails, Aurora immediately repairs the segment. When Aurora repairs the disk segment, it uses the data in the other volumes that make up the cluster volume to ensure that the data in the repaired segment is current. As a result, Aurora avoids data loss and reduces the need to perform a point-in-time restore to recover from a disk failure.
"Survivable" Cache Warming
Aurora "warms" the buffer pool cache when a database starts up after it has been shut down or restarted after a failure. That is, Aurora preloads the buffer pool with the pages for known common queries that are stored in an in-memory page cache. This provides a performance gain by bypassing the need for the buffer pool to "warm up" from normal database use.
The Aurora page cache is managed in a separate process from the database, which allows the page cache to "survive" independently of the database. In the unlikely event of a database failure, the page cache remains in memory, which ensures that the buffer pool is warmed with the most current state when the database restarts.
Aurora is designed to recover from a crash almost instantaneously and continue to serve your application data. Aurora performs crash recovery asynchronously on parallel threads, so that your database is open and available immediately after a crash. For more information, see Fault Tolerance for an Aurora DB Cluster.
Aurora Performance Enhancements
Amazon Aurora includes performance enhancements to support the diverse needs of high-end commercial databases.
Fast insert accelerates parallel inserts sorted by primary key and applies specifically to
INSERT INTO ... SELECT ... statements. Fast insert caches the position of a cursor in an
index traversal while executing the statement. This avoids unnecessarily traversing the index again.
You can monitor the following metrics to determine the effectiveness of fast insert for your DB cluster:
aurora_fast_insert_cache_hits: A counter that is incremented when the cached cursor is successfully retrieved and verified.
aurora_fast_insert_cache_misses: A counter that is incremented when the cached cursor is no longer valid and Aurora performs a normal index traversal.
You can retrieve the current value of the fast insert metrics using the following command:
mysql> show global status like 'Aurora_fast_insert%';
You will get output similar to the following:
+---------------------------------+-----------+ | Variable_name | Value | +---------------------------------+-----------+ | Aurora_fast_insert_cache_hits | 3598300 | | Aurora_fast_insert_cache_misses | 436401336 | +---------------------------------+-----------+
Amazon Aurora Security
Security for Amazon Aurora is managed at three levels:
To control who can perform Amazon RDS management actions on Aurora DB clusters and DB instances, you use AWS Identity and Access Management (IAM). When you connect to AWS using IAM credentials, your IAM account must have IAM policies that grant the permissions required to perform Amazon RDS management operations. For more information, see Authentication and Access Control for Amazon RDS.
If you are using an IAM account to access the Amazon Aurora console, you must first log on to the AWS Management Console with your IAM account, and then go to the Aurora console at https://console.aws.amazon.com/rds.
Aurora DB clusters must be created in an Amazon Virtual Private Cloud (VPC). To control which devices and Amazon EC2 instances can open connections to the endpoint and port of the DB instance for Aurora DB clusters in a VPC, you use a VPC security group. These endpoint and port connections can be made using Secure Sockets Layer (SSL). In addition, firewall rules at your company can control whether devices running at your company can open connections to a DB instance. For more information on VPCs, see Virtual Private Clouds (VPCs) and Amazon RDS.
To authenticate login and permissions for an Amazon Aurora DB instance once a connection has been opened, you take the same approach as with a stand-alone instance of MySQL. Commands such as
SET PASSWORDwork just as they do in on-premises databases, as does directly modifying database schema tables. For information, see MySQL User Account Management in the MySQL documentation.
When you create an Amazon Aurora DB instance, the master user has the following default privileges:
create temporary tables
To provide management services for each DB cluster, the
user is created when the DB cluster is created. Attempting to drop, rename, change
the password, or change privileges for the
rdsadmin account will result
in an error.
For management of the DB cluster, the standard
kill_query commands have been restricted. Instead, use the Amazon RDS commands
rds_kill_query to terminate user sessions or queries on DB instances.
Securing Aurora Data with SSL
Amazon Aurora DB clusters support Secure Sockets Layer (SSL) connections from applications using the same process and public key as Amazon RDS MySQL DB instances.
Amazon RDS creates an SSL certificate and installs the certificate on the DB instance when Amazon RDS provisions the instance. These certificates are signed by a certificate authority. The SSL certificate includes the DB instance endpoint as the Common Name (CN) for the SSL certificate to guard against spoofing attacks. As a result, you cannot use the DB cluster endpoint to connect to the primary instance of the DB cluster using SSL. You must use the endpoint of the primary instance. The public key is stored at http://s3.amazonaws.com/rds-downloads/rds-combined-ca-bundle.pem.
To encrypt connections using the default mysql client,
launch the mysql client using the
--ssl-ca parameter to
reference the public key, for example:
mysql -h mycluster-primary.c9akciq32.rds-us-east-1.amazonaws.com
[full path]rds-combined-ca-bundle.pem --ssl-verify-server-cert
You can use the GRANT statement to require SSL connections for specific users
accounts. For example, you can use the following statement to require SSL
connections on the user account
GRANT USAGE ON *.* TO 'encrypted_user'@'%' REQUIRE SSL
For more information on SSL connections with MySQL, see the MySQL documentation.
Local Time Zone for Amazon Aurora DB Clusters
By default, the time zone for an Amazon Aurora DB cluster is Universal Time Coordinated (UTC). You can set the time zone for instances in your DB cluster to the local time zone for your application instead.
To set the local time zone for a DB cluster, set the
time_zone parameter in the cluster
parameter group for your DB cluster to one of the supported values listed later in this section. When you set
time_zone parameter for a DB cluster, all instances in the DB cluster change to use the new
local time zone. If other Aurora DB clusters are using the same cluster parameter group, then all instances
in those DB clusters will change to use the new local time zone also. For information on cluster-level
parameters, see DB Cluster and DB Instance
After you set the local time zone, all new connections to the database reflect the change. If you have any open connections to your database when you change the local time zone, you won't see the local time zone update until after you close the connection and open a new connection.
If you are replicating across regions, then the replication master DB cluster and the replica use different
parameter groups (parameter groups are unique to a region). To use the same local time zone for each instance,
you must set the
time_zone parameter in the parameter groups for both the replication master and
When you restore a DB cluster from a DB cluster snapshot, the local time zone is set to UTC. You can update the time zone to your local time zone after the restore is complete. If you restore a DB cluster to a point in time, then the local time zone for the restored DB cluster is the time zone setting from the parameter group of the restored DB cluster.
You can set your local time zone to one of the values listed in the following table. For some time zones, time values for certain date ranges can be reported incorrectly as noted in the table. For Australia time zones, the time zone abbreviation returned is an outdated value as noted in the table.
This time zone setting can return incorrect values from 28 Feb 1903 21:49:40 GMT to 28 Feb 1903 21:55:48 GMT.
This time zone setting can return incorrect values from 31 Dec 1939 21:30:00 GMT to 31 Dec 1959 21:15:15 GMT.
This time zone setting can return incorrect values from 23 Nov 1914 04:56:16 GMT to 23 Nov 1914 04:56:20 GMT.
This time zone setting can return incorrect values from 27 Oct 1918 05:00:00 GMT to 31 Oct 1918 05:00:00 GMT.
This time zone setting can return incorrect values from 31 Dec 1919 20:05:36 GMT to 31 Dec 1919 20:05:40 GMT.
This time zone setting can return incorrect values from 13 Mar 1947 20:53:08 GMT to 31 Dec 1949 20:53:08 GMT.
This time zone setting can return incorrect values from 30 Nov 1904 15:30:00 GMT to 07 Sep 1945 15:00:00 GMT.
This time zone setting can return incorrect values from 31 Dec 1927 15:54:08 GMT to 02 Jun 1940 16:00:00 GMT.
This time zone setting can return incorrect values from 30 Sep 1937 16:00:00 GMT to 29 Sep 1979 15:00:00 GMT.
This time zone setting can return incorrect values from 30 Sep 1937 15:00:00 GMT to 31 Dec 1937 15:00:00 GMT.
This time zone setting can return incorrect values from 24 May 1911 01:54:32 GMT to 01 Jan 1912 01:54:32 GMT.
The abbreviation for this time zone is returned as CST instead of ACDT/ACST.
The abbreviation for this time zone is returned as EST instead of AEDT/AEST.
The abbreviation for this time zone is returned as CST instead of ACDT/ACST.
The abbreviation for this time zone is returned as EST instead of AEDT/AEST.
The abbreviation for this time zone is returned as WST instead of AWDT/AWST.
The abbreviation for this time zone is returned as EST instead of AEDT/AEST.
This time zone setting can return incorrect values from 27 Oct 1918 08:00:00 GMT to 31 Oct 1918 08:00:00 GMT.
This time zone setting can return incorrect values from 30 Apr 1921 22:20:08 GMT to 30 Apr 1921 22:20:11 GMT.
This time zone setting can return incorrect values from 21 May 1933 11:30:00 GMT to 30 Sep 1945 11:30:00 GMT.
This time zone setting can return incorrect values from 01 Jan 1911 11:22:48 GMT to 01 Jan 1950 11:30:00 GMT.
Comparison of Amazon Aurora and Amazon RDS for MySQL
Although Aurora instances are compatible with MySQL client applications, Aurora has advantages over MySQL as well as limitations to the MySQL features that Aurora supports. This functionality can influence your decision about whether Amazon Aurora or MySQL on Amazon RDS are the best cloud database for your solution. The following table shows the differences between Amazon Aurora and Amazon RDS for MySQL.
|Feature||Amazon Aurora||Amazon RDS for MySQL|
|Read scaling||Supports up to 15 Aurora Replicas with minimal impact on the performance of write operations.||Supports up to 5 Read Replicas with some impact on the performance of write operations.|
|Failover target||Aurora Replicas are automatic failover targets with no data loss.||Read Replicas can be manually promoted to the master DB instance with potential data loss.|
|MySQL version||Supports only MySQL version 5.6.||Supports MySQL versions 5.1, 5.5, 5.6, and 5.7.|
|AWS Region||Aurora DB clusters can only be created in the US East (N. Virginia) (us-east-1), US West (Oregon) (us-west-2), EU (Ireland) (eu-west-1), Asia Pacific (Tokyo) (ap-northeast-1), Asia Pacific (Mumbai) (ap-south-1), Asia Pacific (Sydney) (ap-southeast-2), or Asia Pacific (Seoul) (ap-northeast-2) regions.||Available in all AWS Regions.|
|MySQL storage engine||
Supports only InnoDB. Tables from other storage engines are automatically converted to InnoDB.
For information on converting existing MySQL tables to InnoDB and importing into an Aurora cluster, see Migrating Data to an Amazon Aurora DB Cluster.
Because Amazon Aurora only supports the InnoDB engine, the
|Supports both MyISAM and InnoDB.|
|Read Replicas with a different storage engine than the master instance||MySQL (non-RDS) Read Replicas that replicate with an Aurora DB cluster can only use InnoDB.||Read Replicas can use both MyISAM and InnoDB.|
|Database engine parameters||Some parameters apply to the entire Aurora DB cluster and are managed by DB cluster parameter groups. Other parameters apply to each individual DB instance in a DB cluster and are managed by DB parameter groups. For more information, see DB Cluster and DB Instance Parameters.||Parameters apply to each individual DB instance or Read Replica and are managed by DB parameter groups.|