Menu
Amazon Relational Database Service
User Guide (API Version 2014-10-31)

Aurora on Amazon RDS

Amazon 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 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.

Amazon Aurora Architecture

Aurora Endpoints

Each Aurora DB cluster has a cluster endpoint that you can connect to. An endpoint is made up of a domain name and a port separated by a colon, for example: mydbcluster.cluster-123456789012.us-east-1.rds.amazonaws.com:3306. The primary instance and Aurora Replicas in a DB cluster all have unique endpoints that do not include cluster- in the identifier. For example, mydbcluster-primary.123456789012.us-east-1.rds.amazonaws.com:3306.

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 a unique endpoint. The difference between the two endpoints is that the cluster endpoint will always point to the primary instance. If the primary instance fails, then the cluster endpoint will point to the new primary instance. For more information on failovers, see Fault Tolerance for an Aurora DB Cluster.

Each Aurora Replica in an Aurora DB cluster has a unique endpoint. 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, which ensures that your application will still be able to read data from your Aurora DB cluster in the event of an Availability Zone failure.

You must connect to the cluster endpoint for high-availability scenarios. This connection ensures that you will continue to have access to the Aurora DB cluster in the event of a failover. During a failover, Aurora continues to serve requests, with minimal interruption of service, to the cluster endpoint from any available instances as it replaces the failed instance.

Consider an Amazon 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 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.

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.

Storage Auto-Repair

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.

Crash Recovery

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

Fast insert accelerates parallel inserts sorted by primary key and applies specifically to LOAD DATA and 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 RDS for 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 CREATE USER, RENAME USER, GRANT, REVOKE, and SET PASSWORD work 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:

  • alter

  • alter routine

  • create

  • create routine

  • create temporary tables

  • create user

  • create view

  • delete

  • drop

  • event

  • execute

  • grant option

  • index

  • insert

  • lock tables

  • process

  • references

  • replication slave

  • select

  • show databases

  • show view

  • trigger

  • update

To provide management services for each DB cluster, the rdsadmin 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 and kill_query commands have been restricted. Instead, use the Amazon RDS commands rds_kill and 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 --ssl-ca=[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 encrypted_user:

GRANT USAGE ON *.* TO 'encrypted_user'@'%' REQUIRE SSL

Note

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 the 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 Parameters.

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 the replica.

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.

Time Zone

Notes

Africa/Harare

This time zone setting can return incorrect values from 28 Feb 1903 21:49:40 GMT to 28 Feb 1903 21:55:48 GMT.

Africa/Monrovia

Africa/Nairobi

This time zone setting can return incorrect values from 31 Dec 1939 21:30:00 GMT to 31 Dec 1959 21:15:15 GMT.

Africa/Windhoek

America/Bogota

This time zone setting can return incorrect values from 23 Nov 1914 04:56:16 GMT to 23 Nov 1914 04:56:20 GMT.

America/Caracas

America/Chihuahua

America/Cuiaba

America/Denver

America/Fortaleza

America/Guatemala

America/Halifax

This time zone setting can return incorrect values from 27 Oct 1918 05:00:00 GMT to 31 Oct 1918 05:00:00 GMT.

America/Manaus

America/Matamoros

America/Monterrey

America/Montevideo

America/Phoenix

America/Tijuana

Asia/Ashgabat

Asia/Baghdad

Asia/Baku

Asia/Bangkok

Asia/Beirut

Asia/Calcutta

Asia/Kabul

Asia/Karachi

Asia/Kathmandu

Asia/Muscat

This time zone setting can return incorrect values from 31 Dec 1919 20:05:36 GMT to 31 Dec 1919 20:05:40 GMT.

Asia/Riyadh

This time zone setting can return incorrect values from 13 Mar 1947 20:53:08 GMT to 31 Dec 1949 20:53:08 GMT.

Asia/Seoul

This time zone setting can return incorrect values from 30 Nov 1904 15:30:00 GMT to 07 Sep 1945 15:00:00 GMT.

Asia/Shanghai

This time zone setting can return incorrect values from 31 Dec 1927 15:54:08 GMT to 02 Jun 1940 16:00:00 GMT.

Asia/Singapore

Asia/Taipei

This time zone setting can return incorrect values from 30 Sep 1937 16:00:00 GMT to 29 Sep 1979 15:00:00 GMT.

Asia/Tehran

Asia/Tokyo

This time zone setting can return incorrect values from 30 Sep 1937 15:00:00 GMT to 31 Dec 1937 15:00:00 GMT.

Asia/Ulaanbaatar

Atlantic/Azores

This time zone setting can return incorrect values from 24 May 1911 01:54:32 GMT to 01 Jan 1912 01:54:32 GMT.

Australia/Adelaide

The abbreviation for this time zone is returned as CST instead of ACDT/ACST.

Australia/Brisbane

The abbreviation for this time zone is returned as EST instead of AEDT/AEST.

Australia/Darwin

The abbreviation for this time zone is returned as CST instead of ACDT/ACST.

Australia/Hobart

The abbreviation for this time zone is returned as EST instead of AEDT/AEST.

Australia/Perth

The abbreviation for this time zone is returned as WST instead of AWDT/AWST.

Australia/Sydney

The abbreviation for this time zone is returned as EST instead of AEDT/AEST.

Brazil/East

Canada/Saskatchewan

This time zone setting can return incorrect values from 27 Oct 1918 08:00:00 GMT to 31 Oct 1918 08:00:00 GMT.

Europe/Amsterdam

Europe/Athens

Europe/Dublin

Europe/Helsinki

This time zone setting can return incorrect values from 30 Apr 1921 22:20:08 GMT to 30 Apr 1921 22:20:11 GMT.

Europe/Paris

Europe/Prague

Europe/Sarajevo

Pacific/Auckland

Pacific/Guam

Pacific/Honolulu

This time zone setting can return incorrect values from 21 May 1933 11:30:00 GMT to 30 Sep 1945 11:30:00 GMT.

Pacific/Samoa

This time zone setting can return incorrect values from 01 Jan 1911 11:22:48 GMT to 01 Jan 1950 11:30:00 GMT.

US/Alaska

US/Central

US/Eastern

US/East-Indiana

US/Pacific

UTC

Comparison of Amazon RDS for 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 RDS for Aurora and Amazon RDS for MySQL.

FeatureAmazon RDS for AuroraAmazon RDS for MySQL
Read scalingSupports 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 targetAurora 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 versionSupports only MySQL version 5.6.Supports MySQL versions 5.1, 5.5, 5.6, and 5.7.
AWS RegionAurora 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 (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 NO_ENGINE_SUBSTITUTION option of the SQL_MODE database parameter is enabled. This disables the ability to create an in-memory table, unless that table is specified as TEMPORARY.

Supports both MyISAM and InnoDB.
Read Replicas with a different storage engine than the master instanceMySQL (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 parametersSome 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.