Learn how Presence migrated off a monolithic Amazon RDS for MySQL instance, with near-zero downtime, using replication filters

Presence is a leading provider of live therapy and evaluation services for PreK-12 schools throughout the United States. Founded in 2009, Presence has delivered over 6 million teletherapy sessions to students with diverse learning needs. As their services scaled to serve their network of thousands of providers and students, they increasingly relied on AWS.

Amazon Relational Database Service (Amazon RDS) for MySQL has been a core part of Presence’s data architecture for many years. Initially, Presence’s core platform services operated using a single large production RDS DB instance. This proved to be an efficient way to manage their growing applications. As both the number of teletherapy services offered and the volume of customers increased, they realized that the coordination of updates across all their product lines impacted their ability to stay agile with software releases and security patching. In addition, there were growing concerns that potential issues with one product update might negatively affect other products as well. To reduce these concerns, they decided it was time to separate the data for each product line into different RDS instances.

Presence used RDS read replicas, with replication filtering, to migrate applications from their centralized RDS for MySQL DB instance to dedicated DB instances. This approach allowed them to migrate each service, on its own schedule, with little downtime. In this post, we provide a practical example for migrating using the same method.

Amazon RDS read replicas are typically used to provide enhanced performance and durability for RDS DB instances. Presence used the read replica feature to create new DB instances for each service they wanted to migrate. As a read replica, the data stayed in sync with the production DB instance while Presence prepared it to be promoted to a standalone DB Instance. By using RDS replication filters, they limited the databases and tables to be synchronized to each read replica. This allowed them to be targeted with each service migration.

Solution overview

For the following walkthrough, we assume a fictitious RDS for MySQL instance, named monolith, that contains multiple databases for several applications as our starting point. We show how to migrate each database to a dedicated RDS instance. For simplicity, this walkthrough only shows the process for one application: the Employee Directory. The database for this application is called employees.

All examples provided in the following steps show how to perform the actions using the AWS Command Line Interface (AWS CLI) in a Linux Shell and the MySQL Shell. Refer to the RDS section of the AWS CLI Command Reference for more details on each command and the parameters used.

While the examples shared in this post show the CLI commands for RDS for MySQL, the same overall approach works to migrate an RDS for MySQL source databases to Amazon Aurora MySQL-Compatible Edition. In addition, these steps will work to migrate an Amazon RDS for MariaDB instance to another RDS MariaDB instance. When applicable, we explain the procedural differences for MariaDB and Aurora MySQL.

The walkthrough is organized into 3 main sections:

Create the read replica using replication filters.
Prepare the replica for production use.
Promote the replica to a standalone instance and migrate the application.

Create an RDS read replica

Complete the following steps to create an RDS read replica from the monolith RDS DB instance and configure it to only synchronize changes from the employees database:

Create an RDS DB parameter group. In this example, we use MySQL8.0 for the parameter group family to match the version of the MySQL engine running on the monolith server:

aws rds create-db-parameter-group \
    --db-parameter-group-name employees-param \
    --db-parameter-group-family mysql8.0 \
    --description "Parameter group for replication filtering on the employees DB"

Modify the parameter group and assign the value employees to the replicate-do-db. This parameter specifies which databases to replicate.

aws rds modify-db-parameter-group \
  --db-parameter-group-name employees-param \
  --parameters "ParameterName=replicate-do-db, ParameterValue='employees', ApplyMethod=immediate"

You can also configure replication filters to exclude databases, or tables, as well as use wildcards. See Replication filtering examples for RDS for MySQL for more examples.

Note
For MariaDB you will need to specify the appropriate db-parameter-group-family for the specific version you are running (e.g., mariadb10.11)

For Aurora, you instead create a cluster parameter group using the create-db-cluster-parameter-group command. You then modify it with the same parameters listed in step 2 using the modify-db-cluster-parameter-group command instead.

Create an RDS read replica with the monolith RDS DB instance as the source database. Name the read replica employees. By default, this replica will be an exact copy of the source database and will immediately start replicating any changes when it’s up and running. The db-instance-class parameter only needs to be specified if you want your replica instance to use a different instance class than the source.
```
aws rds create-db-instance-read-replica \
    --db-instance-identifier employees \
    --source-db-instance-identifier monolith\
    --db-instance-class db.t4g.medium \
    --allocated-storage 100 \
    --max-allocated-storage 200 \
    --upgrade-storage-config
```

The allocated-storage parameter needs to be the same size as, or greater than, the source database. The max-allocated-storage parameter sets the upper size limit when storage autoscaling is enabled.

Attach the parameter group you created to the newly created read replica. Doing so will limit the replication to just changes made to the employees database.
```
aws rds modify-db-instance \
    --db-instance-identifier employees \
    --db-parameter-group-name employees-param \
    --apply-immediately
```

Notice that the mysql system database will replicate even though you didn’t list it in the replicate-do-db parameter. This database stores information required by the MySQL server and replicates by default.

Reboot the instance to apply the new parameter group settings:

aws rds reboot-db-instance \
    --db-instance-identifier employees

At this point, any changes made to the employees database on the monolith instance are replicated to the newly created read replica. Changes made to any other database will not be replicated.

Note
For Aurora, you create a “Replica Cluster” that is attached to the source DB instance. You use the create-db-cluster command and specify the source DB instance ARN using the --replication-source-identifier option. The cluster parameter group you created earlier can be applied to the cluster at creation time using the --db-cluster-parameter-group-name. When attached during creation, there is no need to reboot for the settings to apply. Once the cluster is created, you will add a DB instance to the cluster using the create-db-instance command and specifying the cluster ID in the --db-cluster-identifier option

(Optional) Prepare the read replica for production use

Before promoting the read replica to production, you may want to modify it to prepare it for production use. Because the read replica was created from a snapshot of the monolith instance, it will contain a static copy of all the data from that snapshot. With only the employees database being synchronized, you can safely remove the data from the other applications.

Complete the following steps to prepare the read replica:

Change the read-only parameter in the employees-param parameter group to 0:

aws rds modify-db-parameter-group \
    --db-parameter-group-name employees-param \
    --parameters "ParameterName=read_only,ParameterValue='0',ApplyMethod=immediate"

Parameter groups, by default, set the read-only parameter to {TrueIfReplica}. Setting the value to 0 allows you to modify the data on a read replica. The read_only parameter is dynamic, so this change does not require a restart.

Note
For Aurora, you don’t need to change this setting as the read_only parameter defaults to 0 in a Cluster Parameter group

Log in to the MySQL shell of the replica and delete all the databases except the employees database and the system databases (mysql, information_schema, performance_schema, sys). This is not a required step, but doing so frees up storage for the Employee Directory application to use in the future.
```
mysql> DROP DATABASE unneeded_database;
```

Note
In RDS this frees up space for new growth, but it does not actually decrease the allocated storage. With Aurora, you have the added benefit of the storage volume shrinking if not used.

Change the read_only parameter back to the default value of {TrueIfReplica} to set the data back to read-only:

aws rds modify-db-parameter-group \
  --db-parameter-group-name employees-param \
  --parameters "ParameterName=read_only,ParameterValue='{TrueIfReplica}',ApplyMethod=immediate"

Promote the read replica to a standalone instance and migrate the application

With your changes complete, you can schedule your maintenance window for migrating the Employee Directory application to the new server. Any changes made to the database on the monolith instance will continue to synchronize to your new instance while you wait for that window to occur.

Complete the following steps to promote the read replica to a standalone instance and then re-configure the Employee Directory application to use the new instance.

Pause all writes to the database from your application. During your maintenance window, you start the migration by putting the Employee Directory application into maintenance mode. This will pause writes and prevent new data from being added to the source database.

Note
Since you are only migrating one schema from the source db, it is preferred to pause writes from the application. If this is not possible, you can consider placing the source db itself in read_only mode. However, doing so will impact any remaining applications that still rely on the source db.

Wait for the replication to complete. With writes paused, you can monitor the read replica instance to make sure replication has completed before moving to the next step. To do this, log into the MySQL shell of the replica instance and run the following command:
```
mysql> SHOW REPLICA STATUS\G
```

You know that the synchronization is complete when the metric called Seconds_Behind_Source reports 0.

Note
For MariaDB the metric is called Seconds_Behind_Master

Alternatively, you can monitor the replication status through the ReplicaLag metric in Amazon CloudWatch or the Amazon RDS console. By default, this metric is sent to CloudWatch from Amazon RDS.

Cloudwatch RDS Replica Lag

With the synchronization complete, and writes stopped to the source employees database, promote the read replica to a standalone RDS instance:
```
aws rds promote-read-replica \
    --db-instance-identifier employees
```

As part of the promotion process, the database instance will reboot.

Note
For Aurora you will use the promote-read-replica-db-cluster command to promote the cluster.

When the promotion is complete, the custom parameter group used for the read replica may no longer be appropriate for your production instance. For this scenario, change the parameter group back to the default MySQL 8 parameter group and reboot to apply the new settings:
```
aws rds modify-db-instance \
    --db-instance-identifier employees \
    --db-parameter-group-name default.mysql8.0 \
    --apply-immediately
```

Note
For MariaDB, specify a default db-parameter-group-name specific to the version (e.g., default.mariadb10.11)

For Aurora, you use the modify-db-cluster command to change the cluster parameter group. Use default.aurora-mysql8.0 for the --db-cluster-parameter-group-name option.

For the new parameter group to take effect, reboot the instance:

aws rds reboot-db-instance \
    --db-instance-identifier employees

When the reboot is complete, change the configuration of the Employee Directory application to use the endpoint of your new instance. You can find this information in the Endpoint section returned by this command:
```
aws rds describe-db-instances \
    --db-instance-identifier employees | jq -r '.DBInstances[0].Endpoint.Address'
```

Note
For Aurora you use:

aws rds describe-db-clusters --db-cluster-identifier employees-cluster | jq -r '.DBClusters[0].Endpoint'

You can now take the application out of maintenance mode and resume writes to your application.

You have now completed your migration.

Conclusion

By using RDS read replicas and replication filtering, Presence successfully migrated their applications from a monolithic DB instance to dedicated RDS instances. This approach provided a low-risk method to create a copy of the required data in near real time, while still allowing the existing application to run during the migration process.

Splitting the databases onto separate RDS instances has given Presence more flexibility and agility when managing their applications. They can now make updates, patches, and scaling adjustments specific to a single service without impacting other product offerings.

In this post, we showed you how to follow this same pattern using a fictitious Employee Directory application. Try out the solution yourself and share your feedback and questions in the comments.

About the Author

With over 20 years of IT experience, Daniel has held many architecture and director positions supporting a wide variety of technologies. He currently works as an AWS Solutions Architect supporting Education Technology companies striving to make a difference for learners and educators worldwide. Daniel’s interests outside of work include music, family, health, education and anything that allows him to express himself creatively.

AWS Database Blog