AWS RDS : Cross-Region Read Replicas for MySQL and Snapshots for PostgreSQL

bogotobogo.com site search:

Cross-Region Read Replicas for MySQL

Note this works only for MySQL not for Postgres DB!

Before a MySQL DB instance can serve as a replication source, we must enable automatic backups on the source DB instance by setting the backup retention period to a value other than 0. This requirement also applies to a Read Replica that is the source DB instance for another Read Replica. Automatic backups are supported only for Read Replicas running MySQL 5.6, not 5.1 or 5.5. (MySQL Read Replicas)

We can create cross-region read replicas for Amazon RDS database instances!

Cross-region replication allows a live replica to be created in one region, from a master in a different region.

We can use this feature to implement a cross-region disaster recovery model, scale out globally, or migrate an existing database to a new region:

Migration Between Regions:

The replica could be used for the same purpose of moving a master server to a different region. In other words, the master could be migrated from one region to another with minimum downtime by first setting up a cross-region replica in the desired target region.

Once the target RDS instance had been created and synchronized to the master, we would disconnect the application from the old master, and then convert the new replica in the new region into a standalone master server, by choosing Promote Read Replica from Instance Actions in the console, which would sever the connection between the replica and its old master, and allow direct write access to it, since it would now be the new master.

Improve Disaster Recovery:

We can operate a read replica in a region different from our master database region. In case of a regional disruption, we can promote the replica to be the new master and keep our business in operation.

Scale Out Globally:

If our application has a user base that is spread out all over the planet, we can use Cross Region Read Replicas to serve read queries from an AWS region that is close to the user.

When we create a read replica, we specify an existing DB Instance as the source. Amazon RDS takes a snapshot of the source instance and creates a read-only instance from the snapshot. For MySQL and PostgreSQL, Amazon RDS uses those engines' native asynchronous replication to update the read replica whenever there is a change to the source DB instance. The read replica operates as a DB instance that allows only read-only connections; applications can connect to a read replica just as they would to any DB instance. Amazon RDS replicates all databases in the source DB instance.(Amazon RDS Read Replicas)

Creating Cross-Region Read Replicas

We can create up to five in-region and cross-region replicas per source with a single API call or a couple of clicks in the AWS Management Console. (Cross-Region Read Replicas for Amazon RDS for MySQL)

The cross-region replicas are very easy to create. We simply select the desired region (and optional availability zone) in the AWS Management Console:

The destination is set to "Tokyo":

We can check if we have the DB in Japan:

Reference: Cross-Region Read Replicas for Amazon RDS for MySQL.

Snapshots for Postgres

Suppose we have Postgres DB, but the feature, Cross-Region Read Replicas, is not applicable to Postgres for copying it to another region. So, we want to take alternative approach which is to copy Amazon RDS (Relational Database Service) snapshots between AWS regions. In our case, from N. California to Japan.

Table source: Regions and Availability Zones.

We must create a DB snapshot before we can restore a DB instance from one.

We can initiate the copy from the AWS Management Console, the AWS Command Line Interface (CLI), or through the Amazon RDS APIs. Here's what we will see in the Console when we take a snapshot.

Then, go to RDS Dashboard:

Click "Copy Snapshot":

We can copy snapshots of any size, from any of the database engines (MySQL, Oracle, or SQL Server) that are supported by RDS. Copies can be moved between any of the public AWS regions, and we can copy the same snapshot to multiple Regions simultaneously by initiating more than one transfer.

As is the case with the other copy operations, the copy is done on an incremental basis, and only the data that has changed since the last snapshot of a given Database Instance will be copied. When we delete a snapshot, deletion is limited to the data that will not affect other snapshots.

As in the instruction, if we click "here", we get:

There is no charge for the copy operation itself; we pay only for the data transfer out of the source region and for the data storage in the destination region. We are not charged if the copy fails, but we are charged if we cancel a snapshot that is underway at the time.

Reference: Cross-Region Snapshot Copy for Amazon RDS.

Now, we need to restore the snapshot.

When we restore the DB instance, we provide the name of the DB snapshot to restore from, and then provide a name for the new DB instance that is created from the restore. We cannot restore from a DB snapshot to an existing DB instance; a new DB instance is created when we restore.

When we restore a DB instance, only the default DB parameter and security groups are associated with the restored instance. As soon as the restore is complete, we should associate the custom DB parameter or security group we used by the instance we restored from. We must apply these changes explicitly using the RDS console's Modify command, the ModifyDBInstance API, or the rds-modify-db-instance command line tool, once the DB instance is available.

In the navigation pane, click Snapshots, then click on the DB snapshot that we want to restore from.