AWS RDS migration guide

February 16, 2024

Overview

This document will demonstrate how to migrate a database from AWS Relational Database Services (RDS) to PlanetScale. We’ll also address some common issues associated with migrating from RDS and how to resolve them.

We recommend reading through the Database import documentation to learn how our import tool works before proceeding.

Prerequisites

Before you can perform a migration, you’ll need to make sure the following is configured:

• Your database’s GTID and binlog_format settings are properly configured.
• Your database’s binlog retention settings are properly configured.

You’ll also need to gather the following pieces of information from the AWS Console:

• Endpoint address
• Port number (E.g., 3306)
• Master username and password or the ability to create a new user in the database

The endpoint address and port number are located in the database view under “Connectivity & security”.

The master username can be located under the “Configuration” tab. The master password will be the password used when the database was first created.

Configuring the RDS security group

In order for PlanetScale to connect to your RDS database, you must allow traffic into the database through the associated security group. The specific IP addresses you will need to allow depend on the region you plan to host your PlanetScale database. Check the Import tool public IP addresses page to determine the IP addresses to allow before continuing. This guide will use the AWS us-east-1 (North Virginia) region so we’ll allow the following addresses:

3.209.149.66
3.215.97.46
34.193.111.15
18.117.23.127
3.131.243.164
3.132.168.252
3.131.252.213
3.132.182.173
3.15.49.114

To allow traffic to your database in AWS, navigate to the “Connectivity & security" tab of the database, and click the link under VPC security groups.

In the lower pane of the next page, select the “Inbound rules” tab, then “Edit inbound rules”.

Click the “Add rule” button, and a new line will be added to the list of rules. Select “MYSQL/Aurora” as the Type and enter the first IP address from the list for your region. AWS will automatically reformat it to x.x.x.x/32, so you can select that when it appears. You’ll need to repeat this for every IP address you need to allow.

Once you are finished adding all of the necessary IP addresses for the selected region, click “Save rules” to apply the changes.

Importing your database

In the PlanetScale dashboard, click “New database”, then “Import database”.

Complete the form using the information gathered in the previous section. Click “Connect to database” and the import tool will attempt to connect to your RDS instance.

The “Connect to database” button will update with the connection status.

If the connection is successful, click “Begin database import” to migrate your data to PlanetScale.

The following view will show you the progress of your data being imported.

Once your database has finished importing, the view will update to show the database that was created in PlanetScale, as well as the option to enable primary mode. This button will make the PlanetScale version of the database the primary replica. Clicking “Enable primary mode” will display a modal where you can confirm that you want to make this change.

After primary mode is enabled, the third card in the flow will update to give you two options:

• Enable replica mode — Reverts the change performed in the previous step, making RDS your primary once again.
• Finish import — Detaches the databases from replication. Future changes to either the PlanetScale database or the RDS database will not be replicated to each other.

Click “Finish import” to complete the import process. Confirm in the modal that will be displayed.

This concludes the process used to import a database into PlanetScale from AWS RDS.

Troubleshooting

Enable binary logging

If binary logging is disabled on your RDS server, you'll receive an error like this from the importer: "External database settings are not compatible with PlanetScale: log_bin must be ON, but found: OFF".

On RDS MySQL servers, binary logging can't be directly enabled by changing a parameter. Instead, to set log_bin to on, enable automated backups by setting their retention period to any value greater than zero days.

Once that change has been applied, you can verify that log_bin is on in a MySQL session:

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+

Configure GTID and binlog settings

Navigate to your RDS instance and check the parameter group in the “Configuration” tab. If you are using the default, you’ll need to create a new parameter group to reconfigure the GTID settings.

To create a parameter group, select “Parameter groups” from the left nav and then “Create parameter group”.

Specify the Parameter group family, Type, Group name, and Description. All fields are required.

When you save, you’ll be brought back to the list of available parameter groups. Select your new parameter group from the list to modify the settings.

Click “Edit parameters” to unlock editing.

Search for “binlog_format” in the search box. Update that setting to match:

• binlog_format: ROW

Search for “gtid” in the search box, and update the following settings to match:

• gtid-mode: ON

• enforce_gtid_consistency: ON

Click “Save changes” once you are done.

Now you need to associate the parameter group to the database. Select “Databases” from the left nav, check the select box next to your database, and click “Modify” from the toolbar.

Scroll about halfway down the page and find the section titled Additional configuration. Update the DB parameter group to match the new group created earlier in this section. Scroll to the bottom and click “Continue”.

You’ll be presented with options to apply the changes at the next maintenance window or immediately.

• If you select Apply during the next scheduled maintenance window, the updated configuration group will be applied during that window.

• If you select Apply immediately, the group will be applied immediately but you must manually reboot the database for the changes to take effect.

Once you’ve selected your desired option, click “Modify DB instance” to apply the changes.

If you opted to apply the changes immediately, you can monitor the status on the “Configuration” tab of the database view. The page does not automatically update, so you’ll need to refresh the page to check the status when it updates.

To reboot the database, click the “Modify” button near the top of the screen, then “Reboot”.

You’ll be presented with a page to confirm the database you want to reboot. Click "Confirm” and the database will begin rebooting.

You can view the status of the database in the primary list of databases for this account/region. This view does not refresh automatically, click the “Refresh” button to update the view.

Configure binlog retention hours

You may need to specify the number of hours to retain binary logs in your AWS RDS database.

By default, this value is NULL, which means that binary logs are purged immediately.

You need to specify a period with enough time for replication to occur. For most cases, 48 hours should be sufficient, but you may need to set it higher for large imports.

To specify the number of hours for Amazon RDS to retain binary logs on a DB instance, use the mysql.rds_set_configuration() stored procedure as shown in the following example:

call mysql.rds_set_configuration('binlog retention hours', 48);

Once you call this function, confirm that the value is set correctly by calling this function:

CALL mysql.rds_show_configuration;

+------------------------+-------+-----------------------------------------------------------------------------------------------------------+
| name                   | value | description                                                                                               |
+------------------------+-------+-----------------------------------------------------------------------------------------------------------+
| binlog retention hours | 48    | binlog retention hours specifies the duration in hours before binary logs are automatically deleted.      |
+------------------------+-------+-----------------------------------------------------------------------------------------------------------+

Need help?

Get help from the PlanetScale Support team, or join our GitHub discussion board to see how others are using PlanetScale.