Replication between Aurora and MySQL or between Aurora and another Aurora DB cluster (binary log replication) - Amazon Aurora
Setting up replication with MySQL or another Aurora DB clusterStopping replication between Aurora and MySQL or between Aurora and another Aurora DB clusterUsing Amazon Aurora to scale reads for your MySQL databaseOptimizing binary log replicationSynchronizing passwords between replication source and target

Replication between Aurora and MySQL or between Aurora and another Aurora DB cluster (binary log replication)

Because Amazon Aurora MySQL is compatible with MySQL, you can set up replication between a MySQL database and an Amazon Aurora MySQL DB cluster. This type of replication uses the MySQL binary log replication, also referred to as binlog replication. If you use binary log replication with Aurora, we recommend that your MySQL database run MySQL version 5.5 or later. You can set up replication where your Aurora MySQL DB cluster is the replication source or the replica. You can replicate with an Amazon RDS MySQL DB instance, a MySQL database external to Amazon RDS, or another Aurora MySQL DB cluster.

Note

You can't use binlog replication to or from certain kinds of Aurora clusters. In particular, binlog replication isn't available for Aurora Serverless v1 and multi-master clusters. If the SHOW MASTER STATUS and SHOW SLAVE STATUS (Aurora MySQL version 1 and 2) or SHOW REPLICA STATUS (Aurora MySQL version 3) statement returns no output, check that the cluster you're using is one that supports binlog replication.

You can also replicate with an RDS for MySQL DB instance or Aurora MySQL DB cluster in another AWS Region. When you're performing replication across AWS Regions, make sure that your DB clusters and DB instances are publicly accessible. If the Aurora MySQL DB clusters are in private subnets in your VPC, use VPC peering between the AWS Regions. For more information, see A DB cluster in a VPC accessed by an EC2 instance in a different VPC.

If you want to configure replication between an Aurora MySQL DB cluster and an Aurora MySQL DB cluster in another region, you can create an Aurora MySQL DB cluster as a read replica in a different AWS Region than the source DB cluster. For more information, see Replicating Amazon Aurora MySQL DB clusters across AWS Regions.

With Aurora MySQL 2.04 and higher, you can replicate between Aurora MySQL and an external source or target that uses global transaction identifiers (GTIDs) for replication. Ensure that the GTID-related parameters in the Aurora MySQL DB cluster have settings that are compatible with the GTID status of the external database. To learn how to do this, see Using GTID-based replication for Amazon Aurora MySQL. In Aurora MySQL version 3.01 and higher, you can choose how to assign GTIDs to transactions that are replicated from a source that doesn't use GTIDs. For information about the stored procedure that controls that setting, see mysql.rds_assign_gtids_to_anonymous_transactions (Aurora MySQL version 3 and higher).

Warning

When you replicate between Aurora MySQL and MySQL, ensure that you use only InnoDB tables. If you have MyISAM tables that you want to replicate, you can convert them to InnoDB before setting up replication with the following command. 

alter table <schema>.<table_name> engine=innodb, algorithm=copy;

Setting up MySQL replication with Aurora MySQL involves the following steps, which are discussed in detail in this topic:

1. Turn on binary logging on the replication source

2. Retain binary logs on the replication source until no longer needed

3. Create a snapshot of your replication source

4. Load the snapshot into your replica target

5. Create a replication user on your replication source

6. Turn on replication on your replica target

7. Monitor your replica

Setting up replication with MySQL or another Aurora DB cluster

To set up Aurora replication with MySQL, take the following steps.

1. Turn on binary logging on the replication source

Find instructions on how to turn on binary logging on the replication source for your database engine following.

Database engine Instructions

Aurora

To turn on binary logging on an Aurora MySQL DB cluster

Set the binlog_format parameter to ROW, STATEMENT, or MIXED. MIXED is recommended unless you have a need for a specific binlog format. The binlog_format parameter is a cluster-level parameter that is in the default cluster parameter group. If you're changing the binlog_format parameter from OFF to another value, then you need to reboot your Aurora DB cluster for the change to take effect.

For more information, see Amazon Aurora DB cluster and DB instance parameters and Working with parameter groups.

RDS for MySQL

To turn on binary logging on an Amazon RDS DB instance

You can't turn on binary logging directly for an Amazon RDS DB instance, but you can turn it on by doing one of the following:

  • Turn on automated backups for the DB instance. You can turn on automated backups when you create a DB instance, or you can turn on backups by modifying an existing DB instance. For more information, see Creating a DB instance in the Amazon RDS User Guide.

  • Create a read replica for the DB instance. For more information, see Working with read replicas in the Amazon RDS User Guide.

MySQL (external)

To set up encrypted replication

To replicate data securely with Aurora MySQL versions 1 and 2, you can use encrypted replication.

Currently, encrypted replication with an external MySQL database is supported for Aurora MySQL version 1 and version 2, 2.04.2 and higher.

Note

If you don't need to use encrypted replication, you can skip these steps.

The following are prerequisites for using encrypted replication:

  • Secure Sockets Layer (SSL) must be enabled on the external MySQL source database.

  • A client key and client certificate must be prepared for the Aurora MySQL DB cluster.

During encrypted replication, the Aurora MySQL DB cluster acts a client to the MySQL database server. The certificates and keys for the Aurora MySQL client are in files in .pem format.

  1. Ensure that you are prepared for encrypted replication:

    • If you don't have SSL enabled on the external MySQL source database and don't have a client key and client certificate prepared, turn on SSL on the MySQL database server and generate the required client key and client certificate.

    • If SSL is enabled on the external source, supply a client key and certificate for the Aurora MySQL DB cluster. If you don't have these, generate a new key and certificate for the Aurora MySQL DB cluster. To sign the client certificate, you must have the certificate authority key that you used to configure SSL on the external MySQL source database.

    For more information, see Creating SSL certificates and keys using openssl in the MySQL documentation.

    You need the certificate authority certificate, the client key, and the client certificate.

  2. Connect to the Aurora MySQL DB cluster as the master user using SSL.

    For information about connecting to an Aurora MySQL DB cluster with SSL, see Using SSL/TLS with Aurora MySQL DB clusters.

  3. Run the mysql.rds_import_binlog_ssl_material stored procedure to import the SSL information into the Aurora MySQL DB cluster.

    For the ssl_material_value parameter, insert the information from the .pem format files for the Aurora MySQL DB cluster in the correct JSON payload.

    The following example imports SSL information into an Aurora MySQL DB cluster. In .pem format files, the body code typically is longer than the body code shown in the example. 

    call mysql.rds_import_binlog_ssl_material(
'{"ssl_ca":"-----BEGIN CERTIFICATE-----
AAAAB3NzaC1yc2EAAAADAQABAAABAQClKsfkNkuSevGj3eYhCe53pcjqP3maAhDFcvBS7O6V
hz2ItxCih+PnDSUaw+WNQn/mZphTk/a/gU8jEzoOWbkM4yxyb/wB96xbiFveSFJuOp/d6RJhJOI0iBXr
lsLnBItntckiJ7FbtxJMXLvvwJryDUilBMTjYtwB+QhYXUMOzce5Pjz5/i8SeJtjnV3iAoG/cQk+0FzZ
qaeJAAHco+CY/5WrUBkrHmFJr6HcXkvJdWPkYQS3xqC0+FmUZofz221CBt5IMucxXPkX4rWi+z7wB3Rb
BQoQzd8v7yeb7OzlPnWOyN0qFU0XA246RA8QFYiCNYwI3f05p6KLxEXAMPLE
-----END CERTIFICATE-----\n","ssl_cert":"-----BEGIN CERTIFICATE-----
AAAAB3NzaC1yc2EAAAADAQABAAABAQClKsfkNkuSevGj3eYhCe53pcjqP3maAhDFcvBS7O6V
hz2ItxCih+PnDSUaw+WNQn/mZphTk/a/gU8jEzoOWbkM4yxyb/wB96xbiFveSFJuOp/d6RJhJOI0iBXr
lsLnBItntckiJ7FbtxJMXLvvwJryDUilBMTjYtwB+QhYXUMOzce5Pjz5/i8SeJtjnV3iAoG/cQk+0FzZ
qaeJAAHco+CY/5WrUBkrHmFJr6HcXkvJdWPkYQS3xqC0+FmUZofz221CBt5IMucxXPkX4rWi+z7wB3Rb
BQoQzd8v7yeb7OzlPnWOyN0qFU0XA246RA8QFYiCNYwI3f05p6KLxEXAMPLE
-----END CERTIFICATE-----\n","ssl_key":"-----BEGIN RSA PRIVATE KEY-----
AAAAB3NzaC1yc2EAAAADAQABAAABAQClKsfkNkuSevGj3eYhCe53pcjqP3maAhDFcvBS7O6V
hz2ItxCih+PnDSUaw+WNQn/mZphTk/a/gU8jEzoOWbkM4yxyb/wB96xbiFveSFJuOp/d6RJhJOI0iBXr
lsLnBItntckiJ7FbtxJMXLvvwJryDUilBMTjYtwB+QhYXUMOzce5Pjz5/i8SeJtjnV3iAoG/cQk+0FzZ
qaeJAAHco+CY/5WrUBkrHmFJr6HcXkvJdWPkYQS3xqC0+FmUZofz221CBt5IMucxXPkX4rWi+z7wB3Rb
BQoQzd8v7yeb7OzlPnWOyN0qFU0XA246RA8QFYiCNYwI3f05p6KLxEXAMPLE
-----END RSA PRIVATE KEY-----\n"}');

    For more information, see mysql.rds_import_binlog_ssl_material and Using SSL/TLS with Aurora MySQL DB clusters.

    Note

    After running the procedure, the secrets are stored in files. To erase the files later, you can run the mysql.rds_remove_binlog_ssl_material stored procedure.

To turn on binary logging on an external MySQL database

  1. From a command shell, stop the mysql service. 

    sudo service mysqld stop

  2. Edit the my.cnf file (this file is usually under /etc). 

    sudo vi /etc/my.cnf

    Add the log_bin and server_id options to the [mysqld] section. The log_bin option provides a file name identifier for binary log files. The server_id option provides a unique identifier for the server in source-replica relationships.

    If encrypted replication isn't required, ensure that the external MySQL database is started with binlogs enabled and SSL is turned off.

    The following are the relevant entries in the /etc/my.cnf file for unencrypted data. 

    log-bin=mysql-bin
server-id=2133421
innodb_flush_log_at_trx_commit=1
sync_binlog=1

    If encrypted replication is required, ensure that the external MySQL database is started with SSL and binlogs enabled.

    The entries in the /etc/my.cnf file include the .pem file locations for the MySQL database server. 

    log-bin=mysql-bin
server-id=2133421
innodb_flush_log_at_trx_commit=1
sync_binlog=1

# Setup SSL.
ssl-ca=/home/sslcerts/ca.pem
ssl-cert=/home/sslcerts/server-cert.pem
ssl-key=/home/sslcerts/server-key.pem

    Additionally, the sql_mode option for your MySQL DB instance must be set to 0, or must not be included in your my.cnf file.

    While connected to the external MySQL database, record the external MySQL database's binary log position. 

    mysql> SHOW MASTER STATUS;

    Your output should be similar to the following: 

    +------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000031 |      107 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

    For more information, see Setting the replication source configuration in the MySQL documentation.

  3. Start the mysql service. 

    sudo service mysqld start

2. Retain binary logs on the replication source until no longer needed

When you use MySQL binary log replication, Amazon RDS doesn't manage the replication process. As a result, you need to ensure that the binlog files on your replication source are retained until after the changes have been applied to the replica. This maintenance helps you to restore your source database in the event of a failure.

Use the following instructions to retain binary logs for your database engine.

Database engine Instructions

Aurora

To retain binary logs on an Aurora MySQL DB cluster

You do not have access to the binlog files for an Aurora MySQL DB cluster. As a result, you must choose a time frame to retain the binlog files on your replication source long enough to ensure that the changes have been applied to your replica before the binlog file is deleted by Amazon RDS. You can retain binlog files on an Aurora MySQL DB cluster for up to 90 days.

If you are setting up replication with a MySQL database or RDS for MySQL DB instance as the replica, and the database that you are creating a replica for is very large, choose a large time frame to retain binlog files until the initial copy of the database to the replica is complete and the replica lag has reached 0.

To set the binary log retention time frame, use the mysql.rds_set_configuration procedure and specify a configuration parameter of 'binlog retention hours' along with the number of hours to retain binlog files on the DB cluster. The maximum value for Aurora MySQL version 1 is 2160 (90 days). The maximum value for Aurora MySQL version 2 and 3 is 168 (7 days).

The following example sets the retention period for binlog files to 6 days:

CALL mysql.rds_set_configuration('binlog retention hours', 144);

After replication has been started, you can verify that changes have been applied to your replica by running the SHOW SLAVE STATUS (Aurora MySQL version 1 and 2) or SHOW REPLICA STATUS (Aurora MySQL version 3) command on your replica and checking the Seconds behind master field. If the Seconds behind master field is 0, then there is no replica lag. When there is no replica lag, reduce the length of time that binlog files are retained by setting the binlog retention hours configuration parameter to a smaller time frame.

If this setting isn't specified, the default for Aurora MySQL is 24 (1 day).

If you specify a value for 'binlog retention hours' that is higher than the maximum value, then Aurora MySQL uses the maximum.

RDS for MySQL

To retain binary logs on an Amazon RDS DB instance

You can retain binary log files on an Amazon RDS DB instance by setting the binlog retention hours just as with an Aurora MySQL DB cluster, described in the previous row.

You can also retain binlog files on an Amazon RDS DB instance by creating a read replica for the DB instance. This read replica is temporary and solely for the purpose of retaining binlog files. After the read replica has been created, call the mysql.rds_stop_replication procedure on the read replica. While replication is stopped, Amazon RDS doesn't delete any of the binlog files on the replication source. After you have set up replication with your permanent replica, you can delete the read replica when the replica lag (Seconds behind master field) between your replication source and your permanent replica reaches 0.

MySQL (external)

To retain binary logs on an external MySQL database

Because binlog files on an external MySQL database are not managed by Amazon RDS, they are retained until you delete them.

After replication has been started, you can verify that changes have been applied to your replica by running the SHOW SLAVE STATUS (Aurora MySQL version 1 and 2) or SHOW REPLICA STATUS (Aurora MySQL version 3) command on your replica and checking the Seconds behind master field. If the Seconds behind master field is 0, then there is no replica lag. When there is no replica lag, you can delete old binlog files.

3. Create a snapshot of your replication source

You use a snapshot of your replication source to load a baseline copy of your data onto your replica and then start replicating from that point on.

Use the following instructions to create a snapshot of your replication source for your database engine.

Database engine Instructions

Aurora

To create a snapshot of an Aurora MySQL DB cluster

  1. Create a DB cluster snapshot of your Amazon Aurora DB cluster. For more information, see Creating a DB cluster snapshot.

  2. Create a new Aurora DB cluster by restoring from the DB cluster snapshot that you just created. Be sure to retain the same DB parameter group for your restored DB cluster as your original DB cluster. Doing this ensures that the copy of your DB cluster has binary logging enabled. For more information, see Restoring from a DB cluster snapshot.

  3. In the console, choose Databases and choose the primary instance (writer) for your restored Aurora DB cluster to show its details. Scroll to Recent Events. An event message shows that includes the binlog file name and position. The event message is in the following format. 

    Binlog position from crash recovery is binlog-file-name binlog-position

    Save the binlog file name and position values for when you start replication.

    You can also get the binlog file name and position by calling the describe-events command from the AWS CLI. The following shows an example describe-events command with example output. 

    PROMPT> aws rds describe-events
    {
    "Events": [
        {
            "EventCategories": [],
            "SourceType": "db-instance",
            "SourceArn": "arn:aws:rds:us-west-2:123456789012:db:sample-restored-instance",
            "Date": "2016-10-28T19:43:46.862Z",
            "Message": "Binlog position from crash recovery is mysql-bin-changelog.000003 4278",
            "SourceIdentifier": "sample-restored-instance"
        }
    ]
}

    You can also get the binlog file name and position by checking the MySQL error log for the last MySQL binlog file position.

  4. If your replica target is an Aurora DB cluster owned by another AWS account, an external MySQL database, or an RDS for MySQL DB instance, then you can't load the data from an Amazon Aurora DB cluster snapshot. Instead, create a dump of your Amazon Aurora DB cluster by connecting to your DB cluster using a MySQL client and issuing the mysqldump command. Be sure to run the mysqldump command against the copy of your Amazon Aurora DB cluster that you created. The following is an example. 

    PROMPT> mysqldump --databases <database_name> --single-transaction
--order-by-primary -r backup.sql -u <local_user> -p

  5. When you have finished creating the dump of your data from the newly created Aurora DB cluster, delete that DB cluster as it is no longer needed.

RDS for MySQL

To create a snapshot of an Amazon RDS DB instance

  1. Create a read replica of your Amazon RDS DB instance. For more information, see Creating a read replica in the Amazon Relational Database Service User Guide.

  2. Connect to your read replica and stop replication by running the mysql.rds_stop_replication procedure.

  3. While the read replica is Stopped, Connect to the read replica and run the SHOW SLAVE STATUS (Aurora MySQL version 1 and 2) or SHOW REPLICA STATUS (Aurora MySQL version 3) command. Retrieve the current binary log file name from the Relay_Master_Log_File field and the log file position from the Exec_Master_Log_Pos field. Save these values for when you start replication.

  4. While the read replica remains Stopped, create a DB snapshot of the read replica. For more information, see Creating a DB snapshot in the Amazon Relational Database Service User Guide.

  5. Delete the read replica.

MySQL (external)

To create a snapshot of an external MySQL database

  1. Before you create a snapshot, you need to ensure that the binlog location for the snapshot is current with the data in your source instance. To do this, you must first stop any write operations to the instance with the following command: 

    mysql> FLUSH TABLES WITH READ LOCK;

  2. Create a dump of your MySQL database using the mysqldump command as shown following: 

    PROMPT> sudo mysqldump --databases <database_name> --master-data=2  --single-transaction \
--order-by-primary -r backup.sql -u <local_user> -p

  3. After you have created the snapshot, unlock the tables in your MySQL database with the following command: 

    mysql> UNLOCK TABLES;

4. Load the snapshot into your replica target

If you plan to load data from a dump of a MySQL database that is external to Amazon RDS, then you might want to create an EC2 instance to copy the dump files to, and then load the data into your DB cluster or DB instance from that EC2 instance. Using this approach, you can compress the dump file(s) before copying them to the EC2 instance in order to reduce the network costs associated with copying data to Amazon RDS. You can also encrypt the dump file or files to secure the data as it is being transferred across the network.

Use the following instructions to load the snapshot of your replication source into your replica target for your database engine.

Database engine Instructions

Aurora

To load a snapshot into an Aurora MySQL DB cluster

  • If the snapshot of your replication source is a DB cluster snapshot, then you can restore from the DB cluster snapshot to create a new Aurora MySQL DB cluster as your replica target. For more information, see Restoring from a DB cluster snapshot.

  • If the snapshot of your replication source is a DB snapshot, then you can migrate the data from your DB snapshot into a new Aurora MySQL DB cluster. For more information, see Migrating data to an Amazon Aurora DB cluster.

  • If the snapshot of your replication source is the output from the mysqldump command, then follow these steps:

    1. Copy the output of the mysqldump command from your replication source to a location that can also connect to your Aurora MySQL DB cluster.

    2. Connect to your Aurora MySQL DB cluster using the mysql command. The following is an example. 

      PROMPT> mysql -h <host_name> -port=3306 -u <db_master_user> -p

    3. At the mysql prompt, run the source command and pass it the name of your database dump file to load the data into the Aurora MySQL DB cluster, for example: 

      mysql> source backup.sql;

RDS for MySQL

To load a snapshot into an Amazon RDS DB instance

  1. Copy the output of the mysqldump command from your replication source to a location that can also connect to your MySQL DB instance.

  2. Connect to your MySQL DB instance using the mysql command. The following is an example. 

    PROMPT> mysql -h <host_name> -port=3306 -u <db_master_user> -p

  3. At the mysql prompt, run the source command and pass it the name of your database dump file to load the data into the MySQL DB instance, for example: 

    mysql> source backup.sql;

MySQL (external)

To load a snapshot into an external MySQL database

You cannot load a DB snapshot or a DB cluster snapshot into an external MySQL database. Instead, you must use the output from the mysqldump command.

  1. Copy the output of the mysqldump command from your replication source to a location that can also connect to your MySQL database.

  2. Connect to your MySQL database using the mysql command. The following is an example. 

    PROMPT> mysql -h <host_name> -port=3306 -u <db_master_user> -p

  3. At the mysql prompt, run the source command and pass it the name of your database dump file to load the data into your MySQL database. The following is an example. 

    mysql> source backup.sql;

5. Create a replication user on your replication source

Create a user ID on the source that is used solely for replication. The following is an example.

mysql> CREATE USER 'repl_user'@'<domain_name>' IDENTIFIED BY '<password>';

The user requires the REPLICATION CLIENT and REPLICATION SLAVE privileges. Grant these privileges to the user.

If you need to use encrypted replication, require SSL connections for the replication user. For example, you can use one of the following statement to require SSL connections on the user account repl_user.

GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'repl_user'@'<domain_name>';

GRANT USAGE ON *.* TO 'repl_user'@'<domain_name>' REQUIRE SSL;
Note

If REQUIRE SSL isn't included, the replication connection might silently fall back to an unencrypted connection.

6. Turn on replication on your replica target

Before you turn on replication, we recommend that you take a manual snapshot of the Aurora MySQL DB cluster or RDS for MySQL DB instance replica target. If a problem arises and you need to re-establish replication with the DB cluster or DB instance replica target, you can restore the DB cluster or DB instance from this snapshot instead of having to import the data into your replica target again.

Use the following instructions to turn on replication for your database engine.

Database engine Instructions

Aurora MySQL

To turn on replication from an Aurora MySQL DB cluster

  1. If your DB cluster replica target was created from a DB cluster snapshot, then connect to the DB cluster replica target and issue the SHOW MASTER STATUS command. Retrieve the current binary log file name from the File field and the log file position from the Position field.

    If your DB cluster replica target was created from a DB snapshot, then you need the binlog file and binlog position that are the starting place for replication. You retrieved these values from the SHOW SLAVE STATUS (Aurora MySQL version 1 and 2) or SHOW REPLICA STATUS (Aurora MySQL version 3) command when you created the snapshot of your replication source.

  2. Connect to the DB cluster and issue the mysql.rds_set_external_source and mysql.rds_start_replication procedures to start replication with your replication source using the binary log file name and location from the previous step. The following is an example.

    
CALL mysql.rds_set_external_source ('mydbinstance.123456789012.us-east-1.rds.amazonaws.com', 3306,
    'repl_user', '<password>', 'mysql-bin-changelog.000031', 107, 0);

For all versions:
CALL mysql.rds_start_replication;

To use SSL encryption, set the final value to 1 instead of 0.

RDS for MySQL

To turn on replication from an Amazon RDS DB instance

  1. If your DB instance replica target was created from a DB snapshot, then you need the binlog file and binlog position that are the starting place for replication. You retrieved these values from the SHOW SLAVE STATUS (Aurora MySQL version 1 and 2) or SHOW REPLICA STATUS (Aurora MySQL version 3) command when you created the snapshot of your replication source.

  2. Connect to the DB instance and issue the mysql.rds_set_external_source and mysql.rds_start_replication procedures to start replication with your replication source using the binary log file name and location from the previous step. The following is an example. 

    
For Aurora MySQL version 1 and 2:
CALL mysql.rds_set_external_master ('mydbcluster.cluster-123456789012.us-east-1.rds.amazonaws.com', 3306,
    'repl_user', '<password>', 'mysql-bin-changelog.000031', 107, 0);

For Aurora MySQL version 3 and higher:
CALL mysql.rds_set_external_source ('mydbcluster.cluster-123456789012.us-east-1.rds.amazonaws.com', 3306,
    'repl_user', '<password>', 'mysql-bin-changelog.000031', 107, 0);

For all versions:
CALL mysql.rds_start_replication;

To use SSL encryption, set the final value to 1 instead of 0.

MySQL (external)

To turn on replication from an external MySQL database

  1. Retrieve the binlog file and binlog position that are the starting place for replication. You retrieved these values from the SHOW SLAVE STATUS (Aurora MySQL version 1 and 2) or SHOW REPLICA STATUS (Aurora MySQL version 3) command when you created the snapshot of your replication source. If your external MySQL replica target was populated from the output of the mysqldump command with the --master-data=2 option, then the binlog file and binlog position are included in the output. The following is an example. 

    --
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin-changelog.000031', MASTER_LOG_POS=107;

  2. Connect to the external MySQL replica target, and issue CHANGE MASTER TO and START SLAVE (Aurora MySQL version 1 and 2) or START REPLICA (Aurora MySQL version 3) to start replication with your replication source using the binary log file name and location from the previous step, for example: 

    CHANGE MASTER TO
  MASTER_HOST = 'mydbcluster.cluster-123456789012.us-east-1.rds.amazonaws.com',
  MASTER_PORT = 3306,
  MASTER_USER = 'repl_user',
  MASTER_PASSWORD = '<password>',
  MASTER_LOG_FILE = 'mysql-bin-changelog.000031',
  MASTER_LOG_POS = 107;
-- And one of these statements depending on your engine version:
START SLAVE; -- Aurora MySQL version 1 and 2
START REPLICA; -- Aurora MySQL version 3

If replication fails, it can result in a large increase in unintentional I/O on the replica, which can degrade performance. If replication fails or is no longer needed, you can run the mysql.rds_reset_external_master stored procedure to remove the replication configuration.

7. Monitor your replica

When you set up MySQL replication with an Aurora MySQL DB cluster, you must monitor failover events for the Aurora MySQL DB cluster when it is the replica target. If a failover occurs, then the DB cluster that is your replica target might be recreated on a new host with a different network address. For information on how to monitor failover events, see Working with Amazon RDS event notification.

You can also monitor how far the replica target is behind the replication source by connecting to the replica target and running the SHOW SLAVE STATUS (Aurora MySQL version 1 and 2) or SHOW REPLICA STATUS (Aurora MySQL version 3) command. In the command output, the Seconds Behind Master field tells you how far the replica target is behind the source.

Stopping replication between Aurora and MySQL or between Aurora and another Aurora DB cluster

To stop binary log replication with a MySQL DB instance, external MySQL database, or another Aurora DB cluster, follow these steps, discussed in detail following in this topic.

1. Stop binary log replication on the replica target

2. Turn off binary logging on the replication source

1. Stop binary log replication on the replica target

Find instructions on how to stop binary log replication for your database engine following.

Database engine Instructions

Aurora

To stop binary log replication on an Aurora MySQL DB cluster replica target

Connect to the Aurora DB cluster that is the replica target, and call the mysql.rds_stop_replication procedure.

RDS for MySQL

To stop binary log replication on an Amazon RDS DB instance

Connect to the RDS DB instance that is the replica target and call the mysql.rds_stop_replication procedure. The mysql.rds_stop_replication procedure is only available for MySQL versions 5.5 and later, 5.6 and later, and 5.7 and later.

MySQL (external)

To stop binary log replication on an external MySQL database

Connect to the MySQL database and call the STOP REPLICATION command.

2. Turn off binary logging on the replication source

Find instructions on how to turn off binary logging on the replication source for your database engine following.

Database engine Instructions

Aurora

To turn off binary logging on an Amazon Aurora DB cluster

  1. Connect to the Aurora DB cluster that is the replication source, and set the binary log retention time frame to 0. To set the binary log retention time frame, use the mysql.rds_set_configuration procedure and specify a configuration parameter of 'binlog retention hours' along with the number of hours to retain binlog files on the DB cluster, in this case 0, as shown in the following example. 

    CALL mysql.rds_set_configuration('binlog retention hours', 0);

  2. Set the binlog_format parameter to OFF on the replication source. The binlog_format parameter is a cluster-level parameter that is in the default cluster parameter group.

    After you have changed the binlog_format parameter value, reboot your DB cluster for the change to take effect.

    For more information, see Amazon Aurora DB cluster and DB instance parameters and Modifying parameters in a DB parameter group.

RDS for MySQL

To turn off binary logging on an Amazon RDS DB instance

You can't turn off binary logging directly for an Amazon RDS DB instance, but you can turn it off by doing the following:

  1. Turn off automated backups for the DB instance. You can turn off automated backups by modifying an existing DB instance and setting the Backup Retention Period to 0. For more information, see Modifying an Amazon RDS DB instance and Working with backups in the Amazon Relational Database Service User Guide.

  2. Delete all read replicas for the DB instance. For more information, see Working with read replicas of MariaDB, MySQL, and PostgreSQL DB instances in the Amazon Relational Database Service User Guide.

MySQL (external)

To turn off binary logging on an external MySQL database

Connect to the MySQL database and call the STOP REPLICATION command.

  1. From a command shell, stop the mysqld service, 

    sudo service mysqld stop

  2. Edit the my.cnf file (this file is usually under /etc). 

    sudo vi /etc/my.cnf

    Delete the log_bin and server_id options from the [mysqld] section.

    For more information, see Setting the replication source configuration in the MySQL documentation.

  3. Start the mysql service. 

    sudo service mysqld start

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 MySQL DB cluster and make it a read replica of your MySQL DB instance. This applies to an RDS for 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 MySQL DB cluster. If a failover occurs, then the Aurora Replica that is promoted to the primary instance for the Aurora MySQL DB cluster continues to use the DB cluster endpoint address.

  • Maintain the binlogs on your writer instance until you have verified that they have been applied to the Aurora Replica. This maintenance ensures that you can restore your writer 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 MySQL 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_source and mysql.rds_start_replication procedures to set up replication between your Amazon Aurora MySQL DB cluster and your MySQL DB instance.

Start replication between an external source instance and a MySQL DB instance on Amazon RDS

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

    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 receive output similar to the following example: 

    File                        Position
------------------------------------
 mysql-bin-changelog.000031      107
------------------------------------

  3. Copy the database from the external MySQL DB instance to the Amazon Aurora MySQL DB cluster using mysqldump. For very large databases, you might want to use the procedure in Importing data to a MySQL or MariaDB DB instance with reduced downtime in the Amazon Relational Database Service User Guide.

    For Linux, macOS, or Unix:

    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:

    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 that 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: 

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

    For more information on making backups for use with replication, see Backing up a source or replica 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. 

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

    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. 

    GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'repl_user'@'mydomain.com'
    IDENTIFIED BY '<password>';

  8. Take a manual snapshot of the Aurora MySQL DB cluster to be the read replica before setting up replication. If you need to reestablish replication with the DB cluster as a read replica, you can restore the Aurora MySQL DB cluster from this snapshot instead of having to import the data from your MySQL DB instance into a new Aurora MySQL 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_source procedure. Use the master log file name and master log position that you determined in Step 2. The following is an example. 

    For Aurora MySQL version 1 and 2:
CALL mysql.rds_set_external_master ('mymasterserver.mydomain.com', 3306,
    'repl_user', '<password>', 'mysql-bin-changelog.000031', 107, 0);

For Aurora MySQL version 3 and higher:
CALL mysql.rds_set_external_source ('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 procedure to start replication. 

    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 Adding Aurora Replicas to a DB cluster.

Optimizing binary log replication

Following, you can learn how to optimize binary log replication performance and troubleshoot related issues in Aurora MySQL.

Tip

This discussion presumes that you are familiar with the MySQL binary log replication mechanism and how it works. For background information, see Replication Implementation in the MySQL documentation.

Multithreaded binary log replication (Aurora MySQL version 3 and higher)

With multithreaded binary log replication, a SQL thread reads events from the relay log and queues them up for SQL worker threads to apply. The SQL worker threads are managed by a coordinator thread. The binary log events are applied in parallel when possible.

When an Aurora MySQL instance is configured to use binary log replication, by default the replica instance uses single-threaded replication. To enable multithreaded replication, you update the replica_parallel_workers parameter to a value greater than zero in your custom parameter group.

The following configuration options help you to fine-tune multithreaded replication. For usage information, see Replication and Binary Logging Options and Variables in the MySQL Reference Manual.

Optimal configuration depends on several factors. For example, performance for binary log replication is influenced by your database workload characteristics and the DB instance class the replica is running on. Thus, we recommend that you thoroughly test all changes to these configuration parameters before applying new parameter settings to a production instance.

  • replica_parallel_workers

  • replica_parallel_type

  • replica_preserve_commit_order

  • binlog_transaction_dependency_tracking

  • binlog_transaction_dependency_history_size

  • binlog_group_commit_sync_delay

  • binlog_group_commit_sync_no_delay_count

Optimizing binlog replication (Aurora MySQL 2.10 and higher)

In Aurora MySQL 2.10 and higher, Aurora automatically applies an optimization known as the binlog I/O cache to binary log replication. By caching the most recently committed binlog events, this optimization is designed to improve binlog dump thread performance while limiting the impact to foreground transactions on the binlog source instance.

Note

This memory used for this feature is independent of the MySQL binlog_cache setting.

This feature doesn't apply to Aurora DB instances that use the db.t2 and db.t3 instance classes.

You don't need to adjust any configuration parameters to turn on this optimization. In particular, if you adjust the configuration parameter aurora_binlog_replication_max_yield_seconds to a nonzero value in earlier Aurora MySQL versions, set it back to zero for Aurora MySQL 2.10 and higher.

The status variables aurora_binlog_io_cache_reads and aurora_binlog_io_cache_read_requests are available in Aurora MySQL 2.10 and higher. These status variables help you to monitor how often the data is read from the binlog I/O cache.

  • aurora_binlog_io_cache_read_requests shows the number of binlog I/O read requests from the cache.

  • aurora_binlog_io_cache_reads shows the number of binlog I/O reads that retrieve information from the cache.

The following SQL query computes the percentage of binlog read requests that take advantage of the cached information. In this case, the closer the ratio is to 100, the better it is. 

mysql> SELECT
  (SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS
    WHERE VARIABLE_NAME='aurora_binlog_io_cache_reads')
  / (SELECT VARIABLE_VALUE FROM INFORMATION_SCHEMA.GLOBAL_STATUS
    WHERE VARIABLE_NAME='aurora_binlog_io_cache_read_requests')
  * 100
  as binlog_io_cache_hit_ratio;
+---------------------------+
| binlog_io_cache_hit_ratio |
+---------------------------+
|         99.99847949080622 |
+---------------------------+

The binlog I/O cache feature also includes new metrics related to the binlog dump threads. Dump threads are the threads that are created when new binlog replicas are connected to the binlog source instance.

The dump thread metrics are printed to the database log every 60 seconds with the prefix [Dump thread metrics]. The metrics include information for each binlog replica such as Secondary_id, Secondary_uuid, binlog file name, and the position that each replica is reading. The metrics also include Bytes_behind_primary representing the distance in bytes between replication source and replica. This metric measures the lag of the replica I/O thread. That figure is different from the lag of the replica SQL applier thread, which is represented by the seconds_behind_master metric on the binlog replica. You can determine whether binlog replicas are catching up to the source or falling behind by checking whether the distance decreases or increases.

Optimizing binlog replication (Aurora MySQL 2.04.5 through 2.09)

To optimize binary log replication for Aurora MySQL, you adjust the following cluster-level optimization parameters. These parameters help you to specify the right balance between latency on the binlog source instance and replication lag.

  • aurora_binlog_use_large_read_buffer

  • aurora_binlog_read_buffer_size

  • aurora_binlog_replication_max_yield_seconds

Note

For MySQL 5.7-compatible clusters, you can use these parameters in Aurora MySQL version 2.04.5 through 2.09.*. In Aurora MySQL 2.10.0 and higher, these parameters are superseded by the binlog I/O cache optimization and you don't need to use them.

For MySQL 5.6-compatible clusters, you can use these parameters in Aurora MySQL version 1.17.6 and later.

Overview of the large read buffer and max-yield optimizations

You might experience reduced binary log replication performance when the binary log dump thread accesses the Aurora cluster volume while the cluster processes a high number of transactions. You can use the parameters aurora_binlog_use_large_read_buffer, aurora_binlog_replication_max_yield_seconds, and aurora_binlog_read_buffer_size to help minimize this type of contention.

Suppose that you have a situation where aurora_binlog_replication_max_yield_seconds is set to greater than 0 and the current binlog file of the dump thread is active. In this case, the binary log dump thread waits up to a specified number of seconds for the current binlog file to be filled by transactions. This wait period avoids contention that can arise from replicating each binlog event individually. However, doing so increases the replica lag for binary log replicas. Those replicas can fall behind the source by the same number of seconds as the aurora_binlog_replication_max_yield_seconds setting.

The current binlog file means the binlog file that the dump thread is currently reading to perform replication. We consider that a binlog file is active when the binlog file is updating or open to be updated by incoming transactions. After Aurora MySQL fills up the active binlog file, MySQL creates and switches to a new binlog file. The old binlog file becomes inactive. It isn't updated by incoming transactions any longer.

Note

Before adjusting these parameters, measure your transaction latency and throughput over time. You might find that binary log replication performance is stable and has low latency even if there is occasional contention.

aurora_binlog_use_large_read_buffer

If this parameter is set to 1, Aurora MySQL optimizes binary log replication based on the settings of the parameters aurora_binlog_read_buffer_size and aurora_binlog_replication_max_yield_seconds. If aurora_binlog_use_large_read_buffer is 0, Aurora MySQL ignores the values of the aurora_binlog_read_buffer_size and aurora_binlog_replication_max_yield_seconds parameters.

aurora_binlog_read_buffer_size

Binary log dump threads with larger read buffer minimize the number of read I/O operations by reading more events for each I/O. The parameter aurora_binlog_read_buffer_size sets the read buffer size. The large read buffer can reduce binary log contention for workloads that generate a large amount of binlog data.

Note

This parameter only has an effect when the cluster also has the setting aurora_binlog_use_large_read_buffer=1.

Increasing the size of the read buffer doesn't affect the performance of binary log replication. Binary log dump threads don't wait for updating transactions to fill up the read buffer.

aurora_binlog_replication_max_yield_seconds

If your workload requires low transaction latency, and you can tolerate some replication lag, you can increase the aurora_binlog_replication_max_yield_seconds parameter. This parameter controls the maximum yield property of binary log replication in your cluster.

Note

This parameter only has an effect when the cluster also has the setting aurora_binlog_use_large_read_buffer=1.

Aurora MySQL recognizes any change to the aurora_binlog_replication_max_yield_seconds parameter value immediately. You don't need to restart the DB instance. However, when you turn on this setting, the dump thread only starts to yield when the current binlog file reaches its maximum size of 128 MB and is rotated to a new file.

Related parameters

Use the following DB cluster parameters to turn on the binlog optimization.

Binlog optimization parameters for Aurora MySQL version 2.04.5 and later
Parameter Default Valid Values Description
aurora_binlog_use_large_read_buffer 1 0, 1 Switch for turning on the feature of replication improvement. When it is 1, the binary log dump thread uses aurora_binlog_read_buffer_size for binary log replication; otherwise default buffer size (8K) is used.
aurora_binlog_read_buffer_size 5242880 8192-536870912 Read buffer size used by binary log dump thread when the parameter aurora_binlog_use_large_read_buffer is set to 1.
aurora_binlog_replication_max_yield_seconds 0 0-36000 For Aurora MySQL versions 2.04.5–2.04.8 and 2.05–2.08.*, the maximum accepted value is 45. You can tune it to a higher value on 2.04.9 and later versions of 2.04.*, and on 2.09 and later versions. This parameter works only when the parameter aurora_binlog_use_large_read_buffer is set to 1.
Binlog optimization parameters for Aurora MySQL version 1.17.6 and later
Parameter Default Valid Values Description
aurora_binlog_use_large_read_buffer 0 0, 1 Switch for turning on the feature of replication improvement. When it is 1, the binary log dump thread uses aurora_binlog_read_buffer_size for binary log replication. Otherwise, the default buffer size (8 KB) is used.
aurora_binlog_read_buffer_size 5242880 8192-536870912 Read buffer size used by binary log dump thread when the parameter aurora_binlog_use_large_read_buffer is set to 1.
aurora_binlog_replication_max_yield_seconds 0 0-36000 Maximum seconds to yield when the binary log dump thread replicates the current binlog file (the file used by foreground queries) to replicas. This parameter works only when the parameter aurora_binlog_use_large_read_buffer is set to 1.

Enabling the max-yield mechanism for binary log replication

You can turn on the binary log replication max-yield optimization as follows. Doing so minimizes latency for transactions on the binlog source instance. However, you might experience higher replication lag.

To turn on the max-yield binlog optimization for an Aurora MySQL cluster

  1. Create or edit a DB cluster parameter group using the following parameter settings:

    • aurora_binlog_use_large_read_buffer: turn on with a value of ON or 1.

    • aurora_binlog_replication_max_yield_seconds: specify a value greater than 0.

  2. Associate the DB cluster parameter group with the Aurora MySQL cluster that works as the binlog source. To do so, follow the procedures in Working with parameter groups.

  3. Confirm that the parameter change takes effect. To do so, run the following query on the binlog source instance. 

    SELECT @@aurora_binlog_use_large_read_buffer, @@aurora_binlog_replication_max_yield_seconds;

    Your output should be similar to the following. 

    +---------------------------------------+-----------------------------------------------+
| @@aurora_binlog_use_large_read_buffer | @@aurora_binlog_replication_max_yield_seconds |
+---------------------------------------+-----------------------------------------------+
|                                     1 |                                            45 |
+---------------------------------------+-----------------------------------------------+

Turning off the binary log replication max-yield optimization

You can turn off the binary log replication max-yield optimization as follows. Doing so minimizes replication lag. However, you might experience higher latency for transactions on the binlog source instance.

To turn off the max-yield optimization for an Aurora MySQL cluster

  1. Make sure that the DB cluster parameter group associated with the Aurora MySQL cluster has aurora_binlog_replication_max_yield_seconds set to 0. For more information about setting configuration parameters using parameter groups, see Working with parameter groups.

  2. Confirm that the parameter change takes effect. To do so, run the following query on the binlog source instance. 

    SELECT @@aurora_binlog_replication_max_yield_seconds;

    Your output should be similar to the following. 

    +-----------------------------------------------+
| @@aurora_binlog_replication_max_yield_seconds |
+-----------------------------------------------+
|                                             0 |
+-----------------------------------------------+

Turning off the large read buffer

You can turn off the entire large read buffer feature as follows.

To turn off the large binary log read buffer for an Aurora MySQL cluster

  1. Reset the aurora_binlog_use_large_read_buffer to OFF or 0.

    Make sure that the DB cluster parameter group associated with the Aurora MySQL cluster has aurora_binlog_use_large_read_buffer set to 0. For more information about setting configuration parameters using parameter groups, see Working with parameter groups.

  2. On the binlog source instance, run the following query. 

    SELECT @@ aurora_binlog_use_large_read_buffer;

    Your output should be similar to the following. 

    +---------------------------------------+
| @@aurora_binlog_use_large_read_buffer |
+---------------------------------------+
|                                     0 |
+---------------------------------------+

Synchronizing passwords between replication source and target

When you change user accounts and passwords on the replication source using SQL statements, those changes are replicated to the replication target automatically.

If you use the AWS Management Console, the AWS CLI, or the RDS API to change the master password on the replication source, those changes are not automatically replicated to the replication target. If you want to synchronize the master user and master password between the source and target systems, you must make the same change on the replication target yourself.