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

Best Practices with Amazon Aurora

This topic includes information on best practices and options for using or migrating data to an Amazon Aurora DB cluster.

Determining Which DB Instance You Are Connected To

You can determine which DB instance in an Aurora DB cluster that a connection is connected to by checking the innodb_read_only global variable, as shown in the following example.

Copy
SHOW GLOBAL VARIABLES LIKE 'innodb_read_only';

The innodb_read_only variable will be set to ON if you are connected to an Aurora Replica and OFF if you are connected to the primary instance.

This can be helpful if you want to add logic to your application code to balance the workload or to ensure that a write operation is using the correct connection.

Using T2 Instances

Amazon Aurora instances that use the db.t2.small or db.t2.medium DB instance classes are best suited for applications that do not support a high workload for an extended amount of time. T2 instances are designed to provide moderate baseline performance and the capability to burst to significantly higher performance as required by your workload. They are intended for workloads that don't use the full CPU often or consistently, but occasionally need to burst. The db.t2.small and db.t2.medium DB instance classes are best used for development and test servers, or other non-production servers. For more details on T2 instances, see T2 Instances.

When you use a T2 instance for the primary instance or Aurora Replicas in a DB cluster, we recommend the following:

  • If you use a T2 instance as a DB instance class in your DB cluster, then we recommend that all instances in your DB cluster use the same DB instance class. For example, if you use db.t2.medium for your primary instance, then we recommend that you use db.t2.medium for your Aurora Replicas as well.

  • Monitor your CPU Credit Balance (CPUCreditBalance) to ensure that it is at a sustainable level. That is, CPU credits are being accumulated at the same rate as they are being used.

    When you have exhausted the CPU credits for an instance, you will see an immediate drop in the available CPU and an increase in the read and write latency for the instance. This results in a severe decrease in the overall performance of the instance.

    If your CPU credit balance is not at a sustainable level, then we recommend that you modify your DB instance to use a one of the supported R3 DB instance classes (scale compute).

    For more information on monitoring metrics, see Monitoring an Amazon Aurora DB Cluster.

  • Monitor the replica lag (AuroraReplicaLag) between the primary instance and the Aurora Replicas in your DB cluster.

    If an Aurora Replica runs out of CPU credits before the primary instance, the lag behind the primary instance will result in the Aurora Replica frequently restarting. This is common for scenarios where an application maintains a heavy load of read operations distributed between the Aurora Replicas in a DB cluster, at the same time that the primary instance maintains a minimal load of write operations.

    If you see a sustained increase in replica lag, make sure that your CPU credit balance for the Aurora Replicas in your DB cluster is not being exhausted.

    If your CPU credit balance is not at a sustainable level, then we recommend that you modify your DB instance to use a one of the supported R3 DB instance classes (scale compute).

  • Keep the number of inserts per transaction below 1 million for DB clusters that have binary logging enabled.

    If the DB cluster parameter group for your DB cluster has the binlog_format parameter set to a value other than OFF, then your DB cluster may experience out-of-memory conditions if the DB cluster receives large transactions that contain over 1 million rows to insert. You can monitor the freeable memory (FreeableMemory) metric to determine if your DB cluster is running out of available memory, and then check the write operations (VolumeWriteIOPS) metric to see if your primary instance is receiving a heavy load of writer operations. If this is the case, then we recommend that you update your application to limit the amount of inserts in a transaction to less than 1 million or modify your instance to use one of the supported R3 DB instance classes (scale compute).

Invoking an AWS Lambda Function

We recommend that you wrap calls to the mysql.lambda_async procedure in a stored procedure that can be called from different sources such as triggers or client code. This can help to avoid impedance mismatch issues and make it easier for your database programmers to invoke Lambda functions.

For more information on invoking Lambda functions from Amazon Aurora, see Invoking a Lambda Function from an Amazon Aurora DB Cluster.

The following example shows a Lambda function, a stored procedure that invokes the Lambda function, and a call to run the stored procedure and invoke the Lambda function.

Lambda Function

Copy
import boto3 ses = boto3.client('ses') def SES_send_email(event, context): return ses.send_email( Source=event['email_from'], Destination={ 'ToAddresses': [ event['email_to'], ] }, Message={ 'Subject': { 'Data': event['email_subject'] }, 'Body': { 'Text': { 'Data': event['email_body'] } } } )

Stored Procedure

Copy
DROP PROCEDURE IF EXISTS SES_send_email; DELIMITER ;; CREATE PROCEDURE SES_send_email(IN email_from VARCHAR(255), IN email_to VARCHAR(255), IN subject VARCHAR(255), IN body TEXT) LANGUAGE SQL BEGIN CALL mysql.lambda_async( 'arn:aws:lambda:us-west-2:123456789012:function:SES_send_email', CONCAT('{"email_to" : "', email_to, '", "email_from" : "', email_from, '", "email_subject" : "', subject, '", "email_body" : "', body, '"}') ); END ;; DELIMITER ;

Call the Stored Procedure to Invoke the Lambda Function

Copy
mysql> call SES_send_email('example_to@amazon.com', 'example_from@amazon.com', 'Email subject', 'Email content');

Working with Multi-Threaded Replication Slaves in Amazon Aurora

By default, Aurora uses single-threaded replication when an Aurora DB cluster is used as a replication slave. While Amazon Aurora does not prohibit multi-threaded replication, Aurora has inherited several issues regarding multi-threaded replication from MySQL. We recommend that you do not use multi-threaded replication in production. If you do use multi-threaded replication, we recommend that you test any use thoroughly.

For more information about using replication in Amazon Aurora, see Replication with Amazon Aurora.

Using Amazon Aurora to Scale Reads for Your MySQL Database

You can use Amazon Aurora with your MySQL DB instance to take advantage of the read scaling capabilities of Amazon Aurora and expand the read workload for your MySQL DB instance. To use Aurora to read scale your MySQL DB instance, create an Amazon Aurora DB cluster and make it a replication slave of your MySQL DB instance. This applies to an Amazon RDS MySQL DB instance, or a MySQL database running external to Amazon RDS.

For information on creating an Amazon Aurora DB cluster, see Creating an Amazon Aurora DB Cluster.

When you set up replication between your MySQL DB instance and your Amazon Aurora DB cluster, be sure to follow these guidelines:

  • Use the Amazon Aurora DB cluster endpoint address when you reference your Amazon Aurora DB cluster. If a failover occurs, then the Aurora Replica that is promoted to the primary instance for the Aurora DB cluster will continue to use the DB cluster endpoint address.

  • Maintain the binlogs on your master instance until you have verified that they have been applied to the Aurora replica. This maintenance ensures that you can restore your master instance in the event of a failure.

Important

When using self-managed replication, you're responsible for monitoring and resolving any replication issues that may occur. For more information, see Diagnosing and Resolving Lag Between Read Replicas.

Note

The permissions required to start replication on an Amazon Aurora DB cluster are restricted and not available to your Amazon RDS master user. Because of this, you must use the Amazon RDS mysql.rds_set_external_master and mysql.rds_start_replication commands to set up replication between your Amazon Aurora DB cluster and your MySQL DB instance.

Start Replication Between an External Master Instance and a MySQL DB Instance on Amazon RDS

  1. Make the source MySQL DB instance read-only:

    Copy
    mysql> FLUSH TABLES WITH READ LOCK; mysql> SET GLOBAL read_only = ON;
  2. Run the SHOW MASTER STATUS command on the source MySQL DB instance to determine the binlog location. You will receive output similar to the following example:

    Copy
    File Position ------------------------------------ mysql-bin-changelog.000031 107 ------------------------------------
  3. Copy the database from the external MySQL DB instance to the Amazon Aurora DB cluster using mysqldump. For very large databases, you might want to use the procedure in Importing Data to an Amazon RDS MySQL or MariaDB DB Instance with Reduced Downtime.

    For Linux, OS X, or Unix:

    Copy
    mysqldump \ --databases <database_name> \ --single-transaction \ --compress \ --order-by-primary \ –u <local_user> \ -p <local_password> | mysql \ --host aurora_cluster_endpoint_address \ –-port 3306 \ –u <RDS_user_name> \ –p <RDS_password>

    For Windows:

    Copy
    mysqldump ^ --databases <database_name> ^ --single-transaction ^ --compress ^ --order-by-primary ^ –u <local_user> ^ -p <local_password> | mysql ^ --host aurora_cluster_endpoint_address ^ –-port 3306 ^ –u <RDS_user_name> ^ –p <RDS_password>

    Note

    Make sure there is not a space between the -p option and the entered password.

    Use the ‐‐host, ‐‐user (-u), ‐‐port and –p options in the mysql command to specify the hostname, user name, port, and password to connect to your Aurora DB cluster. The host name is the DNS name from the Amazon Aurora DB cluster endpoint, for example, mydbcluster.cluster-123456789012.us-east-1.rds.amazonaws.com. You can find the endpoint value in the cluster details in the Amazon RDS Management Console.

  4. Make the source MySQL DB instance writeable again:

    Copy
    mysql> SET GLOBAL read_only = OFF; mysql> UNLOCK TABLES;

    For more information on making backups for use with replication, see Backing Up a Master or Slave by Making It Read Only in the MySQL documentation.

  5. In the Amazon RDS Management Console, add the IP address of the server that hosts the source MySQL database to the VPC security group for the Amazon Aurora DB cluster. For more information on modifying a VPC security group, see Security Groups for Your VPC in the Amazon Virtual Private Cloud User Guide.

    You might also need to configure your local network to permit connections from the IP address of your Amazon Aurora DB cluster, so that it can communicate with your source MySQL instance. To find the IP address of the Amazon Aurora DB cluster, use the host command:

    Copy
    host <aurora_endpoint_address>

    The host name is the DNS name from the Amazon Aurora DB cluster endpoint.

  6. Using the client of your choice, connect to the external MySQL instance and create a MySQL user that will be used for replication. This account is used solely for replication and must be restricted to your domain to improve security. The following is an example:

    Copy
    CREATE USER 'repl_user'@'mydomain.com' IDENTIFIED BY '<password>';
  7. For the external MySQL instance, grant REPLICATION CLIENT and REPLICATION SLAVE privileges to your replication user. For example, to grant the REPLICATION CLIENT and REPLICATION SLAVE privileges on all databases for the 'repl_user' user for your domain, issue the following command:

    Copy
    GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'repl_user'@'mydomain.com' IDENTIFIED BY '<password>';
  8. Take a manual snapshot of the Aurora DB cluster that will be the replication slave prior to setting up replication. If you need to reestablish replication with the DB cluster as a replication slave, you can restore the Aurora DB cluster from this snapshot instead of having to import the data from your MySQL DB instance into a new Aurora DB cluster.

  9. Make the Amazon Aurora DB cluster the replica. Connect to the Amazon Aurora DB cluster as the master user and identify the source MySQL database as the replication master by using the mysql.rds_set_external_master command. Use the master log file name and master log position that you determined in Step 2. The following is an example:

    Copy
    CALL mysql.rds_set_external_master ('mymasterserver.mydomain.com', 3306, 'repl_user', '<password>', 'mysql-bin-changelog.000031', 107, 0);
  10. On the Amazon Aurora DB cluster, issue the mysql.rds_start_replication command to start replication:

    Copy
    CALL mysql.rds_start_replication;

After you have established replication between your source MySQL DB instance and your Amazon Aurora DB cluster, you can add Aurora Replicas to your Amazon Aurora DB cluster. You can then connect to the Aurora Replicas to read scale your data. For information on creating an Aurora Replica, see Creating an Aurora Replica Using the Console.

Using Amazon Aurora for Disaster Recovery with Your MySQL Databases

You can use Amazon Aurora with your MySQL DB instance to create an off-site backup for disaster recovery. To use Aurora for disaster recovery of your MySQL DB instance, create an Amazon Aurora DB cluster and make it a replication slave of your MySQL DB instance. This applies to an Amazon RDS MySQL DB instance, or a MySQL database running external to Amazon RDS.

Important

When you set up replication between a MySQL DB instance and an Amazon Aurora DB cluster, the replication is not managed by Amazon RDS. You must monitor the replication to ensure that it remains healthy and repair it if necessary.

For instructions on how to create an Amazon Aurora DB cluster and make it a replication slave of your MySQL DB instance, follow the procedure in Using Amazon Aurora to Scale Reads for Your MySQL Database.

Migrating from MySQL to Amazon Aurora with Reduced Downtime

When importing data from a MySQL database that supports a live application to an Amazon Aurora DB cluster, you can use the procedure documented in Importing Data to an Amazon RDS MySQL or MariaDB DB Instance with Reduced Downtime to reduce the amount of time that service to your data is interrupted in order to migrate your data to Aurora. The procedure can especially help if you are working with a very large database, because you can reduce the cost of the import by minimizing the amount of data that is passed across the network to AWS.

The procedure lists steps to transfer a copy of your database data to an Amazon EC2 instance and import the data into a new Amazon RDS MySQL DB instance. Because Amazon Aurora is compatible with MySQL, you can instead use an Amazon Aurora DB cluster for the target Amazon RDS MySQL DB instance.