AWS Database Blog

Upgrade Amazon RDS for MySQL or MariaDB database schemas with minimal downtime

Modifying the schema of an SQL database can be time-consuming, resource-intensive, and error-prone, and often requires long application maintenance windows that negatively affect the end-user experience. Amazon Relational Database Service (Amazon RDS) for MySQL or MariaDB allows you to upgrade your schema while your application is still running, with minimal or zero downtime. In this post, I walk you through the process of using an RDS for MySQL or MariaDB replica to perform a schema update on a database without negatively affecting the user experience.

The solution uses an Amazon RDS for MySQL version 8.0 database, but you can easily modify it to use other MySQL or MariaDB versions, as needed.

The cost of implementing the solution is equal to the cost of running additional RDS replicas for the time needed to perform the schema change. For more information, see Amazon RDS Pricing.

NOTE: This solution doesn’t necessarily eliminate downtime, but it can reduce it dramatically for very large databases, and for complicated schema changes. The procedure mentions when read-only mode for your application begins and ends. During this window, it’s important that your application deal with read-only databases gracefully, for example by telling customers that their data is read-only during the maintenance window. It’s also important that you streamline the testing phase to run as quickly as possible, without sacrificing test accuracy and coverage.

Get started

I strongly recommend you test this solution in a sandbox environment several times until you’re comfortable attempting it in a production environment.

This post explains how to implement this procedure using the AWS Command Line Interface (AWS CLI), which allows you to interact with the AWS control plane from a Linux, UNIX, Windows, or Macintosh shell, or the AWS CloudShell. You can also perform the steps via the AWS Management Console, if you prefer.

Create the required parameter groups

You need two parameter groups: one for the source, and one for the target. You can’t use the default parameter groups because the default parameter groups can’t be modified, and you need to make changes to the read_only parameter during this process. Run the following CLI commands.

aws rds create-db-parameter-group \
  --db-parameter-group-name source-parameter-group \
  --db-parameter-group-family mysql8.0 \
  --description 'Parameter group for source database'

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

aws rds create-db-parameter-group \
  --db-parameter-group-name target-parameter-group \
  --db-parameter-group-family mysql8.0 \
  --description 'Parameter group for target database'

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

After those commands are complete, the architecture looks like the following diagram.

Create the source database

NOTE: If you already have an RDS database to use as the source database, you can skip this step and use that database’s information instead. However, the source database must be attached to a custom parameter group that isn’t attached to any other databases. If your source database uses the default parameter group, or a parameter group that is shared with other databases, you must attach the parameter group you created in the previous step to the source database before you begin. Attaching a custom parameter group to a database requires a reboot, so your should do this during a normal RDS maintenance window to minimize impact on your application.

To create a suitable RDS database, use the following CLI command.

aws rds create-db-instance \
  --db-name source \
  --db-instance-identifier source-database \
  --db-parameter-group-name source-parameter-group \
  --allocated-storage 20 \
  --db-instance-class db.t3.medium \
  --engine mysql \
  --engine-version 8.0.21 \
  --master-username dba \
  --master-user-password dbaPassword1234 \
  --no-multi-az \
  --no-publicly-accessible

Then wait for the RDS database to be available.

aws rds wait db-instance-available \
  --db-instance-identifier source-database

When the RDS instance is available, obtain its endpoint name.

aws rds describe-db-instances \
  --db-instance-identifier source-database \
  --output text \
  --query 'DBInstances[0].Endpoint.Address'

Now you can connect to the RDS instance using its endpoint address.

mysql \
  --host source-database.RANDOM.REGION.rds.amazonaws.com \
  --user dba \
  --password

Now the architecture looks like the following diagram.

At this point, if you want to test this solution against your actual application schema, you should load it and the associated application data into the RDS database you created. Otherwise, continue with the following steps.

Create a test table and insert some test data.

create database test;

use test;

create table test (
  id int unsigned not null auto_increment primary key,
  first_hash char(64) not null
);

Now let’s insert a row of data into the schema:

insert into test (first_hash) values (sha2(rand(), 256));

To make things slightly more interesting, you can increase the amount of data in the table exponentially by running the following query a few times.

insert into test (first_hash) select sha2(rand(), 256) from test;
insert into test (first_hash) select sha2(rand(), 256) from test;
# some number of additional runs here
insert into test (first_hash) select sha2(rand(), 256) from test;
insert into test (first_hash) select sha2(rand(), 256) from test;

You can repeat the insert statement as many times as you like. Each time you run the query, you double the number of rows in the database. After 16 runs, you should have 2^16 or 32,768 rows. After 24 runs, you should have 2^24 or 16,777,216 rows. Any number of rows is enough for this experiment, but larger databases take longer to upgrade, and so this solution is more impactful for those. To find out how many rows you have in your table, you can run the following query.

select count(*) from test;

Once you’re done adding test data to the database, exit the MySQL client.

exit;

Create the target database

Create a replica database from the source.

aws rds create-db-instance-read-replica \
  --db-instance-identifier target-database \
  --source-db-instance-identifier source-database

Wait for the instance to become available.

aws rds wait db-instance-available \
  --db-instance-identifier target-database

Attach the database parameter group to the replica.

aws rds modify-db-instance \
  --db-instance-identifier target-database \
  --db-parameter-group-name target-parameter-group

Reboot the replica so that it picks up the new parameter group immediately.

aws rds reboot-db-instance \
  --db-instance-identifier target-database

Wait for it to become available again.

aws rds wait db-instance-available \
  --db-instance-identifier target-database

When the replica database is available, obtain its endpoint address.

aws rds describe-db-instances \
  --db-instance-identifier target-database \
  --output text \
  --query 'DBInstances[0].Endpoint.Address'

At this point, queries to the read replica will return the same data that queries to the primary return.

Once the target-database comes on-line, the architecture looks like the following diagram.

Upgrade the schema

Now that you have a writable replica up and running, you can modify the schema on it.

The SQL statements that you use to modify the schema will vary according to your application and upgrade path. The following examples are very simple, and can take anywhere from less than 1 second up to several minutes or even hours, depending on how many rows you inserted into the database during setup.

There are two possible paths.

  • The schema changes are fully backward-compatible
  • The schema changes have backward-incompatible changes

In this context, backward-compatible means that the changes won’t break replication. For example, adding a new column, increasing the size of an existing column, or adding or removing indexes, are all generally backward-compatible. Operations such as dropping columns, changing column names or types, reducing the size of an existing column, or adding new foreign keys, are potentially backward-incompatible. A qualified database administrator (DBA) can identify which of these cases applies to your upgrade.

Schema updates that include backward-incompatible changes will result in longer read-only periods during the schema change process. To the maximum extent possible, try to make your schema changes backward-compatible for this reason. If you must perform a backward-incompatible schema change, consider breaking it up into two separate upgrades: one that is backward-compatible, and one that is backward-incompatible, so that the read-only time period is minimized.

If you’re using your actual application schema and data, you need to replace the following SQL statements with those that actually upgrade your database.

Backward-compatible schema changes

If your schema changes are backward-compatible, follow these steps.

Connect to the target-database.

mysql \
  --host target-database.RANDOM.REGION.rds.amazonaws.com \
  --user dba \
  --password \
  test

Upgrade the schema.

alter table test add column second_hash char(64) not null;

update test set second_hash = sha2(concat(first_hash, rand()), 256);

exit;

When the schema change on the target-database is complete, make the source-database read-only by modifying its parameter group.

aws rds modify-db-parameter-group \
  --db-parameter-group-name source-parameter-group \
  --parameters 'ParameterName=read_only,ParameterValue=1,ApplyMethod=immediate'

This change takes a few minutes to complete. You can check to see if it has completed by running the following query on the source-database.

show global variables like 'read_only';

NOTE: From this point on until the end of the procedure, the application is in read-only mode.

Backward-incompatible schema changes

If your schema changes are backward-incompatible, follow these steps.

Stop all updates on the source-database by modifying its parameter group.

aws rds modify-db-parameter-group \
  --db-parameter-group-name source-parameter-group \
  --parameters 'ParameterName=read_only,ParameterValue=1,ApplyMethod=immediate'

This change takes a few minutes to complete. You can check to see if it has completed by running the following query on the source-database.

show global variables like 'read_only';

NOTE: From this point on until the end of the procedure, the application is in read-only mode.

Next, log into the target-database to upgrade the schema.

mysql \
  --host target-database.RANDOM.REGION.rds.amazonaws.com \
  --user dba \
  --password test

Upgrade the schema.

alter table test add column second_hash char(64) not null;

update test set second_hash = sha2(concat(first_hash, rand()), 256);

alter table test drop column first_hash;

Review the architecture

Now that you have modified the parameters for the source-database, the architecture looks like the following diagram.

At this point, your application is still accessing source-database in read-only mode, and you promote the target-database to a standalone instance so that you can perform testing and validation against it. Your application should be architected so that it returns a user-friendly message letting people know that it’s currently in read-only mode.

Promote the replica to a stand-alone database

Promote the replica to a stand-alone database.

aws rds promote-read-replica \
  --db-instance-identifier target-database

Wait for the instance to become available.

aws rds wait db-instance-available \
  --db-instance-identifier target-database

NOTE: This doesn’t reconfigure source-database to be a replica. This command splits the databases into two independent databases.

When the promotion is complete, the architecture looks like the following diagram.

Create additional read replicas, if needed

If your original database had read replicas, you need to add read replicas to the target-database at this point. For example, if you had three read replicas on the source-database, you should probably create three read replicas on target-database. If your schema change increases database efficiency, you might be able to create fewer replicas on the target-database. On the other hand, if your schema changes introduce new complexity that requires more processing power, you might need to create more than three replicas on the target-database.

How many replicas you require depends on the application. Your developers and DBAs should be able to determine how many replicas are required for the target-database. When in doubt, I recommend that you overprovision read replicas initially, and scale back when you’re confident in your application’s performance.

Test, test, test

Perform whatever testing is necessary against the target-database to validate that the schema has been successfully migrated. This step is application specific, and what tests are required and how long they take depend on the specifics of your workload.

If your tests fail, you can either repair the schema on the target-database manually, or terminate the target-database, modify your upgrade procedure, and start again. If you choose to terminate the target-database and revert back to the source-database, you must change the parameter group on the source-database to permit writes again.

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

Switch to the target database

After the new schema is validated and the necessary replicas have been created, reconfigure the application to use the target-database endpoint, and validate that the application is still performing correctly. If you’re using the Amazon RDS Proxy, make your changes in the proxy configuration instead of your application configuration. Using Amazon RDS Proxy may reduce connection errors that your application sees during the transition to the target-database.

Cleaning up

To remove all the resources you created during this walk-through, use the following commands.

aws rds delete-db-instance \
  --db-instance-identifier source-database \
  --final-db-snapshot-identifier source-database-final-snapshot

aws rds delete-db-instance \
  --db-instance-identifier target-database \
  --final-db-snapshot-identifier target-database-final-snapshot

aws rds delete-db-parameter-group \
  --db-parameter-group-name source-parameter-group

aws rds delete-db-parameter-group \
  --db-parameter-group-name target-parameter-group

Summary

With the solution outlined in this post, you can reduce or eliminate application down-time associated with database schema changes in Amazon RDS for MySQL and MariaDB. The steps described in this solution allow you to make database schema changes with reduced impact to your application, improving the end-user experience and reducing the burden of database schema changes.

You can use a similar process to upgrade RDS MySQL and MariaDB databases across different major versions, for example upgrading a MySQL 5.7 database to MySQL 8.0. For more information, see Best practices for using a MySQL read replica to upgrade an Amazon RDS for MySQL database. For more information about MySQL replication on Amazon RDS, see Working with MySQL replication in Amazon RDS.

About the author

Tim Gustafson is a Senior Database Specialist Solutions Architect working primarily with open-source database engines.